Tranposing a table google sheet

Question

In Google Sheet / google script / javascript, . how can I convert my table

from

05/05/2017  Peter   Apple
05/05/2017  John    Banana
05/05/2017  Jake    Apple
05/06/2017  John    Apple
05/06/2017  Jake    Banana
05/06/2017  Peter   Banana

to

      05/05/2017    05/06/2017
Peter   Apple        Banana
John    Banana       Apple
Jake    Apple        Banana

TIA.

score 1 · Answer 1 · answered May 08 '17 at 16:24

1

Here is a method of doing that I think, image attached:

quick google search

answered May 08 '17 at 16:24

David

131
8

1

You answered the question, but I think this is not exactly what the OP wanted to do. – Max Makhrov May 10 '17 at 07:22

score 0 · Answer 2 · edited May 23 '17 at 12:10

The code you are asking is not transposing cells. You need keep track of:

Unique list of Dates
Unique list of Names
Find all the intersections.

You also need to solve this Q:

What if data have more then 1 matches for pair: date-name i.e. Peter in 05/05/2017 has Apple and Banana

Try solving the problem by parts and then come up with some code and improve it.

I suggest these info as starting point:

get value in one column in spreadsheet using google apps script

Unique values in an array

score 0 · Answer 3 · edited Nov 17 '17 at 11:00

0

1: Paste the table in B1 across D6.

2: In A1 enter this formula -> =ArrayFormula(C1:C&B1:B)

3. In F1, enter this -> =TRANSPOSE(unique({B:B}))

4. IN E2, enter this -> =UNIQUE(C:C)

5. In F2, enter this -> =ArrayFormula(iferror(VLOOKUP($E2:$E&F1,$A1:$D,4,0),""))

You can grad the formula in F2 across next columns to get the data.

edited Nov 17 '17 at 11:00

Litisqe Kumar

2,512
4
26
40

answered Nov 17 '17 at 10:36

Ken Adams

70
1
1
7

score 0 · Answer 4 · answered Nov 20 '20 at 18:34

The question is not full-explained, so the answer is based on this assumption: for each distinct date and for each distinct name the source range contans exactly one data row. Thus we will not take care of possible data aggregation.

The image below is useful to explain the solution.

Row headers array in E4:E6 is filled by the formula =UNIQUE($B$1:$B$6), because we should avoid duplicates. The formula is for the cell E4. Column headers array with dates are calculated by a similar formula with additional transposition =TRANSPOSE(UNIQUE($A$1:$A$6)) in the cell F3.

At last, data cells F4:G6 have the same logics and their formula can be expanded by copying from the cell F4. The result should be found in column C using 2 keys for A and B columns. There is no "VLOOKUP-like" function to do that directly, so we use filtering before VLOOKUP.

More information about these functions can be found here: VLOOKUP, FILTER.

Tranposing a table google sheet

4 Answers4