Merge two files or two tables

Question

I have two Excel files, in the first this table:

Date        Water  Eggs Potato
01/09/15    10     52   642

in second:

Date        Eggs   Potato Water Banana
01/09/15    50     300    15    10

In a result file or table I need:

Date        Water  Eggs Potato Banana
01/09/15    25     102  942    10

You can see that I have summed from first and second table. In first table, I don't have column Banana, but in result table its column must be inserted.

How to do this, might the problem be solved by any functions or do I need to use VBA?

score 1 · Answer 1 · edited May 23 '17 at 12:29

1

Sort the columns so that they are in the same order. Then append one table to the other but keep only one (complete) set of column labels. Apply the unpivot procedure detailed here. When you get to the new Table, create from it a PivotTable with Column in COLUMN, Row in ROWS and Value in VALUES as Sum of.

edited May 23 '17 at 12:29

Community

1
1

answered Oct 20 '15 at 06:55

pnuts

58,317
11
87
139

but, i have different colums. in first table, water in a second column. in a second table, water is fourth column. how to sum its ? – Valeriu Oct 20 '15 at 07:13

score 1 · Accepted Answer · answered Oct 20 '15 at 11:56

This solution assumes the following:

Both tables are located in the same worksheet at ranges B6:E8 and G6:K8 (based on response from the @Valeriu) . Ranges include headers, ranges to be adjusted as required by user (see Fig. 1)
Solution range is located at M6:Q12

All formulas are FormulaArray

(Formulas Array are entered by pressing [Ctrl] + [Shift] + [Enter] simultaneously)

Date : In order to extract a combined list of unique dates from both tables enter this FormulaArray in M7 then copy till last record

=IFERROR( INDEX( $B$6:$B$8, MATCH( 0, COUNTIF( M$6:M7, $B$6:$B$8 ), 0 ) * 1 ),
IFERROR( INDEX( $G$6:$G$8, MATCH( 0, COUNTIF( M$6:M7, $G$6:$G$8 ), 0 ) * 1 ), "" ))

Then to obtain the totals for each type enter this FormulaArray in cell N7 then copy till last record then copy to O7:Q7

=IF( EXACT( $M7, "" ), "",
IFERROR( SUMIF( $B$6:$B$8, $M7, INDEX( $C$6:$E$8, 0, MATCH( N$6, $C$6:$E$6, 0 ))), 0 )
+ IFERROR( SUMIF( $G$6:$G$8, $M7, INDEX( $H$6:$K$8, 0, MATCH( N$6, $H$6:$K$6, 0 ))), 0 ))

Merge two files or two tables

2 Answers2