Transposing like all into the same column

[chilly_bang]

I have a table like

[TABLE="class: tg"]
<tbody>[TR]
[TH="class: tg-0lax"]date[/TH]
[TH="class: tg-0lax"]organic[/TH]
[TH="class: tg-0lax"]referrer[/TH]
[TH="class: tg-0lax"]direct[/TH]
[/TR]
[TR]
[TD="class: tg-0lax"]01.01.2019[/TD]
[TD="class: tg-0lax"]12345[/TD]
[TD="class: tg-0lax"]123[/TD]
[TD="class: tg-0lax"]23[/TD]
[/TR]
[TR]
[TD="class: tg-0lax"]25.01.2019[/TD]
[TD="class: tg-0lax"]23456[/TD]
[TD="class: tg-0lax"]234[/TD]
[TD="class: tg-0lax"]34[/TD]
[/TR]
[TR]
[TD="class: tg-0lax"]03.03.2019[/TD]
[TD="class: tg-0lax"]34567[/TD]
[TD="class: tg-0lax"]345[/TD]
[TD="class: tg-0lax"]56[/TD]
[/TR]
[TR]
[TD="class: tg-0lax"]15.04.2019[/TD]
[TD="class: tg-0lax"]45678[/TD]
[TD="class: tg-0lax"]456[/TD]
[TD="class: tg-0lax"]78[/TD]
[/TR]
</tbody>[/TABLE]

I want to get the following output:

[TABLE="class: tg"]
<tbody>[TR]
[TH="class: tg-0lax"]type[/TH]
[TH="class: tg-0lax"]amount[/TH]
[TH="class: tg-0lax"]date[/TH]
[/TR]
[TR]
[TD="class: tg-0lax"]organic[/TD]
[TD="class: tg-0lax"]12345[/TD]
[TD="class: tg-0lax"]01.01.2019[/TD]
[/TR]
[TR]
[TD="class: tg-0lax"]referrer[/TD]
[TD="class: tg-0lax"]123[/TD]
[TD="class: tg-0lax"]01.01.2019[/TD]
[/TR]
[TR]
[TD="class: tg-0lax"]direct[/TD]
[TD="class: tg-0lax"]23[/TD]
[TD="class: tg-0lax"]01.01.2019[/TD]
[/TR]
[TR]
[TD="class: tg-0lax"]organic[/TD]
[TD="class: tg-0lax"]23456[/TD]
[TD="class: tg-0lax"]25.01.2019[/TD]
[/TR]
[TR]
[TD="class: tg-0lax"]referrer[/TD]
[TD="class: tg-0lax"]234[/TD]
[TD="class: tg-0lax"]25.01.2019[/TD]
[/TR]
[TR]
[TD="class: tg-0lax"]direct[/TD]
[TD="class: tg-0lax"]34[/TD]
[TD="class: tg-0lax"]25.01.2019[/TD]
[/TR]
[TR]
[TD="class: tg-0lax"]organic[/TD]
[TD="class: tg-0lax"]34567[/TD]
[TD="class: tg-0lax"]03.03.2019[/TD]
[/TR]
[TR]
[TD="class: tg-0lax"]referrer[/TD]
[TD="class: tg-0lax"]345[/TD]
[TD="class: tg-0lax"]03.03.2019[/TD]
[/TR]
[TR]
[TD="class: tg-0lax"]direct[/TD]
[TD="class: tg-0lax"]56[/TD]
[TD="class: tg-0lax"]03.03.2019[/TD]
[/TR]
[TR]
[TD="class: tg-0lax"]organic[/TD]
[TD="class: tg-0lax"]45678[/TD]
[TD="class: tg-0lax"]15.04.2019[/TD]
[/TR]
[TR]
[TD="class: tg-0lax"]referrer[/TD]
[TD="class: tg-0lax"]456[/TD]
[TD="class: tg-0lax"]15.04.2019[/TD]
[/TR]
[TR]
[TD="class: tg-0lax"]direct[/TD]
[TD="class: tg-0lax"]78[/TD]
[TD="class: tg-0lax"]15.04.2019[/TD]
[/TR]
</tbody>[/TABLE]

