Dynamic number of rows -

[luispsimoes]

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 can do this pretty easy with VBA.... but the company doesn't want to allow macros.

Any ideas on the alternative implementation?

(also not office 365...  - yes all very old IT school)

thanks

[#2]

@luispsimoes 

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.

[#3]

@Sergei Baklan 

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!

[#4]

@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?

[#5]

"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

[#6]

@luispsimoes 

Do you consider PivotTable? Keep the source as structured table, build on it PivotTable and only Refresh it when data is updated.

[#7]

yes.... that is what I am doing now... just wanted to be sure there wasn't any other way outside using VBA (which I did too)
thanks