Subject: reverse partial lookup.

E

ellison

Active Member
Joined
Aug 1, 2012
Messages
356
Office Version
  1. 365
Platform
  1. Windows
Hi, we need to do a “reverse partial lookup”.

i.e. we have a list of old_product_codes (Col A) and new_product_codes (col B).

And we need to know if any of the new_product_codes contain (or equal) any of the old_product_codes

We have found ways of making this work on smaller lists, but we now have 317K new_product_codes and 40,000 old_product_codes.

And any method we have tried has timed out. Or crashed Excel.

If you have any suggestions, we would gladly take a look!

Quick Sample:[TABLE="class: grid, width: 500, align: center"]
<tbody>[TR]
[TD]Old_product_does(smaller list)[/TD]
[TD]blank[/TD]
[TD]new_product_codes(larger list)[/TD]
[TD]blank[/TD]
[TD]contain or equals[/TD]
[/TR]
[TR]
[TD][TABLE="width: 240"]
<tbody>[TR]
[TD="width: 240"]ZVN0106B[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][/TD]
[TD]ZV374[/TD]
[TD][/TD]
[TD]Y[/TD]
[/TR]
[TR]
[TD][TABLE="width: 240"]
<tbody>[TR]
[TD="width: 240"]ZV374[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][/TD]
[TD]ASSN01958141222[/TD]
[TD][/TD]
[TD]Y[/TD]
[/TR]
[TR]
[TD][TABLE="width: 240"]
<tbody>[TR]
[TD="width: 240"]MC04ZE047[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][/TD]
[TD][TABLE="width: 240"]
<tbody>[TR]
[TD="width: 240"]SMS996D1[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][/TD]
[TD]N[/TD]
[/TR]
[TR]
[TD][TABLE="width: 240"]
<tbody>[TR]
[TD="width: 240"]SN0195814[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][/TD]
[TD][TABLE="width: 240"]
<tbody>[TR]
[TD="width: 240"]LT1004MH25883[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][/TD]
[TD]N[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]1111ZVN0106B16[/TD]
[TD][/TD]
[TD]Y[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][TABLE="width: 240"]
<tbody>[TR]
[TD="width: 240"]54LS00BCAJC[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][/TD]
[TD]N[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][TABLE="width: 240"]
<tbody>[TR]
[TD="width: 240"]73X014M001[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][/TD]
[TD]N[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
 

Excel Facts

Can you AutoAverage in Excel?
There is a drop-down next to the AutoSum symbol. Open the drop-down to choose AVERAGE, COUNT, MAX, or MIN
Sort by date Sort by votes
maybe like this, with PowerQuery

[Table="width:, class:head"]
[tr=bgcolor:#FFFFFF][td=bgcolor:#5B9BD5]Old[/td][td][/td][td=bgcolor:#5B9BD5]New[/td][td][/td][td=bgcolor:#70AD47]New[/td][td=bgcolor:#70AD47]Old.1[/td][td=bgcolor:#70AD47]Custom[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#DDEBF7]ZVN0106B[/td][td][/td][td=bgcolor:#DDEBF7]ZV374[/td][td][/td][td=bgcolor:#E2EFDA]ZV374[/td][td=bgcolor:#E2EFDA]ZVN0106B[/td][td=bgcolor:#E2EFDA]N[/td][/tr]

[tr=bgcolor:#FFFFFF][td]ZV374[/td][td][/td][td]ASSN01958141222[/td][td][/td][td]ZV374[/td][td]ZV374[/td][td]Y[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#DDEBF7]MC04ZE047[/td][td][/td][td=bgcolor:#DDEBF7]SMS996D1[/td][td][/td][td=bgcolor:#E2EFDA]ZV374[/td][td=bgcolor:#E2EFDA]MC04ZE047[/td][td=bgcolor:#E2EFDA]N[/td][/tr]

[tr=bgcolor:#FFFFFF][td]SN0195814[/td][td][/td][td]LT1004MH25883[/td][td][/td][td]ZV374[/td][td]SN0195814[/td][td]N[/td][/tr]

[tr=bgcolor:#FFFFFF][td][/td][td][/td][td=bgcolor:#DDEBF7]1111ZVN0106B16[/td][td][/td][td=bgcolor:#E2EFDA]ASSN01958141222[/td][td=bgcolor:#E2EFDA]ZVN0106B[/td][td=bgcolor:#E2EFDA]N[/td][/tr]

[tr=bgcolor:#FFFFFF][td][/td][td][/td][td]54LS00BCAJC[/td][td][/td][td]ASSN01958141222[/td][td]ZV374[/td][td]N[/td][/tr]

[tr=bgcolor:#FFFFFF][td][/td][td][/td][td=bgcolor:#DDEBF7]73X014M001[/td][td][/td][td=bgcolor:#E2EFDA]ASSN01958141222[/td][td=bgcolor:#E2EFDA]MC04ZE047[/td][td=bgcolor:#E2EFDA]N[/td][/tr]

[tr=bgcolor:#FFFFFF][td][/td][td][/td][td][/td][td][/td][td]ASSN01958141222[/td][td]SN0195814[/td][td]Y[/td][/tr]

[tr=bgcolor:#FFFFFF][td][/td][td][/td][td][/td][td][/td][td=bgcolor:#E2EFDA]SMS996D1[/td][td=bgcolor:#E2EFDA]ZVN0106B[/td][td=bgcolor:#E2EFDA]N[/td][/tr]

[tr=bgcolor:#FFFFFF][td][/td][td][/td][td][/td][td][/td][td]SMS996D1[/td][td]ZV374[/td][td]N[/td][/tr]

[tr=bgcolor:#FFFFFF][td][/td][td][/td][td][/td][td][/td][td=bgcolor:#E2EFDA]SMS996D1[/td][td=bgcolor:#E2EFDA]MC04ZE047[/td][td=bgcolor:#E2EFDA]N[/td][/tr]

[tr=bgcolor:#FFFFFF][td][/td][td][/td][td][/td][td][/td][td]SMS996D1[/td][td]SN0195814[/td][td]N[/td][/tr]

[tr=bgcolor:#FFFFFF][td][/td][td][/td][td][/td][td][/td][td=bgcolor:#E2EFDA]LT1004MH25883[/td][td=bgcolor:#E2EFDA]ZVN0106B[/td][td=bgcolor:#E2EFDA]N[/td][/tr]

[tr=bgcolor:#FFFFFF][td][/td][td][/td][td][/td][td][/td][td]LT1004MH25883[/td][td]ZV374[/td][td]N[/td][/tr]

[tr=bgcolor:#FFFFFF][td][/td][td][/td][td][/td][td][/td][td=bgcolor:#E2EFDA]LT1004MH25883[/td][td=bgcolor:#E2EFDA]MC04ZE047[/td][td=bgcolor:#E2EFDA]N[/td][/tr]

[tr=bgcolor:#FFFFFF][td][/td][td][/td][td][/td][td][/td][td]LT1004MH25883[/td][td]SN0195814[/td][td]N[/td][/tr]

[tr=bgcolor:#FFFFFF][td][/td][td][/td][td][/td][td][/td][td=bgcolor:#E2EFDA]1111ZVN0106B16[/td][td=bgcolor:#E2EFDA]ZVN0106B[/td][td=bgcolor:#E2EFDA]Y[/td][/tr]

[tr=bgcolor:#FFFFFF][td][/td][td][/td][td][/td][td][/td][td]1111ZVN0106B16[/td][td]ZV374[/td][td]N[/td][/tr]

[tr=bgcolor:#FFFFFF][td][/td][td][/td][td][/td][td][/td][td=bgcolor:#E2EFDA]1111ZVN0106B16[/td][td=bgcolor:#E2EFDA]MC04ZE047[/td][td=bgcolor:#E2EFDA]N[/td][/tr]

[tr=bgcolor:#FFFFFF][td][/td][td][/td][td][/td][td][/td][td]1111ZVN0106B16[/td][td]SN0195814[/td][td]N[/td][/tr]

[tr=bgcolor:#FFFFFF][td][/td][td][/td][td][/td][td][/td][td=bgcolor:#E2EFDA]54LS00BCAJC[/td][td=bgcolor:#E2EFDA]ZVN0106B[/td][td=bgcolor:#E2EFDA]N[/td][/tr]

[tr=bgcolor:#FFFFFF][td][/td][td][/td][td][/td][td][/td][td]54LS00BCAJC[/td][td]ZV374[/td][td]N[/td][/tr]

[tr=bgcolor:#FFFFFF][td][/td][td][/td][td][/td][td][/td][td=bgcolor:#E2EFDA]54LS00BCAJC[/td][td=bgcolor:#E2EFDA]MC04ZE047[/td][td=bgcolor:#E2EFDA]N[/td][/tr]

[tr=bgcolor:#FFFFFF][td][/td][td][/td][td][/td][td][/td][td]54LS00BCAJC[/td][td]SN0195814[/td][td]N[/td][/tr]

[tr=bgcolor:#FFFFFF][td][/td][td][/td][td][/td][td][/td][td=bgcolor:#E2EFDA]73X014M001[/td][td=bgcolor:#E2EFDA]ZVN0106B[/td][td=bgcolor:#E2EFDA]N[/td][/tr]

[tr=bgcolor:#FFFFFF][td][/td][td][/td][td][/td][td][/td][td]73X014M001[/td][td]ZV374[/td][td]N[/td][/tr]

[tr=bgcolor:#FFFFFF][td][/td][td][/td][td][/td][td][/td][td=bgcolor:#E2EFDA]73X014M001[/td][td=bgcolor:#E2EFDA]MC04ZE047[/td][td=bgcolor:#E2EFDA]N[/td][/tr]

[tr=bgcolor:#FFFFFF][td][/td][td][/td][td][/td][td][/td][td]73X014M001[/td][td]SN0195814[/td][td]N[/td][/tr]
[/table]

Code: 
[SIZE=1]let
    Source = Table1,
    #"Added Custom" = Table.AddColumn(Source, "Old", each Table2),
    #"Expanded Old" = Table.ExpandTableColumn(#"Added Custom", "Old", {"Old"}, {"Old.1"}),
    #"Added Conditional Column" = Table.AddColumn(#"Expanded Old", "Custom", each if Text.Contains([New], [Old.1]) then "Y" else "N")
in
    #"Added Conditional Column"[/SIZE]
 
Upvote 0
That looks excellent, that'll be our first "power query" so we may be on a bit of a learning curve getting that working!
BUT certainly look forward to getting my teeth into that..............
Thanks
 
Upvote 0
Excellent, that is a MUCH better place for me to start than the scratching of my head that I was doing, ha
 
Upvote 0
Hi,

Here's another way, formula copied down:


Book1
ABCDE
1Old_product_does(smaller list)new_product_codes(larger list)contain or equals
2ZVN0106BZV374Y
3ZV374ASSN01958141222Y
4MC04ZE047SMS996D1N
5SN0195814LT1004MH25883N
61111ZVN0106B16Y
754LS00BCAJCN
873X014M001N
Sheet384
Cell Formulas
RangeFormula
E2=IF(ISNUMBER(LOOKUP(2,1/SEARCH(A$2:A$5,C2))),"Y","N")
 
Upvote 0
You must log in or register to reply here.

Similar threads

E
  • Question Question
tweak a formula from "contains" to "starts with"
Replies
2
Views
503
ellison
E
D
  • Question Question
Return lookup/index(match) list based on partial text - with no blanks
Replies
5
Views
951
Fluff
Fluff
L
  • Question Question
Find Partial Duplicate values in a column
2 3
Replies
20
Views
15K
leeandoona
L
T
  • Question Question
Lookup from multiple criteria, including potential partial matches
Replies
0
Views
637
trancer78
T
Q
  • Locked
  • Question Question
Help with dynamic unique list
Replies
4
Views
693
Fluff
Fluff

Forum statistics

Threads
1,224,823
Messages
6,181,178
Members
453,021
Latest member
Justyna P

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