more excel help requested: condense column into unique discrete cells

[prophetik music]

have a multi-worksheet file that i'm working with that includes several report worksheets and one data worksheet. I need to be able to copy-paste a large amount of exported data into the data worksheet and have it auto-populate onto the other worksheets. because of this, i need a formula that can be put solely on the destination sheets and not on the data sheet. i cannot use a macro as they're restricted on my client's machine. it must be a formula.

column B in the data worksheet is a list of names with multiple duplicates and blanks. i can remove the blanks and alphabetize if required pre-entry. here's an example:

Last Name, First Name:

WXXXXXX, CHRISTOPHER

WXXXXXX, CHRISTOPHER

WXXXXXX, CHRISTOPHER

WXXXXXX, CHRISTOPHER

WXXXXXX, CHRISTOPHER

WXXXXXX, CHRISTOPHER

WYYYYYY, REBECCA

WYYYYYY, REBECCA

TXXXXXX, YI-WEN

i need this list to pull as follows:

WXXXXXX, CHRISTOPHER

WYYYYYY, REBECCA

TXXXXXX, YI-WEN

etc. the list will continue to grow as things get more complex. i think a pivot table may work, but i'd have to build seperate tables per month. a formula would be much easier.

i've tried using some match-based formulas i've found on the web, but they simply don't pull correctly cross-worksheet. assume that column B on the data worksheet is the names, starting at B3 and continuing indefinitely. also please assume that the output starts at B11 and continues for thirty lines.

is this possible? i think i spent two hours this morning trying to beat square formulas into my round requirement hole, but i just can't seem to get them to work.

[Tyler Gill]

Can you store some calculation data in some other columns either in the source or the destination worksheet? If so, this is one way I figured out (I tried also doing it all in a single function, as this site: http://www.exceltip.com/st/Retrieving_Unique_Values_From_A_List/805.html says, but I couldn't get that to work for me.)

Anyway, this method is based on adding an extra column to the original list that simple keeps a counter of the 'id' of each value found, and then using a VLOOKUP on those ids to build the unique list. I would recommend doing these calculations on some other worksheet that just copies in the original list to column B, so that column A can have the ids. Then, the VLOOKUPs can be in column D and E, and the final copy of the list can just directly copy those cells over.

Here's how it works using that layout:

Col A

0

=IF(B2=B1,A1,A1+1)

=IF(B3=B2,A2,A2+1)

...

Col B

Original list (copied in from source)

Col D

0

1

2

3

...

Col E

=IF(ISNA(VLOOKUP(D1,A$1:B$9,2,FALSE)),"",VLOOKUP(D1,A$1:B$9,2,FALSE))

=IF(ISNA(VLOOKUP(D2,A$1:B$9,2,FALSE)),"",VLOOKUP(D2,A$1:B$9,2,FALSE))

...

Column E can then just be grabbed by whatever needs the unique list. Naturally, you'll have to expand the lengths of the VLOOKUPs past 9 to however long the list is, but as long as the input is sorted and without duplicates, this should work.

Here's what it looks like:

Col A	Col B			Col C	Col D	Col E
0	WXXXXXX, CHRISTOPHER		0	WXXXXXX, CHRISTOPHER
0	WXXXXXX, CHRISTOPHER		1	WYYYYYY, REBECCA
0	WXXXXXX, CHRISTOPHER		2	TXXXXXX, YI-WEN
0	WXXXXXX, CHRISTOPHER		3	
0	WXXXXXX, CHRISTOPHER		4	
0	WXXXXXX, CHRISTOPHER			
1	WYYYYYY, REBECCA			
1	WYYYYYY, REBECCA			
2	TXXXXXX, YI-WEN			

[prophetik music]

i'll test this out. i can definitely have a few spare columns in the test sheet, so hopefully this works.

thanks man =)

[prophetik music]

YOU ARE A GOD AMONG INSECTS asdf

[Nonamer]

Subbed. I need to know this in the future!

[Tyler Gill]

Glad to be able to help