multi value lookup using offset etc

[whitbybob]

I have a workbook with two sheets
first sheet "P1" has
A B
1 Item Code
2 A1 Toy
3 A1 dress
4 A1 Mop
5 B1 Toy
6 B1 dress
7 B1 Mop
8 B1 house
Second sheet "Ug" has unique list of Items and should look like this
A B
1 Count 7
2 Item Code1 Code2 Code3 Code4
3 A1 Toy dress Mop
4 B1 Toy dress Mop house
The formula in B1 is =MATCH(REPT("z",40),'P1'!A:A)-(CELL("Row",A2)-1)
The formula in b3 is =IF(LEN(Ug!$B$1),VLOOKUP(A3,OFFSET('P1'!$A$1,0,0,Ug!$B$1,2),2,0),"")
The formula in c3 is
=IF(COUNTIF(OFFSET('P1'!$A$1,0,0,Ug!$B$1,1),$A3)>COUNTA($B3:B3),INDEX(OFFSET('P1'!$B$1,MATCH(B3,OFFSET('P1'!$B$1,0,0,Ug!$B$1,1),0),0,Ug!$B$1,1),MATCH($A3,OFFSET('P1'!$A$1,MATCH(B3,OFFSET('P1'!$B$1,0,0,Ug!$B$1,1),0),0,Ug!$B$1,1),0)),"")
The formula in d3 is copied from c3
=IF(COUNTIF(OFFSET('P1'!$A$1,0,0,Ug!$B$1,1),$A3)>COUNTA($B3:C3),INDEX(OFFSET('P1'!$B$1,MATCH(C3,OFFSET('P1'!$B$1,0,0,Ug!$B$1,1),0),0,$B$1,1),MATCH($A3,OFFSET('P1'!$A$1,MATCH(C3,OFFSET('P1'!$B$1,0,0,Ug!$B$1,1),0),0,Ug!$B$1,1),0)),"")

If I then copy down cells b3:d3 to line 4 I should get the values shown, but instead I get Toy Toy Toy Toy Toy

I believe this happens because the Match uses "0" as the match type allowing it to find any of the occurrences.
A look at my 800 line table shows some working correctly and others not correct.

Is there a way to fix this?

 

[GlennUK]

How about describing in English what it is that you are trying to achieve?

 

[whitbybob]

So I am trying to get a cross list of the rows. This will let me see that customer A1 bought Toy Dress and Mop.
Keep in mind that there are over 8000 lines in the real P1 sheet, and anywhere from 1 to 100 items per customer, so easier to see how the products are bought by a single customer line in the Ug sheet.

 

[whitbybob]

As a temporary fix, I added a series of fields calculating the row of each customer line in P1 then used that number for an Index lookup that gave me the desired results but not in a nice way.

 

[GlennUK]

If you want a crosstab type of analysis, how about using a PivotTable?