Formula to get vertical data into horizontal format

Hi,

In D3 and copied across....


=INDEX('Get data from'!$B$3:$B$14,MATCH('Send data to'!D1,'Get data from'!$A$3:$A$14,0))

=INDEX('Get data from'!$B$3:$B$14,MATCH('Send data to'!D$1,'Get data from'!$A$3:$A$14,0),1) -Put this in cell D1 as copy/paste as needed.

See attached:

I've just noticed that you want to match the account code also, which makes things a little more complex. Can you give a further example of what the source data will look like? Particularly the position of the account id in respect to the list of values to be transposed, and if there are varying lengths of data to be transposed.

Hi razkowski,

A pivot table can do your work and no formulas are needed. I would question your motive as the original table is how Excel likes to see data best. See the attached with Pivot that does what you want.

If you have the account code repeated down a column you could filter the pivot using it and deal with them also.

Question - is the source data going to have multiple blocks for different Account numbers? If so, are there spaces between the blocks? When a period is missing, is there a gap where that period would be or not?


I have a reliable, but fairly slow to calculate version you might be able to use (attached).

You don't need the Q/R columns in the "Send Data to" tab - I just put them there for illustration. What I'm doing is locating the start and end for each "account block" in the data sheet - then matching the period if possible.

If there is no match, you get the "#N/A" return as you would expect.

This solution takes a moment to calculate, probably could be optimized.


Formulas:



For period returns (columns D through O):
=INDEX(INDEX('Get data from'!$B:$B,MyAccountBlock_start):INDEX('Get data from'!$B:$B,MyAccountBlock_end),MATCH('Send data to'!D$1,INDEX('Get data from'!$A:$A,MyAccountBlock_start):INDEX('Get data from'!$A:$A,MyAccountBlock_end),0))


myAccountBlock_start (named formula):
=ROW(INDEX('Get data from'!$A:$A,MATCH('Send data to'!$A11,'Get data from'!$B:$B,0)))+2 - enter as an 'array' formula when authored in the name manager.

MyAccountBlock_end (named formula):
=MIN(IF((INDEX('Get data from'!$A:$A,MATCH('Send data to'!$A7,'Get data from'!$B:$B,0)+2):'Get data from'!$A$1048571)="",ROW(INDEX('Get data from'!$A:$A,MATCH('Send data to'!$A7,'Get data from'!$B:$B,0)+2):'Get data from'!$A$1048571),""))

--EDIT---

Add some columns (something like what I've done) to make a unique key column - then hit off that column.

See attached new example.



The previous example required no new columns but it wasn't very memory friendly - the trick to this one was finding a way to assign a block ID to the b column on the data tab.