Can this can be done???

[DA@PD]

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!

[Toppers]

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!

[Tim Williams]

If your data is in sheet1 columns A-C and your reformatted data is to be
placed in sheet2

Sub reformat()

Dim r As Long, x As Integer
Dim arr As Variant
Dim s As Worksheet
Dim c As Range

r = 2
Set s = ThisWorkbook.Sheets("Sheet2")


With ThisWorkbook.Sheets("Sheet1")

'adjust range to suit
For Each c In .Range("A2:A2000")

arr = Split(c.Offset(0, 2).Value, ",")
For x = LBound(arr) To UBound(arr)
c.Resize(1, 2).Copy s.Cells(r, 1)
s.Cells(r, 3).Value = Trim(arr(x))
r = r + 1
Next x


Next c


End With
End Sub



--
Tim Williams
Palo Alto, CA


"DA@PD" <[email protected]> wrote in message
news:[email protected]...
> 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!

[RB Smissaert]

Something like this will do it:

Sub MakeList()

Dim LR As Long
Dim i As Long
Dim n As Long
Dim x As Long
Dim lCount As Long
Dim coll As Collection
Dim arr1
Dim arr2
Dim arr3

LR = Cells(1).End(xlDown).Row

arr1 = Range(Cells(2, 1), Cells(LR, 3))
Set coll = New Collection

For i = 1 To UBound(arr1)
arr2 = Split(arr1(i, 3), ",")
lCount = lCount + UBound(arr2) + 1
coll.Add arr2
Next

ReDim arr3(1 To lCount, 1 To 3)

For i = 1 To coll.Count
For n = 0 To UBound(coll(i))
x = x + 1
arr3(x, 1) = arr1(i, 1)
arr3(x, 2) = arr1(i, 2)
arr3(x, 3) = Trim(coll(i)(n))
Next
Next

Sheets(2).Activate

Range(Cells(2, 1), Cells(lCount + 1, 3)) = arr3

End Sub


RBS

"DA@PD" <[email protected]> wrote in message
news:[email protected]...
> 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!

[Edd]

HI:

Where the scripts given to you are great (and I am all for scripting), it
may be simpler and quicker if you simply copied and renamed. For instance,
for Bolts category, make four groups (because you have four Models). So copy
this category three time, so that you have four sets of identical data, then
simply rename each of the group to each model name. Hence, group one: Cabrio;
group two: Golf, etc.

Might this be simpler than typing code and setting it all up within VBA?

Ed.


"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!

[RB Smissaert]

> typing code

? Copy and paste from these mailings is all that is needed. Much easier I
think.

RBS

"Edd" <[email protected]> wrote in message
news:[email protected]...
> HI:
>
> Where the scripts given to you are great (and I am all for scripting), it
> may be simpler and quicker if you simply copied and renamed. For instance,
> for Bolts category, make four groups (because you have four Models). So
> copy
> this category three time, so that you have four sets of identical data,
> then
> simply rename each of the group to each model name. Hence, group one:
> Cabrio;
> group two: Golf, etc.
>
> Might this be simpler than typing code and setting it all up within VBA?
>
> Ed.
>
>
> "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!

[DA@PD]

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!

[DA@PD]

Hi Tim,

It looks as if your script is short and effective, execpt I actually
simplified my example to make it easy to understand, there are actually 8
columns of descriptive data (like part number & description), and the models
are actually in column P (the 9th column). What changes to your script would
allow this to work? I thought it would be easy to modify the script to go to
column p, but I don't understand VBA enough to accomplish this modification.

Thanks!

David

"Tim Williams" wrote:

> If your data is in sheet1 columns A-C and your reformatted data is to be
> placed in sheet2
>
> Sub reformat()
>
> Dim r As Long, x As Integer
> Dim arr As Variant
> Dim s As Worksheet
> Dim c As Range
>
> r = 2
> Set s = ThisWorkbook.Sheets("Sheet2")
>
>
> With ThisWorkbook.Sheets("Sheet1")
>
> 'adjust range to suit
> For Each c In .Range("A2:A2000")
>
> arr = Split(c.Offset(0, 2).Value, ",")
> For x = LBound(arr) To UBound(arr)
> c.Resize(1, 2).Copy s.Cells(r, 1)
> s.Cells(r, 3).Value = Trim(arr(x))
> r = r + 1
> Next x
>
>
> Next c
>
>
> End With
> End Sub
>
>
>
> --
> Tim Williams
> Palo Alto, CA
>
>
> "DA@PD" <[email protected]> wrote in message
> news:[email protected]...
> > 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!
>
>
>

[Toppers]

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!

[avveerkar]

