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

D

danhendo888

Board Regular
Joined
Jul 15, 2019
Messages
167
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:
Sort by date Sort by votes
Upvote 0

Excel Facts

Select a hidden cell
Somehide hide payroll data in column G? Press F5. Type G1. Enter. Look in formula bar while you arrow down through G.
danhendo888, I have full free Power Query Classes at YouTube. From Basics to hard core M Code. The MSPTDA class I teach is advanced, but if you study videos #3 - 12, it is most of what you need to know about Power Query, including videos, pdf notes (free book), Excel and Power BI files, and even practice problems: https://www.youtube.com/playlist?list=PLrRPvpgDmw0ks5W7U5NmDCU2ydSnNZA_1

Leave a comment below each video as you are watching and have fun learning : )
 
Upvote 0
mgirvin said:
danhendo888, I have full free Power Query Classes at YouTube. From Basics to hard core M Code. The MSPTDA class I teach is advanced, but if you study videos #3 - 12, it is most of what you need to know about Power Query, including videos, pdf notes (free book), Excel and Power BI files, and even practice problems: https://www.youtube.com/playlist?list=PLrRPvpgDmw0ks5W7U5NmDCU2ydSnNZA_1

Leave a comment below each video as you are watching and have fun learning : )
Click to expand...

I will check out the Power Query classes over the weekend, should be fun.

Regarding cell B16 in your EMT1575 excel file. You use $B$5:$B$11+$C$5:$C$11 in the FREQUENCY function.
This works for invoice numbers that are just numbers.
With invoices numbers that have text in them like:

NZXA0003617
02146433P1806
39979-2​

​
Can we tweak our formula OR would we need to extract the numbers from the text and THEN proceed as normal?
 
