Returning Sorted Values

[Kevin Mulvaney]

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

 

[Pete_UK]

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

 

[Kevin Mulvaney]

Thanks that works perfectly!!!

Kevin

 

[Pete_UK]

You're welcome, Kevin - thanks for feeding back.

Pete