Transpose

[Murari Rajagopalan]

Folks,

I need help in transposing the daily sales data as per the sample Input/ output attached.

the data would be appended on a daily basis and I would want the next row to be created, if there is a change in City Name. Can someone help me on this ?

Please refer the input sheet for the inputs that I would be getting on a daily basis and the preferred output mentioned in the output sheet.


https://docs.google.com/open?id=0B_X21Zn6KJMEWTFTVDN3Tlpnd0U

 

[Faseeh]

Hi murari,


With data in following format:

[pre]

1-Dec	2-Dec	3-Dec	4-Dec	5-Dec	6-Dec	7-Dec	8-Dec	9-Dec	10-Dec
A	10	106	202	298	394	0	0	0	0	0
B	22	118	214	310	406	0	0	0	0	0
C	34	130	226	322	418	0	0	0	0	0
D	46	142	238	334	430	0	0	0	0	0
E	58	154	250	346	442	0	0	0	0	0
F	70	166	262	358	454	0	0	0	0	0
G	82	178	274	370	466	0	0	0	0	0
H	94	190	286	382	478	0	0	0	0	0[/pre]
Present in Cells E1:O9, Enter this formula in F2 and drag down/across:


=SUMPRODUCT(($B$2:$B$41=$E2)*($A$2:$A$41=F$1)*($C$2:$C$41))

Formula refers to your sheet Input.


Regards,

 

[Murari Rajagopalan]

the first column should be the city name and from second column onwards, the date should start. Also, I tried entered the formula and I am getting zeros. Can you help me in using this formula as per the sample file attached ?

 

[NARAYANK991]

Hi Rajagopalan ,


Faseeh's formula is correct ; you just need to put in the sheet names appropriately.


Use this :


=SUMPRODUCT((Input!$B$2:$B$41=$A2)*(Input!$A$2:$A$41=B$1)*(Input!$C$2:$C$41))


Put it in cell B2 on the Output tab , copy it across and down.


Narayan

 

[Murari Rajagopalan]

Thanks Narayan. This is now clear and works fine