+ Reply to Thread
Results 1 to 6 of 6

Remove duplicate rows based on criteria

  1. #1
    Registered User
    Join Date
    05-17-2010
    Location
    New York, USA
    MS-Off Ver
    Excel 2010
    Posts
    44

    Remove duplicate rows based on criteria

    I need help to remove duplicate rows based on the following criteria:
    Col A = Location (Destination Company Number)
    Col B = LotNo (Lot number of the specific product that I ship to many companies)
    Col E = ShipNo (Shipment number)
    Each shipment may contain items fromm different lots. Each lot may have many shipments. Col C and D may or may not be unique - they are irrelevant.
    I need to produce rows where LotNo, ShipNo & Location are unique.
    I am attaching a file. 1st tab shows input raw data, 2nd tab shows needed result. The file contains only one location. The actual file file has all locations.
    Thank you.
    Attached Files Attached Files

  2. #2
    Forum Contributor
    Join Date
    12-03-2009
    Location
    Providence RI
    MS-Off Ver
    Excel 2007, Excel 2010
    Posts
    260

    Re: Remove duplicate rows based on criteria

    For an easy fix why don't you just combine the 3 values and then remove the use remove duplictates? For example in F3 and copied down just do =CONCATENATE(A3,B3,E3) or =A3&B3&E3.

  3. #3
    Forum Contributor day92's Avatar
    Join Date
    04-20-2010
    Location
    Los Angeles
    MS-Off Ver
    Excel 360
    Posts
    600

    Re: Remove duplicate rows based on criteria

    The best way to do it with a macro is:

    - Select all the rows, including the column headers, in the list you want to filter.
    - On the Data menu, point to Filter, and then click Advanced Filter.
    - In the Advanced Filter dialog box, click Filter the list, in place.
    - Select the Unique records only check box, and then click OK.

  4. #4
    Registered User
    Join Date
    05-17-2010
    Location
    New York, USA
    MS-Off Ver
    Excel 2010
    Posts
    44

    Re: Remove duplicate rows based on criteria

    Excellent! Thank you. Now, how do I remove rows that are not displayed?

  5. #5
    Registered User
    Join Date
    05-17-2010
    Location
    New York, USA
    MS-Off Ver
    Excel 2010
    Posts
    44

    Re: Remove duplicate rows based on criteria

    These instructions are not clear. What do you mean by "use remove duplicates"? Concatenation will change the display of the important data. I can't use it.

  6. #6
    Forum Contributor day92's Avatar
    Join Date
    04-20-2010
    Location
    Los Angeles
    MS-Off Ver
    Excel 360
    Posts
    600

    Re: Remove duplicate rows based on criteria

    If you are asking me, then just copy the unique rows into a new worksheet.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1