Need to Transpose and Flip Table of Data - Macro Probably Needed

**BobTomlinson** · 02-06-2013, 06:03 PM

Hello All,

I am a longtime user of excelforum, but I haven't thought about posting until now. I'm hoping you can help with an issue I'm having.

On a monthly basis I receive a table of data that has 24 rows and columns. Once I receive it, I manually transpose and flip the columns and rows. My hope is that one of the VBA superstars on the forum can write a macro to do this automatically. I have attached a workbook that shows the before (how I receive the data) and after (how I'd like it to look after using a macro). It's hard to explain without looking at the workbook, but normal transpose functions don't seem to work here.

Any help would be greatly appreciated!

Best,
Bob

**BobTomlinson** · 02-07-2013, 12:01 PM

Can anyone help with this issue?

**FDibbins** · 02-07-2013, 12:18 PM

Hi and welcome to the forum

This isnt VBA, but will do the job jut as well...

=IF(INDEX($C$2:$AA$26,MATCH($C32,$C$2:$C$26,0),MATCH(D$31,$C$2:$AA$2,0))=0,"",INDEX($C$2:$AA$26,MATCH($C32,$C$2:$C$26,0),MATCH(D$31,$C$2:$AA$2,0)))

copied down and across

**FDibbins** · 02-07-2013, 12:19 PM

deleted duplicate post

**BobTomlinson** · 02-07-2013, 02:11 PM

Wow! That works really well. Thank you so much.

Is it possible to modify the formula to include the dates as well? It seems this formula uses the dates in the "Before" and "After" table as the reference points. If there were to be no dates, is it still possible for it the formula to work?

**FDibbins** · 02-07-2013, 02:22 PM

Sure, change the formula to...
=IF(INDEX($C$2:$AA$26,COLUMN(A1)+1,ROW(A1)+1)=0,"",INDEX($C$2:$AA$26,COLUMN(A1)+1,ROW(A1)+1))

NOTE however, this is for a table that starts exactly where yours does. If it starts further to the right, increase the COLUMN(A1)+1 part, if it starts lower down, adjust the ROW(A1)+1 part
a simplified version would be...
=IF(INDEX($C$2:$AA$26,COLUMN(B1),ROW(A2))=0,"",INDEX($C$2:$AA$26,COLUMN(B1),ROW(A2)))