Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Returning Sorted Values
I have the list of names and abriviations on a sheet called info
A B C 1 Copper pdx CC 2 Edge pdx EW 3 Max pdx MX 4 North Albany cor NA 5 North Pointe cor NP 6 Stoney cor SB 7 Taro pdx TL 8 Trillium pdx TW 9 Victoria pdx VG 10 Village pdx VO 11 Villean pdx VI 12 Walnut pdx WC 13 Willmont cor WL 14 Witham cor WO In another sheet I would like to have two rows that return in order the values in column "C", each in a seperate cell. The top row would only return the items that match "pdx" in column "B", and the bottom row would only return the items that match "cor". What function, or group of functions, do I use to get this result? The column of names must be sorted alphabetically to work correctly with other formulas in the sheet. Essentially I want cell A1 to contain the first value from column "c" that matches pdx, B1 to return the second .... A B C D E F G H I 1 CC EW MX TL TW VG VO VI WC 2 NA NP SB WL WO Thank you for any help you can give. Kevin |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Returning Sorted Values
Put this formula in column D of your info sheet:
=B1&"_"&COUNTIF(B$1:B1,B1) and then copy this down - it will give you a sequential reference number for each value in column B. Then in your summary sheet you can put this formula in A1: =INDEX(Info!$C:$C,MATCH("pdx_"&COLUMN(A1),Info!$D: $D,0)) and this one in A2: =INDEX(Info!$C:$C,MATCH("cor_"&COLUMN(A1),Info!$D: $D,0)) and then you can copy these across until you get #N/A errors. If you want to avoid these then you can use: A1: =IF(ISNA(MATCH("pdx_"&COLUMN(A1),Info!$D:$D,0)),"" ,INDEX(Info!$C: $C,MATCH("pdx_"&COLUMN(A1),Info!$D:$D,0))) and similar for A2 but changing "pdx" to "cor". Then copy them across as far as you wish. Hope this helps. Pete On Jan 8, 10:12*pm, Kevin Mulvaney wrote: I have the list of names and abriviations on a sheet called info * * * A * * * * * * * * * * * B * * * * C 1 * * Copper * * * * * * *pdx * * *CC 2 * * Edge * * * * * * * * pdx * * *EW 3 * * Max * * * * * * * * *pdx * * *MX 4 * * North Albany * * *cor * * *NA 5 * * North Pointe * * *cor * * * NP 6 * * Stoney * * * * * * *cor * * *SB 7 * * Taro * * * * * * * * pdx * * *TL 8 * * Trillium * * * * * * pdx * * *TW 9 * * Victoria * * * * * * pdx * * *VG 10 * Village * * * * * * *pdx * * *VO 11 * Villean * * * * * * *pdx * * * VI 12 * Walnut * * * * * * pdx * * * WC 13 * Willmont * * * * * cor * * * WL 14 * Witham * * * * * *cor * * * WO In another sheet I would like to have two rows that return in order the values in column "C", each in a seperate cell. The top row would only return the items that match "pdx" in column "B", and the bottom row would only return the items that match "cor". What function, or group of functions, do I use to get this result? The column of names must be sorted alphabetically to work correctly with other formulas in the sheet. Essentially I want cell A1 to contain the first value from column "c" that matches pdx, B1 to return the second .... * * * A * *B * C * D * E * F * *G * H * I 1 * CC EW MX TL TW VG VO VI WC 2 * NA NP *SB *WL WO Thank you for any help you can give. Kevin |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Returning Sorted Values
Thanks that works perfectly!!!
Kevin "Pete_UK" wrote: Put this formula in column D of your info sheet: =B1&"_"&COUNTIF(B$1:B1,B1) and then copy this down - it will give you a sequential reference number for each value in column B. Then in your summary sheet you can put this formula in A1: =INDEX(Info!$C:$C,MATCH("pdx_"&COLUMN(A1),Info!$D: $D,0)) and this one in A2: =INDEX(Info!$C:$C,MATCH("cor_"&COLUMN(A1),Info!$D: $D,0)) and then you can copy these across until you get #N/A errors. If you want to avoid these then you can use: A1: =IF(ISNA(MATCH("pdx_"&COLUMN(A1),Info!$D:$D,0)),"" ,INDEX(Info!$C: $C,MATCH("pdx_"&COLUMN(A1),Info!$D:$D,0))) and similar for A2 but changing "pdx" to "cor". Then copy them across as far as you wish. Hope this helps. Pete |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Returning Sorted Values
You're welcome, Kevin - thanks for feeding back.
Pete On Jan 9, 4:27*pm, Kevin Mulvaney wrote: Thanks that works perfectly!!! Kevin "Pete_UK" wrote: Put this formula in column D of your info sheet: =B1&"_"&COUNTIF(B$1:B1,B1) and then copy this down - it will give you a sequential reference number for each value in column B. Then in your summary sheet you can put this formula in A1: =INDEX(Info!$C:$C,MATCH("pdx_"&COLUMN(A1),Info!$D: $D,0)) and this one in A2: =INDEX(Info!$C:$C,MATCH("cor_"&COLUMN(A1),Info!$D: $D,0)) and then you can copy these across until you get #N/A errors. If you want to avoid these then you can use: A1: * =IF(ISNA(MATCH("pdx_"&COLUMN(A1),Info!$D:$D,0)),"" ,INDEX(Info!$C: $C,MATCH("pdx_"&COLUMN(A1),Info!$D:$D,0))) and similar for A2 but changing "pdx" to "cor". Then copy them across as far as you wish. Hope this helps. Pete- Hide quoted text - - Show quoted text - |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Pivot Table Field Values not sorted | Excel Discussion (Misc queries) | |||
Returning Values | Excel Discussion (Misc queries) | |||
Returning Values | Excel Discussion (Misc queries) | |||
Returning a sum when looking up two different values | Excel Worksheet Functions | |||
Returning all values | Excel Discussion (Misc queries) |