0

I have a table where each row represents one logical value.

Sort of like this:

| January  | Margaret | 10 |
| February | Jeff     | 20 |
| March    | William  | 30 |

I want to transfer the data to another table, where the data would be modelled as following:

|          | January | February | March
+----------+---------+----------+------
| Margaret | 10      |          |
| Jeff     |         | 20       |  
| William  |         |          | 30

In the case of changes in the Table 1 same changes should be reflected in the Table 2

There are only 4 names and 12 months. Names are repeated A,B,C,D,A,B,C,D,A,B,C,D. Numbers are 10-120. Obviously I can use = and click on the cell in the first table but I need more optimal solution. I can't figure it out. Any advise would be much appreciated!

ϹοδεMεδιϲ
  • 2,790
  • 3
  • 33
  • 54
Maelstorm
  • 1
  • 1
  • Please [edit] your question to show your input / expected output more clearly - and show what you have researched / tried already. See [ask] for more information. – Olly Aug 22 '18 at 15:22
  • 1
    Have you tried using `Transpose`? Just copy the table, right click where you want to paste the data and select the Transpose option (4th one over on the list of special pastes). – dwirony Aug 22 '18 at 15:26

2 Answers2

0

It sounds like what you're trying to do is called a long to wide transformation. In this case, since you just have months, it might be easiest to create a table with your months as headers (i.e. January - December), and then write a INDEX/MATCH formula to fill it in.

Let's say this is your table, starting in cell A2

Date        Name      Value
January     Jone      1
January     Mike      2
January     Bob       3
February    Jone      4
February    Mike      5
February    Bob       6

Then create a table with a header row for the months, starting in B10

January February    March   April   May June    July    August  September   October November    December

And a column for the unique names, starting in A11

Jone
Mike
Bob

So the whole thing looks like this: 

    January February    March ...
Jone            
Mike            
Bob

Then we use an INDEX/MATCH formula to fill in the table. Write this in cell B11, then fill down and across:

=INDEX($C$3:$C$8,MATCH(1, INDEX(($A11=$B$3:$B$8)*(B$10=$A$3:$A$8),0,1),0))

This says, return a value from C3:C8 where both A11 and B10 match in their respective ranges B3:B8 and A3:A8.

You end up with:

    January February  ...
Jone    1   4
Mike    2   5
Bob     3   6
Mako212
  • 6,787
  • 1
  • 18
  • 37
0

You can use Get&Transform to

  • Pivot Column 1
    • Advanced Options: Don't Aggregate
    • Values Column: Column 3

enter image description here

enter image description here

Ron Rosenfeld
  • 53,870
  • 7
  • 28
  • 60