BradF
12/13/2006 10:02:00 PM
On Dec 13, 11:11 am, "BradF" <rpn...@gmail.com> wrote:
> Hi Bob,
> This is great and it works. It displays thepinname at the
> intersection of the X and Y
> however I want to do the other way around, given apinname, I want to
> know its corresponding X and Y headers such that given IO1, excel
> function will give me A and 1,
> given IO2, it will give me B1 and so on. Thanks for the help.
>
> Brad
>
> Bob Phillips wrote:
> > =INDEX($A$1:$F$15,MATCH("A",$A$1:$A$15,0),MATCH(1,$A$1:$F$1,0))
>
> > For the duplicates, select a range of cells, say M1:M15, and in the formula
> > bar add
>
> > =IF(ISERROR(SMALL(IF(COUNTIF(B$1:B$15,B$1:B$15)>1,ROW($A1:$A15),""),ROW($A1:$A15))),"",
> >INDEX($A$1:$A$15,SMALL(IF(COUNTIF(B$1:B$15,B$1:B$15)>1,ROW($A1:$A15),""),ROW($A1:$A15))))
>
> > and commit this with Ctrl-Shift-Enter, not just enter.
>
> > It will show the values in the first column for duplicates in the second.
> > Copy acroos to the next column to see third column duplicates.
>
> > --
> > ---
> > HTH
>
> > Bob
>
> > (change the xxxx to gmail if mailing direct)
>
> > "BradF" <rpn...@gmail.com> wrote in message
> >news:1165897188.944331.249780@j72g2000cwa.googlegroups.com...
> > > Hi Friends,
>
> > > I have the following table ofpinnamesas shown below. 1,2,3,4,5 are
> > > the header rows and A,B,C,D... are header columns.
>
> > > X/Y 1 2 3 4 5
> > > A IO1 IO5 IO9 IO23 IO37 --->Pinnames
> > > B IO2 IO6 IO10 IO24 IO38
> > > C IO3 IO7 IO11 IO25 IO39
> > > D IO4 IO8 IO12 IO26 IO40
> > > E IO5 IO9 IO13 IO27 IO41
> > > F IO6 IO10 IO14 IO28 IO42
> > > G IO7 IO11 IO15 IO29 IO43
> > > H IO8 IO12 IO16 IO30 IO44
> > > I IO9 IO13 IO17 IO31 IO45
> > > J VDD VSS IO18 IO32 IO46
> > > K VDD VSS IO19 IO33 IO47
> > > L VDD VSS IO20 IO34 IO48
> > > M IO13 IO17 IO21 IO35 IO49
> > > N IO14 IO18 IO22 IO36 IO50
>
> > > How do I useINDEXandMATCHto lookup the contents of the table and
> > > display
> > > its corresponding header rows and columns in a separate sheet. I will
> > > use the pinname (cell content) as the lookup item as shown by the
> > > example below:
>
> > >PIN X-Y
> > > IO1 A,1
> > > IO2 B,1
> > > IO3 C,1
> > > IO4 D,1
> > > . .
> > > . .
> > > IO50 N,5
>
> > > and also, how do i display the cell headers of cell contents which
> > > appear more the once such VDD in J1,K1 and L1 and VSS in J2, K2 and L2?
> > > How do I make it display VDD = J1,K1,L1
>
> > > Thanks in advance,
> > > Brad