The "normal" transposing is pretty close to what i want - but not exactly, and i miss the point, how to pivot the data to get the goal...


<style type="text/css">.tg {border-collapse:collapse;border-spacing:0;}.tg td{font-family:Arial, sans-serif;font-size:14px;padding:10px 5px;border-style:solid;border-width:1px;overflow:hidden;word-break:normal;border-color:black;}.tg th{font-family:Arial, sans-serif;font-size:14px;font-weight:normal;padding:10px 5px;border-style:solid;border-width:1px;overflow:hidden;word-break:normal;border-color:black;}.tg .tg-0lax{text-align:left;vertical-align:top}</style>

 

[Joe4]

I don't know that you are going to be able use Transpose or a Pivot to get that. Is VBA an acceptable alternative?
Will there only be these 4 columns, or might there be more?

 

[chilly_bang]

There could be any amount of rows and columns in the original document. I will be forced to adopt the solution to Google Spreadsheets, so maybe formula or pivoting would be best alternatives - otherwise there will be too much coding. I'm sure, you will be able to get a solution in all methods you mentioned: formula, pivot and VBA - but please, suggesting a solution begin with the simplest one.

 

[Joe4]

I am sorry, short of doing it manually, I can only think of a VBA solution. And I do not know anything about Google Sheets. So I don't think I can help you out.

By the way, the simple example you posted could be done manually quite easily with a bunch of copying and pasting. However, if you have a lot of columns, that could get to be a bit cumbersome.

 

[sandy666]

you can try with PowerQuery (Get&Transform)

