Need formula to split data from 2 columns into multiple columns

A

andrew.mead

This is hard to visualize, but basically I have a spreadsheet with a
couple of Columns. Column A can contain duplicate part numbers and
Column B contains a person.

A B
12345 Billy
12346 John
12346 Peter
12346 James
12347 Andy

I can't really use a vlookup, but I'm interested in somehow splitting
the data up across multiple columns (by the use of a formula) so that
column A no longer contains multiple part numbers. New columns would
be created to accommodate the other entries in Column B. (i.e. I only
want one cell that contains "12346" and columns C & D would now
contain Billy and James, respectively)

So now I'd have:

A B C D
12345 Billy
12346 John Peter James
12347 Andy
 
A

andrew.mead

The i.e. above should read: (i.e. I only want one cell that contains
"12346" and columns C and D would now contain PETER and James,
respectively).
 
J

JE McGimpsey

This is hard to visualize, but basically I have a spreadsheet with a
couple of Columns. Column A can contain duplicate part numbers and
Column B contains a person.

A B
12345 Billy
12346 John
12346 Peter
12346 James
12347 Andy

I can't really use a vlookup, but I'm interested in somehow splitting
the data up across multiple columns (by the use of a formula) so that
column A no longer contains multiple part numbers. New columns would
be created to accommodate the other entries in Column B. (i.e. I only
want one cell that contains "12346" and columns C & D would now
contain Billy and James, respectively)

So now I'd have:

A B C D
12345 Billy
12346 John Peter James
12347 Andy
Click to expand...

This will likely require a macro rather than formulae, unless the
entries are more structured than you indicate (e.g., there are three
names for every part number). You don't say whether the part numbers
will always be in sorted order, or whether they may be duplicate names
for a single part number (and what should be done about it), so this
would be winging it:

Public Sub CombineNamesForPartNumbers()
Dim i As Long
Dim nRow As Long
On Error Resume Next
With ActiveSheet
For i = .Cells(.Rows.Count, 1).End(xlUp).Row To 2 Step -1
nRow = Application.Match(.Cells(i, 1).Value, _
.Range(.Cells(1, 1), .Cells(i - 1, 1)), False)
If Err.Number = 0 Then
.Cells(nRow, .Columns.Count).End(xlToLeft).Offset( _
0, 1).Value = .Cells(i, 2).Value
.Cells(i, 1).EntireRow.Delete
Else
Err.Clear
End If
Next i
End With
On Error GoTo 0
End Sub
 
Top