Results 1 to 3 of 3
  1. #1
    2 Star Lounger
    Join Date
    Mar 2003
    Thanked 0 Times in 0 Posts

    Finding duplicate records (XP)

    Hi all. Some help please.
    Every month I inherit a spreadsheet I have to work with. It is compiled by people who are not very computer literate and at times contains many errors. I am required to find these errors and send the repaired spreadsheet onto someone else.
    The spreadsheet is a simple spreadsheet using colums A through to W. What I need to do is to find the duplicate records, identify which of those duplicate records contains a value of $60.00 in a certain column then paste those duplicate records with that value into a new worksheet.
    The relevant information is:

    1. Duplicate records can be identified by a eight digit Invoice Number which is in Column D,
    2. The column which would contain the $60.00 value is Column I,
    3. Column D does contain blank fields which should be ignored,
    4. Rows 1 - 7 are headings which should be ignored, and
    5. The new worksheet should be added to the current document.

    Can the above be achieved with a macro? What is the best way of solving this problem? Any ideas appreciated.

    Thanks in advance.


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

    Re: Finding duplicate records (XP)

    Advanced filter can copy "unique records" from the dataset where Col D is NOT blank and Col I =60. Then you can paste the filtered data to the proper location in the current document. You could also do all the coding in VB that adv filter does, but I would think that the filter-in-place would be easier to use than figuring how to tell excel to do it


  3. #3
    Uranium Lounger
    Join Date
    Jan 2001
    South Carolina, USA
    Thanked 0 Times in 0 Posts

    Re: Finding duplicate records (XP)

    This can be done with a macro, and that would probably be the easiest if you have to do this often. Thanks for the good description of the problem, it makes answering much easier. However, there are still a couple of questions that need to be answered before writing the macro:

    1- Can the original sheet be sorted on column D (this would make the macro easier), or must it be left in the original order?

    2- If two rows are found where column D is equal and where column I contains 60, do you want both rows copied to the new sheet or just one? If just one, which one?

    3- If more than two rows are found with column D equal and column I contains 60, what do you want copied to the new sheet?

    4- Do you want any indication in the new sheet of where on the original sheet the data came from?
    Legare Coleman

Posting Permissions

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