Hi & welcome to MrExcel.
How about
+Fluff 1.xlsm |
---|
|
---|
| A | B | C | D | E | F | G |
---|
1 | | | | | | | |
---|
2 | 1 | Line Item | a | | 1 | a | b |
---|
3 | 1 | Line Item | b | | 2 | f | |
---|
4 | 1 | | c | | 3 | i | k |
---|
5 | 1 | | d | | | | |
---|
6 | 2 | | e | | | | |
---|
7 | 2 | Line Item | f | | | | |
---|
8 | 2 | | g | | | | |
---|
9 | 2 | | h | | | | |
---|
10 | 3 | Line Item | i | | | | |
---|
11 | 3 | | j | | | | |
---|
12 | 3 | Line Item | k | | | | |
---|
13 | 3 | | l | | | | |
---|
14 | | | | | | | |
---|
|
---|
Fluff, thank you! Your solution does solve the simplified example, however, I found a better solution which worked for me just now. Yours is simpler as a formula and I actually didn't know FIlter function, so thanks for that as well. However, it does force the result column to be following one after another. The solution provided below will be taking n'th repetition only and hence will allow me to organise columns as I wish (ie I would have Product 1, then product 1 price, then product 1 quantity etc instead to be grouping them like product 1, product 2, etc and so on). I appreciate I didn't mention those specifications in the question and you solved for the example provided.
The solution for me was the following:
=IFERROR(INDEX($C$2:$C$13,SMALL(IF(($A$2:$A$13=$E2)*($B$2:$B$13="Line Item"),ROW($A$2:$A$13)-ROW(INDEX($A$2:$A$13,1,1))+1),
2))," ") -array formula, where bolded 2 is the n'th repetition, so can be changed to 1 for product 1, to 2 for product 2, etc