need formula to merge data table

[sumitv2]

Good Morning All,


can someone please help me find a way or formula to merge given sample date in excel

sheet 1,2,3 &4 are my sample data table i want to merge all this table in sheet 5

[Pepe Le Mokko]

You can find all explanations at https://www.rondebruin.nl/ (try the Merge add-in)

[Bo_Ry]

Please try at C4

=SUMPRODUCT(SUMIFS(INDIRECT("Sheet"&{1;2;3;4}&"!"&{"C:C","D:D","E:E","F:F","G:G"}),INDIRECT("Sheet"&{1;2;3;4}&"!A:A"),$A4)*(CELL("contents",INDIRECT("Sheet"&{1;2;3;4}&"!"&{"C1","D1","E1","F1","G1"}))=C$1))

or ribbon Data> Consolidate

Consolidate.png

or Power Query
Change file path in Blue

Please Login or Register  to view this content.

[sumitv2]

data consolidation works fine but what if i have to match two condition row A and row b like in sheet 4

file attched

[Bo_Ry]

Data consolidation only work with 1 column,1 Row header

try
C4
=SUMPRODUCT(SUMIFS(INDIRECT("Sheet"&{1;2;3;4}&"!"&{"C:C","D:D","E:E","F:F","G:G"}),INDIRECT("Sheet"&{1;2;3;4}&"!A:A"),$A4,INDIRECT("Sheet"&{1;2;3;4}&"!B:B"),$B4)*(CELL("contents",INDIRECT("Sheet"&{1;2;3;4}&"!"&{"C1","D1","E1","F1","G1"}))=C$1))

or Power query

Please Login or Register  to view this content.