A Challenge for you Excel experts

[mljohn]

I have a large list of parts that I want to change how the data is laid out.

It was laid out by the part number and which machines went to that part number. The machines were all together in one cell.

Now I want to have each machine have it's own row.

I'll need a way to automate the process since I have so many records.

I have a link to my Box.net account that shows an example spreadsheet.

http://www.box.net/shared/cl8l0vi9qh

I hope you are up to the challenge, I sure need the help.

Thanks

Matt

 

[hiker95]

Bump

 

[brucef2112]

sEEMS you took down the source data, so I can only go by the code provided by hiker95.
But it seems you only need to make 4 changes to his code to extend beyond column 'J'.

The first two you figured out.
On the line with: wR.Range("A1:J1").Value = w1.Range("A1:J1").Value
Change the two 'J' references to whatever column you need to extend to.
For example if you need to extend to 'N', the line would be:
wR.Range("A1:N1").Value = w1.Range("A1:N1").Value

The third and forth changes are further down in the FOR...Loop on this line.
wR.Range("D" & NR).Resize(s, 7).Value = w1.Range("D" & a).Resize(, 7).Value

Following the above example to extend to column 'N', you would change the 7's to 11's (the number of columns offset from column C)
wR.Range("D" & NR).Resize(s, 11).Value = w1.Range("D" & a).Resize(, 11).Value

You'll have to test, as I cannot.

*Bruce secretly pondering what data could be beyond column 'J'. Source and pricing to get cartridges for a nickle? Grandma's secrete ingredients for her apple pie? *
Bruce loves Apple pie!!
Good luck!

Hats off to hiker95 for a great chunk of code!

 

[brucef2112]

Ooops, Fix for reference to the offset. Previous post said offset was from column C it is actually from column B.

Also, if your columns beyond J are more dollar figures and you want the format the same as those up to column J, then there is one more change to extend the dollar format beyond column J.
Change the 'J' in: wR.Range("H2:J" & NR + s).NumberFormat = "[$$-409]#,##0.00_);([$$-409]#,##0.00)" , to your extended column letter.

Keeping with the previous post's example, of going to column N, the obvious change is:
wR.Range("H2:N" & NR + s).NumberFormat = "[$$-409]#,##0.00_);([$$-409]#,##0.00)"

If the columns beyond 'J' have text or you don't care of the format then leave it as is so it does format up to column J.
If needed, you can modify or add code at that location to set the formatting of the extended columns.

 

[brucef2112]

minor correction to the format of '95's code.

this is off by one column to center text and then format the dollar columns.
wR.Range("C2:G" & NR + s).HorizontalAlignment = xlCenter
wR.Range("H2:N" & NR + s).NumberFormat = "[$$-409]#,##0.00_);([$$-409]#,##0.00)".

wR.Range("C2:F" & NR + s).HorizontalAlignment = xlCenter
wR.Range("G2:N" & NR + s).NumberFormat = "[$$-409]#,##0.00_);([$$-409]#,##0.00)".

 

[mljohn]

Thanks for the explanation that's what I needed.

It works great.

Matt