# merge duplicates items for more than 16000 rows & 150 columns across sheets



## Alaa mg (Jan 2, 2023)

Hi

I  search  for  macro  to  merge duplicates items  for  about 16000 rows & 150 columns for  each  sheet 

so  should  match  items  in column B  across  sheets  and merge duplicates items and  summing  the  values  for  each  month   whether there are duplicates  items or  new items ,  should  show  the  whole  data  in sheet  summary   and  finally  should  insert  column AGGREGATE  to  sum  the  whole values  for  all of  the  months  .

every  time  I  will add  new  sheets and  sometimes  changes  data in  sheets have  already  existed  then  should  update  data in sheet summary 

as  to  columns  every  time  I  add new  columns  based on current month   for  each sheet .
some  data in some sheets
MUL.xlsxABCDEFGHIJKLMNOPQR1ITEMIDJANFEBMARAPRMAYJUNJULAUGSEPOCTNOVDECJANFEBMARAPR21MT ASGL-VEN SD-11210------------44-32C-ZER130--11------------43BB 2000MN TT GH90-----111--------54TRM 2000MN TT GH-16-111--123-33--33---65BD 234 NH GG7870--------------76BR 111/33M ER HJ------------6680187VB 1222** SS TF HJK45-----------2334--98VB 145** SS TF HJK-45-----11123-------109DEWR 1222** SS TF HJK3434--------------1110DAS 1222** SS TF HJK-35-------------44DA


MUL.xlsxABCDEFGHIJKLM1ITEMIDJANFEBMARAPRMAYJUNJULAUGSEPOCTNOV22C-ZER22--11-------310DAS 1222** SS TF HJK-45---------41MT ASGL-VEN SD-13410----22---1253MT ASGL-VEN SD-222---------3068VB 145** SS TF HJK-56----12----79DEWR 1222** SS TF HJK12340--------12MA

MUL.xlsxBCDEFGHIJKLMNOPQRS1IDJANFEBMARAPRMAYJUNJULAUGSEPOCTNOVDECJANFEBMARAPRMAY2BD 234 NH GG7070---------------3BR 111/33M ER HJ6680------------33--4VB 1222** SS TF HJK45----------------5C-ZER130--11-------------6BB 2000MN TT GH90-----111--------17TRM 2000MN TT GH-16-111--123-33--33--6-8MT ASGL-VEN SD-11210------------44--9VB 145** SS TF HJK-45-----11123--------10BVG2340/123-BBG12-12---230----110----SE

my goal should  be like  this 


MUL.xlsxABCDEFGHIJKLMNOPQRST1ITEMIDJANFEBMARAPRMAYJUNJULAUGSEPOCTNOVDECJANFEBMARAPRMAYAGGREGATE21MT ASGL-VEN SD-15830----22-------88-19832C-ZER282--33-------------31543BB 2000MN TT GH180-----222--------120554TRM 2000MN TT GH-32-222--246-66--66--6-44065BD 234 NH GG148140---------------28876BR 111/33M ER HJ6680----------668033--32587VB 1222** SS TF HJK90-----------2334---14798VB 145** SS TF HJK-146----1222246--------426109DEWR 1222** SS TF HJK15774--------12------2431110DAS 1222** SS TF HJK-80-------------44-1241211MT ASGL-VEN SD-222---------30------521312BVG2340/123-BBG12-12---230----110----352SUMMARY


----------



## jdellasala (Jan 2, 2023)

After pulling in the three source tables into Power Query:

```
let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Replaced Value" = Table.ReplaceValue(Source,"-","",Replacer.ReplaceValue,{"JAN", "FEB", "MAR", "APR", "MAY", "JUN", "JUL", "AUG", "SEP", "OCT", "NOV", "DEC", "JAN2", "FEB3", "MAR4", "APR5"}),
    #"Changed Type" = Table.TransformColumnTypes(#"Replaced Value",{{"JAN", Int64.Type}, {"FEB", Int64.Type}, {"MAR", Int64.Type}, {"APR", Int64.Type}, {"MAY", Int64.Type}, {"JUN", Int64.Type}, {"JUL", Int64.Type}, {"AUG", Int64.Type}, {"SEP", Int64.Type}, {"OCT", Int64.Type}, {"NOV", Int64.Type}, {"DEC", Int64.Type}, {"JAN2", Int64.Type}, {"FEB3", Int64.Type}, {"MAR4", Int64.Type}, {"APR5", Int64.Type}, {"ID", type text}, {"ITEM", Int64.Type}})
in
    #"Changed Type"
```
(Repeated with Table2 and Table3), they were appended to each other, and then each ID was grouped with each month summed:

