Create New Worksheet based on duplicates in Multiple worksheets

Hi porucha vevrku,

Sure, I can do that. I’m currently away from my computer but in the meantime I’ll explain again. The code matches one cell, that is correct. Each worksheet has the same headings, but the information under the headings could be different.

For example: one worksheet, the row has “John” “Smith” “Fat” “5’6” inputted into A2; B2; C2;D2 respectively. Labeled underneath their respective headings. In this worksheet, there could be 100+ names in the rows. Now in Sheet2, there is another list of names with the same headings and so on and so forth. I want to compare all the sheets, locate the duplicated rows, and create entire new sheet with all those duplicate rows. So if John, Smith, Fat and 5’6 were located in all the sheets, then that should show up into the newly created worksheet. Does that explain a little better? The code I have only compares one cell and then makes a new sheet based off of sheet1 and sheet2. But I want it to look at sheet3, 4,5 or how many sheets I put in it. Does this explain a little better?

As there will not be duplicated rows within the same sheet,

you may try:
1. merge all sheets.
2. Countif to identify duplicate
3. Remove if < Number of Sheet (ThisWorkbook.Worksheets.Count)
4. Remove the duplication.

Hi BoredWorker,

But where in the code would I put that? Would you be able to copy the entire code I need to this thread?

i mean is to create a new code...

edit the red one to the unique id column. (i.e can ignore the sheet, it just need column number.)
it only limited table only start will row1 or change the Blue one to the header cells (i.e. any location of the table header)

Please Login or Register  to view this content.

Originally Posted by BoredWorker

1. merge all sheets.
2. Countif to identify duplicate
3. Remove if < Number of Sheet (ThisWorkbook.Worksheets.Count)
4. Remove the duplication.

Originally Posted by BoredWorker

i mean is to create a new code...

Good idea, but:

1. You did not take it into account:

Originally Posted by a1cswiz

... the row has “John” “Smith” “Fat” “5’6” inputted into A2; B2; C2;D2 respectively ...
... So if John, Smith, Fat and 5’6 were located in all the sheets, then that should ...

You take into account in your code, only one cell from one column ('A'), not many cells from different columns.

“John Smith Fat 5’6” <> “John Smith Fatt 5’7”

2. Not everyone has an excel version above 2k3 yet, so you can not always use the "Delete duplicates" method.

BoredWorker,

Okay, I just plugged the Code in and ran it and it is not working how I want it too. It is not picking up on the duplicates. I'll lay out an example below:

Sheet1
ADDRESS LAST NAME FIRST NAME ZIP CODE
123 Chester St Smith John 45125
563 Weed Ln Jones Ronald 17845

Sheet2
ADDRESS LAST NAME FIRST NAME ZIP CODE
4444 Hell Rd Heck Sarah 17845
563 Weed Ln Jones Ronald 17845

"Report Sheet" (Created after running the macro")
ADDRESS LAST NAME FIRST NAME ZIP CODE
563 Weed Ln Jones Ronald 17845

Hi porucha vevrku,

I would be satisfied if it would just pick up on one column; however, it is not even doing that with the code provided above

Hi porucha vevrku,

If it would just search for the words John in Column A, located the duplicates and then transfer for the entire Row that "john" was on, would be exactly what I am trying to do

Solved: With all of your great help, I finally have what I was looking for. You guys and this forum has been a tremendous help. Below is the code and it checks all out.

Please Login or Register  to view this content.

Hi a1cswiz,

Please do not quote full posts as this just causes thread clutter.

it just need to change Red (WK_Count) to any amount of duplication you need, it based on the below rule.

in your case change then condition to >2

3. Remove if (the number of duplicates) <> Number of Sheet (ThisWorkbook.Worksheets.Count)

Please Login or Register  to view this content.