Consolidating duplicates

[jsiason]

Hello,

whats the function/formula to copy to column D the values in column C, and when duplicates are encountered, only 1 of each type of duplicates will turn out in D, such that everything in C is in D, but list D contains unique values.

was that confusing?
thanks!

 

[Norie]

Why not try Data>Advanced filter...?

 

[Aladin Akyurek]

Hello,

whats the function/formula to copy to column D the values in column C, and when duplicates are encountered, only 1 of each type of duplicates will turn out in D, such that everything in C is in D, but list D contains unique values.

was that confusing?
thanks!

Book5
	C	D	E	F
1		0	4
2	Item	Idx	D-List
3	Vda	1	Vda
4	Fda	2	Fda
5	Fra	3	Fra
6	Vda		Tfa
7	Fda
8	Tfa	4
9
Sheet1

D1 must house a 0.

D3, copied down:

=IF(C3<>"",IF(ISNA(MATCH(C3,$C$2:C2,0)),LOOKUP(9.99999999999999E+307,$D$1:D2)+1,""),"")

E1:

=LOOKUP(9.99999999999999E+307,D1:D8)

E3, copy down:

=IF(ROW()-ROW($E$3)+1<=$E$1,LOOKUP(ROW()-ROW($E$3)+1,$D$3:$D$8,$C$3:$C$8),"")

 

[jsiason]

if i use advanced filter, what shoulde be my list and criteria range (C has 500 rows).

sorry, newbie here...

 

[Norie]

You don't need a criteria range if you just want the unique values.

The list range should just be however many entries are in C.

 

[jsiason]

thank you for that!

how about if column C has corresponding values in A and i'd like to capture this value in column E?
assuming
column A=age
column C=name

all the unique values that appeared in C must have corresponding "A" value in column D.