```
let
    Source = Table.Combine({Table1, Table2, Table3}),
    RemovedItem = Table.RemoveColumns(Source,{"ITEM"}),
    #"Grouped Rows" = Table.Group(RemovedItem, {"ID"}, {{"Jan", each List.Sum([JAN]), type nullable number}, {"Feb", each List.Sum([FEB]), type nullable number}, {"Mar", each List.Sum([MAR]), type nullable number}, {"Apr", each List.Sum([APR]), type nullable number}, {"May", each List.Sum([MAY]), type nullable number}, {"Jun", each List.Sum([JUN]), type nullable number}, {"Jul", each List.Sum([JUL]), type nullable number}, {"Aug", each List.Sum([AUG]), type nullable number}, {"Sep", each List.Sum([SEP]), type nullable number}, {"Oct", each List.Sum([OCT]), type nullable number}, {"Nov", each List.Sum([NOV]), type nullable number}, {"Dec", each List.Sum([DEC]), type nullable number}, {"Jan2", each List.Sum([JAN2]), type nullable number}, {"Feb3", each List.Sum([FEB3]), type nullable number}, {"Mar4", each List.Sum([MAR4]), type nullable number}, {"Apr5", each List.Sum([APR5]), type nullable number}, {"May6", each List.Sum([MAY6]), type nullable number}}),
    #"Added Index" = Table.AddIndexColumn(#"Grouped Rows", "Item", 1, 1, Int64.Type),
    #"Reordered Columns" = Table.ReorderColumns(#"Added Index",{"Item", "ID", "Jan", "Feb", "Mar", "Apr", "May", "Jun", "Jul", "Aug", "Sep", "Oct", "Nov", "Dec", "Jan2", "Feb3", "Mar4", "Apr5", "May6"})
in
    #"Reordered Columns"
```
which yielded this:
Book1ABCDEFGHIJKLMNOPQRS35ItemIDJanFebMarAprMayJunJulAugSepOctNovDecJan2Feb3Mar4Apr5May6361MT ASGL-VEN SD-15830221288372C-ZER28233383BB 2000MN TT GH1802221394TRM 2000MN TT GH3222224666666405BD 234 NH GG148140416BR 111/33M ER HJ66806680331427VB 1222** SS TF HJK902334438VB 145** SS TF HJK1461222246449DEWR 1222** SS TF HJK15774124510DAS 1222** SS TF HJK80444611MT ASGL-VEN SD-222304712BVG2340/123-BBG1212230110Sheet2
You cannot have duplicate column headers in PQ which is why the second instances of months have numbers. There is no doubt that someone better versed in M Language could do the summing part of the Grouping more efficiently, and one down side is that if additional months were added to the largest table (Jun, Jul), they would not aggregate properly without this code being adjusted.
However, this was nearly all done with the Power Query UI (I manually renamed Index to Item), and as the three source tables updated, the final table would just need a refresh.


----------



## jdellasala (Jan 2, 2023)

The grouping and adding a SUM aggregation for each column was a pain! It occurred to me that a straight APPEND of the 3 source tables, loaded as Connections Only to the Data Model would allow for a much faster and easier way to get the desired information by loading the data to a Pivot Table:
Book1TUVWXYZAAABACADAEAFAGAHAIAJAK6Row Labels JAN FEB MAR APR MAY JUN JUL AUG SEP OCT NOV DEC JAN2 FEB3 MAR4 APR5 MAY67BB 2000MN TT GH18022218BD 234 NH GG1481409BR 111/33M ER HJ6680668033110BVG2340/123-BBG121223011011C-ZER2823312DAS 1222** SS TF HJK804413DEWR 1222** SS TF HJK157741214MT ASGL-VEN SD-1583022128815MT ASGL-VEN SD-2223016TRM 2000MN TT GH322222466666617VB 1222** SS TF HJK90233418VB 145** SS TF HJK146122224619Grand Total100359455223036490466654265114121511Sheet2
The first final output wasn't sorted, so here it is with the ID Sorted for comparison:
Book1AMANAOAPAQARASATAUAVAWAXAYAZBABBBCBDBE10ItemIDJanFebMarAprMayJunJulAugSepOctNovDecJan2Feb3Mar4Apr5May6113BB 2000MN TT GH1802221125BD 234 NH GG148140136BR 111/33M ER HJ668066803311412BVG2340/123-BBG1212230110152C-ZER282331610DAS 1222** SS TF HJK8044179DEWR 1222** SS TF HJK1577412181MT ASGL-VEN SD-158302212881911MT ASGL-VEN SD-22230204TRM 2000MN TT GH3222224666666217VB 1222** SS TF HJK902334228VB 145** SS TF HJK1461222246Sheet2


----------



## Alaa mg (Jan 3, 2023)

thanks 
but  I  said 


> I search for macro





> You cannot have duplicate column headers in PQ which is why the second instances of months have numbers.


I  believe  the  macro  does  that  even  if  there  is  duplicates  MONTH name  , could  depends  start  from  the  beginning column  to end  column  not  the  headers  , so  in  this  case duplicates  MONTH name doesn't not  affect  in  my  view  .


----------



## Alaa mg (Jan 4, 2023)

Could  be  vba experts?


----------



## Alaa mg (Jan 5, 2023)




----------

