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

danhendo888

Board Regular
Joined
Jul 15, 2019
Messages
142
Office Version
  1. 365
Platform
  1. Windows
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

Square and cube roots
The =SQRT(25) is a square root. For a cube root, use =125^(1/3). For a fourth root, use =625^(1/4).
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))))
 
Upvote 0
Sorry, I do not have the nice solution formatter that you had in your post.
 
Upvote 0
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))))
 
Upvote 0
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)))
 
Upvote 0
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!
 
Upvote 0
with PowerQuery aka Get&Transform:

VendorInvoiceReferenceVendorInvoiceRef
100001003422167058190052331
1000010034​
2216705​
8190052331, 8190000001
100001003422167058190052331
1000010035​
2216706​
8190000022
100001003422167058190000001
100001003422167058190000001
100001003522167068190000022
100001003522167068190000022
100001003522167068190000022
100001003522167068190000022

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
 
Upvote 0
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'.
 
Upvote 0
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.
 
Upvote 0

Forum statistics

Threads
1,213,524
Messages
6,114,117
Members
448,549
Latest member
brianhfield

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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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