finding unique records

[HGood]

I have a column (B) that can be anywhere from 1 to several hundred rows. In
this column can be listed any three digit Project #, up to five unique
Project numbers in this column. Some may be listed many times. There may be
less than 5 unique records, but no more.

Then in a separate summary table to the right I have expenses summed in 5
columns (J,K,L,M,N), each of the five columns corresponding to the unique
records in B. If there are only 3 unique Project numbers among the 100 or so
records in Col B, then only J,K,L will show summary figures.

I'd like the header cells for columns J-N to fill in automatically with the
unique records from Col B, whether there are 1 or 3 or 4 or 5 unique
records.

Assuming there are only 10 entries in Col B, and 4 are unique it should look
like this:

B J K L M N
051 051 062 810 123
062
810
123
810
051
051
051
062
810

Thanks for any ideas you may have for formulas to put in the headers cells
for J-N to do this automatically.

Harold

 

[Frank Kabel]

Hi
use the following formulas (assuming your data is in B3:B100):
J2:
=B3

K2: Enter the array formula (committed with CTRL+SHIFT+ENTER):
=IF(ISERROR(INDEX(IF(ISBLANK($B$3:$B$100),"",$B$3:$B$100),MATCH(0,
COUNTIF($J$2:J$2,$B$3:$B$100&""),0))),"",INDEX(IF(ISBLANK($B$3:$B$100),"",$B$3:$B$100),MATCH(0,
COUNTIF($J$2:J$2,$B$3:$B$100&""),0)))

and copy this to the right

 

[Alan Beban]

I have a column (B) that can be anywhere from 1 to several hundred rows. In
this column can be listed any three digit Project #, up to five unique
Project numbers in this column. Some may be listed many times. There may be
less than 5 unique records, but no more.

Then in a separate summary table to the right I have expenses summed in 5
columns (J,K,L,M,N), each of the five columns corresponding to the unique
records in B. If there are only 3 unique Project numbers among the 100 or so
records in Col B, then only J,K,L will show summary figures.

I'd like the header cells for columns J-N to fill in automatically with the
unique records from Col B, whether there are 1 or 3 or 4 or 5 unique
records.

Assuming there are only 10 entries in Col B, and 4 are unique it should look
like this:

B J K L M N
051 051 062 810 123
062
810
123
810
051
051
051
062
810

Thanks for any ideas you may have for formulas to put in the headers cells
for J-N to do this automatically.

Harold

For those to whose workbook the functions in the freely downloadable
file at http://home.pacbell.net/beban are available

=ArrayUniques(B2:B11,,"0horiz") array entered into J1:N1

Alan Beban

 

[HGood]

Well it's the most complicated formula I've ever seen but it does work! Wow.
Thanks for this Frank, it's a great Christmas present. You've helped me with
a number of my questions over the past few months.

Thanks,

Harold
==========