Collapsing Repeated Data

**teddybouch** · 07-09-2008, 11:08 AM

I am working with a spreadsheet with internal part numbers in the leftmost column, vendors as the headers, and the associated vendor part number in the corresponding cell. I think that makes sense the way I put it, but I think y'all get the idea. The problem is that a number of the internal part numbers are repeated because we get the item from multiple vendors, and the source for this data repeated them. My job is to take the repeats and collapse them into a single column.

For example:

Part No. | Vendor 1 | Vendor 2 | Vendor 3
ABC123 | MSN521 | |
ABC123 | | | VIN654
ABC124 | | NBC234 |
ABC125 | MSN522 | |
ABC125 | | NBC235 |

Becomes:

Part No. | Vendor 1 | Vendor 2 | Vendor 3
ABC123 | MSN521 | | VIN654
ABC124 | | NBC234 |
ABC125 | MSN522 | NBC235 |

*EDIT* I just discovered that my spacing doesn't display right in the thread. The pipes are supposed to delineate the columns, if that helps.

Right now, I'm trying to do it one at a time, but there are over 40,000 items in the database and it will take forever. Does anyone know of a way to automate this? I'm still a bit of an Excel greenhorn. Thanks!

**TheNorm** · 07-09-2008, 11:48 AM

You could try making a Pivot Table.

**teddybouch** · 07-09-2008, 01:10 PM

Thanks for the tip! I had never looked at Pivot tables before (as I said, still new to Excel beyond SUM and the like), and I'm halfway there. Unfortunately, the entries that it is giving me in the table values are the count of how many vendor numbers exist for a given vendor and part number. Any suggestions on how to get it to actually display the vendor numbers? Thanks again.

**TheNorm** · 07-10-2008, 08:57 AM

I tried a Pivot Table for your example after posting the suggestion, and as you found, couldn't get it to work. In Access you can use "max" with text, but for some reason in an Excel Pivot Table "max" gives you zero with text.

Sorry to have misled you. Perhaps someone else has a suggestion?

**teddybouch** · 07-10-2008, 12:16 PM

No worries - I was glad to use pivot tables anyway, and it gave me some useful information. The approach that I'm taking now is using formulas. Namely,

IF(INDEX(Database, MATCH($A2, instruments!$B$1:$B$43483, 0), 3) = Sheet2!B$1, INDEX(Database, MATCH($A2, instruments!$B$1:$B$43483, 0), 1), " ")

Where Database is the whole sheet of original data, with the vendor part number in the first column, our part number in the second column, and the vendor name in the third column. This formula goes into the fields that would have been the data in the pivot table - I simply copied the column and row headers. This means that vendor names are in the topmost row (all cells ending in 1) and our part numbers in the leftmost column (all cells beginning with A).

As I understand it, the first match statement returns for a given our part number what row that number is in. If the vendor name in that row is equal to the column header, then it populates the cell with the vendor's part number. Otherwise, it fills with an empty string.

The problem that I am now running into is that this will only work for the first time that it finds a given vendor-our part number combination. Don't ask me why, but some vendors have multiple different part numbers that correspond to our one. What I would like to do is to create additional columns for the vendors for which this is true, then modify the above formula to return the second, third, fourth, etc. result rather than the first. The pivot table tells me the number of times I have to do this for each vendor, so I know how many columns to do already.

Thoughts? Thanks for all the help so far!

**teddybouch** · 07-11-2008, 09:09 AM

Okay - progress has been made. A fine gentleman in the IT department threw together a VB script that successfully populated the interior of what would have been the Pivot Table (if the tool had worked as desired) with the list of all part numbers that correspond to that vendor and internal part number. Here's his code:

Sub buildTable()
Dim instrumentsSheet As Worksheet
Dim sheet1Sheet As Worksheet
Dim targetCell As Range

Application.ScreenUpdating = False
Set instrumentsSheet = ThisWorkbook.Sheets("instruments")
Set sheet1Sheet = ThisWorkbook.Sheets("Sheet1")
On Error GoTo what

For i = 2 To 43484
If WorksheetFunction.IsNA(instrumentsSheet.Cells(i, 4).Value) = False And WorksheetFunction.IsNA(instrumentsSheet.Cells(i, 5).Value) = False Then
Set targetCell = sheet1Sheet.Cells(instrumentsSheet.Cells(i, 4).Value, instrumentsSheet.Cells(i, 5).Value)
If Len(targetCell.Value) = 0 Then
targetCell.Value = instrumentsSheet.Cells(i, 1).Value
Else
targetCell.Value = targetCell.Value & ", " & instrumentsSheet.Cells(i, 1).Value
End If

End If
If i Mod 100 = 0 Then Debug.Print i

Next i

Application.ScreenUpdating = True
what:
Debug.Print i

End Sub

Note that the sheet "instruments" contains the following columns: vendor part number, internal part number, vendor name, row in the resulting table corresponding to the internal part number, column in the resulting table corresponding to the vendor name.

Now, if I am to do exactly what was asked of me, I now need to modify this code to split the cells with multiple entries into new columns. For instance,

------------| VendorName
Internal P/N | MJK2332, VEN87509

Becomes

------------| VendorName1 | VendorName2
Internal P/N | __MJK2332__ | VEN87509

Does anyone know how to do this? I'm not really well-versed in VBScript for Excel. Are there cheat sheets anywhere of syntax that could help me learn what commands exist?

Collapsing Repeated Data

LinkBack

Thread Tools

Rate This Thread

Display

Hybrid View

Collapsing Repeated Data

Thread Information

Users Browsing this Thread

Bookmarks

Bookmarks

Posting Permissions