Combine & sum of multiple tables

[Saeed Ghazi Joolaee]

Hi all,

I have several tables with same size with some data (Tables "Data1","Data2", "Data3" in my example). I want to combine and extract sum data like Table "Result". Please help me.

Thanks.

 

[kchiba]

Hi Saeed,

You can use the COUNTIF formula, but you must ensure that the names are all the same. So Dr.James is not the same as Dr. James because of the space between the period and James.

 

[Saeed Ghazi Joolaee]

Thanks kchiba,

But is there any other way? something like PivotTable?

 

[Somendra Misra]

@Saeed Ghazi Joolaee

Can you combine all the tables into one master table?

Regards,

 

[Saeed Ghazi Joolaee]

@Somendra Misra

Thanks for your attention, My tables are over 50 and every table has 100 rows.
Doing manually is boring.

 

[Somendra Misra]

@Saeed Ghazi Joolaee

Why not use a macro to combine data and than create a PIVOT TABLE out of it.

Regards,

 

[prasaddn]

Hi All,

Just wondering what happened to earlier option of PT where it was allowing to create PT from multiple sheets?

Regards,
Prasad DN
PS: I know this should go as separate thread, but still thought it make sense to check in reference to this thread.

 

[prasaddn]

Hi Again,

Alt + d + p gives the PT wizard, where we can use multiple range as data for PT.

@OP
As one time effort you can add all the tables as your data source and make PT. when ever there is increase or decrease in table data the PT on refresh will accomodate the same.

Regards,
Prasad DN
PS: BTW, I am confused if my previous post should be deleted or retained.

 

[Saeed Ghazi Joolaee]

Thanks to all, nice discussion,

@prasaddin nice question and also nice answer
@Somendra Misra, can you clearify me how to use a macro? please.

 

[Somendra Misra]

@prasaddn

No doubt Consolidating multiple range in Pivot is gr8, but if you see the output as required by OP in this case will not be possible through it. I already tried it before posting the suggestion of MACRO.

@Saeed Ghazi Joolaee

1. Will all the tables be on one sheet or multiple sheet?
2. Will the no.s of columns in each table is same or they can vary in future?
3. Do each table has a unique number or it's just a data dump.?

Regards,

 

[prasaddn]

@Somendra Misra, I did not go thru the output.

 

[Saeed Ghazi Joolaee]

1. Will all the tables be on one sheet or multiple sheet?
2. Will the no.s of columns in each table is same or they can vary in future?
3. Do each table has a unique number or it's just a data dump.?

answers:
1- All tables are in one sheet by now.
2- Number of Columns and Rows are the same in all tables.
3- By now I had't defined a name for tables.

Actually a consolidated Pivot table can't fulfil my request, too; i.e can't calculate sum of "Sessions" column.

I separated tables only because I want an Auto Sum cell for every page on print. If there is a way that I can have an AutoSum cell under a column like "Sessions" which is Autosums only that printed page, I can combine tables.

Regards.

 

[Somendra Misra]

Thanks kchiba,

But is there any other way? something like PivotTable?

Actually a consolidated Pivot table can't fulfil my request, too; i.e can't calculate sum of "Sessions" column.

Both above comments are contradictory.

I had given suggestion based on your above comment otherwise we can do the summary in your sample file with EXCEL in-built formulas only.

Can you clarify?

Regards,

 

[Saeed Ghazi Joolaee]

Thanks Somendra Misra,

I only said "something like PivotTable" not "exactly PivotTable". I'm not professional.

I'm Ok with formulas but my tables' data are very extensive and flexible. If there is a formula that can extract each data (i.e. can find doctors names automatically) and then counts them It is Ok.