Could finally get what you wanted? If not and still want some help why don't you give exact layout of your sheet eg.

Row1 Headers
Col A PartNO
Col B Desc
Col C ...
Col P Model Names
etc
You said model starts only from col P but then you also said it is col 9 which is confusing. And in your desc you have shown models separated by "," and we are all assuming that all model names (for a row of a part number ) are in one cell separated by ",". Is that true? eg we assume a2 has "7V4325", B2 has " Bolt " and C2 has " Cabrio, Golf, GTI ". Is that what you meant? Or you meant model names start from Col C and thus C2 has "Cabrio", D2 has "Golf" and E2 has "GTI"? Also would you require help to know as to where to copy the procedure and how to run it? I know it is very basic but I got stuck here when I started with VBA programming.

A V Veerkar

[DA@PD]

Good point Avveerkar,

The Column Headers are as follows:
Column A = Part #
Column B = Part Description
Column C = Item Name Code
Column D = Group Class
Column E = Stock Class
Columns F-J Have Headers, but the rest of columns are left blank (I had them
hidden)
Column K = Stock Type
Column L = Unit of Issue
Column M = Preference Code (they will all be 1)
Column N = Equipment No (Model)
Column O = APL Type (All rows are blank)

Row 2 looks as follows:
Col A:1342570
Col B: WRENCH-SPANNER
Col C: C0348
Col D:5110
Col E:R
Col K:L
Col L:EA
Col M:1
Col N: 785B, 789, 789B

Row 3 looks like this
Col A:7X0562
Col B:WASHER HARD
Col C:03476
Col D:5310
Col E:R
Col K:L
Col L:EA
Col M:1
Col N:D9R, D9N, D8R, D7HLGP, D6HLGP, D11R, D11N, D10R, D10N, 994, 922G,
992D, 988G, 988B, 980G, 980C, 973, 966F, 950F, 14H, 16G, 16H, 325BL, 330BL,
345BL, 446D, 613C, 621E, 631E, 637E, 637G, 769D, 773B, 824C, 834B
If you cut and pasted each of the headers into the first row on excel, and
then put the info following the ":"'s for each column in my row descriptions,
you would have a good idea of what my worksheet looks like. Sorry for the
confusion. If I could cut & paste rows of an excel worksheet intact on these
pages it would solve alot of communications errors I think.

I hope this helps.

"avveerkar" wrote:

>
> Could finally get what you wanted? If not and still want some help why
> don't you give exact layout of your sheet eg.
>
> Row1 Headers
> Col A PartNO
> Col B Desc
> Col C ...
> Col P Model Names
> etc
> You said model starts only from col P but then you also said it is col
> 9 which is confusing. And in your desc you have shown models separated
> by "," and we are all assuming that all model names (for a row of a
> part number ) are in one cell separated by ",". Is that true? eg we
> assume a2 has "7V4325", B2 has " Bolt " and C2 has " Cabrio, Golf, GTI
> ". Is that what you meant? Or you meant model names start from Col C
> and thus C2 has "Cabrio", D2 has "Golf" and E2 has "GTI"? Also would
> you require help to know as to where to copy the procedure and how to
> run it? I know it is very basic but I got stuck here when I started
> with VBA programming.
>
> A V Veerkar
>
>
> --
> avveerkar
> ------------------------------------------------------------------------
> avveerkar's Profile: http://www.excelforum.com/member.php...o&userid=30338
> View this thread: http://www.excelforum.com/showthread...hreadid=510236
>
>

[DA@PD]

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!

[Toppers]

Col N is 14 not 15!

"DA@PD" wrote:

> 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!

[Toppers]

Amended code:

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, 14), ",")
For i = LBound(v) To UBound(v)
.Cells(r, 1).Resize(1, 13).Copy ws2.Cells(rr, 1)
ws2.Cells(rr, 14) = Trim(v(i))
rr = rr + 1
Next i
Next r
End With

End Sub


"DA@PD" wrote:

