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
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
You can find all explanations at https://www.rondebruin.nl/ (try the Merge add-in)
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.
data consolidation works fine but what if i have to match two condition row A and row b like in sheet 4
file attched
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.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Bookmarks