how do i eliminate duplicate records?

[RobR]

I have a spreadsheet with about 30,000 records. It's a combination of two
different databases. I need to eliminate all duplicate records (it's determined
to be a duplicate if there more than one item in column A has the same value).
Then what's left over will be a list of records that are in one database but not
the other. What's the best way to go about this? Thanks!

[RobR]

Actually my description is misleading, I don't want to eliminate only
the duplicate, what I want to do it if a duplicate is found, I want to
eliminate BOTH records.

"RobR" <nospam@nospam.com> wrote in message news:Ognt$YqbGHA.3364@TK2MSFTNGP05.phx.gbl...
>I have a spreadsheet with about 30,000 records. It's a combination of two
> different databases. I need to eliminate all duplicate records (it's determined
> to be a duplicate if there more than one item in column A has the same value).
> Then what's left over will be a list of records that are in one database but not
> the other. What's the best way to go about this? Thanks!
>
>

[Dave Peterson]

I'd use a helper column adjacent to that key column:

=countif(a:a,a1)
and drag down
and let excel calculate

Then convert that column to values
Select the column
edit|copy
edit|paste special|values

Then filter on that helper column.
show the values greater than 1
and delete those visible rows.

Deleting may work better if you sort by that key column first.


RobR wrote:
>
> Actually my description is misleading, I don't want to eliminate only
> the duplicate, what I want to do it if a duplicate is found, I want to
> eliminate BOTH records.
>
> "RobR" <nospam@nospam.com> wrote in message news:Ognt$YqbGHA.3364@TK2MSFTNGP05.phx.gbl...
> >I have a spreadsheet with about 30,000 records. It's a combination of two
> > different databases. I need to eliminate all duplicate records (it's determined
> > to be a duplicate if there more than one item in column A has the same value).
> > Then what's left over will be a list of records that are in one database but not
> > the other. What's the best way to go about this? Thanks!
> >
> >

--

Dave Peterson

[RobR]

Thanks for the help, I'm ALMOST there. I'm just trying to
delete the rows now (also tried to just select and copy)
and am being told:

cannot create or use the data range reference because it is too complex. Try to:
- Use data that can be selected in one continuous rectangle
- use data from the same sheet

"Dave Peterson" <petersod@verizonXSPAM.net> wrote in message news:44589D78.69315583@verizonXSPAM.net...
> I'd use a helper column adjacent to that key column:
>
> =countif(a:a,a1)
> and drag down
> and let excel calculate
>
> Then convert that column to values
> Select the column
> edit|copy
> edit|paste special|values
>
> Then filter on that helper column.
> show the values greater than 1
> and delete those visible rows.
>
> Deleting may work better if you sort by that key column first.
>
>
> RobR wrote:
>>
>> Actually my description is misleading, I don't want to eliminate only
>> the duplicate, what I want to do it if a duplicate is found, I want to
>> eliminate BOTH records.
>>
>> "RobR" <nospam@nospam.com> wrote in message news:Ognt$YqbGHA.3364@TK2MSFTNGP05.phx.gbl...
>> >I have a spreadsheet with about 30,000 records. It's a combination of two
>> > different databases. I need to eliminate all duplicate records (it's determined
>> > to be a duplicate if there more than one item in column A has the same value).
>> > Then what's left over will be a list of records that are in one database but not
>> > the other. What's the best way to go about this? Thanks!
>> >
>> >
>
> --
>
> Dave Peterson

[RobR]

I think I got it. I just sorted by the helper column
instead of using a filter, then just scrolled down
until I hit the 2s and cut everything downward.
(At least that's the plan,haven't done it yet ).

