Subject: reverse partial lookup.

[ellison]

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:

Old_product_does(smaller list)	blank	new_product_codes(larger list)	blank	contain or equals
ZVN0106B <tbody> </tbody>		ZV374		Y
ZV374 <tbody> </tbody>		ASSN01958141222		Y
MC04ZE047 <tbody> </tbody>		SMS996D1 <tbody> </tbody>		N
SN0195814 <tbody> </tbody>		LT1004MH25883 <tbody> </tbody>		N
		1111ZVN0106B16		Y
		54LS00BCAJC <tbody> </tbody>		N
		73X014M001 <tbody> </tbody>		N

<tbody>
</tbody>

 

[sandy666]

maybe like this, with PowerQuery

Old		New		New	Old.1	Custom
ZVN0106B		ZV374		ZV374	ZVN0106B	N
ZV374		ASSN01958141222		ZV374	ZV374	Y
MC04ZE047		SMS996D1		ZV374	MC04ZE047	N
SN0195814		LT1004MH25883		ZV374	SN0195814	N
		1111ZVN0106B16		ASSN01958141222	ZVN0106B	N
		54LS00BCAJC		ASSN01958141222	ZV374	N
		73X014M001		ASSN01958141222	MC04ZE047	N
				ASSN01958141222	SN0195814	Y
				SMS996D1	ZVN0106B	N
				SMS996D1	ZV374	N
				SMS996D1	MC04ZE047	N
				SMS996D1	SN0195814	N
				LT1004MH25883	ZVN0106B	N
				LT1004MH25883	ZV374	N
				LT1004MH25883	MC04ZE047	N
				LT1004MH25883	SN0195814	N
				1111ZVN0106B16	ZVN0106B	Y
				1111ZVN0106B16	ZV374	N
				1111ZVN0106B16	MC04ZE047	N
				1111ZVN0106B16	SN0195814	N
				54LS00BCAJC	ZVN0106B	N
				54LS00BCAJC	ZV374	N
				54LS00BCAJC	MC04ZE047	N
				54LS00BCAJC	SN0195814	N
				73X014M001	ZVN0106B	N
				73X014M001	ZV374	N
				73X014M001	MC04ZE047	N
				73X014M001	SN0195814	N

[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]

 

[ellison]

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

 

[sandy666]

• Getting Started with Get & Transform in Excel
• Microsoft Power Query for Excel Help
• Excel Power Query (Get & Transform) Tutorial for Beginners

 

[ellison]

Excellent, that is a MUCH better place for me to start than the scratching of my head that I was doing, ha

 

[sandy666]

H t h

 

[jtakw]

Hi,

Here's another way, formula copied down:

Book1
	A	B	C	D	E
1	Old_product_does(smaller list)		new_product_codes(larger list)		contain or equals
2	ZVN0106B		ZV374		Y
3	ZV374		ASSN01958141222		Y
4	MC04ZE047		SMS996D1		N
5	SN0195814		LT1004MH25883		N
6			1111ZVN0106B16		Y
7			54LS00BCAJC		N
8			73X014M001		N
Sheet384

Cell Formulas
Range	Formula
E2	=IF(ISNUMBER(LOOKUP(2,1/SEARCH(A$2:A$5,C2))),"Y","N")