Duplicate Entries (2000)

Topic

New Reply

Hi, I am looking for duplicate payments. I have a list of data with the following fields:
Unit Journal Date Class DeptID Ref Line # Amount Line Descr

I have sorted by Class, Line Descr, and Amount. What I’d like to accomplish is be able to identify, highlight and segregate the rows whereby the Line Descr and Amount are identical to the Line Descr and Amount in another row(s).

Any suggestions?

Thanks!

Reply | Quote

Replies

Several suggestions:
You can use adv filter to filter out unique entries

You can create a new column to AND all the appropriate comparisons to and then filter on this column

For just formatting, you could use conditional formatting to fomat based on the comparison (eg certain columns the same as the one above or below)

We would need more details to give more specific instructions on the techniques (if required)

Steve

Reply | Quote

Steve, thanks. What type of incremental details do you need?

Once I decipher the unique entries, I’d like to be able to group them (both duplicate records) together (and then again all duplicates in one part of the spreadsheet) vs. them being mixed in the other 5000 rows of data.

Reply | Quote

Basic setup and what you want as a final output (do you want a new list, do you want the current formatted, “live”, macro to sort and rearrange, etc). Is this a one time thing or are you going to want to do this yearly, monthly, weekly, daily, with every new additon,etc?

You mentioned what columns to you want to compare, but you said rows implying multiple “unique” so I am not clear what you are truly after. If nothing else, post an example workbook with a “Current” and an “after” sheet

I will add details to my suggestions.

adv filter would be used if you want to “extract” a copy of a unique entries in a column or columns

If you are only looking for formatting a new column with ANDs in conjunction with cond formatting might be appropriate This would be in the existing list

If you are trying to “group” to create subtotals:
Subtotals might be the method to group and subtotal the list (in place)

A pivot table is another way the data could be summarized into a separate table
I think I understand what you currently have, I am not sure on what you want:
New output (separate sheet or location)
Same output formatted,
Combined, what exactly.

Steve

Reply | Quote

Ok. Here is the attached file. Assume this has 5k rows of data vs. 5… If you will note, rows 2 and 3 Amount and Line Descr are identical. What I’d like to do is:
1. Identify all duplicates whereby these 2 fields are the same.
2. Isolate all duplicates such as rows 1 and 2 on this worksheet where all duplicates are together as opposed to being mixed w/the non-dup rows or move the dups to a separate workseet.

Thanks again.

Reply | Quote

