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

#### danhendo888

##### Board Regular
Book1
ABL
1VendorInvoiceReference
2100001003422167058190052331
Burnard
Cell Formulas
RangeFormula
L2L2=(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
IKLM
1VendorInvoiceReference
2100001003422167058190052331
3100001003422167058190052331
4100001003422167058190000001
5100001003422167058190000001
6
7
8Output:
9VendorInvoiceReference
101000010034221670581900000018190052331
Sheet2

Last edited by a moderator:

### Excel Facts

If Excel says you have links but you can't find them, go to Formulas, Name Manager. Look for old links to dead workbooks & delete.

#### mgirvin

##### Well-known Member
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

##### Well-known Member
Sorry, I do not have the nice solution formatter that you had in your post.

#### mgirvin

##### Well-known Member

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

##### Well-known Member
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

##### Board Regular

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

##### Banned - Rules violations
with PowerQuery aka Get&Transform:

 Vendor Invoice Reference Vendor Invoice Ref 1000010034 2216705 8190052331 1000010034​ 2216705​ 8190052331, 8190000001 1000010034 2216705 8190052331 1000010035​ 2216706​ 8190000022 1000010034 2216705 8190000001 1000010034 2216705 8190000001 1000010035 2216706 8190000022 1000010035 2216706 8190000022 1000010035 2216706 8190000022 1000010035 2216706 8190000022

Code:
``````[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

##### Board Regular
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

##### Well-known Member
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.

Replies
3
Views
238
Replies
2
Views
202
Replies
1
Views
100
Replies
8
Views
749
Replies
12
Views
533

1,130,281
Messages
5,641,288
Members
417,202
Latest member
AndyVBA

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back