当前位置: 动力学知识库 > 问答 > 编程问答 >

How to convert flatten table to 2D Excel table

问题描述:

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):

=INDEX($C$2:$C$6,MATCH($F3&G$2,$A$2:$A$6&$B$2:$B$6,0)) (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.

分享给朋友:
您可能感兴趣的文章:
随机阅读: