Transpose special case

[sonkar]

Hi,

I have a data which looks like this with headers where the columns variable.

423115000001 TYPE -- -- MATERIAL COTTON COTN WEIGHT 100 GM 100GM DIMENSION -- -- ADDITIONAL INFORMATION -- --


I am trying to get the output like below

423115000001 TYPE -- --
423115000001 MATERIAL COTTON COTN
423115000001 WEIGHT 100 GM 100GM
423115000001 DIMENSION -- --
423115000001 ADDITIONAL INFORMATION -- --


any help would be great i have more than 30000 rows to be converted in this pattern.

Regards

Sachin

 

[bobsan42]

Please give some more information - i am confused on the result you need to achieve.

Describe the input and outputs a bit further.

 

[sonkar]

Hi,

Sorry for the late reply,I was trying to attach excel file but don't know how to do that here cannot see the options for doing that.

Pasting the cell contents hope that helps you to understand my problem.

the input is as below with headers
[TABLE="width: 1433"]
<colgroup><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col></colgroup><tbody>[TR]
[TD]Code[/TD]
[TD]ATTRIBUTE[/TD]
[TD]ATT_VALUE[/TD]
[TD]ABB_VALUE[/TD]
[TD]ATTRIBUTE[/TD]
[TD]ATT_VALUE[/TD]
[TD] ABB_VALUE [/TD]
[TD]ATTRIBUTE[/TD]
[TD] ATT_VALUE[/TD]
[TD] ABB_VALUE [/TD]
[TD] ATTRIBUTE[/TD]
[TD]ATT_VALUE [/TD]
[TD]ABB_VALUE[/TD]
[TD] ATTRIBUTE[/TD]
[TD]ATT_VALUE[/TD]
[TD]ABB_VALUE[/TD]
[/TR]
[TR]
[TD]423115000001[/TD]
[TD]TYPE[/TD]
[TD]--[/TD]
[TD]--[/TD]
[TD]MATERIAL[/TD]
[TD]COTTON[/TD]
[TD] COTN[/TD]
[TD]WEIGHT[/TD]
[TD]100 GM[/TD]
[TD]100GM[/TD]
[TD]DIMENSION[/TD]
[TD]--[/TD]
[TD]--[/TD]
[TD]ADDITIONAL INFORMATION[/TD]
[TD]--[/TD]
[TD]--[/TD]
[/TR]
</tbody>[/TABLE]

And the ouptput is as below.

[TABLE="width: 511"]
<colgroup><col><col><col><col></colgroup><tbody>[TR]
[TD] Code[/TD]
[TD]ATTRIBUTE[/TD]
[TD]ATT_VALUE [/TD]
[TD]ABB_VALUE[/TD]
[/TR]
[TR]
[TD]423115000001[/TD]
[TD]TYPE[/TD]
[TD]--[/TD]
[TD]--[/TD]
[/TR]
[TR]
[TD]423115000001[/TD]
[TD]MATERIAL[/TD]
[TD]COTTON[/TD]
[TD]COTN[/TD]
[/TR]
[TR]
[TD]423115000001[/TD]
[TD]WEIGHT[/TD]
[TD]100 GM[/TD]
[TD]100GM[/TD]
[/TR]
[TR]
[TD]423115000001[/TD]
[TD]DIMENSION[/TD]
[TD]--[/TD]
[TD]--[/TD]
[/TR]
[TR]
[TD]423115000001[/TD]
[TD]ADDITIONAL INFORMATION[/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
</tbody>[/TABLE]



Thanks

Sachin

 

[bobsan42]

OK - I think I got it now.
First - a few assumptions:
- you data is coherent - structured the same way all the way down
- The data is on "Sheet1" and starts from cell A1 (the value in it is "Code" - the header)
I assume your data, including the Headers, occupies the range A1:P3000 (or P10000 - doesn't really matter how long is the list)
- The product codes are unique (or at least you don't have identical codes for different products immediately one after the other)

That said, the directions:
- go to "sheet2" or any other
- put the Headers on row 4 (A4-> Code, B4 ->Attribute, C4-> ATT_VALUE, D4-> ABB_VALUE)
- put the following formula in cell A5:
=INDIRECT("'Sheet1'!A"&TRUNC(ROW()/5)+1)
- put the following formula in cells B5, C5, D5:
=INDIRECT("'Sheet1'!R"&TRUNC(ROW()/5)+1&"C"&(COUNTIF($A1:$A5,$A5)-1)*3+COLUMN(),FALSE)
- now multiply (FillDown) the range A5:D5 for about 15 000 rows

Hope this helps

 

[sonkar]

Hi Bobsan42,

Thanks for the help it worked for me.
I modifed it as below for my requirements cause i had more columns
=INDIRECT("'Sheet1'!A"&TRUNC(ROW()/12)+1)
=INDIRECT("'Sheet1'!R"&TRUNC(ROW()/12)+1&"C"&(COUNTIF($A1:$A12,$A12)-1)*3+COLUMN(),FALSE)

Thanks

Sachin