How to extract multiple values (exc duplicates) with two criteria

[danhendo888]

Book1
	A	B										L
1	Vendor	Invoice										Reference
2	1000010034	2216705										8190052331
Burnard

Cell Formulas
Range	Formula
L2	L2	=(INDEX(Sheet2!$L$3:$L$21835, SMALL(IF(FREQUENCY(IF(Sheet2!$K$3:$K$21835=$B2, Sheet2!$L$3:$L$21835), Sheet2!$L$3:$L$21835), ROW(Sheet2!$L$3:$L$21835)-ROW(Sheet2!$L$3)+1), COLUMNS($L2:L2))))
Press CTRL+SHIFT+ENTER to enter array formulas.

Above is what I am using currently.
Searching with one criteria; the invoice number.

Is it possible to search using two criteria? (invoice number + vendor number)
The tricky part is that there are duplicate values in column L for a given invoice.
If possible, it should look something like the below.

Book1
	I		K	L	M
1	Vendor		Invoice	Reference
2	1000010034		2216705	8190052331
3	1000010034		2216705	8190052331
4	1000010034		2216705	8190000001
5	1000010034		2216705	8190000001
6
7
8	Output:
9	Vendor		Invoice	Reference
10	1000010034		2216705	8190000001	8190052331
Sheet2

 

[Marcelo Branco]

I have Excel 365 in another computer - I'll try..

M.

 

[sandy666]

and let me know how it works if I may ask

 

[Marcelo Branco]

Wow worked perfectly in 365

After loading..

	A​	B​	C​
1​	Vendor​	Invoice​	Reference​
2​	1000010034​	2216705​	8190052331, 8190000001​
3​	1000010035​	2216706​	8190000022​

<tbody>
</tbody>

Looks like we have a problem with PQ versions

M.

 

[sandy666]

maybe any update of PQ for Ex2010?

 

[Marcelo Branco]

Sandy,

Seeing better your image in post 21 I noticed that the Reference column contains
List
List

In my case, I don't see this (see post 25) neither in XL 2010 nor in XL 365 - I have the values.
What did I do differently? I followed exactly the steps of the video.

M.

 

[sandy666]

in PQ editor click on Personalização Adicionada step and you should see columns: Table and List
after this step you will not see List column because it's expanded "in place"

 

[Marcelo Branco]

Sandy,

Seeing better your image in post 21 I noticed that the Reference column contains
List
List

In my case, I don't see this (see post 25) neither in XL 2010 nor in XL 365 - I have the values.
What did I do differently? I followed exactly the steps of the video.

M.

Disregard this message

M.

 

[sandy666]

Disregard this message

Too late

 

[Marcelo Branco]

maybe any update of PQ for Ex2010?

I did an update of PQ for XL 2010 - downloaded and installed last version.
Same behavior - column Contagem (contains

-

) was loaded.

M.

 

[sandy666]

so this is a bug of old add-in version of PQ I think (probably the same will be in Ex2013 with PQ add-in)
MS p i s s off PQ add-in because they released Excel with PQ built-in.