Location data to grid

[fredlo]

The grid is a usual data table. As discribed it is 26 row * 26 columns
The clolumns header are letters , the row headers are numbers. Th
entry in each cell should be the product name.

example:
Input data
item 1 - a1, b2
item 2 - a2
item 3 - b1

Result:
---| a | b |
1 | item 1 | item 3 |
2 | item 2 | item 1

 

[Dave Peterson]

I'd use a small macro:

Option Explicit
Sub testme()

Dim myRng As Range
Dim myCell As Range
Dim testRng As Range
Dim mySplit As Variant
Dim iCtr As Long

Dim curWks As Worksheet
Dim newWks As Worksheet

Set curWks = Worksheets("Sheet1")
Set newWks = Worksheets.Add

With curWks
'headers in row 1 of item list sheet???
Set myRng = .Range("a2", .Cells(.Rows.Count, "A").End(xlUp))

For Each myCell In myRng.Cells
mySplit = Split97( _
Application.Substitute(myCell.Offset(0, 1).Value, " ", ""), ",")
'mySplit = Split(Replace(myCell.Offset(0, 1).Value, " ", ""), ",")
For iCtr = LBound(mySplit) To UBound(mySplit)
Set testRng = Nothing
On Error Resume Next
Set testRng = newWks.Range(mySplit(iCtr))
On Error GoTo 0
If testRng Is Nothing Then
MsgBox "Error with row: " & myCell.Row _
& " value: " & mySplit(iCtr)
Else
If testRng.Value = "" Then
testRng.Value = myCell.Value
Else
testRng.Value = testRng.Value & "," & myCell.Value
End If
End If
Next iCtr
Next myCell
End With

End Sub

'from Tom Ogilvy
Function Split97(sStr As Variant, sdelim As String) As Variant
Split97 = Evaluate("{""" & _
Application.Substitute(sStr, sdelim, """,""") & """}")
End Function

===
If you're using xl2k or higher, delete this logical line:

mySplit = Split97( _
Application.Substitute(myCell.Offset(0, 1).Value, " ", ""), ",")

And uncomment the Split() version.

And you can delete the split97 function, too.

Both Split and Replace were added in xl2k.

If you're new to macros, you may want to read David McRitchie's intro at:
http://www.mvps.org/dmcritchie/excel/getstarted.htm