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

 

[sandy666]

I think I don't understand, where are the square brackets?

 

[Marcelo Branco]

M-CODE (in Portuguese...)

let
    Fonte = Excel.CurrentWorkbook(){[Name="Tabela2"]}[Content],
    #"Tipo Alterado" = Table.TransformColumnTypes(Fonte,{{"Vendor", Int64.Type}, {"Invoice", Int64.Type}, {"Reference", Int64.Type}}),
    #"Linhas Agrupadas" = Table.Group(#"Tipo Alterado", {"Vendor", "Invoice"}, {{"Contagem", each _, type table}}),
    #"Personalização Adicionada" = Table.AddColumn(#"Linhas Agrupadas", "Reference", each List.Distinct(Table.Column([Contagem],"Reference"))),
    #"Valores Extraídos" = Table.TransformColumns(#"Personalização Adicionada", {"Reference", each Text.Combine(List.Transform(_, Text.From), ", "), type text})
in
    #"Valores Extraídos"

M.

 

[Marcelo Branco]

After loading to Excel i see in the Contagem column exactly this
Contagem

M.

 

[sandy666]

M-CODE (in Portuguese...)

let
    Fonte = Excel.CurrentWorkbook(){[Name="Tabela2"]}[Content],
    #"Tipo Alterado" = Table.TransformColumnTypes(Fonte,{{"Vendor", Int64.Type}, {"Invoice", Int64.Type}, {"Reference", Int64.Type}}),
    #"Linhas Agrupadas" = Table.Group(#"Tipo Alterado", {"Vendor", "Invoice"}, {{"Contagem", each _, type table}}),
    #"Personalização Adicionada" = Table.AddColumn(#"Linhas Agrupadas", "Reference", each List.Distinct(Table.Column([Contagem],"Reference"))),
    #"Valores Extraídos" = Table.TransformColumns(#"Personalização Adicionada", {"Reference", each Text.Combine(List.Transform(_, Text.From), ", "), type text})
in
    #"Valores Extraídos"

M.

this is OK, all is the same, you can remove first Tipo Alterado

but this?

After loading to Excel i see in the Contagem column exactly this
Contagem

I really do know why column with Tables is loaded into the sheet

 

[Marcelo Branco]

Image

 

[Marcelo Branco]

this is OK, all is the same, you can remove first Tipo Alterado

but this?

I really do know why column with Tables is loaded into the sheet

See image above
By the way, i'm using Excel 2010 - not sure if this matter...

M.

 

[sandy666]

Ex2010? this is add-in PQ, maybe they stopped updating ,

could you post a picture from PQ Editor after last step (as I did in the post #21 ?

 

[Marcelo Branco]

Ex2010? this is add-in PQ, maybe they stopped updating ,

could you post a picture from PQ Editor after last step (as I did in the post #21 ?

Identical to yours, but after loading to Excel the column Contagem shows up

M.

 

[Marcelo Branco]

Image2

 

[sandy666]

IMHO this is an add-in and Excel version problem, maybe try reinstall add-in (hope this is not a problem of localised version)
I don't remember how it was before because I didn't use Ex2010/PQ from the years

option: if you've a friend with Excel 2016 or higher try your file there, I mean execute code, don't remove Tables column and load to the sheet then you'll see where problem is.

Edit: I just saw image so I think you'll need remove Tables column before loading to the sheet (I don't) as standard behaviour