Duplicate Cells

[Ken McGonagle]

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.

[Bernie Deitrick]

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 McGonagle@discussions.microsoft.com> wrote in message
news:C2D9130F-679D-4979-A8E6-07AB0478AE1F@microsoft.com...
>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 McGonagle]

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 McGonagle@discussions.microsoft.com> wrote in message
> news:C2D9130F-679D-4979-A8E6-07AB0478AE1F@microsoft.com...
> >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.
>
>
>

[Bernie Deitrick]

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" <KenMcGonagle@discussions.microsoft.com> wrote in message
news:A8C57140-15BB-45F3-8411-2B88301BEBD4@microsoft.com...
> 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 McGonagle@discussions.microsoft.com> wrote in message
>> news:C2D9130F-679D-4979-A8E6-07AB0478AE1F@microsoft.com...
>> >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.
>>
>>
>>

[Bernie Deitrick]

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
news:e%23UYxyo%23FHA.912@TK2MSFTNGP11.phx.gbl...
> 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" <KenMcGonagle@discussions.microsoft.com> wrote in message
> news:A8C57140-15BB-45F3-8411-2B88301BEBD4@microsoft.com...
>> 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 McGonagle@discussions.microsoft.com> wrote in message
>>> news:C2D9130F-679D-4979-A8E6-07AB0478AE1F@microsoft.com...
>>> >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 McGonagle]

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
> news:e%23UYxyo%23FHA.912@TK2MSFTNGP11.phx.gbl...
> > 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" <KenMcGonagle@discussions.microsoft.com> wrote in message
> > news:A8C57140-15BB-45F3-8411-2B88301BEBD4@microsoft.com...
> >> 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 McGonagle@discussions.microsoft.com> wrote in message
> >>> news:C2D9130F-679D-4979-A8E6-07AB0478AE1F@microsoft.com...
> >>> >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.
> >>>
> >>>
> >>>
> >
> >
>
>
>