Rows to Columns - Excel 1

[stnkyminky]

Data formatted in the spreadsheet is as follows

1 ABC EU 999,998,997,996
1 ABC T 999,998,997,996
1 ABC CI 999,998,997,996

In need it in this format to import into SQL Server

1 ABC EU 999
1 ABC EU 998
1 ABC EU 997
etc.......

If duplicating 1 ABC EU is not possible but splitting the last column into rows is acceptable

How do I convert the last column and create rows with the data? Scott
Programmer Analyst
<{{><

 

[ETID]

What columns will make up the &quot;KEY&quot; in the new table?

 

[stnkyminky]

The first column (1) will be the PK. Scott
Programmer Analyst
<{{><

 

[stnkyminky]

sorry wasn't thinking. The PK will be autonumbered but the first column will be used in joins Scott
Programmer Analyst
<{{><

 

[xlbo]

1st step - format all your 999,998,997,996 as text
then use text to columns with a delimeter of &quot;,&quot;
you should now have each 3 digit figure in seperate cells
now run this macro:
Sub reFormat()
Dim valArr()
Pos = 0
For r = 1 To Range(&quot;A65536&quot.End(xlUp).Row
ReDim valArr(Range(&quot;D&quot; & r).End(xlToRight).Column - 3)
For c = 4 To Range(&quot;D&quot; & r).End(xlToRight).Column

valArr(c - 3) = Cells(r, c).Value
Next c
mMax = UBound(valArr())

Sheets(&quot;Sheet1&quot.Range(&quot;A&quot; & r & &quot;:C&quot; & r).Copy Destination:=Sheets(&quot;Sheet2&quot.Range(&quot;A&quot; & Pos + 1 & &quot;:C&quot; & Pos + mMax)
For x = 1 To UBound(valArr())
Sheets(&quot;Sheet2&quot.Range(&quot;D&quot; & Pos + x).Value = valArr(x)
Next x
Pos = Pos + mMax
Next r
End Sub

It puts the new formatted data onto Sheet2, assuming that it is on Sheet1 to start with in columns A,B,C for the initial data and columns D onwards for the comma seperated numbers
HTH Rgds
~Geoff~