Microsoft Excel – Expand Counts into Rows Using Functions

microsoft excelworksheet-function

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 |

Screenshot of data table

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.

Related Question