Duplicate Cells

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I am working on spreadsheets that contain 9 colums and anywhere up to 1,500
rows of duplicate information. I am trying to update my customer bases
pricing for an upcoming price increase. One customer could only buy 10
different items but purchase those items hundreds of times within a year. I
am trying to find a macro or something that can identify duplicates and leave
the most current item on the spreadsheet. I have over 1,000 to do right now
and going through and manually deleting that info isn't cutting the mustard.
Any help would be very appreciated.
 
Ken,

Post a very small sample of your data, and indicate what you would consider a duplicate that needs
to be deleted, and what needs to be saved. It should be easy to do, using a helper column of
formulas or filters.

HTH,
Bernie
MS Excel MVP
 
A B C
D
1 Purchase Date Description Dimensions Unit Price
2 1/1/05 SCE41B .125" x 42" x 50' $100.00
3 2/1/05 SCE41B .125" x 42" x 50' $100.00
4 3/1/05 SCE41B .125" x 42" x 50' $100.00
5 4/1/05 SCE41B .125" x 42" x 50' $100.00
6 5/1/05 SCE41B .125" x 42" x 50' $100.00
7 6/1/05 SCE41B .125" x 42" x 50' $100.00

I only put the first few colums of an item as an example. Basically I want
to keep the row that has the most recent purchase date and delete the
previous dates without having to go through and manually deleting them like i
am doing now.
 
Ken,

Sort your data table based on column A, then use this formula in another column, row 2:

=COUNTIF(B2:$B$2000,B2)<>1

Then copy down to match your data table. Any row where that shows TRUE should be deleted: you could
copy /paste special values on that column, then sort on that column, and select all the TRUEs and
delete the entire rows, and you're done.

HTH,
Bernie
MS Excel MVP
 
Ken,

I should have been specific - sort the data table Ascending on date.... Sorry.

HTH,
Bernie
MS Excel MVP
 
Bernie:
Thanks a million! It worked like a charm. Can't thank you enough. This is
going to make my life a bit easier. Have a great Holiday!

Ken
 
I have a similar problem with a little complexity added. I need to either
leave the rows in place while deleting duplicate information in column A
OR
Delete the duplicate information in column A and total the values in columes
B through F into the remaining corresponding cells in column A.

EXAMPLE: (sheet currently)

20090822C 14 4
20090801B 12
20090801B 10 1
20090801B 7 10
20090801B 4 18
20090807B 10
20090822C 10
WHAT I NEED SHEET TO LOOK LIKE:
20090822C 14 4
20090801B 33 29
20090807B 10
20090822C 10
 
Sub sumanddeletedups()
mc = 1 'column A
For i = Cells(Rows.Count, mc).End(xlUp).Row To 2 Step -1
If Cells(i - 1, mc) = Cells(i, mc) Then
Cells(i - 1, mc + 1) = _
Cells(i - 1, mc + 1) + Cells(i, mc + 1)
Cells(i - 1, mc + 2) = _
Cells(i - 1, mc + 2) + Cells(i, mc + 2)
Rows(i).Delete
End If
Next i
End Sub
 
Excellent!! Thank you so much!
--
Meg


Don Guillett said:
Sub sumanddeletedups()
mc = 1 'column A
For i = Cells(Rows.Count, mc).End(xlUp).Row To 2 Step -1
If Cells(i - 1, mc) = Cells(i, mc) Then
Cells(i - 1, mc + 1) = _
Cells(i - 1, mc + 1) + Cells(i, mc + 1)
Cells(i - 1, mc + 2) = _
Cells(i - 1, mc + 2) + Cells(i, mc + 2)
Rows(i).Delete
End If
Next i
End Sub


--
Don Guillett
Microsoft MVP Excel
SalesAid Software
(e-mail address removed)
Click to expand...
 
Back
Top