Transposing like all into the same column

chilly_bang

Board Regular
Joined
Jun 17, 2016
Messages
57
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:

Excel Facts

Enter current date or time
Ctrl+: enters current time. Ctrl+; enters current date. Use Ctrl+: Ctrl+; Enter for current date & time.
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:
Upvote 0
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.
 
Upvote 0
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.
 
Upvote 0
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]
 
Upvote 0
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.
 
Upvote 0
Joe, so I think the best option should be delete my posts and move this thread to appropriate forum
 
Upvote 0
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.
 
Upvote 0
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:
Upvote 0
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
 
Upvote 0

Forum statistics

Threads
1,213,527
Messages
6,114,144
Members
448,552
Latest member
WORKINGWITHNOLEADER

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top