How to Merge Two Dynamic Tables Into One Dynamic Table?

[BrettOlbrys1]

Hello. I have two different sources of data, and each dataset continually grows larger (source A & source B). I have a summary file that has two summary sheets (summary A & summary B). The relationship is:

Source A >>> Summary A
Source B >>> Summary B

I want to replace Summary A and Summary B with Summary C. The relationship would be:

Source A & Source B >>> Summary C

How do I create one "Summary C" sheet from two source sheets (A & B) when both A & B are constantly growing larger?

Thanks

Brett

 

[SanjayGMusafir]

Hello. I have two different sources of data, and each dataset continually grows larger (source A & source B). I have a summary file that has two summary sheets (summary A & summary B). The relationship is:

Source A >>> Summary A
Source B >>> Summary B

I want to replace Summary A and Summary B with Summary C. The relationship would be:

Source A & Source B >>> Summary C

How do I create one "Summary C" sheet from two source sheets (A & B) when both A & B are constantly growing larger?

Thanks

Brett

XLOOKUP can be an answer. But without seeing what kind of data you want to pull from Summary A & Summary B to Summary C, it's difficult to comment further.

Please share some data file or screenshot to understand things better.

 

[alansidman]

 

[BrettOlbrys1]

XLOOKUP can be an answer. But without seeing what kind of data you want to pull from Summary A & Summary B to Summary C, it's difficult to comment further.

Please share some data file or screenshot to understand things better.

This table represents Summary A from data source A:

Summary A (from Data Source A)
Customer	Quarter	Product	Total Qty	Date	Segment	Prod A	Prod B	Prod C
Cust B	1	Prod B	3,429	1/12/22	Seg A		3,429
Cust B	1	Prod B	3,800	2/4/22	Seg B		3,800
Cust D	1	Prod C	1,957	3/17/22	Seg A			1,957
Cust C	1	Prod A	2,188	3/22/22	Seg A	2,188
Cust A	1	Prod B	4,052	3/24/22	Seg A		4,052

This table represents Summary B from data source B (they are separate because they have different PRODUCTS):

Summary B (from Data Source B)
Customer	Quarter	Product	Total Qty	Date	Segment	Prod A	Prod B	Prod C
Cust G	1	Prod D	2,108	1/6/22	Seg B		2,108
Cust D	1	Prod E	2,821	1/12/22	Seg A		2,821
Cust B	1	Prod D	3,088	2/4/22	Seg A		3,088
Cust E	1	Prod F	2,500	3/15/22	Seg B		2,500
Cust F	1	Prod D	2,406	3/22/22	Seg C	2,406

Both Summary A and Summary B are individual line items and each summary has thousands of line items. Each summary grows because as products are sold, more line items will appear in each summary. I don't want two summaries, I only want one (Summary C as an example below).

How do I create a summary C that contains everything in Summary A and Summary B as Data Source A & B keeps growing every day?

Summary C (from Data Source A & Data Source B)
Customer	Quarter	Product	Total Qty	Date	Segment	Prod A	Prod B	Prod C
Cust G	1	Prod D	2,108	1/6/22	Seg B		2,108
Cust B	1	Prod B	3,429	1/12/22	Seg A		3,429
Cust D	1	Prod E	2,821	1/12/22	Seg A		2,821
Cust B	1	Prod B	3,800	2/4/22	Seg B		3,800
Cust B	1	Prod D	3,088	2/4/22	Seg A		3,088
Cust E	1	Prod F	2,500	3/15/22	Seg B		2,500
Cust D	1	Prod C	1,957	3/17/22	Seg A			1,957
Cust C	1	Prod A	2,188	3/22/22	Seg A	2,188
Cust F	1	Prod D	2,406	3/22/22	Seg C	2,406
Cust A	1	Prod B	4,052	3/24/22	Seg A		4,052

 

[BrettOlbrys1]

I don't think the Power Query will work because it says the first thing I need to do is convert my data into tables. The sheets I will be pulling from have a formula in cell B1 that is:

SORT(FILTER(INDEX('DATA - Summary Data B'!$A:$AV,SEQUENCE(ROWS('DATA - Summary Data B'!$A:$AV)),{22,21,6,48,2,7,8,20}),('DATA - Summary Data B'!$A:$A<>"")),{4,6},{1,-1})

When I try to create the table, it says there is a SPILL issue because Excel says "We can't SPILL within a Table."

 

[SanjayGMusafir]

This table represents Summary A from data source A:

Summary A (from Data Source A)
Customer	Quarter	Product	Total Qty	Date	Segment	Prod A	Prod B	Prod C
Cust B	1	Prod B	3,429	1/12/22	Seg A		3,429
Cust B	1	Prod B	3,800	2/4/22	Seg B		3,800
Cust D	1	Prod C	1,957	3/17/22	Seg A			1,957
Cust C	1	Prod A	2,188	3/22/22	Seg A	2,188
Cust A	1	Prod B	4,052	3/24/22	Seg A		4,052

This table represents Summary B from data source B (they are separate because they have different PRODUCTS):

Summary B (from Data Source B)
Customer	Quarter	Product	Total Qty	Date	Segment	Prod A	Prod B	Prod C
Cust G	1	Prod D	2,108	1/6/22	Seg B		2,108
Cust D	1	Prod E	2,821	1/12/22	Seg A		2,821
Cust B	1	Prod D	3,088	2/4/22	Seg A		3,088
Cust E	1	Prod F	2,500	3/15/22	Seg B		2,500
Cust F	1	Prod D	2,406	3/22/22	Seg C	2,406

Both Summary A and Summary B are individual line items and each summary has thousands of line items. Each summary grows because as products are sold, more line items will appear in each summary. I don't want two summaries, I only want one (Summary C as an example below).

How do I create a summary C that contains everything in Summary A and Summary B as Data Source A & B keeps growing every day?

Summary C (from Data Source A & Data Source B)
Customer	Quarter	Product	Total Qty	Date	Segment	Prod A	Prod B	Prod C
Cust G	1	Prod D	2,108	1/6/22	Seg B		2,108
Cust B	1	Prod B	3,429	1/12/22	Seg A		3,429
Cust D	1	Prod E	2,821	1/12/22	Seg A		2,821
Cust B	1	Prod B	3,800	2/4/22	Seg B		3,800
Cust B	1	Prod D	3,088	2/4/22	Seg A		3,088
Cust E	1	Prod F	2,500	3/15/22	Seg B		2,500
Cust D	1	Prod C	1,957	3/17/22	Seg A			1,957
Cust C	1	Prod A	2,188	3/22/22	Seg A	2,188
Cust F	1	Prod D	2,406	3/22/22	Seg C	2,406
Cust A	1	Prod B	4,052	3/24/22	Seg A		4,052

Your requirement is all together different than I initially thought, that's why data sample helps.

The best solution to your problem is merging data using power query. It can be achieved using pivot tables also. These two methods are fast and can append ever increasing data instantly with just a click. Due to Forum rules, I can't share any outside source link. But I have told you the way.