Removing Duplicates Across Multiple Sheets Help Please

**richter394** · 10-25-2013, 09:22 PM

Here is what I need to do:

I have a workbook with 6 sheets. It includes data that takes up Columns A through R (A:R). Row 1 is a header so data starts at Row 2. The data in Column B is what I am mainly concerned about. What I want to do is take the data from "Sheet1" and remove the duplicate data from Sheets 2-6.

Sheet 1 is blank besides Row 1 which is a consistent header that runs through all Sheets 1-6 taking up Columns A:R . Sheets 2-6 have the header in Row 1 and a random number of rows of data below taking up Columns A:R . So I will be entering a certain set of data that randomly changes in number (Ex: Today I will post 30 rows of data, next time it may be 25 rows of data...) below the header in "Sheet1". Then I want to be able to take the values from Column B, Row 2 and below and remove all the duplicate values of Column B, Row 2 and below of sheets 2-6. So the code would create a macro that can read all the values in Column B, Row 2 and below from "Sheet1", it would then compare these values to the values in Column B, Row 2 and below from Sheets 2-6. Essentially I would be left with Sheet1 including only the values in Column B that are not on Sheets 2-6. Then I could tell which data I just entered is not on the other sheets of the workbook.

Sheets 2:6 all have a different number of data rows below the header so I will need it to be able to search a dynamic number of data rows under the header for these sheets as well as Sheet1 since that will be a random number of data rows to search through as well.

Sorry I don't have a sample workbook since this computer doesn't have microsoft office. If you can help me I would appreciate it.

**GC Excel** · 10-25-2013, 11:28 PM

Hi ritcher394,
Try this code :

Please Login or Register  to view this content.

**richter394** · 10-28-2013, 03:58 PM

That all works except at the end my Column B format becomes "General". I need it to be "Text". Column B is all numberical values but a lot of them start with a Zero (Ex:00001). When I run that macro if 00001 is not a duplicate it stays in Sheet1, which is great but then it gets reformatted to read "1" without the zeros because the format becomes "General" and not "Text". Is there any way to fix that?

I really appreciate the help.