Taking all matches from multiple criteria search into different columns or taking n'th match into specific column

[Biana]

The simplified version of what I need to solve for is shown below. I work in Excel Tables. In the example below one customer order might include several items. I need to rearrange for the output tables the data so that each order has 1 line and the different products purchased are shown in different columns. The multiple criteria which need to be answered for is that Order # matches and that the Line:Type needs to be "Line Item". I have tried combinations of Index/Small/If functions but it didn't work with Excel Tables. Could anyone help me please?

 

[Ezguy4u]

Would you consider using a VBA program?

 

[Biana]

Would you consider using a VBA program?

maybe, though i know close to 0 in vba. Also would it be possible to have though results still in the table? Or then using VBA we extract that data to another sheet and then link it to my output table already?

 

[Fluff]

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
Data

Cell Formulas
Range	Formula
E2:E4	E2	=UNIQUE(FILTER(A2:A100,B2:B100="Line Item"))
F2:G2,F4:G4,F3	F2	=TRANSPOSE(FILTER($C$2:$C$100,($A$2:$A$100=E2)*($B$2:$B$100="Line Item")))
Dynamic array formulas.

 

[Biana]

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
Data

Cell Formulas
Range	Formula
E2:E4	E2	=UNIQUE(FILTER(A2:A100,B2:B100="Line Item"))
F2:G2,F4:G4,F3	F2	=TRANSPOSE(FILTER($C$2:$C$100,($A$2:$A$100=E2)*($B$2:$B$100="Line Item")))
Dynamic array formulas.

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

 

[Fluff]

Glad you sorted it & thanks for the feedback.