How to convert rows in to column by using formula

[bill13]

Hi Experts,

I am new with excel. i am getting some data in excel which can be data for 12 months or less then 12 months. i need to make a formulla that can transform my rows into columns. challange is sometimes i am getting data for 12 months and some times not for 12 months. I need to show null or zero in the columns for missing months. Can some one make 1 formulla so that i can understand and follow it. i have attached the excel file as well. Please need it urgent.

Origional Data
A1:E7
Country Month Accounts Payable Accounts RA Accounts PR
United States 8 200 241 123
United States 5 100 201 133
United States 6 150 198 144
United States 7 170 167 155
United States 9 130 180 166
United States 10 125 150 177

Required Format:
I4:U5
Months
1 2 3 4 5 6 7 8 9 10 11 12
Accounts Payable 100 150 170 200 130 125
Accounts RA 201 198 167 241 180 150
Accounts PR 133 144 155 123 166 177

[Richard Buttrey]

Hi,

Maybe not the answer you're looking for but if you were prepared to change the layout of your original data you would open up the whole world of the Pivot Table functionality and make subsequent analysis and summarisation so much easier and without needing any formulae - see attached.

[bill13]

Richard thanks for your reply. But i dont want to use pivot tables because this excel sheet is going to be source for a dashboard and dashboarding tool does not support pivot tables of excel.

[Richard Buttrey]

Richard thanks for your reply. But i dont want to use pivot tables because this excel sheet is going to be source for a dashboard and dashboarding tool does not support pivot tables of excel.

Hi, whilst your dashboard may not support PTs, PTs could certainly support your dashboard and be much more useful not to say flexible.

[bill13]

Richard,

I have made one formula and it is working fine

IFERROR(INDEX($B$2:$F$13,MATCH(O$1,$C$2:$C$13,0),MATCH($J2,$B$1:$F$1,0)),"")

[Richard Buttrey]

OK, that's good.

I didn't say that there wasn't a formula, just that a Pivot table is ultimately more flexible. You should always try and capture your data in a 'normalised' table where fields that contain similar items - albeit different values are unique.