Find doubles in a column and copy them to a seperate place

[NewExcelNewbie]

Hi I have the following problem in Excel:

I want to find text doubles in a column and then automatically copy the information of that row to a another, seperate table. This second table then will only contain the doubles.

This is how my table looks now:

NAME	PRODUCT	ID
John	Apples	112
Marc	Apples	113
Irene	Oranges	114

In this case I would like to copy out the information of Marc and John, because they have the same product. I'm sure there is a relativley easy solution to this problem but i was not able to find one. What would you guys propose? Thanks in advance!

[PaulM100]

A VBA solution will work?
To copy the unique rows based on criteria on column B to Sheet2:

Please Login or Register  to view this content.

To copy the rows based on duplicates in column B to Sheet3:

Please Login or Register  to view this content.

To copy the duplicated rows to Sheet3 and remove them from Sheet1(I've used Sheet one for the data):

Please Login or Register  to view this content.

[Glenn Kennedy]

or a formula:

=IFERROR(INDEX(A:A,SMALL(INDEX((COUNTIF($B$2:$B$8,$B$2:$B$8)=1)*10^10+ROW($B$2:$B$8),0),ROWS(E$2:E2))),"")

in E2, copied across and down. You may need ; instead of ,

[NewExcelNewbie]

Thank you both so much! Glenn I tried your solution because it's a bit easier for me and it works. Is there a way to order the result in the table? So that the outcome is all apples first, then all peaches etc..?

[Glenn Kennedy]

that does make it more complicated!!

In E2, copied down (and copy pasted into G2)

=IFERROR(INDEX(A:A,SMALL(IF($B$2:$B$8=$F2,ROW($B$2:$B$8)),COUNTIF($F$2:$F2,$F2))),"")

in F2:
=IFERROR(INDEX($B$2:$B$8,MATCH(1/(1/LARGE((COUNTIF($B$2:$B$8,">="&$B$2:$B$8)*(COUNTIF($B$2:$B$8,$B$2:$B$8)<>1)),ROWS($1:1))),(COUNTIF($B$2:$B$8,">="&$B$2:$B$8)*(COUNTIF($B$2:$B$8,$B$2:$B$8)<>1)),0)),"")

These are ALL array formulae.


Array Formulae are a little different from ordinary formulae in that they MUST be confirmed in the FIRST CELL ONLY by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER. After that, the array can be dragged down as normal, to cover the desired range.

You will know the array is active when you see curly brackets { } - or "curly braces" for those of you in the USA, or "flower brackets" for those of you in India - appear around the outside of your formula. If you do not use CTRL+SHIFT+ENTER you will (almost always) get an error message or an incorrect answer. Press F2 on that cell and try again.

Don't type the curly brackets yourself - it won't work...

[NewExcelNewbie]

thanks, it's working. However if I try to expand the code on more rows (for example from 1 to 1000), a lot of values stay empty. So it now looks like this:

Name	Product	ID
John	Apple	111
	Apple
	Apple
Irene	Peaches	119
	Peaches

Any idea why that could be?

I use the code:

in H =IFERROR(INDEX(B:B,SMALL(IF($E$2:$E$1000=$I2,ROW($E$2:$E$1000)),COUNTIF($I$2:$I2,$I2))),"")

in I =IFERROR(INDEX($E$2:$E$1000,MATCH(1/(1/LARGE((COUNTIF($E$2:$E$1000,">="&$E$2:$E$1000)*(COUNTIF($E$2:$E$1000,$E$2:$E$1000)<>1)),ROWS($1:1))),(COUNTIF($E$2:$E$1000,">="&$E$2:$E$1000)*(COUNTIF($E$2:$E$1000,$E$2:$E$1000)<>1)),0)),"")

in J =IFERROR(INDEX(F:F,SMALL(IF($E$2:$E$1000=$I2,ROW($E$2:$E$1000)),COUNTIF($I$2:$I2,$I2))),"")

[Glenn Kennedy]

Post an updated spreadsheet. If the above is your INPUT rather than your output, it is a totally different question !!

[Glenn Kennedy]

Unfortunately the attachment icon doesn't work at the moment. So, to attach an Excel file you have to do the following: Just before posting, scroll down to Go Advanced and then scroll down to Manage Attachments. Now follow the instructions at the top of that screen.

[NewExcelNewbie]

here is what I have:

[Glenn Kennedy]

From H4 downwards, you had upset the array and the {} had gone. I have also added a tweak to stop the 0s from appearring.

[NewExcelNewbie]

solved the problem, thanks a lot

[Glenn Kennedy]

You're welcome.

---

If that takes care of your original question, please select "Thread Tools" from the menu link above and mark this thread as SOLVED.

It'd also be appreciated if you were to click the Add Reputation button at the foot of any of the posts of all members who helped you reach a solution.