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

 

[mgirvin]

I will think about a solution. In the meantime, check out this video of potential ideas that should work: https://www.youtube.com/watch?v=_8dgNqkV1f8

 

[mgirvin]

Maybe something like this:

Data in K1:M9:

Vendor Invoice Reference
1000010034 2216705 8190052331
1000010034 2216705 8190052331
1000010034 2216705 8190000001
1000010034 2216705 8190000001
1000010035 2216706 8190000022
1000010035 2216706 8190000022
1000010035 2216706 8190000022
1000010035 2216706 8190000022

Output (column headers and formulas looks like this (in range K22:O27:

Count Unique Records from the Vendor and Invoice Column
2

Vendor Invoice Count Reference Reference
1000010034 2216705 2 8190052331 8190000001
1000010035 2216706 1 8190052331

Formula in K23: =SUM(--(FREQUENCY($K$2:$K$9+$L$2:$L$9,$K$2:$K$9+$L$2:$L$9)>0))

Formula in K26, and copied through to L30: =IF(ROWS(K$26:K26)>$K$23,"",INDEX(K$2:K$9,AGGREGATE(15,6,(ROW($K$2:$K$9)-ROW($K$1))/(FREQUENCY($K$2:$K$9+$L$2:$L$9,$K$2:$K$9+$L$2:$L$9)>0),ROWS(K$26:K26))))

Formula in M26, and copied through to M30: =IF(L26="","",SUM(--(FREQUENCY(IF($K$2:$K$9+$L$2:$L$9=$K26+$L26,$M$2:$M$9),IF($K$2:$K$9+$L$2:$L$9=$K26+$L26,$M$2:$M$9))>0)))

Formula in N26, and copied through to O30: =IF(OR($M26="",COLUMNS($N26:N26)>$M26),"",INDEX($M$2:$M$9,AGGREGATE(15,6,(ROW($M$2:$M$9)-ROW($M$1))/(FREQUENCY(IF($K$2:$K$9+$L$2:$L$9=$K26+$L26,$M$2:$M$9),IF($K$2:$K$9+$L$2:$L$9=$K26+$L26,$M$2:$M$9))>0),COLUMNS($N26:N26))))

 

[mgirvin]

Sorry, I do not have the nice solution formatter that you had in your post.

 

[mgirvin]

Opps. The correct formula for N26, should be: =IF(OR($M26="",COLUMNS($N26:N26)>$M26),"",INDEX($M$2:$M$9,SMALL(IF(FREQUENCY(IF($K$2:$K$9+$L$2:$L$9=$K26+$L26,MATCH($M$2:$M$9,$M$2:$M$9,0)),ROW($M$2:$M$9)-ROW($M$1)),ROW($M$2:$M$9)-ROW($M$1)),COLUMNS($N26:N26))))

 

[mgirvin]

If you have Office 365 Insider Edition, there is a MUCH easier solution. Here it is:

Data in K1:M9:

Vendor Invoice Reference
1000010034 2216705 8190052331
1000010034 2216705 8190052331
1000010034 2216705 8190000001
1000010034 2216705 8190000001
1000010035 2216706 8190000022
1000010035 2216706 8190000022
1000010035 2216706 8190000022
1000010035 2216706 8190000022

Output (column headers and formulas looks like this (in range K22:O27:

Count Unique Records from the Vendor and Invoice Column
2

Vendor Invoice Count Reference Reference
1000010034 2216705 2 8190052331 8190000001
1000010035 2216706 1 8190052331

Formula in K23: =ROWS(UNIQUE(K2:L9))

Formula in K26, and it will spill automatically: =UNIQUE(K2:L9)

Formula in N26, and it will spill automatically: =TRANSPOSE(UNIQUE(FILTER($M$2:$M$9,$K$2:$K$9+$L$2:$L$9=K26+L26)))

 

[danhendo888]

If you have Office 365 Insider Edition, there is a MUCH easier solution

Hi Mike,

Thanks for checking out my question, I really appreciate it
I watched your video on this problem and downloaded the EMT1575 file.
The formulas are pretty insane so I will have to spend some time studying them.

FWIW we use Office Professional Plus 2016 at our company. I don't see our office adopting Office 365 anytime soon to be honest.

Thank you again, Mike!

 

[sandy666]

with PowerQuery aka Get&Transform:

[Table="width:, class:head"]
[tr=bgcolor:#FFFFFF][td=bgcolor:#5B9BD5]Vendor[/td][td=bgcolor:#5B9BD5]Invoice[/td][td=bgcolor:#5B9BD5]Reference[/td][td][/td][td=bgcolor:#70AD47]Vendor[/td][td=bgcolor:#70AD47]Invoice[/td][td=bgcolor:#70AD47]Ref[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#DDEBF7]1000010034[/td][td=bgcolor:#DDEBF7]2216705[/td][td=bgcolor:#DDEBF7]8190052331[/td][td][/td][td=bgcolor:#E2EFDA]

1000010034​

[/td][td=bgcolor:#E2EFDA]

2216705​

[/td][td=bgcolor:#E2EFDA]8190052331, 8190000001[/td][/tr]

[tr=bgcolor:#FFFFFF][td]1000010034[/td][td]2216705[/td][td]8190052331[/td][td][/td][td]

1000010035​

[/td][td]

2216706​

[/td][td]8190000022[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#DDEBF7]1000010034[/td][td=bgcolor:#DDEBF7]2216705[/td][td=bgcolor:#DDEBF7]8190000001[/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td]1000010034[/td][td]2216705[/td][td]8190000001[/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#DDEBF7]1000010035[/td][td=bgcolor:#DDEBF7]2216706[/td][td=bgcolor:#DDEBF7]8190000022[/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td]1000010035[/td][td]2216706[/td][td]8190000022[/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#DDEBF7]1000010035[/td][td=bgcolor:#DDEBF7]2216706[/td][td=bgcolor:#DDEBF7]8190000022[/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td]1000010035[/td][td]2216706[/td][td]8190000022[/td][td][/td][td][/td][td][/td][td][/td][/tr]
[/table]

[SIZE=1]// Table1
let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    Group = Table.Group(Source, {"Vendor", "Invoice"}, {{"Count", each _, type table}}),
    List = Table.AddColumn(Group, "Ref", each List.Distinct(Table.Column([Count],"Reference"))),
    Extract = Table.TransformColumns(List, {"Ref", each Text.Combine(List.Transform(_, Text.From), ", "), type text})
in
    Extract[/SIZE]

Reference can be splitted to columns

 

[danhendo888]

with PowerQuery aka Get&Transform

What do I need to learn (or search on Google/YouTube) in order to do this?
Most of the YouTube videos on PowerQuery are basic videos on 'how to clean data' and 'Load it'.

 

[mgirvin]

That is funny: tomorrows video shows how to do this with Power Query.

Important note: Microsoft says it is only in Office 365. There are other things besides Dynamic Arrays and the new Excel Calculation Engine that are also only in Office 365. Since that will be THE only version in a few years, if we can we should all get it now. Just show your office the difference between the old array formulas and the new array formulas and how Office 365 will make solutions at work significantly more efficient. Hopefully they will come to their senses.