# Combining rows in dynamic array



## depcdivr (Jan 4, 2023)

I am trying to create a dynamic array of data from a large set of data so that I can create an interactive chart.  I have the following columns

UnitNameRegionRepMonthYearPrice

I can create the dynamic array but I run into multiple rows where the first 6 columns are the same but only the last one is different.  Is there a way to automatically combine those rows into one where the price is the sum of combinations where the first 6 columns are identical? 

For example I have have these three rows in the array

Unit 1DepcdivrEastNoneJan2022$10,000Unit 1DepcdivrEastNoneJan2022$20,000

Unit 1DepcdivrEastNoneJan2022$15,000

and I want to combine them to be 


Unit 1DepcdivrEastNoneJan2022$45,000

I know this can all be handled by a pivot table the ultimate goal is to create an interactive chart where you can select different sets of data with just a click of a button.


----------



## jkpieterse (Jan 4, 2023)

You should be able to use SUMIFS to do this?


----------



## depcdivr (Jan 4, 2023)

jkpieterse said:


> You should be able to use SUMIFS to do this?


How would I use the sumifs if I am using a filter function to create the dynamic array?


----------



## Fluff (Jan 4, 2023)

What version of Excel are you using?

I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

Also what is your current formula?


----------



## depcdivr (Jan 4, 2023)

Updated profile.  Using office 365.

I am using the following in order to create my dynamic array =FILTER(Data!L:L,Data!$Q:$Q>0) where the L:L changes based on what column I am populating


----------



## Fluff (Jan 4, 2023)

Thanks for that.

Can you post a sample of your original data, as that formula does not really tell us much.

MrExcel has a tool called “XL2BB” that lets you post samples of your data that will allow us to copy/paste it to our Excel spreadsheets, so we can work with the same copy of data that you are. Instructions on using this tool can be found here: XL2BB Add-in

Note that there is also a "Test Here” forum on this board. This is a place where you can test using this tool (or any other posting techniques that you want to test) before trying to use those tools in your actual posts.


----------



## depcdivr (Jan 4, 2023)

2022 TOTAL YEAR DATA.xlsxABCDEFGHIJKLMNOPQ1UnitCust No.NameRegionRep No.MonthYearItemItem ClassMarket CodeOrder QtyExt PriceMargin %User IDDateWeekshow2SGI908000CARLTON-BATES CO.59112022B127J11Z3Q22M501120010250$1,125.000.49575AWILLIAMS1/4/2022113SGI9E+07COLLINS AVIONICS GROUP31012022JT-2477501210030-2($20.92)0.86668AMIKULA1/4/2022114SGI9E+07COLLINS AVIONICS GROUP31012022JT-24775012100302$20.920.86659AMIKULA1/4/2022115SGI9E+07COLLINS AVIONICS GROUP31012022JT-24775012100302$20.920.86659AMIKULA1/4/202211Data


----------



## Fluff (Jan 4, 2023)

Thanks for that.
How about
Fluff.xlsmABCDEFGHIJKLMNOPQ1UnitCust No.NameRegionRep No.MonthYearItemItem ClassMarket CodeOrder QtyExt PriceMargin %User IDDateWeekshow2SGI908000CARLTON-BATES CO.59112022B127J11Z3Q22M50112001025011250.49575067AWILLIAMS01/04/2022113SGI90368000COLLINS AVIONICS GROUP31012022JT-2477501210030-2-20.920.86667623AMIKULA01/04/2022114SGI90368000COLLINS AVIONICS GROUP31012022JT-2477501210030220.920.86659338AMIKULA01/04/2022115SGI90368000COLLINS AVIONICS GROUP31012022JT-2477501210030220.920.86659338AMIKULA01/04/2022116789UnitNameRegionRepMonthYearPrice10SGICARLTON-BATES CO.59112022112511SGICOLLINS AVIONICS GROUP3101202220.9212Sheet1Cell FormulasRangeFormulaA10:G11A10=LET(u,UNIQUE(FILTER(CHOOSECOLS(A2:L6,1,3,4,5,6,7),D2:D6>0)),HSTACK(u,SUMIFS(L2:L6,A2:A6,CHOOSECOLS(u,1),C2:C6,CHOOSECOLS(u,2),F2:F6,CHOOSECOLS(u,5),G2:G6,CHOOSECOLS(u,6))))Dynamic array formulas.


----------



## depcdivr (Jan 4, 2023)

Sweet that worked.  

Can you explain how it does what it does for my own knowledge


----------



## Fluff (Jan 4, 2023)

Choosecols allows you to return the values from specific columns & they are then used as the criteria for the sumifs.


----------



## depcdivr (Jan 4, 2023)

I am trying to create a dynamic array of data from a large set of data so that I can create an interactive chart.  I have the following columns

UnitNameRegionRepMonthYearPrice

I can create the dynamic array but I run into multiple rows where the first 6 columns are the same but only the last one is different.  Is there a way to automatically combine those rows into one where the price is the sum of combinations where the first 6 columns are identical? 

For example I have have these three rows in the array

Unit 1DepcdivrEastNoneJan2022$10,000Unit 1DepcdivrEastNoneJan2022$20,000

Unit 1DepcdivrEastNoneJan2022$15,000

and I want to combine them to be 


Unit 1DepcdivrEastNoneJan2022$45,000

I know this can all be handled by a pivot table the ultimate goal is to create an interactive chart where you can select different sets of data with just a click of a button.


----------



## depcdivr (Jan 4, 2023)

One last question pertaining to this application.  I want to add a sortby so that the data will always be sorted by largest to smallest.  I know how the sort by works but what two arguments do I use in it.  Well  the array would be the formula from above but how would you specify the  sortby value?


----------



## Fluff (Jan 4, 2023)

To sort use

```
=LET(u,UNIQUE(FILTER(CHOOSECOLS(A2:L6,1,3,4,5,6,7),D2:D6>0)),SORT(HSTACK(u,SUMIFS(L2:L6,A2:A6,CHOOSECOLS(u,1),C2:C6,CHOOSECOLS(u,2),F2:F6,CHOOSECOLS(u,5),G2:G6,CHOOSECOLS(u,6))),7,-1))
```


----------

