How to transpose data across worksheets into one Analysis sheet

[mimih23]

Hello,
I've used the following formula successfully to pull data from multiple sheets and provide the total on an analysis worksheet. Example of worksheets and formula.

=DSUM(INDIRECT("'"&A2&"'!D:D"),$B$1,INDIRECT("'"&A2&"'!D:D"))

Example of one of the individual sheets holding original data: [TABLE="class: grid, width: 500, align: center"]
<TBODY>[TR]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]Merchant Name[/TD]
[TD]Name[/TD]
[TD]Date[/TD]
[TD]Number[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]Louise[/TD]
[TD]James[/TD]
[TD]05-01-13[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]Louise[/TD]
[TD]Lou[/TD]
[TD]09-08-12[/TD]
[TD]3[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</TBODY>[/TABLE]

Merchant Name is the same name as the tab for the individual sheet. Hence the INDIRECT argument.


Example of Analysis Sheet Running above formula:
[TABLE="width: 500, align: center"]
<TBODY>[TR]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]Merchant Name[/TD]
[TD]Number[/TD]
[TD]Date[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]Louise[/TD]
[TD]4[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]Julie[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</TBODY>[/TABLE]


Example of what I want to see using the Transpose formula (Analysis sheet):

=TRANSPOSE(INDIRECT("'"&A2&"'!C:C"),$C$1,INDIRECT("'"&A2&"'!C:C")) (My theory that currently doesn't work. I'd like to be able to pull data across worksheets and yield the transposed data into the corresponding row of the same merchant row.
[TABLE="class: outer_border, width: 500, align: center"]
<TBODY>[TR]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]Merchant Name[/TD]
[TD]Number[/TD]
[TD]Date[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]Louise[/TD]
[TD]4[/TD]
[TD]05-01-13[/TD]
[TD]09-08-12[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]Julie[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</TBODY>[/TABLE]

Let me know what you guys think. Is it possible to do?

Thanks!!

 

[Andrew Poulsom]

You can increase the 100 if you want.

 

[mimih23]

You can increase the 100 if you want.

I just realized that I used C to represent the both sheets. When you reference ROW, are you referring to the row on the analysis page or on the original data sheet?

 

[mimih23]

Also I think I also see that my explanation of a duplicate was not clear as well. It looks like your formula is looking to rid duplicates from the column on the analysis tab.
I'm concerned with duplicates within the row of data pulled from the original column. I am not concerned with duplicates within the overall sheet simply within the exclusive row of data pulled.

For example:

Workbook contains 5 sheets (sheet names):
Analysis
Tour
France
Jesuit
Louie

Analysis page:
[TABLE="class: grid, width: 500, align: center"]
<tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[TD]E[/TD]
[TD]F[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]Merchant Name[/TD]
[TD]Total Volume[/TD]
[TD]Monthly Amount[/TD]
[TD]Sales[/TD]
[TD]Code[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]Tour[/TD]
[TD]$2824396.97[/TD]
[TD]$2592.64[/TD]
[TD]$49668.07[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]France[/TD]
[TD]$16866173.23[/TD]
[TD]$2131.26[/TD]
[TD]$52136.87[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]Jesuit[/TD]
[TD]$10[/TD]
[TD][/TD]
[TD]$50[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]Louie[/TD]
[TD]$26011168.43[/TD]
[TD]$21966.21[/TD]
[TD]$29037.61[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

Tour page:
[TABLE="class: grid, width: 500, align: center"]
<tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]Merchant Name[/TD]
[TD]Merchant Number[/TD]
[TD]Code[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]Tour[/TD]
[TD]444501[/TD]
[TD]5212[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]Tour[/TD]
[TD]444503[/TD]
[TD]5212[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]Tour[/TD]
[TD]444509[/TD]
[TD]5444[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]


France page:

[TABLE="class: grid, width: 500, align: center"]
<tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]Merchant Name[/TD]
[TD]Merchant Number[/TD]
[TD]Code[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]France[/TD]
[TD]444508[/TD]
[TD]5212[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]France[/TD]
[TD]444510[/TD]
[TD]1258[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]France[/TD]
[TD]444523[/TD]
[TD]6666[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]France[/TD]
[TD]444588[/TD]
[TD]5212[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]


Final Analysis page after new formula generated:

[TABLE="class: grid, width: 500, align: center"]
<tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[TD]E[/TD]
[TD]F[/TD]
[TD]G[/TD]
[TD]H[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]Merchant Name[/TD]
[TD]Total Volume[/TD]
[TD]Monthly Amount[/TD]
[TD]Sales[/TD]
[TD]Code[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]Tour[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]5212[/TD]
[TD]5444[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]France[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]5212[/TD]
[TD]1258[/TD]
[TD]6666[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]Jesuit[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]Louie[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

Let me know if that helps or if I'm confusing. Thank you very much.

Thanks,
Mirian

 

[Andrew Poulsom]

Example:

Excel 2010
	A	B	C	D	E	F	G
1	Merchant Name	Total Volume	Monthly Amount	Sales	Code
2	Tour	£2824396.97	£2592.64	£49668.07	5212	5444
3	France	£16866173.23	£2131.26	£52136.87	5212	1258	6666
4	Jesuit	£10.00		£50.00
5	Louie	£26011168.43	£21966.21	£29037.61
Sheet1

Cell Formulas
Range	Formula
E2	{=INDEX(INDIRECT("'"&$A2&"'!C2:C100"),SMALL(IF(FREQUENCY(IF(INDIRECT("'"&$A2&"'!C2:C100")<>"",MATCH(INDIRECT("'"&$A2&"'!C2:C100"),INDIRECT("'"&$A2&"'!C2:C100"),0)),ROW($E$2:$E$100)-ROW($E$2)+1),ROW($E$2:$E$100)-ROW($E$2)+1),COLUMNS($E2:E2)))}
Press CTRL+SHIFT+ENTER to enter array formulas.

 

[mimih23]

Example:

Excel 2010

	A	B	C	D	E	F	G
Merchant Name	Total Volume	Monthly Amount	Sales	Code
Tour
France
Jesuit
Louie

<COLGROUP><COL><COL><COL><COL><COL><COL><COL><COL></COLGROUP><THEAD>
</THEAD><TBODY>
[TD="align: center"]1[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]2[/TD]

[TD="align: right"]£2824396.97[/TD]
[TD="align: right"]£2592.64[/TD]
[TD="align: right"]£49668.07[/TD]
[TD="align: right"]5212[/TD]
[TD="align: right"]5444[/TD]
[TD="align: right"][/TD]

[TD="align: center"]3[/TD]

[TD="align: right"]£16866173.23[/TD]
[TD="align: right"]£2131.26[/TD]
[TD="align: right"]£52136.87[/TD]
[TD="align: right"]5212[/TD]
[TD="align: right"]1258[/TD]
[TD="align: right"]6666[/TD]

[TD="align: center"]4[/TD]

[TD="align: right"]£10.00[/TD]
[TD="align: right"][/TD]
[TD="align: right"]£50.00[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]5[/TD]

[TD="align: right"]£26011168.43[/TD]
[TD="align: right"]£21966.21[/TD]
[TD="align: right"]£29037.61[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

</TBODY>

Sheet1​

[TABLE="width: 85%"]
<TBODY>[TR]
[TD]Array Formulas[TABLE="width: 100%"]
<THEAD>[TR="bgcolor: #dae7f5"]
[TH="width: 10"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</THEAD><TBODY>[TR]
[TH="width: 10, bgcolor: #dae7f5"]E2[/TH]
[TD="align: left"]{=INDEX(INDIRECT("'"&$A2&"'!C2:C100"),SMALL(IF(FREQUENCY(IF(INDIRECT("'"&$A2&"'!C2:C100")<>"",MATCH(INDIRECT("'"&$A2&"'!C2:C100"),INDIRECT("'"&$A2&"'!C2:C100"),0)),ROW($E$2:$E$100)-ROW($E$2)+1),ROW($E$2:$E$100)-ROW($E$2)+1),COLUMNS($E2:E2)))}[/TD]
[/TR]
</TBODY>[/TABLE]
Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}.
Note: Do not try and enter the {} manually yourself[/TD]
[/TR]
</TBODY>[/TABLE]

I'm going to assume that maybe my issue is that I'm using Excel 2007. I'm yielding the following:

	A	B	C	D	E	F	G
Merchant Name	Total Volume	Monthly Amount	Sales	Code
Tour
France
Jesuit
Louie

<COLGROUP><COL><COL><COL><COL><COL><COL><COL><COL></COLGROUP><THEAD>
</THEAD><TBODY>
[TD="align: center"]1[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]2[/TD]

[TD="align: right"]£2824396.97[/TD]
[TD="align: right"]£2592.64[/TD]
[TD="align: right"]£49668.07[/TD]
[TD="align: right"] 5444 [/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]3[/TD]

[TD="align: right"]£16866173.23[/TD]
[TD="align: right"]£2131.26[/TD]
[TD="align: right"]£52136.87[/TD]
[TD="align: right"] 5212 [/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]4[/TD]

[TD="align: right"]£10.00[/TD]
[TD="align: right"][/TD]
[TD="align: right"]£50.00[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]5[/TD]

[TD="align: right"]£26011168.43[/TD]
[TD="align: right"]£21966.21[/TD]
[TD="align: right"]£29037.61[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

</TBODY>

It's not spreading to the cells to the right. It's only giving me the first known code. Any ideas? Also, the longest sheet is approx. over 8000 codes in one column with duplicates. Let me know.

Mirian

 

[mimih23]

Never mind it works!!! I just have to drag it to the right until I receive a #NUM! THis is perfect! Thank you!

 

[Andrew Poulsom]

To avoid the #NUM! error you can use:

=IFERROR(INDEX(INDIRECT("'"&$A2&"'!C2:C100"),SMALL(IF(FREQUENCY(IF(INDIRECT("'"&$A2&"'!C2:C100")<>"",MATCH(INDIRECT("'"&$A2&"'!C2:C100"),INDIRECT("'"&$A2&"'!C2:C100"),0)),ROW($E$2:$E$100)-ROW($E$2)+1),ROW($E$2:$E$100)-ROW($E$2)+1),COLUMNS($E2:E2))),"")

 

[mimih23]

To avoid the #NUM! error you can use:

=IFERROR(INDEX(INDIRECT("'"&$A2&"'!C2:C100"),SMALL(IF(FREQUENCY(IF(INDIRECT("'"&$A2&"'!C2:C100")<>"",MATCH(INDIRECT("'"&$A2&"'!C2:C100"),INDIRECT("'"&$A2&"'!C2:C100"),0)),ROW($E$2:$E$100)-ROW($E$2)+1),ROW($E$2:$E$100)-ROW($E$2)+1),COLUMNS($E2:E2))),"")

Ok good. One other thing. If I know that data exists but it yields #N/A in the first cell, what could be the reason for that? Formatting? It's hit or miss over the whole range so an easy manual update but curious what's causing that.