Questions

Excel

+
0 Votes
Locked

Excel

josepht
How do i filter duplicates from fresh data inputed into an existing data in excel worksheet
  • +
    0 Votes
    Kingbackwards

    http://office.microsoft.com/en-us/excel/HP100739431033.aspx

    If that doesn't do what you need you'll need to explain what kind and how the data is oriented. There are dozens of ways to deal with duplicate information.

    +
    0 Votes
    sabretel

    Method 1: Merge the Import and Master data files including duplicates. Run the Advanced Data Filter specifying to copy "Unique Records Only" to a new location, then that becomes the new Master File. You may need to concatenate specific fields to a single matching key field if the records are not 100% duplicated.

    More detail here:

    http://en.allexperts.com/q/Excel-1059/merging-files-removing-duplicates.htm

    Method 2: Export original data to a Master File, build a quick VB (or other) routine to sort Master and Import File into same sequence by dup data area key, then:

    1) Read Master File
    2) Write record to New File
    3) Read and match Import File Record to New File Record just written based on dup data area key
    - If no match write Import Record to new file
    - If match, discard Import Record, read next Import Record, repeat until no match, then reset pointer so this record will be read again on next pass, and return to 1)
    4) Repeat above sequence until end of Master file.
    5) Write any remaining Import File records.

    Personally I like the first way best. Even if you have to do this repeatedly method 1 could be built into a macro.

    Method 2 would make sense if you need to build the solution into a process that just runs itself "hands off".

  • +
    0 Votes
    Kingbackwards

    http://office.microsoft.com/en-us/excel/HP100739431033.aspx

    If that doesn't do what you need you'll need to explain what kind and how the data is oriented. There are dozens of ways to deal with duplicate information.

    +
    0 Votes
    sabretel

    Method 1: Merge the Import and Master data files including duplicates. Run the Advanced Data Filter specifying to copy "Unique Records Only" to a new location, then that becomes the new Master File. You may need to concatenate specific fields to a single matching key field if the records are not 100% duplicated.

    More detail here:

    http://en.allexperts.com/q/Excel-1059/merging-files-removing-duplicates.htm

    Method 2: Export original data to a Master File, build a quick VB (or other) routine to sort Master and Import File into same sequence by dup data area key, then:

    1) Read Master File
    2) Write record to New File
    3) Read and match Import File Record to New File Record just written based on dup data area key
    - If no match write Import Record to new file
    - If match, discard Import Record, read next Import Record, repeat until no match, then reset pointer so this record will be read again on next pass, and return to 1)
    4) Repeat above sequence until end of Master file.
    5) Write any remaining Import File records.

    Personally I like the first way best. Even if you have to do this repeatedly method 1 could be built into a macro.

    Method 2 would make sense if you need to build the solution into a process that just runs itself "hands off".