Results 1 to 8 of 8
  1. #1
    3 Star Lounger
    Join Date
    Mar 2004
    Location
    North Carolina, USA
    Posts
    268
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Duplicate Entries (2000)

    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!

  2. #2
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Duplicate Entries (2000)

    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

  3. #3
    3 Star Lounger
    Join Date
    Mar 2004
    Location
    North Carolina, USA
    Posts
    268
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Duplicate Entries (2000)

    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.

  4. #4
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Duplicate Entries (2000)

    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

  5. #5
    3 Star Lounger
    Join Date
    Mar 2004
    Location
    North Carolina, USA
    Posts
    268
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Duplicate Entries (2000)

    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.

  6. #6
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Duplicate Entries (2000)

    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:
    <pre>=OR(AND(E2=E1,D2=D1),AND(E2=E3,D2=D3))</pre>


    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:
    <pre>=AND(E2=E1,D2=D1)</pre>



    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

  7. #7
    3 Star Lounger
    Join Date
    Mar 2004
    Location
    North Carolina, USA
    Posts
    268
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Duplicate Entries (2000)

    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!!!!!

  8. #8
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Duplicate Entries (2000)

    <img src=/S/bullseye.gif border=0 alt=bullseye width=45 height=15>You got it exactly correct. <img src=/S/thumbup.gif border=0 alt=thumbup width=15 height=15>

    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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •