How to Extract a List of Duplicates From a Column

[atb88]

Hello!

So I've been having trouble with this - I have a column of user IDs, and I need to find which IDs appear more than once in the column, and extract those IDs to a new column. The scenario, if it helps, is that the user IDs represent purchases, and we want to reward customers who purchase more than once, so we need the list of IDs of customers who have purchased multiple times. I can't use a Pivot Table to count the number of times a value occur. Also, I have no idea what IDs are being put into the ID column, the order the IDs are in the ID column, or how many IDs there are in total.

If Column A is the provided data, and Column C is the extracted duplicates, it should look like:

[TABLE="width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]ID[/TD]
[TD][/TD]
[TD]ID's that occur more than once[/TD]
[/TR]
[TR]
[TD]2
[/TD]
[TD]100[/TD]
[TD][/TD]
[TD]100[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]200[/TD]
[TD][/TD]
[TD]400[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]300[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]100[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD]400[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]7[/TD]
[TD]400[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]


Thanks in advance! Let me know if you need more information.

 

[Aladin Akyurek]

Hello!

So I've been having trouble with this - I have a column of user IDs, and I need to find which IDs appear more than once in the column, and extract those IDs to a new column. The scenario, if it helps, is that the user IDs represent purchases, and we want to reward customers who purchase more than once, so we need the list of IDs of customers who have purchased multiple times. I can't use a Pivot Table to count the number of times a value occur. Also, I have no idea what IDs are being put into the ID column, the order the IDs are in the ID column, or how many IDs there are in total.

If Column A is the provided data, and Column C is the extracted duplicates, it should look like:

[TABLE="width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]A
[/TD]
[TD]B
[/TD]
[TD]C
[/TD]
[/TR]
[TR]
[TD]1
[/TD]
[TD]ID
[/TD]
[TD][/TD]
[TD]ID's that occur more than once
[/TD]
[/TR]
[TR]
[TD]2
[/TD]
[TD]100
[/TD]
[TD][/TD]
[TD]100
[/TD]
[/TR]
[TR]
[TD]3
[/TD]
[TD]200
[/TD]
[TD][/TD]
[TD]400
[/TD]
[/TR]
[TR]
[TD]4
[/TD]
[TD]300
[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]5
[/TD]
[TD]100
[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]6
[/TD]
[TD]400
[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]7
[/TD]
[TD]400
[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]


Thanks in advance! Let me know if you need more information.

[TABLE="width: 262"]
<colgroup><col style="width: 48pt;" span="3" width="64"> <col style="width: 118pt; mso-width-source: userset; mso-width-alt: 5603;" width="158"> <tbody>[TR]
[TD="class: xl64, width: 64, bgcolor: white"]ID[/TD]
[TD="class: xl63, width: 64, bgcolor: white"]0[/TD]
[TD="class: xl63, width: 64, bgcolor: white"] [/TD]
[TD="class: xl63, width: 158, bgcolor: white"]2[/TD]
[/TR]
[TR]
[TD="class: xl63, width: 64, bgcolor: white"]100[/TD]
[TD="class: xl63, width: 64, bgcolor: white"] [/TD]
[TD="class: xl63, width: 64, bgcolor: white"] [/TD]
[TD="class: xl64, width: 158, bgcolor: white"]Returning ID[/TD]
[/TR]
[TR]
[TD="class: xl63, width: 64, bgcolor: white"]200[/TD]
[TD="class: xl63, width: 64, bgcolor: white"] [/TD]
[TD="class: xl63, width: 64, bgcolor: white"] [/TD]
[TD="class: xl63, width: 158, bgcolor: white"]100[/TD]
[/TR]
[TR]
[TD="class: xl63, width: 64, bgcolor: white"]300[/TD]
[TD="class: xl63, width: 64, bgcolor: white"] [/TD]
[TD="class: xl63, width: 64, bgcolor: white"] [/TD]
[TD="class: xl63, width: 158, bgcolor: white"]400[/TD]
[/TR]
[TR]
[TD="class: xl63, width: 64, bgcolor: white"]100[/TD]
[TD="class: xl63, width: 64, bgcolor: white"]1[/TD]
[TD="class: xl63, width: 64, bgcolor: white"] [/TD]
[TD="class: xl63, width: 158, bgcolor: white"] [/TD]
[/TR]
[TR]
[TD="class: xl63, width: 64, bgcolor: white"]400[/TD]
[TD="class: xl63, width: 64, bgcolor: white"] [/TD]
[TD="class: xl63, width: 64, bgcolor: white"] [/TD]
[TD="class: xl63, width: 158, bgcolor: white"] [/TD]
[/TR]
[TR]
[TD="class: xl63, width: 64, bgcolor: white"]400[/TD]
[TD="class: xl63, width: 64, bgcolor: white"]2[/TD]
[TD="class: xl63, width: 64, bgcolor: white"] [/TD]
[TD="class: xl63, width: 158, bgcolor: white"] [/TD]
[/TR]
</tbody>[/TABLE]

B1 must house a 0.

B2, just enter and copy down:

=IF(ISNUMBER(MATCH(A2,$A$1:A1,0)),LOOKUP(9.99999999999999E+307,$B$1:B1)+1,"")

D1, just enter:

=LOOKUP(9.99999999999999E+307,B:B)

D3, just enter and copy down:

=IF(ROWS($D$3:D3)<=$D$1,LOOKUP(ROWS($D$3:D3),B:B,A:A),"")

If needed, you can also have...

E3, copied down:

=COUNTIF(A:A,$D3)

 

[atb88]

When I do that and extend D3 down, multiple instances of the duplicated numbers are displayed, and I need it so only one instance of each number is displayed... how can I make it so only one instance of the duplicated values is displayed in the new column (regardless of how many instances there are of the value in the original column). Thanks!

 

[Aladin Akyurek]

When I do that and extend D3 down, multiple instances of the duplicated numbers are displayed, and I need it so only one instance of each number is displayed... how can I make it so only one instance of the duplicated values is displayed in the new column (regardless of how many instances there are of the value in the original column). Thanks!

Change B2 to:

=IF(COUNTIF($A$2:A2,A2)=2,LOOKUP(9.99999999999999E+307,$B$1:B1)+1,"")

 

[atb88]

Change B2 to:

=IF(COUNTIF($A$2:A2,A2)=2,LOOKUP(9.99999999999999E+307,$B$1:B1)+1,"")

It worked! YOU ROCK Aladin! Could you possibly explain a little bit to me of what you did there?

 

[Aladin Akyurek]

It worked! YOU ROCK Aladin! Could you possibly explain a little bit to me of what you did there?

The formula in B2 effects a serialized count: 1, 2,3,... When the progressively determined occurrence count of an ID is 2 (meaning a duplicate), mark this item as Nth observation, where N is built up as Last count + 1. Last count is monitored by the LOOKUP expression that picks up the last numeric value in the range it looks at. At A5 we see CountIf yields a 2 for the ID in this cell(which is 100). LOOKUP then picks up the last value in B1:B5, which is 0, and augments this by 1. The set up continues to apply this logic for the whole reference. Hope this helps.