Last edited:
Upvote 0
Those video are not just light weekend reading : ( Video #9 alone would take the whole weekend, as it is about a lot of the nuts and bolts of M Code. For # 9 video, I think the pdf notes are 40 pages. The reason this class is not like the basics videos of cleaning and loading is because there is A LOT to learn and learning new things takes time. But if you want to learn, it is there for you for free, danhendo888 : )
 
Last edited:
Upvote 0
danhendo88, for text maybe these:

Formula in cell B16: =SUM(--(FREQUENCY(MATCH($B$5:$B$11&$C$5:$C$11,$B$5:$B$11&$C$5:$C$11,0),ROW($B$5:$B$11)-ROW($B$4))>0))

Formula in cell B19: =IF(ROWS(B$19:B19)>$B$16,"",INDEX(B$5:B$11,AGGREGATE(15,6,(ROW($B$5:$B$11)-ROW($B$4))/(FREQUENCY(MATCH($B$5:$B$11&$C$5:$C$11,$B$5:$B$11&$C$5:$C$11,0),ROW($B$5:$B$11)-ROW($B$4))>0),ROWS(B$19:B19))))

Formula in cell D19: =IF(C19="","",SUM(--(FREQUENCY(IF(($B$5:$B$11=$B19)*($C$5:$C$11=$C19),$D$5:$D$11),IF(($B$5:$B$11=$B19)*($C$5:$C$11=$C19),$D$5:$D$11))>0)))

Formula in cell E19: =IF(OR($D19="",COLUMNS($E19:E19)>$D19),"",INDEX($D$5:$D$11,SMALL(IF(FREQUENCY(IF(($B$5:$B$11=$B19)*($C$5:$C$11=$C19),MATCH($D$5:$D$11,$D$5:$D$11,0)),ROW($D$5:$D$11)-ROW($D$4)),ROW($D$5:$D$11)-ROW($D$4)),COLUMNS($E19:E19))))
 
Upvote 0
sandy666 said:
here is a video for this case: Video
Click to expand...


Sandy,

Thanks for sharing. Cool!

I did exactly the same, but after adding the custom column with the formula (= List.Distinct ...) and loading, I got the result below immediately (the last steps were not necessary).


[TABLE="class: grid"]
<tbody>[TR]
[TD="bgcolor: #DCE6F1"][/TD]
[TD="bgcolor: #DCE6F1"]
A​
[/TD]
[TD="bgcolor: #DCE6F1"]
B​
[/TD]
[TD="bgcolor: #DCE6F1"]
C​
[/TD]
[TD="bgcolor: #DCE6F1"]
D
​
[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]
6​
[/TD]
[TD]
Vendor​
[/TD]
[TD]
Invoice​
[/TD]
[TD]
Contagem​
[/TD]
[TD]
Reference​
[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]
7​
[/TD]
[TD]
1000010034​
[/TD]
[TD]
2216705​
[/TD]
[TD]
Table​
[/TD]
[TD]
8190052331, 8190000001​
[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]
8​
[/TD]
[TD]
1000010035​
[/TD]
[TD]
2216706​
[/TD]
[TD]
Table​
[/TD]
[TD]
8190000022​
[/TD]
[/TR]
</tbody>[/TABLE]



In Excel I deleted the "Contagem" column and ... bingo!

[TABLE="class: grid"]
<tbody>[TR]
[TD="bgcolor: #DCE6F1"][/TD]
[TD="bgcolor: #DCE6F1"]
A​
[/TD]
[TD="bgcolor: #DCE6F1"]
B​
[/TD]
[TD="bgcolor: #DCE6F1"]
C​
[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]
6​
[/TD]
[TD]
Vendor​
[/TD]
[TD]
Invoice​
[/TD]
[TD]
Reference​
[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]
7​
[/TD]
[TD]
1000010034​
[/TD]
[TD]
2216705​
[/TD]
[TD]
8190052331, 8190000001​
[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]
8​
[/TD]
[TD]
1000010035​
[/TD]
[TD]
2216706​
[/TD]
[TD]
8190000022​
[/TD]
[/TR]
</tbody>[/TABLE]


Is there a problem deleting the "Contagem" column (Count in English)?

M.
 
Upvote 0
You do not need to remove column (s) with Tables because these columns will not load into the worksheet.
 
Upvote 0
sandy666 said:
You do not need to remove column (s) with Tables because these columns will not load into the worksheet.
Click to expand...

Well, in my case the column was loaded ...


[TABLE="class: grid"]
<tbody>[TR]
[TD]
Vendor​
[/TD]
[TD]
Invoice​
[/TD]
[TD]
Contagem​
[/TD]
[TD]
Reference​
[/TD]
[/TR]
[TR]
[TD]
1000010034​
[/TD]
[TD]
2216705​
[/TD]
[TD]
Table​
[/TD]
[TD]
8190052331, 8190000001​
[/TD]
[/TR]
[TR]
[TD]
1000010035​
[/TD]
[TD]
2216706​
[/TD]
[TD]
Table​
[/TD]
[TD]
8190000022​
[/TD]
[/TR]
</tbody>[/TABLE]


(I deleted manually the square brackets around [Table] because I could not paste correctly the table with them)

M.
 
Upvote 0
You must log in or register to reply here.

Similar threads

I
  • Question Question
Find the second highest attribute based on criteria. I know its a combo of index(match(large, but I just can't get it to work correctly.
Replies
1
Views
570
StephenCrump
S
C
  • Question Question
Setting up a PO tracking file
Replies
5
Views
492
Micron
M
P
  • Question Question
Populating, aggregating and sorting multiple matching values in Excel
Replies
0
Views
199
Pbobas
P
M
  • Solved
VBA Help - Duplicate & Unwanted Data Removal
Replies
6
Views
935
MoonDancer
M
A
  • Solved
add formulas in last column after sorting for each duplicates ID
Replies
9
Views
131
Peter_SSs
Peter_SSs

Forum statistics

Threads
1,223,952
Messages
6,175,593
Members
452,654
Latest member
mememe101

Share this page

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