Hi HTH,
I took your suggested edits, and came up with the following. Based on the
info in Avveekar's reply, you should be able to make sense of why such
extensive adds.

Let me know your thoughts, when I run the below script in ws2, all I get is
a 1 line copy of row 512 from ws1.

Sub reformatParts()

Dim ws1 As Worksheet, ws2 As Worksheet
Dim lastrow As Long, r As Long, rr As Long
Dim v As Variant

Set ws1 = Worksheets("sheet1")
Set ws2 = Worksheets("sheet2")

rr = 2

With ws1
lastrow = .Cells(Rows.Count, "A").End(xlUp).Row
.Range("a1:o1").Copy ws2.Range("a1")
For r = 2 To lastrow
v = Split(.Cells(r, 15), ",")
For i = LBound(v) To UBound(v)
ws2.Cells(rr, 1) = .Cells(r, 1)
ws2.Cells(rr, 2) = .Cells(r, 2)
ws2.Cells(rr, 3) = .Cells(r, 3)
ws2.Cells(rr, 4) = .Cells(r, 4)
ws2.Cells(rr, 5) = .Cells(r, 5)
ws2.Cells(rr, 6) = .Cells(r, 6)
ws2.Cells(rr, 7) = .Cells(r, 7)
ws2.Cells(rr, 8) = .Cells(r, 8)
ws2.Cells(rr, 9) = .Cells(r, 9)
ws2.Cells(rr, 10) = .Cells(r, 10)
ws2.Cells(rr, 11) = .Cells(r, 11)
ws2.Cells(rr, 12) = .Cells(r, 12)
ws2.Cells(rr, 13) = .Cells(r, 13)
ws2.Cells(rr, 14) = .Cells(r, 14)
ws2.Cells(rr, 15) = Trim(v(i))
rr = rr + 1
Next i
Next r
End With

End Sub



"Toppers" wrote:

Hi,

v = Split(.Cells(r, 9), ",") <=== Model in column 9

ws2.Cells(rr, 1) = .Cells(r, 1) <=== Change =.Cells(r,?) if part # not in
col 1 (A)
ws2.Cells(rr, 2) = .Cells(r, 2) <=== Change =.Cells(r,?) if Description not
in col 2 (B)


HTH
"DA@PD" wrote:

Hi toppers,

I think your code looks great! I have only one delemma though, my example I
gave was simplified, the actual spreadsheet has 8 columns of data (similar to
part #'s and description), and the model column is not until column P (the
9th column) what changes to your script would make this work? I'm sorry, I
figured to edit a 3 column script to go to the 9th column would not be that
difficult, but I can't seem to get it to work...


"Toppers" wrote:

Hi,
Try this: Change worksheets etc as required

Sub reformatParts()

Dim ws1 As Worksheet, ws2 As Worksheet
Dim lastrow As Long, r As Long, rr As Long
Dim v As Variant

Set ws1 = Worksheets("sheet1")
Set ws2 = Worksheets("sheet2")

rr = 2

With ws1
lastrow = .Cells(Rows.Count, "A").End(xlUp).Row
.Range("a1:c1").Copy ws2.Range("a1")
For r = 2 To lastrow
v = Split(.Cells(r, 3), ",")
For i = LBound(v) To UBound(v)
ws2.Cells(rr, 1) = .Cells(r, 1)
ws2.Cells(rr, 2) = .Cells(r, 2)
ws2.Cells(rr, 3) = Trim(v(i))
rr = rr + 1
Next i
Next r
End With

End Sub


"DA@PD" wrote:

Hi Experts!

I have been working on an inventory project where I am trying to match parts
to the model of car they fit. I had built a spreadsheet in the following
format:

Part # Description Model
7V4325 Bolt Cabrio, Golf, GTI, Jetta
9G3263 Gasket Barretta, Cavalier, Corsica

Of course after I'm sent out to compile a huge list of about 1,000 lines of
parts, and compile litterally thousands of models the parts fit. The IS
department tells me I have to put the lists in the following format:

7V4325 Bolt Cabrio
7V4325 Bolt Golf
7V4325 Bolt GTI
7V4325 Bolt Jetta
9G3263 Gasket Barretta
9G3263 Gasket Cavalier
9G3263 Gasket Corsica

As you can see, this could become quite frustrating, as it took me a month
to build the list, and could take me another two weeks to seperate out each
row for some of the parts which fit over 20 vehicles. Is it possible to
write a script or something that could tranform the data that is in the upper
format to the lower format?

Any help would be GREATLY APPRECIATED!