I am adding a lookup sheet onto a large dataset of inventory for work, there are multiple jobs mixed on the list and potentially multiple lines of a given item if it has revisions. It is possible for the same item number to appear in more than one job
Column A has control numbers, columns D++ have results table working with index/match( )
What I am trying to put in B & C are helper functions column B is simply a countif( ) of values in A:A what I need help with is expanding the counts into row references in column C something like this:
| Row | A | B | C |
|-----|------|---|---|
| 4 | 1234 | 2 | 1 |
| 5 | 2332 | 1 | 1 |
| 6 | 9912 | 2 | 2 |
| 7 | 3785 | 3 | 3 |
| 8 | 3090 | 2 | 3 |
| 9 | | | 4 |
| 10 | | | 4 |
| 11 | | | 4 |
| 12 | | | 5 |
| 13 | | | 5 |
I can work with any kind of return value really, starting from 1 or actual row(4) or even reference a4 etc.
Any assistance would be a huge help thank you. {C+S+E} Array Functions are acceptable if needed.
Best Answer
I ended up finding what I was looking for. This Link uses helper columns with
VLOOKUP( )
range lookups.So in addition to your data add a column with the number of how many you want each to repeat.
A helper column starting from 1 that adds its previous with the repeat value
Then in the output area number the rows or just use the rows( ) to vlookup for the row number from the helper column that added app the repeat values and it will give you the correct row.