"RobR" <nospam@nospam.com> wrote in message news:%23tnMl9qbGHA.2456@TK2MSFTNGP04.phx.gbl...
> Thanks for the help, I'm ALMOST there. I'm just trying to
> delete the rows now (also tried to just select and copy)
> and am being told:
>
> cannot create or use the data range reference because it is too complex. Try to:
> - Use data that can be selected in one continuous rectangle
> - use data from the same sheet
>
> "Dave Peterson" <petersod@verizonXSPAM.net> wrote in message news:44589D78.69315583@verizonXSPAM.net...
>> I'd use a helper column adjacent to that key column:
>>
>> =countif(a:a,a1)
>> and drag down
>> and let excel calculate
>>
>> Then convert that column to values
>> Select the column
>> edit|copy
>> edit|paste special|values
>>
>> Then filter on that helper column.
>> show the values greater than 1
>> and delete those visible rows.
>>
>> Deleting may work better if you sort by that key column first.
>>
>>
>> RobR wrote:
>>>
>>> Actually my description is misleading, I don't want to eliminate only
>>> the duplicate, what I want to do it if a duplicate is found, I want to
>>> eliminate BOTH records.
>>>
>>> "RobR" <nospam@nospam.com> wrote in message news:Ognt$YqbGHA.3364@TK2MSFTNGP05.phx.gbl...
>>> >I have a spreadsheet with about 30,000 records. It's a combination of two
>>> > different databases. I need to eliminate all duplicate records (it's determined
>>> > to be a duplicate if there more than one item in column A has the same value).
>>> > Then what's left over will be a list of records that are in one database but not
>>> > the other. What's the best way to go about this? Thanks!
>>> >
>>> >
>>
>> --
>>
>> Dave Peterson
>
>

[Dave Peterson]

That was my (almost hidden) warning was about:

> > Deleting may work better if you sort by that key column first.

If you can sort by the helper column, then it will be in ascending order and
your delete process will work.

If you have to put the data back in its original order, I do this:

Insert another helper column (column A???)
type 1 in A1
type 2 in A2
select a1:a2 and drag down the column

Then you can sort by the "duplicate" helper column, delete the duplicates, then
resort by the "index" helper column.

When you're done, just delete that "index" helper column.

RobR wrote:
>
> Thanks for the help, I'm ALMOST there. I'm just trying to
> delete the rows now (also tried to just select and copy)
> and am being told:
>
> cannot create or use the data range reference because it is too complex. Try to:
> - Use data that can be selected in one continuous rectangle
> - use data from the same sheet
>
> "Dave Peterson" <petersod@verizonXSPAM.net> wrote in message news:44589D78.69315583@verizonXSPAM.net...
> > I'd use a helper column adjacent to that key column:
> >
> > =countif(a:a,a1)
> > and drag down
> > and let excel calculate
> >
> > Then convert that column to values
> > Select the column
> > edit|copy
> > edit|paste special|values
> >
> > Then filter on that helper column.
> > show the values greater than 1
> > and delete those visible rows.
> >
> > Deleting may work better if you sort by that key column first.
> >
> >
> > RobR wrote:
> >>
> >> Actually my description is misleading, I don't want to eliminate only
> >> the duplicate, what I want to do it if a duplicate is found, I want to
> >> eliminate BOTH records.
> >>
> >> "RobR" <nospam@nospam.com> wrote in message news:Ognt$YqbGHA.3364@TK2MSFTNGP05.phx.gbl...
> >> >I have a spreadsheet with about 30,000 records. It's a combination of two
> >> > different databases. I need to eliminate all duplicate records (it's determined
> >> > to be a duplicate if there more than one item in column A has the same value).
> >> > Then what's left over will be a list of records that are in one database but not
> >> > the other. What's the best way to go about this? Thanks!
> >> >
> >> >
> >
> > --
> >
> > Dave Peterson

--

Dave Peterson

[RobR]

Thank you for all the help, I found my missing $4.4 million. Now I
just need someone smarter than me to figure out why it wasn't pulled
into the database .

