Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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. |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Duplicate Cells
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. |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Duplicate Cells
test
|
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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. |
#10
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
finding & filtering out duplicate cells | Excel Discussion (Misc queries) | |||
Is there any way of searching for duplicate cells? | Excel Discussion (Misc queries) | |||
To safety merge cells without data destroyed, and smart unmerge! | Excel Discussion (Misc queries) | |||
Heps to design Locked/Unlocked cells in protected worksheet | Excel Discussion (Misc queries) | |||
how can I duplicate or copy a workbook then divide selected cells. | Excel Discussion (Misc queries) |