[Table="width:, class:head"]
[tr=bgcolor:#FFFFFF][td=bgcolor:#5B9BD5]date[/td][td=bgcolor:#5B9BD5]organic[/td][td=bgcolor:#5B9BD5]referrer[/td][td=bgcolor:#5B9BD5]direct[/td][td][/td][td=bgcolor:#70AD47]date[/td][td=bgcolor:#70AD47]type[/td][td=bgcolor:#70AD47]amount[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#DDEBF7]01.01.2019[/td][td=bgcolor:#DDEBF7]

12345​

[/td][td=bgcolor:#DDEBF7]

123​

[/td][td=bgcolor:#DDEBF7]

23​

[/td][td][/td][td=bgcolor:#E2EFDA]01.01.2019[/td][td=bgcolor:#E2EFDA]organic[/td][td=bgcolor:#E2EFDA]

12345​

[/td][/tr]

[tr=bgcolor:#FFFFFF][td]25.01.2019[/td][td]

23456​

[/td][td]

234​

[/td][td]

34​

[/td][td][/td][td]01.01.2019[/td][td]referrer[/td][td]

123​

[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#DDEBF7]03.03.2019[/td][td=bgcolor:#DDEBF7]

34567​

[/td][td=bgcolor:#DDEBF7]

345​

[/td][td=bgcolor:#DDEBF7]

56​

[/td][td][/td][td=bgcolor:#E2EFDA]01.01.2019[/td][td=bgcolor:#E2EFDA]direct[/td][td=bgcolor:#E2EFDA]

23​

[/td][/tr]

[tr=bgcolor:#FFFFFF][td]15.04.2019[/td][td]

45678​

[/td][td]

456​

[/td][td]

78​

[/td][td][/td][td]25.01.2019[/td][td]organic[/td][td]

23456​

[/td][/tr]

[tr=bgcolor:#FFFFFF][td][/td][td][/td][td][/td][td][/td][td][/td][td=bgcolor:#E2EFDA]25.01.2019[/td][td=bgcolor:#E2EFDA]referrer[/td][td=bgcolor:#E2EFDA]

234​

[/td][/tr]

[tr=bgcolor:#FFFFFF][td][/td][td][/td][td][/td][td][/td][td][/td][td]25.01.2019[/td][td]direct[/td][td]

34​

[/td][/tr]

[tr=bgcolor:#FFFFFF][td][/td][td][/td][td][/td][td][/td][td][/td][td=bgcolor:#E2EFDA]03.03.2019[/td][td=bgcolor:#E2EFDA]organic[/td][td=bgcolor:#E2EFDA]

34567​

[/td][/tr]

[tr=bgcolor:#FFFFFF][td][/td][td][/td][td][/td][td][/td][td][/td][td]03.03.2019[/td][td]referrer[/td][td]

345​

[/td][/tr]

[tr=bgcolor:#FFFFFF][td][/td][td][/td][td][/td][td][/td][td][/td][td=bgcolor:#E2EFDA]03.03.2019[/td][td=bgcolor:#E2EFDA]direct[/td][td=bgcolor:#E2EFDA]

56​

[/td][/tr]

[tr=bgcolor:#FFFFFF][td][/td][td][/td][td][/td][td][/td][td][/td][td]15.04.2019[/td][td]organic[/td][td]

45678​

[/td][/tr]

[tr=bgcolor:#FFFFFF][td][/td][td][/td][td][/td][td][/td][td][/td][td=bgcolor:#E2EFDA]15.04.2019[/td][td=bgcolor:#E2EFDA]referrer[/td][td=bgcolor:#E2EFDA]

456​

[/td][/tr]

[tr=bgcolor:#FFFFFF][td][/td][td][/td][td][/td][td][/td][td][/td][td]15.04.2019[/td][td]direct[/td][td]

78​

[/td][/tr]
[/table]

[SIZE=1]// Table1
let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    Unpivot = Table.UnpivotOtherColumns(Source, {"date"}, "Attribute", "Value"),
    Rename = Table.RenameColumns(Unpivot,{{"Attribute", "type"}, {"Value", "amount"}})
in
    Rename[/SIZE]

 

[Joe4]

Sandy, looks good, but I am concerned about this part of their request:

I will be forced to adopt the solution to Google Spreadsheets

chilly_bang,
If that is the case, would it be fair to say that you are really looking for a Google Sheets solution, and not an Excel one?
If so, we should move this thread appropriately.

 

[sandy666]

Joe, so I think the best option should be delete my posts and move this thread to appropriate forum

 

[Joe4]

Joe, so I think the best option should be delete my posts and move this thread to appropriate forum

I want them to confirm whether or not they are really looking for a Google Sheets solution before I do anything.

 

[steve the fish]

Can do it with formula but they are a bit long...

Place the table in A1:D5. Then in F2:

=IF(COUNTIF($F$1:F1,INDEX($B$1:$D$1,1,1+MOD(ROWS($A$1:A3),3))) < ROW(OFFSET($A$1,COUNTA($A:$A)-1,0))-1,INDEX($B$1:$D$1,1,1+MOD(ROWS($A$1:A3),3)),"")<row(offset($a$1,counta($a:$a)-1,0))-1,index($b$1:$d$1,1,1+mod(rows($a$1:a3),3)),"")


In G2:

=IFERROR(INDEX($B$2:$D$5,COUNTIF($F$2:F2,F2),MATCH(F2,$B$1:$D$1,0)),"")

In H2:

=IFERROR(INDEX($A$2:$A$5,MATCH(1,INDEX(--(G2=INDEX($B$2:$D$5,,MATCH(F2,$B$1:$D$1,0))),0),0)),"")

There may be better solutions but it seems to produce the same data table as the original. Ranges need to be changed to suit.</row(offset($a$1,counta($a:$a)-1,0))-1,index($b$1:$d$1,1,1+mod(rows($a$1:a3),3)),"")

 

[chilly_bang]

Please don't delete anything Indeed, the final suite, where the task will run, is GSheets. But with approaches from Sandy and Steve i will get the goal - it isn't a rocket science to translate things from Excel to GSheets (in case counterparts exist). From this point - this forum is as always just gre