1) Put the data of all 12 sheets in one single sheet
2) On this new sheet on column G add this formula
=b2&c2&d2&e2
assuming that the column B contains "111111", Column C contains"Lamaya" etc.
If not then sadjust the formula to select those 4 column that you need
compared
3) Copy this formula down to the last row.
4) Sort the data based on Column G
5) On ColumnH type the formula
=G2=G3
All duplicates will show True.
6) Select Column G and Column H then copy then edit-Paste Special-- Values
7) Sort the data again based on col H . That way all true comes together and
delete those rows
"SSHO_99" wrote in message
...
I have 12 separate months of data, each in their own spreadsheet. I want
to
simply copy/paste all of the data from the 12 separate spreadsheets into
one
master worksheet, then find all of the duplicates and somehow identity
them
so I can manually remove the duplicates (or automate a way to 'eliminate'
them). To slightly complicate this, let's say the data has 6 columns.
The
first 4 columns can have duplicates while the date in columns 5 and 6 may
vary. A simple way to describe this would be to "Find duplicates in
column
"A" and after the first unique value, turn all other duplicate rows
yellow".
So far I have copied/pasted the data into one master spreadsheet, then I
have sorted the data which made the duplicates one on top of the other. I
can see the duplicates and elimiate them one by one, however I have
thousands
of rows, so I want to automate it somehow. Here is an example:
A B D E F
1 111111 Lamaya 1319.00 359.00 354.60
2 222222 John 2755.81 286.06 0.00
3 333333 Steve 2873.12 0.00 85.00
4 333333 Steve 2873.12 44.20 0.00
5 333333 Steve 2873.12 0.00 368.30
6 444444 Gail 2450.00 0.00 23.98
7 555555 Joe 1086.57 887.87 226.30
8 555555 Joe 1086.57 665.21 0.00
9 666666 Bob 96.40 0.00 201.30
10 777777 Jenn 2075.00 5531.00 101.20
11 777777 Jenn 2075.00 2040.00 20.30
12 777777 Jenn 2075.00 1020.00 512.30
13 777777 Jenn 2075.00 119.00 71.00
14 888888 Peter 391.30 0.00 1.99
15 888888 Peter 391.30 0.00 35.03
16 999999 Tony 3077.00 110.12 0.00
In this example I want to 'eliminate (or somehow idenity) rows 4, 5, 8,
11,
12, 13 and 15 as they are the duplicates.
I think I could do a conditional format, but I don't know if that will do
what I need. Possibly a macro? I'm ok with macro's and not afraid to
play
with them, I'm just not a programmer and have hit a dead end. Possibly
have
another worksheet that picks each unique date in column A and ignores the
duplicates?
Thanks,
Steve
|