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

danhendo888

Board Regular
Joined
Jul 15, 2019
Messages
129
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:

Some videos you may like

Excel Facts

Round to nearest half hour?
Use =MROUND(A2,"0:30") to round to nearest half hour. Use =CEILING(A2,"0:30") to round to next half hour.

mgirvin

Well-known Member
Joined
Dec 15, 2005
Messages
1,216
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
Joined
Dec 15, 2005
Messages
1,216

ADVERTISEMENT

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
Joined
Dec 15, 2005
Messages
1,216
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
Joined
Jul 15, 2019
Messages
129
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

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

Banned - Rules violations
Joined
Oct 24, 2015
Messages
7,499
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
 

danhendo888

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

Watch MrExcel Video

Forum statistics

Threads
1,127,566
Messages
5,625,545
Members
416,116
Latest member
Joemamasuka

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
Top