> Good point Avveerkar,
>
> The Column Headers are as follows:
> Column A = Part #
> Column B = Part Description
> Column C = Item Name Code
> Column D = Group Class
> Column E = Stock Class
> Columns F-J Have Headers, but the rest of columns are left blank (I had them
> hidden)
> Column K = Stock Type
> Column L = Unit of Issue
> Column M = Preference Code (they will all be 1)
> Column N = Equipment No (Model)
> Column O = APL Type (All rows are blank)
>
> Row 2 looks as follows:
> Col A:1342570
> Col B: WRENCH-SPANNER
> Col C: C0348
> Col D:5110
> Col E:R
> Col K:L
> Col L:EA
> Col M:1
> Col N: 785B, 789, 789B
>
> Row 3 looks like this
> Col A:7X0562
> Col B:WASHER HARD
> Col C:03476
> Col D:5310
> Col E:R
> Col K:L
> Col L:EA
> Col M:1
> Col N:D9R, D9N, D8R, D7HLGP, D6HLGP, D11R, D11N, D10R, D10N, 994, 922G,
> 992D, 988G, 988B, 980G, 980C, 973, 966F, 950F, 14H, 16G, 16H, 325BL, 330BL,
> 345BL, 446D, 613C, 621E, 631E, 637E, 637G, 769D, 773B, 824C, 834B
> If you cut and pasted each of the headers into the first row on excel, and
> then put the info following the ":"'s for each column in my row descriptions,
> you would have a good idea of what my worksheet looks like. Sorry for the
> confusion. If I could cut & paste rows of an excel worksheet intact on these
> pages it would solve alot of communications errors I think.
>
> I hope this helps.
>
> "avveerkar" wrote:
>
> >
> > Could finally get what you wanted? If not and still want some help why
> > don't you give exact layout of your sheet eg.
> >
> > Row1 Headers
> > Col A PartNO
> > Col B Desc
> > Col C ...
> > Col P Model Names
> > etc
> > You said model starts only from col P but then you also said it is col
> > 9 which is confusing. And in your desc you have shown models separated
> > by "," and we are all assuming that all model names (for a row of a
> > part number ) are in one cell separated by ",". Is that true? eg we
> > assume a2 has "7V4325", B2 has " Bolt " and C2 has " Cabrio, Golf, GTI
> > ". Is that what you meant? Or you meant model names start from Col C
> > and thus C2 has "Cabrio", D2 has "Golf" and E2 has "GTI"? Also would
> > you require help to know as to where to copy the procedure and how to
> > run it? I know it is very basic but I got stuck here when I started
> > with VBA programming.
> >
> > A V Veerkar
> >
> >
> > --
> > avveerkar
> > ------------------------------------------------------------------------
> > avveerkar's Profile: http://www.excelforum.com/member.php...o&userid=30338
> > View this thread: http://www.excelforum.com/showthread...hreadid=510236
> >
> >

[Toppers]

Amended code:

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, 14), ",")
For i = LBound(v) To UBound(v)
.Cells(r, 1).Resize(1, 13).Copy ws2.Cells(rr, 1)
ws2.Cells(rr, 14) = Trim(v(i))
rr = rr + 1
Next i
Next r
End With

End Sub


"Toppers" wrote:

> Col N is 14 not 15!
>
> "DA@PD" wrote:
>
> > 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!

[Tim Williams]

see edits in original message



--
Tim Williams
Palo Alto, CA


"DA@PD" <[email protected]> wrote in message
news:[email protected]...
> Hi Tim,
>
> It looks as if your script is short and effective, execpt I actually
> simplified my example to make it easy to understand, there are actually 8
> columns of descriptive data (like part number & description), and the
models
> are actually in column P (the 9th column). What changes to your script
would
> allow this to work? I thought it would be easy to modify the script to go
to
> column p, but I don't understand VBA enough to accomplish this
modification.
>
> Thanks!
>
> David
>
> "Tim Williams" wrote:
>
> > If your data is in sheet1 columns A-C and your reformatted data is to be
> > placed in sheet2
> >
> > Sub reformat()
> >
> > Dim r As Long, x As Integer
> > Dim arr As Variant
> > Dim s As Worksheet
> > Dim c As Range
> >
> > r = 2
> > Set s = ThisWorkbook.Sheets("Sheet2")
> >
> >
> > With ThisWorkbook.Sheets("Sheet1")
> >
> > 'adjust range to suit
> > For Each c In .Range("A2:A2000")
> >
> > arr = Split(c.Offset(0, 8).Value, ",")
> > For x = LBound(arr) To UBound(arr)
> > c.Resize(1, 8).Copy s.Cells(r, 1)
> > s.Cells(r, 9).Value = Trim(arr(x))
> > r = r + 1
> > Next x
> >
> >
> > Next c
> >
> >
> > End With
> > End Sub
> >
> >
> >
> > --
> > Tim Williams
> > Palo Alto, CA
> >
> >
> > "DA@PD" <[email protected]> wrote in message
> > news:[email protected]...
> > > 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!
> >
> >
> >

[avveerkar]

Toppers,

That was neat. Duplicating A2:A13 as a range in one go instead of one cell at a time really simplified the code.
I think it will work.

A V Veerkar

[avveerkar]

Dear DA@PD,

Topper's solution seems to be elegant and should work. You may want to give us a feedback.

A V Veerkar