Transposing like all into the same column

chilly_bang

New Member
Joined
Jun 17, 2016
Messages
39
I have a table like

dateorganicreferrerdirect
01.01.20191234512323
25.01.20192345623434
03.03.20193456734556
15.04.20194567845678

<tbody>
</tbody>

I want to get the following output:

typeamountdate
organic1234501.01.2019
referrer12301.01.2019
direct2301.01.2019
organic2345625.01.2019
referrer23425.01.2019
direct3425.01.2019
organic3456703.03.2019
referrer34503.03.2019
direct5603.03.2019
organic4567815.04.2019
referrer45615.04.2019
direct7815.04.2019

<tbody>
</tbody>

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>
 
Last edited:

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
51,126
Office Version
365
Platform
Windows
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?
 
Last edited:

chilly_bang

New Member
Joined
Jun 17, 2016
Messages
39
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

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
51,126
Office Version
365
Platform
Windows
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

Well-known Member
Joined
Oct 24, 2015
Messages
3,199
you can try with PowerQuery (Get&Transform)

dateorganicreferrerdirectdatetypeamount
01.01.2019
12345​
123​
23​
01.01.2019organic
12345​
25.01.2019
23456​
234​
34​
01.01.2019referrer
123​
03.03.2019
34567​
345​
56​
01.01.2019direct
23​
15.04.2019
45678​
456​
78​
25.01.2019organic
23456​
25.01.2019referrer
234​
25.01.2019direct
34​
03.03.2019organic
34567​
03.03.2019referrer
345​
03.03.2019direct
56​
15.04.2019organic
45678​
15.04.2019referrer
456​
15.04.2019direct
78​

Code:
[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

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
51,126
Office Version
365
Platform
Windows
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

Well-known Member
Joined
Oct 24, 2015
Messages
3,199
Joe, so I think the best option should be delete my posts and move this thread to appropriate forum
 

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
51,126
Office Version
365
Platform
Windows
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

Well-known Member
Joined
Oct 20, 2009
Messages
7,712
Office Version
365
Platform
Windows
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)),"")
 
Last edited:

chilly_bang

New Member
Joined
Jun 17, 2016
Messages
39
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
 

Forum statistics

Threads
1,077,638
Messages
5,335,393
Members
399,015
Latest member
emalabel

Some videos you may like

This Week's Hot Topics

Top