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]

This works in C2:D2 confirmed with Ctl+Shift+Enter:

=TRANSPOSE(INDIRECT("'"&A2&"'!C2:C3"))

 

[mimih23]

This works in C2:D2 confirmed with Ctl+Shift+Enter:

=TRANSPOSE(INDIRECT("'"&A2&"'!C2:C3"))

Thanks! The formula is definitely pulling the first item of the column but it's not transposing the rest of the data to the right of it filling the next cells. What do you suggest? Thanks for your help!

 

[Andrew Poulsom]

Select C2:D2 (or more columns if you want) enter the formula and press Ctrl+Shift+Enter.

 

[mimih23]

Is it possible to specify the number of cells based on the real time data on each page (pulled quarterly sometimes monthly) as well as delete duplicates at the same time? A lot I know. THanks again!!

 

[mimih23]

Is it possible to specify the number of cells based on the real time data on each page (pulled quarterly sometimes monthly) as well as delete duplicates at the same time? A lot I know. THanks again!!

For instance, first sheet has 1287 items in the the column needed. I need to transpose and delete duplicates at the same time, do we think that's possible? (also delete header)

But yep I understand now how to use Ctrl+Shift+Enter!! Thanks!!!!!
Mirian

 

[Andrew Poulsom]

How do you define a duplicate?

 

[mimih23]

How do you define a duplicate?

Using the original example:
The column yields 5-1-13 five times. I only need it to show 5-1-13 once on the analysis page. Let me know if that helps.

 

[Andrew Poulsom]

In C2 confirmed with Ctrl+Shift+Enter and copied across:

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

 

[mimih23]

In C2 confirmed with Ctrl+Shift+Enter and copied across:

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

Thanks! Should I change C100 to the last row within the sheet or keep it at 100?