List duplicates in separate column

Hi gillmacca,

There are a few ways to do this problem. It is easier if you supply a sample workbook so we can see the data. See the attached for what I think you want?

There are 3 methods shown.
1. Advanced Filters with a Unique box checked
2. Pivot Tables with a filter of 2 filtered
3. Conditional Formatting - not sure you can see this with 2003 Excel

1 and 2 above use the Helper Column Formula.

Hi,

My example shows different ways to solve your problem. I've used Column A instead of Column B for my numbers. When you get to your computer open the workbook and find some different methods. They all center around what I did in Col B and a CountIf function.

If your data is in B2:B11, in C2, copied down, use this...
=IF(COUNTIF($B$2:B2,B2)>1,MAX($C$1:C1)+1,MAX($C$1:C1))

Then in D2, copied down, use this...
=IFERROR(INDEX($B$2:$B$11,MATCH(ROW(A1),$C$2:$C$11,0),1),"")

Marvin's solution will work perfectly for you though

Hi @FDibbins,

What am I missing with your answer?? See the attached where I tried your formula on my random data.

Thanks for picking that up Marvin, the formula in C should be...
=IF(COUNTIF($B$2:B2,B2)=2,MAX($C$1:C1)+1,MAX($C$1:C1))

If you upload Marvin's file from post #6 and change the formula in C2 to...
=IF(COUNTIF($B$2:B2,B2)=2,MAX($C$1:C1)+1,MAX($C$1:C1))
copied down, and then in D2, copied down, use this...
=IFERROR(INDEX($B$2:$B$45,MATCH(ROW(A1),$C$2:$C$45,0),1),"")

for excel 2003 what about the Array Formula

=IF(ISERROR(INDEX(B1:B11, MATCH(0, COUNTIF(C$1:$C1, B1:B11)+IF(COUNTIF(B1:B11, B1:B11)>1, 0, 1), 0))), "", INDEX(B1:B11, MATCH(0, COUNTIF(C$1:$C1, B1:B11)+IF(COUNTIF(B1:B11, B1:B11)>1, 0, 1), 0)))

aahhh I missed the "running 2003" bit, I just looked at the profile - no wonder you get an error trying to use iferror, sorry (please update your profile to what version you are using)

=IF(ISERROR(INDEX($B$2:$B$45,MATCH(ROW(A1),$C$2:$C$45,0),1)),"",INDEX($B$2:$B$45,MATCH(ROW(A1),$C$2:$C$45,0),1),""))

two many arguments
FDibbins
should that be ...
=IF(ISERROR(INDEX($B$2:$B$45,MATCH(ROW(A1),$C$2:$C$45,0),1)),"",INDEX($B$2:$B$45,MATCH(ROW(A1),$C$2:$C$45,0),1))

But still prefer the formula with out helper column
C2=IF(ISERROR(INDEX(B1:B50, MATCH(0, COUNTIF(C$1:$C1, B1:B50)+IF(COUNTIF(B1:B11, B1:B50)>1, 0, 1), 0))), "", INDEX(B1:B50, MATCH(0, COUNTIF(C$1:$C1, B1:B50)+IF(COUNTIF(B1:B50, B1:B50)>1, 0, 1), 0))) .... Ctrl+shift+enter fill down