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 ca...
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?
- "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...
thanksDo you consider PivotTable? Keep the source as structured table, build on it PivotTable and only Refresh it when data is updated.
- 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