"Dave Peterson" <petersod@verizonXSPAM.net> wrote in message news:4458A74F.8ADC2C47@verizonXSPAM.net...
> That was my (almost hidden) warning was about:
>
>> > Deleting may work better if you sort by that key column first.
>
> If you can sort by the helper column, then it will be in ascending order and
> your delete process will work.
>
> If you have to put the data back in its original order, I do this:
>
> Insert another helper column (column A???)
> type 1 in A1
> type 2 in A2
> select a1:a2 and drag down the column
>
> Then you can sort by the "duplicate" helper column, delete the duplicates, then
> resort by the "index" helper column.
>
> When you're done, just delete that "index" helper column.
>
> RobR wrote:
>>
>> Thanks for the help, I'm ALMOST there. I'm just trying to
>> delete the rows now (also tried to just select and copy)
>> and am being told:
>>
>> cannot create or use the data range reference because it is too complex. Try to:
>> - Use data that can be selected in one continuous rectangle
>> - use data from the same sheet
>>
>> "Dave Peterson" <petersod@verizonXSPAM.net> wrote in message news:44589D78.69315583@verizonXSPAM.net...
>> > I'd use a helper column adjacent to that key column:
>> >
>> > =countif(a:a,a1)
>> > and drag down
>> > and let excel calculate
>> >
>> > Then convert that column to values
>> > Select the column
>> > edit|copy
>> > edit|paste special|values
>> >
>> > Then filter on that helper column.
>> > show the values greater than 1
>> > and delete those visible rows.
>> >
>> > Deleting may work better if you sort by that key column first.
>> >
>> >
>> > RobR wrote:
>> >>
>> >> Actually my description is misleading, I don't want to eliminate only
>> >> the duplicate, what I want to do it if a duplicate is found, I want to
>> >> eliminate BOTH records.
>> >>
>> >> "RobR" <nospam@nospam.com> wrote in message news:Ognt$YqbGHA.3364@TK2MSFTNGP05.phx.gbl...
>> >> >I have a spreadsheet with about 30,000 records. It's a combination of two
>> >> > different databases. I need to eliminate all duplicate records (it's determined
>> >> > to be a duplicate if there more than one item in column A has the same value).
>> >> > Then what's left over will be a list of records that are in one database but not
>> >> > the other. What's the best way to go about this? Thanks!
>> >> >
>> >> >
>> >
>> > --
>> >
>> > Dave Peterson
>
> --
>
> Dave Peterson

[Don Guillett]

Assuming you can sort first, this should work

Sub deletebothifsame()
On Error Resume Next
For i = Cells(Rows.Count, "a").End(xlUp).Row To 1 Step -1
If Cells(i - 1, 1).Value = Cells(i, 1).Value Then
Cells(i - 1, 1).Resize(2).EntireRow.Delete
End If
Next i
End Sub

--
Don Guillett
SalesAid Software
dguillett1@austin.rr.com
"RobR" <nospam@nospam.com> wrote in message
news:OcMnOcqbGHA.628@TK2MSFTNGP04.phx.gbl...
> Actually my description is misleading, I don't want to eliminate only
> the duplicate, what I want to do it if a duplicate is found, I want to
> eliminate BOTH records.
>
> "RobR" <nospam@nospam.com> wrote in message
> news:Ognt$YqbGHA.3364@TK2MSFTNGP05.phx.gbl...
>>I have a spreadsheet with about 30,000 records. It's a combination of two
>> different databases. I need to eliminate all duplicate records (it's
>> determined
>> to be a duplicate if there more than one item in column A has the same
>> value).
>> Then what's left over will be a list of records that are in one database
>> but not
>> the other. What's the best way to go about this? Thanks!
>>
>>
>
>

[RobR]

Thanks Don, wish I had seen this before I started down the other road.
I suspect I'll be doing this again though so I've bookmarked this thread.
Next time around I'll use your method.

"Don Guillett" <dguillett1@austin.rr.com> wrote in message news:OT4rRyqbGHA.3840@TK2MSFTNGP04.phx.gbl...
> Assuming you can sort first, this should work
>
> Sub deletebothifsame()
> On Error Resume Next
> For i = Cells(Rows.Count, "a").End(xlUp).Row To 1 Step -1
> If Cells(i - 1, 1).Value = Cells(i, 1).Value Then
> Cells(i - 1, 1).Resize(2).EntireRow.Delete
> End If
> Next i
> End Sub
>
> --
> Don Guillett
> SalesAid Software
> dguillett1@austin.rr.com
> "RobR" <nospam@nospam.com> wrote in message news:OcMnOcqbGHA.628@TK2MSFTNGP04.phx.gbl...
>> Actually my description is misleading, I don't want to eliminate only
>> the duplicate, what I want to do it if a duplicate is found, I want to
>> eliminate BOTH records.
>>
>> "RobR" <nospam@nospam.com> wrote in message news:Ognt$YqbGHA.3364@TK2MSFTNGP05.phx.gbl...
>>>I have a spreadsheet with about 30,000 records. It's a combination of two
>>> different databases. I need to eliminate all duplicate records (it's determined
>>> to be a duplicate if there more than one item in column A has the same value).
>>> Then what's left over will be a list of records that are in one database but not
>>> the other. What's the best way to go about this? Thanks!
>>>
>>>
>>
>>
>
>

