Forum Discussion
luispsimoes
May 08, 2023Copper Contributor
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...
luispsimoes
May 08, 2023Copper Contributor
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
May 08, 2023Diamond Contributor
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?
- luispsimoesMay 08, 2023Copper Contributor"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- SergeiBaklanMay 10, 2023Diamond Contributor
Do you consider PivotTable? Keep the source as structured table, build on it PivotTable and only Refresh it when data is updated.
- luispsimoesMay 11, 2023Copper Contributoryes.... 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