Here is a manual approach:
sort the data
it could be done without sorting, but the formulas are more complicated and requires array so is much slower to calc
Select the range
data – sort
sort by “line description” then by “amount”,
You could also sort by “amount”, then by “line description”, it doesn’t matter, [you could also include the line# or the date, if you wanted to

Test for Duplicates
In your example you speak of both lines 1 and 2 as “Duplicates”. Enter the following in cell F2:

=OR(AND(E2=E1,D2=D1),AND(E2=E3,D2=D3))

I would speak of line 1 as unique and line 2 as a duplicate. I would tend to remove/separate the duplicate(s) and keep the unique 1, so 1 would stay and 2 would be “moved”. If you meant this then use in F2:

=AND(E2=E1,D2=D1)

Now autofill F2 down the columns:
Select F2,
move the mouse to the lower right corner of the cell it changes to a “+”
dbl click left mouse button when the “+” is visible

Now F2 has duplicates marked with TRUE, non-dupes with False

change formulas to values
highlight column F
Copy (ctrl-c)
edit – paste special – values

Sort the data
Select the range
data – sort
sort by “Column F” name it if desired then by whatever you want

The duplicates are all together and can be moved or deleted or whatever.

Hope this helps,

Steve

Reply | Quote

Steve, man this is slick. Thanks a bunch! Hey, mind helping me to understand the formula?

=OR(AND(E2=E1,D2=D1),AND(E2=E3,D2=D3))

I think I may know but please tell me if correct. This is saying – the 1st AND part – if e2=e1 and d2=d1 then assign true; 2nd AND is if e2=e3 and d2=d3 then assign true. The OR then says for the overall expression – if either AND statement is true then assign a TRUE to overall expression – else false.

Is this remotely correct? Also, what is the benefit of cutting and pasting – as value the true/false evaluation?

Thanks again!!!!!

Reply | Quote

You got it exactly correct.

I copied (not cut) and paste special – values, otherwise after you reSORT the data, they will be reacalculated and excel could get confused sorting when the values of the column you sort in are changing. The paste values “locks” the currrent value in the cell, putting in the cell true/false instead of a formula.

Steve

Reply | Quote

You got it exactly correct.

I copied (not cut) and paste special – values, otherwise after you reSORT the data, they will be reacalculated and excel could get confused sorting when the values of the column you sort in are changing. The paste values “locks” the currrent value in the cell, putting in the cell true/false instead of a formula.

Steve

Reply | Quote

Steve, man this is slick. Thanks a bunch! Hey, mind helping me to understand the formula?

=OR(AND(E2=E1,D2=D1),AND(E2=E3,D2=D3))

I think I may know but please tell me if correct. This is saying – the 1st AND part – if e2=e1 and d2=d1 then assign true; 2nd AND is if e2=e3 and d2=d3 then assign true. The OR then says for the overall expression – if either AND statement is true then assign a TRUE to overall expression – else false.

Is this remotely correct? Also, what is the benefit of cutting and pasting – as value the true/false evaluation?

Thanks again!!!!!

Reply | Quote

Here is a manual approach:
sort the data
it could be done without sorting, but the formulas are more complicated and requires array so is much slower to calc
Select the range
data – sort
sort by “line description” then by “amount”,
You could also sort by “amount”, then by “line description”, it doesn’t matter, [you could also include the line# or the date, if you wanted to

Test for Duplicates
In your example you speak of both lines 1 and 2 as “Duplicates”. Enter the following in cell F2:

=OR(AND(E2=E1,D2=D1),AND(E2=E3,D2=D3))

I would speak of line 1 as unique and line 2 as a duplicate. I would tend to remove/separate the duplicate(s) and keep the unique 1, so 1 would stay and 2 would be “moved”. If you meant this then use in F2:

=AND(E2=E1,D2=D1)

Now autofill F2 down the columns:
Select F2,
move the mouse to the lower right corner of the cell it changes to a “+”
dbl click left mouse button when the “+” is visible

Now F2 has duplicates marked with TRUE, non-dupes with False

change formulas to values
highlight column F
Copy (ctrl-c)
edit – paste special – values

Sort the data
Select the range
data – sort
sort by “Column F” name it if desired then by whatever you want

The duplicates are all together and can be moved or deleted or whatever.

Hope this helps,

Steve

Reply | Quote

Ok. Here is the attached file. Assume this has 5k rows of data vs. 5… If you will note, rows 2 and 3 Amount and Line Descr are identical. What I’d like to do is:
1. Identify all duplicates whereby these 2 fields are the same.
2. Isolate all duplicates such as rows 1 and 2 on this worksheet where all duplicates are together as opposed to being mixed w/the non-dup rows or move the dups to a separate workseet.

Thanks again.

Reply | Quote

Basic setup and what you want as a final output (do you want a new list, do you want the current formatted, “live”, macro to sort and rearrange, etc). Is this a one time thing or are you going to want to do this yearly, monthly, weekly, daily, with every new additon,etc?

You mentioned what columns to you want to compare, but you said rows implying multiple “unique” so I am not clear what you are truly after. If nothing else, post an example workbook with a “Current” and an “after” sheet

I will add details to my suggestions.

adv filter would be used if you want to “extract” a copy of a unique entries in a column or columns

If you are only looking for formatting a new column with ANDs in conjunction with cond formatting might be appropriate This would be in the existing list

If you are trying to “group” to create subtotals:
Subtotals might be the method to group and subtotal the list (in place)

A pivot table is another way the data could be summarized into a separate table
I think I understand what you currently have, I am not sure on what you want:
New output (separate sheet or location)
Same output formatted,
Combined, what exactly.

Steve

Reply | Quote

Steve, thanks. What type of incremental details do you need?

Once I decipher the unique entries, I’d like to be able to group them (both duplicate records) together (and then again all duplicates in one part of the spreadsheet) vs. them being mixed in the other 5000 rows of data.

Reply | Quote

Several suggestions:
You can use adv filter to filter out unique entries

You can create a new column to AND all the appropriate comparisons to and then filter on this column

For just formatting, you could use conditional formatting to fomat based on the comparison (eg certain columns the same as the one above or below)

We would need more details to give more specific instructions on the techniques (if required)

Steve

Reply | Quote