[Dave Peterson]

Be careful if any of your values appear an odd number of times.

RobR wrote:
>
> Thanks Don, wish I had seen this before I started down the other road.
> I suspect I'll be doing this again though so I've bookmarked this thread.
> Next time around I'll use your method.
>
> "Don Guillett" <dguillett1@austin.rr.com> wrote in message news:OT4rRyqbGHA.3840@TK2MSFTNGP04.phx.gbl...
> > Assuming you can sort first, this should work
> >
> > Sub deletebothifsame()
> > On Error Resume Next
> > For i = Cells(Rows.Count, "a").End(xlUp).Row To 1 Step -1
> > If Cells(i - 1, 1).Value = Cells(i, 1).Value Then
> > Cells(i - 1, 1).Resize(2).EntireRow.Delete
> > End If
> > Next i
> > End Sub
> >
> > --
> > Don Guillett
> > SalesAid Software
> > dguillett1@austin.rr.com
> > "RobR" <nospam@nospam.com> wrote in message news:OcMnOcqbGHA.628@TK2MSFTNGP04.phx.gbl...
> >> Actually my description is misleading, I don't want to eliminate only
> >> the duplicate, what I want to do it if a duplicate is found, I want to
> >> eliminate BOTH records.
> >>
> >> "RobR" <nospam@nospam.com> wrote in message news:Ognt$YqbGHA.3364@TK2MSFTNGP05.phx.gbl...
> >>>I have a spreadsheet with about 30,000 records. It's a combination of two
> >>> different databases. I need to eliminate all duplicate records (it's determined
> >>> to be a duplicate if there more than one item in column A has the same value).
> >>> Then what's left over will be a list of records that are in one database but not
> >>> the other. What's the best way to go about this? Thanks!
> >>>
> >>>
> >>
> >>
> >
> >

--

Dave Peterson

[RobR]

Thanks, the values will just be 1 or 2 for my current issue.
2 if it's in both databases, 1 if it's in only the original database.

"Dave Peterson" <petersod@verizonXSPAM.net> wrote in message news:4458B233.79319F31@verizonXSPAM.net...
> Be careful if any of your values appear an odd number of times.
>
> RobR wrote:
>>
>> Thanks Don, wish I had seen this before I started down the other road.
>> I suspect I'll be doing this again though so I've bookmarked this thread.
>> Next time around I'll use your method.
>>
>> "Don Guillett" <dguillett1@austin.rr.com> wrote in message news:OT4rRyqbGHA.3840@TK2MSFTNGP04.phx.gbl...
>> > Assuming you can sort first, this should work
>> >
>> > Sub deletebothifsame()
>> > On Error Resume Next
>> > For i = Cells(Rows.Count, "a").End(xlUp).Row To 1 Step -1
>> > If Cells(i - 1, 1).Value = Cells(i, 1).Value Then
>> > Cells(i - 1, 1).Resize(2).EntireRow.Delete
>> > End If
>> > Next i
>> > End Sub
>> >
>> > --
>> > Don Guillett
>> > SalesAid Software
>> > dguillett1@austin.rr.com
>> > "RobR" <nospam@nospam.com> wrote in message news:OcMnOcqbGHA.628@TK2MSFTNGP04.phx.gbl...
>> >> Actually my description is misleading, I don't want to eliminate only
>> >> the duplicate, what I want to do it if a duplicate is found, I want to
>> >> eliminate BOTH records.
>> >>
>> >> "RobR" <nospam@nospam.com> wrote in message news:Ognt$YqbGHA.3364@TK2MSFTNGP05.phx.gbl...
>> >>>I have a spreadsheet with about 30,000 records. It's a combination of two
>> >>> different databases. I need to eliminate all duplicate records (it's determined
>> >>> to be a duplicate if there more than one item in column A has the same value).
>> >>> Then what's left over will be a list of records that are in one database but not
>> >>> the other. What's the best way to go about this? Thanks!
>> >>>
>> >>>
>> >>
>> >>
>> >
>> >
>
> --
>
> Dave Peterson