lookup-type functionality
Posted by Robin on May 11, 2001 10:32 AM
I am trying to do the following: I am working from a large worksheet in the following format:
A B C
LABEL MIN MAX
abcd 45.2 98.2
abcd 40.6 74.2
abcd 55.0 101.4
efg 2.35 5.4
efg 3.8 4.9
efg 1.6 3.96
I need to produce a summary list of unique labels (column A) and show the min. value from column b and the max value from column C.
For example,
LABEL MIN MAX
abcd 40.6 101.1
efg 1.6 5.4
Any suggestions would be greatly appreciated.
Thanks
Posted by Barrie Davidson on May 11, 2001 11:09 AM
Hi Robin, have you tried a pivot table? If you need any help on creating a pivot table let me know.
Barrie
Posted by Aladin Akyurek on May 11, 2001 12:03 PM
Robin,
I'm sure you'll get other type solutions which will be anycase less complicated. Here is mine though: a system of formulas that does what you want.
I'll assume that your example data
{"LABEL","MIN","MAX";"abcd",45.2,98.2;"abcd",40.6,74.2;"abcd",55,101.4;"efg",2.35,5.4;"efg",3.8,4.9;"efg",1.6,3.96}
occupy A1:C7.
Enter the following labels in E from E2 on:
{"Count";"LabelList";"MinList";"MaxList";"LocList";"SortList";"NoDupsList"}
In F2 enter: =COUNTA(A:A)
In F3 enter: =ADDRESS(ROW(A2),COLUMN(A2))&":"&ADDRESS(F2,COLUMN(A2))
In F4 enter: =ADDRESS(ROW(B2),COLUMN(B2))&":"&ADDRESS(F2,COLUMN(B2))
In F5 enter: =ADDRESS(ROW(C2),COLUMN(C2))&":"&ADDRESS(F2,COLUMN(C2))
In F6 enter: =ADDRESS(ROW(G2),COLUMN(G2))&":"&ADDRESS(F2,COLUMN(G2))
In F7 enter: =ADDRESS(ROW(H2),COLUMN(H2))&":"&ADDRESS(F2,COLUMN(H2))
In F8 enter: =ADDRESS(ROW(I2),COLUMN(I2))&":"&ADDRESS(F2,COLUMN(I2))
In G2 array-enter: =IF(ROW()-1<=$F$2,SUM(IF(A2>INDIRECT($F$3),1),1),"")
Copy down this array-formula as far as needed.
In H2 enter: =IF(ROW()-1<=$F$2,IF(ISNUMBER(MATCH(ROW()-ROW($G$2)+1,INDIRECT($F$6),0)),INDEX(INDIRECT($F$3),MATCH(ROW()-ROW($G$2)+1,INDIRECT($F$6),0)),0),"")
Copy down this formula as far as needed.
In E2 array-enter: =IF(ROW()-ROW(INDIRECT($F$8))+1>ROWS(INDIRECT($F$7))-COUNTIF(INDIRECT($F$7),0),"",INDIRECT(ADDRESS(SMALL((IF(INDIRECT($F$7)<>0,ROW(INDIRECT($F$7)),ROW()+ROWS(INDIRECT($F$7)))),ROW()-ROW(INDIRECT($F$8))+1),COLUMN(INDIRECT($F$7)))))
Copy down this array-formula as far as needed.
In J2 array-enter: =IF(LEN(I2)>0,MIN(IF(INDIRECT($F$3)=I2,INDIRECT($F$4))),"")
Copy down this array-formula as far as needed.
In K2 array-enter: =IF(LEN(I2)>0,MAX(IF(INDIRECT($F$3)=I2,INDIRECT($F$5))),"")
Copy down this array-formula as far as needed.
Note. To array-enter a formula, you need to hit CONTROL+SHIFT+ENTER at the same time, not just ENTER.
Here is how the sheet looks like from E1 to H8:
{0,0,0,0;"Count",7,1,"abcd";"LabelList","$A$2:$A$7",1,0;"MinList","$B$2:$B$7",1,0;"MaxList","$C$2:$C$7",4,"efg";"LocList","$G$2:$G$7",4,0;"SortList","$H$2:$H$7",4,0;"NoDupsList","$I$2:$I$7",1,0}
Here the results section from I to K:
{"LABEL","MIN","MAX";"abcd",40.6,101.4;"efg",1.6,5.4}
Aladin
