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

Easy bullets in Excel
If you have a numeric keypad, press Alt+7 on numeric keypad to type a bullet in Excel.

Marcelo Branco

MrExcel MVP
Joined
Aug 23, 2010
Messages
16,486
Office Version
  1. 2019
  2. 2010
Platform
  1. Windows
Wow worked perfectly in 365

After loading..

A
B
C
1
Vendor​
Invoice​
Reference​
2
1000010034​
2216705​
8190052331, 8190000001​
3
1000010035​
2216706​
8190000022​

<tbody>
</tbody>


Looks like we have a problem with PQ versions ;)

M.
 

Marcelo Branco

MrExcel MVP
Joined
Aug 23, 2010
Messages
16,486
Office Version
  1. 2019
  2. 2010
Platform
  1. Windows

ADVERTISEMENT

Sandy,

Seeing better your image in post 21 I noticed that the Reference column contains
List
List

In my case, I don't see this (see post 25) neither in XL 2010 nor in XL 365 - I have the values.
What did I do differently? I followed exactly the steps of the video.

M.
 

sandy666

Banned - Rules violations
Joined
Oct 24, 2015
Messages
7,499
in PQ editor click on Personalização Adicionada step and you should see columns: Table and List
after this step you will not see List column because it's expanded "in place"
 
Last edited:

Marcelo Branco

MrExcel MVP
Joined
Aug 23, 2010
Messages
16,486
Office Version
  1. 2019
  2. 2010
Platform
  1. Windows

ADVERTISEMENT

Sandy,

Seeing better your image in post 21 I noticed that the Reference column contains
List
List

In my case, I don't see this (see post 25) neither in XL 2010 nor in XL 365 - I have the values.
What did I do differently? I followed exactly the steps of the video.

M.

Disregard this message :oops:

M.
 

Marcelo Branco

MrExcel MVP
Joined
Aug 23, 2010
Messages
16,486
Office Version
  1. 2019
  2. 2010
Platform
  1. Windows
maybe any update of PQ for Ex2010?

I did an update of PQ for XL 2010 - downloaded and installed last version.
Same behavior - column Contagem (contains
-
) was loaded.

M.
 
Last edited:

sandy666

Banned - Rules violations
Joined
Oct 24, 2015
Messages
7,499
so this is a bug of old add-in version of PQ I think (probably the same will be in Ex2013 with PQ add-in)
MS p i s s off PQ add-in because they released Excel with PQ built-in.
 

Watch MrExcel Video

Forum statistics

Threads
1,126,998
Messages
5,622,097
Members
415,876
Latest member
csibonga2k17

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