Skip Duplicates

[zit1343]

I got in Column A

A0
A0
A0
A0
B2
B2
B2
B3
B3
B4
B5
B5
B6
B7
B7
C0
C0
C0
C1
C2
C2
...
...
...

I need write formula in Column C1 which will SKIP duplicates
A0
B2
B3
B4
B5
B6
B7
C0
C1
C2

Plz help Thanks

[NBVC]

You can use Data|Filter|Advanced Filter to get a unique list from the original.

Original should have a column header

Else with a formula.

Starting in row 2, i.e. C2 try:

=LOOKUP(REPT("z",255),CHOOSE({1,2},"",INDEX($A$2:$A$200,MATCH(0, INDEX(COUNTIF(C$1:C1,$A$2:$A$200),0),0))))

Where A2:A200 contains original list.. copy formula down the column

[zit1343]

I cannot just filter them out of the sheet because I need to add the sum of the them
like
A0 2555
A0 2241
A0 3432
B2 2135
B2 2576

A0 = SUM OF ALL A0
B2 = SUM OF ALL B2

I just need to column which will list A0 B2.B3,B4....will ignore and skip repeats
I already know how to get sum of them

[NBVC]

You can just "filter" the column of interest, then use the SUMIF formula to sum....

I showed a formula solution also above to get the unique list.

[zit1343]

Oh man thanks alot, I was going nuts trying to figure this out... you saved my so much time
plz help me understand how did you do it
if you have time

[NBVC]

This part:

INDEX($A$2:$A$200,MATCH(0, INDEX(COUNTIF(C$1:C1,$A$2:$A$200),0),0)))

is the main workhorse.

basically it works like a regular Index/Match formula where a Match is found a position is returned, then indexed against the Index array.

the Match part looks for a 0 in this lookup array:

INDEX(COUNTIF(C$1:C1,$A$2:$A$200),0),0)

the lookup array is counting how many of the values in the cells above your active cell are in the original array, A2:A200. The result is an array of 0's and 1's... 0 where no match is found and 1 where a match is found... the first 0 in the array is what Match returns the position of and this is essentially lined up with the next term in the original array that hasn't yet been listed in the cells above. The INDEX() that wraps this COUNTIF function is used to create an array of results since this is an array formula and is an alternative to using CTRL+SHIFT+ENTER to activate the array formula.

This part: LOOKUP(REPT("z",255),CHOOSE({1,2},"" is a technique used to error trap and return an alternate error to the typical error, that is it will return a null instead of #N/A or other error. This one is for text strings. If you were working with numerics, then the error trap would be: LOOKUP(9.999999E+307,CHOOSE({1,2},""

Lookup looks for the last entry in an array that is smaller than or equal to the lookup value.... in these case a "z" repeated 255 times or a very large number. Since, in any array there will likely be no number or text string larger, then the last entry is what is returned as the last value that is smaller than the lookup value. So the Index() part will either return a value if a match is found or #N/A if not found. The CHOOSE() function creates a small list of values to choose from.. a null and either the match result or #N/A. LOOKUP then looks for the last of these two items that is smaller than 255 z's. Lookup also ignores erros.. so the answer will be null or the matching item (if it exists) and error will never be returned.

It's hard to explain it all without you have some experience.. so I hope you understood.

The formula Auditor found in the Tools menu can help you step through.