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..

[TABLE="class: grid"]
<tbody>[TR]
[TD="bgcolor: #DCE6F1"][/TD]
[TD="bgcolor: #DCE6F1"]

A​

[/TD]
[TD="bgcolor: #DCE6F1"]

B​

[/TD]
[TD="bgcolor: #DCE6F1"]

C​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

1​

[/TD]
[TD]

Vendor​

[/TD]
[TD]

Invoice​

[/TD]
[TD]

Reference​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

2​

[/TD]
[TD]

1000010034​

[/TD]
[TD]

2216705​

[/TD]
[TD]

8190052331, 8190000001​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

3​

[/TD]
[TD]

1000010035​

[/TD]
[TD]

2216706​

[/TD]
[TD]

8190000022​

[/TD]
[/TR]
</tbody>[/TABLE]


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.