#1   Report Post  
Posted to microsoft.public.excel.worksheet.functions
Ken McGonagle
 
Posts: n/a
Default Duplicate Cells

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.
  #3   Report Post  
Posted to microsoft.public.excel.worksheet.functions
Ken McGonagle
 
Posts: n/a
Default Duplicate Cells

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.

"Bernie Deitrick" wrote:

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


"Ken McGonagle" <Ken wrote in message
...
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.




  #4   Report Post  
Posted to microsoft.public.excel.worksheet.functions
Bernie Deitrick
 
Posts: n/a
Default Duplicate Cells

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 McGonagle" wrote in message
...
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.

"Bernie Deitrick" wrote:

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


"Ken McGonagle" <Ken wrote in message
...
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.






  #5   Report Post  
Posted to microsoft.public.excel.worksheet.functions
Bernie Deitrick
 
Posts: n/a
Default Duplicate Cells

Ken,

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

HTH,
Bernie
MS Excel MVP


"Bernie Deitrick" <deitbe @ consumer dot org wrote in message
...
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 McGonagle" wrote in message
...
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.

"Bernie Deitrick" wrote:

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


"Ken McGonagle" <Ken wrote in message
...
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.









  #6   Report Post  
Posted to microsoft.public.excel.worksheet.functions
Ken McGonagle
 
Posts: n/a
Default Duplicate Cells

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

"Bernie Deitrick" wrote:

Ken,

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

HTH,
Bernie
MS Excel MVP


"Bernie Deitrick" <deitbe @ consumer dot org wrote in message
...
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 McGonagle" wrote in message
...
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.

"Bernie Deitrick" wrote:

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


"Ken McGonagle" <Ken wrote in message
...
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.








  #7   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 2
Default Duplicate Cells

test
  #8   Report Post  
Posted to microsoft.public.excel.worksheet.functions
Meg Meg is offline
external usenet poster
 
Posts: 22
Default Duplicate Cells

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


--
Meg


"Bernie Deitrick" wrote:

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 McGonagle" wrote in message
...
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.

"Bernie Deitrick" wrote:

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


"Ken McGonagle" <Ken wrote in message
...
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.






  #9   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 10,124
Default Duplicate Cells

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

"Meg" wrote in message
...
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


--
Meg


"Bernie Deitrick" wrote:

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 McGonagle" wrote in message
...
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.

"Bernie Deitrick" wrote:

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


"Ken McGonagle" <Ken
wrote in
message
...
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.







  #10   Report Post  
Posted to microsoft.public.excel.worksheet.functions
Meg Meg is offline
external usenet poster
 
Posts: 22
Default Duplicate Cells

Excellent!! Thank you so much!
--
Meg


"Don Guillett" wrote:

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

"Meg" wrote in message
...
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


--
Meg


"Bernie Deitrick" wrote:

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 McGonagle" wrote in message
...
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.

"Bernie Deitrick" wrote:

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


"Ken McGonagle" <Ken
wrote in
message
...
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.








Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
finding & filtering out duplicate cells Jackie Excel Discussion (Misc queries) 4 October 21st 05 06:17 AM
Is there any way of searching for duplicate cells? Lisa Excel Discussion (Misc queries) 1 October 3rd 05 09:19 AM
To safety merge cells without data destroyed, and smart unmerge! Kevin Excel Discussion (Misc queries) 0 December 30th 04 08:17 AM
Heps to design Locked/Unlocked cells in protected worksheet Kevin Excel Discussion (Misc queries) 0 December 30th 04 08:09 AM
how can I duplicate or copy a workbook then divide selected cells. macros excel... duplication and calculat Excel Discussion (Misc queries) 1 November 29th 04 04:16 PM


All times are GMT +1. The time now is 02:35 AM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"