How to Combine multiple Column into single row

[Did13n]

Hello Everyone

i need your help
i have data with format as below

actually this data was in Pivot format before i pasted as text

but now i need to break it as raw data which was used when generating pivot table , with combine each model and sizes in single row

i have more than 50 model to combine with size, so if it done manually, will took a time

attached the file with expected result

thank you

[Special-K]

Suggest you rename the subject while you can before a mod steps in.

Rule 1
http://www.excelforum.com/forum-rule...rum-rules.html

With 52 posts you should know this by now.

Until you change the subject noone can solve your problem for fear of receiving infractions.

[Did13n]

Suggest you rename the subject while you can before a mod steps in.

Rule 1
http://www.excelforum.com/forum-rule...rum-rules.html

With 52 posts you should know this by now.

Until you change the subject noone can solve your problem for fear of receiving infractions.

ok K, thanks for reminding,

[Special-K]

This should work

Sub k1()
lastrow = Worksheets("Sheet1").Cells(Rows.Count, "B").End(xlUp).Row
j = 6
For i = 6 To lastrow
For k = 5 To 7
Cells(j, 11) = Cells(i, 2) & "-" & Cells(5, k)
Cells(j, 12) = Cells(i, 3)
Cells(j, 13) = Cells(i, k)
Cells(j, 14) = Cells(i, 4)
Cells(j, 15) = Cells(j, 13) * Cells(j, 14)
j = j + 1
Next k
Next i
End Sub

Input on Sheet1
Output goes to the cells defined in your output grid so you'll need to move them if you want it somewhere else.

[Did13n]

This should work

Sub k1()
lastrow = Worksheets("Sheet1").Cells(Rows.Count, "B").End(xlUp).Row
j = 6
For i = 6 To lastrow
For k = 5 To 7
Cells(j, 11) = Cells(i, 2) & "-" & Cells(5, k)
Cells(j, 12) = Cells(i, 3)
Cells(j, 13) = Cells(i, k)
Cells(j, 14) = Cells(i, 4)
Cells(j, 15) = Cells(j, 13) * Cells(j, 14)
j = j + 1
Next k
Next i
End Sub

Input on Sheet1
Output goes to the cells defined in your output grid so you'll need to move them if you want it somewhere else.

actually i am not familiar with how work with macro code as mostly i finished my excel works with formulas instead macro codes
however let me try your solutions into my working deck, this can be something new for me ...

if others have a solutions in formulas, highly appreaciated.

[Special-K]

Using formulas would be very fiddly.

Hit Alt-F11 and copy the VBA into the VBA editor

[Did13n]

Using formulas would be very fiddly.

Hit Alt-F11 and copy the VBA into the VBA editor

thanks K, on the above case it's done properly ... well it's amazed with that simple codes, i really need to start to learn how works with VBA


but not run properly to my real case ...i dont know why

here attached my working sheet ... the result expected in sheet 2

kindly help
rgards

[Special-K]

This is completely different from what you first posted and Im starting to lose interest as I feel Ive been wasting part of my time solving something that wasn't the intended result.

In your latest spreadsheet:

1. Sheet 1 column D is called price yet its just a column of ascending values so they dont look like prices to me.
2. There's no price column in Sheet 2 yet this was in your initial output spreadsheet.
3. Column Q labelled Quantity looks more like a price.

If the new spreadsheet is what youre aiming for you should have provided that in the beginning and checked it before posting.

Use this

Sub k1()
lastrow = Worksheets("Sheet1").Cells(Rows.Count, "B").End(xlUp).Row
j = 4
For i = 8 To lastrow
For k = 5 To 16
Worksheets("Sheet2").Cells(j, 2) = Worksheets("Sheet1").Cells(i, 2) & "-" & Worksheets("Sheet1").Cells(7, k)
Worksheets("Sheet2").Cells(j, 3) = Worksheets("Sheet1").Cells(i, 3)
Worksheets("Sheet2").Cells(j, 4) = Worksheets("Sheet1").Cells(i, k)
Worksheets("Sheet2").Cells(j, 5) = Worksheets("Sheet1").Cells(i, 4) * Worksheets("Sheet1").Cells(i, k)

j = j + 1
Next k
Next i
End Sub

[Did13n]

actually mostly same.... but i should keep confidential on some information related to the contains of the file and i believed u understood this already...

thanks anyway for your codes, again..

cheers

[Special-K]

Confidential information can be replaced with mock data.