Forum Discussion

Copper Contributor

May 08, 2023

Dynamic number of rows -

I have a table with col-a: supplier col-b: article col-c: order I would to create a new table with just the suppliers that have an order... so this means the number of rows is dynamic I ca...

Formulas and Functions

Diamond Contributor

May 08, 2023

For the legacy Excel and without VBA most probably that could be done with AGGREGATE() function which shall be drag down till empty rows appears

Another approach is Power Query it it's allowed in your environment.

In any case, it's better to discuss details having small sample file.

luispsimoes
Copper Contributor
May 08, 2023
SergeiBaklan
thanks.
Sorry, but I am not seeing how the aggregate would create dynamically the number of rows in the target table.... can you please help further and elaborate?

Here is an example file (with the fields that are relevant...)
supplier article orders
S1 A1 2
S2 A2
S3 A3 1
S1 A3 1
S1 A2
S2 A1
S2 A2
S3 A1

the target table should be something like
supplier spent
S1
S3

(obviously I will get additional infos for the spent from sumifs etc etc )

thanks!
- SergeiBaklan
  Diamond Contributor
  May 08, 2023
  luispsimoes
  
  If to copy/paste your first table how "spent" shall be generated in the second table? If that's actual sample why "spent" is empty in it?
  - luispsimoes
    Copper Contributor
    May 08, 2023
    "Spent" will be filled from columns in the supplier table (omitted because as mentioned they were not relevant, but as info: there is a price for the article and a total cost - order * cost)
    
    so "Spent" as mentioned will be calculated with sumifs.... based on the supplier that have orders
    
    the key is though: I just want a table with as many suppliers as the ones that have orders...
    
    thanks

Resources