Collapsing Repeated Data

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!

Alright, I'm almost there and every part of it has worked at some point. However, the constantly finicky part of code is that for inserting a column. Initially, I used this:

targetCell.Select
MsgBox "Target Cell Selected", 0, "Got here"
Selection.EntireColumn.Insert
MsgBox "Column Inserted", 0, "Got here"

That worked great for a little while, then after working on some other parts of code, it changed its mind and for some reason decided not to like that syntax. It would select the correct cell, but wouldn't do the column insertion. In desperation, I tried a little re-ordering.

targetCell.EntireColumn.Select
MsgBox "Target Cell Selected", 0, "Got here"
Selection.Insert
MsgBox "Column Inserted", 0, "Got here"

This inexplicably worked, but I wasn't asking any questions. I kept working, and now it doesn't like either form, nor will it take

targetCell.EntireColumn.Insert

Here is all the code in its current form together:

Sub buildTable()
    Dim instrumentsSheet As Worksheet
    Dim sheet1Sheet As Worksheet
    Dim pivotSheet As Worksheet
    Dim targetCell As Range
    
    Application.ScreenUpdating = False
    Set instrumentsSheet = ThisWorkbook.Sheets("instruments")
    Set sheet1Sheet = ThisWorkbook.Sheets("Sheet1")
    Set pivotSheet = ThisWorkbook.Sheets("pivot")
    On Error GoTo what
    
    'Delete the existing data in all columns so that the macro can be run without
    '   creating extra columns accidentally
    sheet1Sheet.Range("B1:IV8155").Select
    Selection.Value = ""
    
    'Replace vendor headers
    Set targetCell = sheet1Sheet.Cells(1, 2)
    pivotSheet.Range("B1:CX1").Copy Destination:=targetCell
    
    'Go through and create all needed columns for every vendor (2 through 102)
    For i = 2 To 102
    
        'Parse through the maximum number of multiple entries for each vendor
        Vendor = pivotSheet.Cells(1, i).Value
        VendorMax = pivotSheet.Cells(2, i).Value
        
        'Create that number of columns for the vendor
        If (VendorMax > 1) Then
            For j = 1 To (VendorMax - 1)
        
                'Select the first row in that column
                VendorCol = Application.Match(Vendor, sheet1Sheet.Range("A1:IV1"))
                Set targetCell = sheet1Sheet.Cells(1, VendorCol + j)
            
                'Insert the column
                targetCell.EntireColumn.Insert
                MsgBox "Column selected", 0, "Got here"
                'Selection.Insert
                MsgBox "Column inserted", 0, "Got here"
                
                'Title the column
                Set targetCell = sheet1Sheet.Cells(1, VendorCol + j)
                targetCell.Value = Vendor & (j + 1)
            
            Next j
        End If
    Next i
    
    'Go through the vendor item numbers (2 to 38346)
    For i = 2 To 38346
    
        'Get the row that the internal part number is in
        PartRow = instrumentsSheet.Cells(i, 4).Value
        MsgBox PartRow, 0, "PartRow"
        
        'For each vendor item number, make sure that an internal part number exists
        If WorksheetFunction.IsNA(PartRow) = False Then
            
            'Set the target cell as the leftmost cell for the vendor in the internal part
            '   number row
            Vendor = instrumentsSheet.Cells(i, 3).Value
            'MsgBox Vendor, 0, "Vendor"
            VendorCol = Application.Match(Vendor, sheet1Sheet.Range("A1:IV1"))
            'MsgBox VendorCol, 0, "VendorCol"
            Set targetCell = sheet1Sheet.Cells(PartRow, VendorCol)
            
            'While the target cell has something in it, move the target cell one to the right
            Do While ((Len(targetCell.Value) = 0) = False)
                VendorCol = VendorCol + 1
                Set targetCell = sheet1Sheet.Cells(PartRow, VendorCol)
            Loop
            
            'Put the vendor part number in the target cell
            targetCell.Value = instrumentsSheet.Cells(i, 1).Value
            
        End If
        If i Mod 100 = 0 Then Debug.Print i

    Next i

    Application.ScreenUpdating = True
what:
    Debug.Print i
    
End Sub

If anyone has any ideas as to what the deuce is going on here, I would really appreciate some help. I just learned to program Excel macros yesterday, and this problem is really confusing the heck out of me.

Alright, apparently it's some odd formatting error that was caused by my clearing all of the cells in the first portion by setting their value to empty string. By using the Delete method instead, it works as desired.