Location data to grid
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 -- fredl ----------------------------------------------------------------------- fredlo's Profile: http://www.excelforum.com/member.php...fo&userid=1453 View this thread: http://www.excelforum.com/showthread.php?threadid=26160 |
Location data to grid
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 fredlo wrote: 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. The 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 | -- fredlo ------------------------------------------------------------------------ fredlo's Profile: http://www.excelforum.com/member.php...o&userid=14535 View this thread: http://www.excelforum.com/showthread...hreadid=261601 -- Dave Peterson |
All times are GMT +1. The time now is 09:31 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com