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?
Bookmarks