Excel 2013 - Combining Data with Different Number of Columns and/or Rows

[beadedbytes]

Hello, Is there an automated solution (without macros/VBA) for merging multiple Excel worksheets containing different number of columns and rows into one master? The worksheets are all located into one workbook.

Thank you in advance for feedback.

 

[SkipVought]

HI,

It all depends if and how the tables are related? Need a whole lot more description of the tables and how they are related.

An upload might be in order.

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[SkipVought]

Did you post your workbook in the wrong thread?

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[beadedbytes]

Yep, I did, Skip. My bad.
Doc re-posted here.

 

[SkipVought]

FAQ68-5287: NORMALIZE Your Table using the PivotTable Wizard
First you must NORMALIZE your data.

The result will be 3 tables with 3 columns. The data in the 3 columns can be appended into ONE TABLE with 3 columns.

From there you have the entire plethora of Excel features to analyze and report.

Let me know how its working for you.

If this ia an ongoing issue of nermalizing new data periodically, you may need some VBA

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[beadedbytes]

Skip,
I was hoping there was a way to consolidate *first* and then normalize. Thanks for feedback.

 

[SkipVought]

Okay try this...

We will use SampleWk_02 as the master, since the other two tabs have the same rows & columns.

COPY the date headings from the other tab to SampleWk_02.

Use Formulas > Defined Names > Create from selection on the other two tables, starting with the first tab and then the last tab

So here's the formula that combines the data...
[tt]
R6: =IFERROR(INDEX(INDIRECT("_"&TEXT(R$5,"mm")&"_"&TEXT(R$5,"dd")&"_"&TEXT(R$5,"yy")),MATCH($B6,SEGMT_PKG,0),1),0)+
IFERROR(INDEX(INDIRECT("SampleWk_01!_"&TEXT(R$5,"mm")&"_"&TEXT(R$5,"dd")&"_"&TEXT(R$5,"yy")),MATCH($B6,SEGMT_PKG,0),1),0)
[/tt]
...and copy paste down and across to the right.

Now you have ONE TABLE to Normalize.

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]