+ Reply to Thread
Results 1 to 3 of 3

Remove Duplicates While Leaving Blanks

  1. #1
    Registered User
    Join Date
    04-04-2014
    Location
    Dallas, Texas
    MS-Off Ver
    Excel 2010
    Posts
    6

    Remove Duplicates While Leaving Blanks

    I have exported data from a database where the resultant csv file (excel.csv) places all of the serial numbers of each part number in the same column as the part number. I filtered out approximately 50,000 records by a method that now leaves me with duplicate data in cells. I had intended to paste in some data, but the result is to jumbled to make out. Instead I attached an excerpt from the spreadsheet as an example.
    I am trying to remove duplicate rows in columns B thru F; column A is okay as it is. For the first three part numbers (Col A), 309620-B23, 406740-B21, and 410916-B21 I have removed the duplicate rows using Data> Remove Duplicates (Excel 2010). "Example 1" below shows the first part number, 309620-B23, and the Description, Location, etc data in columns B thru F. I simply selected Column B2 and dragged across and down to F4, then used Data > Remove Duplicates to delete the duplicate cell values in columns B thru F. In the attachment you will see the same method was used for the next two part numbers. From the attachment you may also see that column A, containing both part and serial number info, necessitates that the FIRST entry of the duplicate cells is the cell value that needs to remain, while the subsequent duplicates are deleted. "Example 2" below show the result that I need after the duplicates are removed.

    Example 1:

    A B C D E F
    1 PartNumber PartDescription Location Qty UOM Cost
    2 309620-B23 HP EVA5000 2C2D-C 60Hz Dual Controller Array Dallas Lab-BLC 7000 RACK 1 ea 500
    3 Serial Number HP EVA5000 2C2D-C 60Hz Dual Controller Array Dallas Lab-BLC 7000 RACK 1 ea 500
    4 4316JWKRA009 HP EVA5000 2C2D-C 60Hz Dual Controller Array Dallas Lab-BLC 7000 RACK 1 ea 500


    Example 2:

    A B C D E F
    1 PartNumber PartDescription Location Qty UOM Cost
    2 309620-B23 HP EVA5000 2C2D-C 60Hz Dual Controller Array Dallas Lab-BLC 7000 RACK 1 ea 500
    3 Serial Number
    4 4316JWKRA009

    In many cases there are more that one serial number because the database contains more that one of that particular part number and each one is serialized.
    "Example 3" below is the last part number shown on the attached spreadsheet. The example below shows how I need the result to be after the duplicates are removed, that is, the first cell value must remain while the subsequent six duplicate values, in the example of this part number, (B15:F15 thru B20:F20) must be deleted.

    Example 3:

    A B C D E F
    1 PartNumber PartDescription Location Qty UOM Cost

    14 416003-001 HP c-Class Blade SUV Cable Dallas Lab-BLC 7000 RACK 5 ea 500
    15 Serial Number
    16 134756
    17 TRK#201564-001
    18 TRK#201564-002
    19 TRK#201564-003
    20 TRK#201564-004

    Most examples I have found in my search on the web are methods using formulas or macros, but they seem to always delete the first duplicate cell values while leaving the last cell value. Also, the Data > Remove Duplicates method can only be used on one part number at a time, otherwise all the blank rows(resulting from removing the duplicates) are shifted up on the spreadsheet.
    I need a method of removing the duplicates, keeping the first cell value (PartDescription, Location, etc, on the same row as the PartNumber) and removing the duplicates thereby leaving blank cells without shifting the blank cells up. The spreadsheet is about 10,000 rows so using the Data > Remove Duplicates method is not viable.
    I am somewhat familiar with the use of formulas, but may need a step-by-step explanation to implement the procedure.
    I hope I was able to get the attachment uploaded.
    Can someone help me out?
    Attached Files Attached Files

  2. #2
    Forum Expert
    Join Date
    05-30-2012
    Location
    The Netherlands
    MS-Off Ver
    Excel 2016
    Posts
    13,764

    Re: Remove Duplicates While Leaving Blanks

    show us in the file the expected result (manualy).
    Notice my main language is not English.

    I appreciate it, if you reply on my solution.

    If you are satisfied with the solution, please mark the question solved.

    You can add reputation by clicking on the star * add reputation.

  3. #3
    Registered User
    Join Date
    04-04-2014
    Location
    Dallas, Texas
    MS-Off Ver
    Excel 2010
    Posts
    6

    Re: Remove Duplicates While Leaving Blanks

    The included Inventory.xlsx file does show the expected result. The first three part numbers (Col A) are followed in Col A by "Serial Number" then the actual serial number for that part number. Columns B3 & B4, B6 & B7, B9 & B10 thru F3 & F4, F6 & F7, F9 & F10 are all now showing blank. This is the expected result for all of the part numbers listed in the entire spreadsheet. This was achieved manually by selecting each of the first three part numbers individually, ie, for the first part number (309620-B23), Columns B3 & B4 thru Columns F3 & F4 , then selecting "Remove Duplicates" from the Data Menu. The same was accomplished manually for the next two part numbers, 406740-B21 and 410916-B21. I purposely removed the duplicate rows (B thru F) as an example of what I need the rest of the spreadsheet to look like. The other rows in the example (rows 11 thru 20, representing two more part numbers and associated serial numbers) look like the rest of the spreadsheet, that is, with data duplicated (rows B thru F) for each part number. The first four part numbers (in the example), as well as many others in the spreadsheet (database) have only one in stock, thus only one serial number. However, many part numbers in the spreadsheet (database) have more than one serial number as shown in the Inventory.xlsx example for part number 416003-001. It has five serial numbers following it indicating there are five parts in stock in the database. The PartDescription, Location, Qty, UOM, & Cost, (Columns B thru F) all have duplicate info for each part number. I want that info only on the row that contains the part number itself. In other words, Row B14 thru F14 are info that is pertinent to part number 413996-001 in Row A14. The rows below Row 14, all the way thru Row 20, are duplicated on Columns B thru F and those cells need to be blank. However, the spreadsheet is about 10,000 rows and I cannot use the "Remove Duplicates" function for each individual part number since it would take too long. I need a way to keep the first of Columns B thru F corresponding on the row that displays the part number and make the other duplicate cells blank in the subsequent rows for each part number. I know it seems complicated and I hope I am explaining it adequately, although a bit verbose. Thank you for you quick response earlier.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. Leaving blanks in pivottable
    By stephme55 in forum Excel Charting & Pivots
    Replies: 3
    Last Post: 01-27-2016, 04:05 AM
  2. Remove Duplicates in column and remove blanks
    By geliedee in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 07-13-2015, 07:25 AM
  3. Remove 'duplicates' to leaving lowest value
    By alseeon in forum Excel General
    Replies: 9
    Last Post: 06-29-2015, 06:22 AM
  4. [SOLVED] Remove Blanks & Duplicates using only formulas
    By johnhe in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 04-30-2015, 12:18 PM
  5. Remove duplicates - leaving most recent dated line
    By Nikki Fox in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 02-04-2015, 06:36 AM
  6. [SOLVED] VBA Remove Duplicates From Column & Ignore Blanks
    By hobbiton73 in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 02-26-2014, 04:56 AM
  7. [SOLVED] Remove duplicates and blanks from Combo Box Excel Userform?
    By jmotuk in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 08-13-2013, 12:22 PM

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