Hi,
I have a complex work match problem which I hope someone could help me as I'm new to macro.
I have a list of product description and in the description are certain text that will tell me if the product is a single unit or multi-pack. For every product in the list I will need to find if there are particular text in the product description and if the text is found then it is a multi-pack. The only problem is that there is no convention as to how multi-pack are recorded in the product description. For example a 2-pack could be recorded in the product description as "2P" or "2PK" or just "2X". Therefore to reduce the resource consumption of the workflow all I need is that once a match is found in a particular product description then stop the matching and flag that product as a multi-pack and then go onto to the next product.
Here is an example of 5 products:
PURINA F/FST ROY TUNA 3X85GM |
DINE CHICKEN MORSELS 85GM |
S/TOM F/CTCH JMBOPACK 2X100GM |
WHISKAS FAV CF TUNA IN JELLY 5P |
PURINA F/FST CHICK GRAVY 12PK |
I have created a file with all the possible pack size key word. Here is an example of 3 possible convention to denoting a 6 multi-pack:
packsize1 | packsize2 | packsize3 |
2P | 2PK | 2X |
3P | 3PK | 3X |
4P | 4PK | 4X |
5P | 5PK | 5X |
6P | 6PK | 6X |
For the first product, PURINA F/FST ROY TUNA 3X85GM, I will need to match against row 1 & 2 as well as column 1,2,& 3 - however once it finds a match in row 3/column 3 then it will stop there and write to a separate column that says its 'multipack'.
The pack size key word file is 24row by 3 column so I do not want to attach this file to every product record because it might make the file too big as there's about 1million rows of product data. I was hoping you could help me with a macro.
Solved! Go to Solution.
Regex would be a great option for this - see below for an example:
The regex used for your use case is explained in more detail here:
@LIOUJ
Appeciate if you would mark it as accept if you find the workflow useful.
Thanks both.
I am new to Regex so keen to learn more about it. I find Regex a 'cleaner' solution as there's less step but am not familiar with the coding.
thanks.