I have a "flatten" table with countries and years in Excel. eg.
Country Year Value
USA 1961 a
USA 1962 x
USA 1963 g
USA 1964 y
France 1961 u
And I'd like to have a two dimensional table as following:
1961 1962 1963 1964
USA a x g y
France u e h a
Germany o x n p
Do you happen to know, how to do that programmatically in Excel using VBA. Many thanks in forward for any hint or advice.
You can use Index/Match, as an array formula, to fill this in without VBA. (If you need VBA, record this as a macro to see how the formula is entered, then just apply it to the empty table range):
(enter with CTRL+SHIFT+ENTER)
Here's how the data's laid out, so you can see where I'm getting the ranges:
Then you can drag the formula over and down to fill in the rest of the table. Natrually you would need to adjust the ranges used based on your data.