Forum Discussion
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 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
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.
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!
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?
