VBA Code to Remove Larger Values between Two Delimiters

[eonblue2]

I have a set of data in column A that looks like this:

White:0:0|Counter Height:0:0|Orange:40:0|Counter Height:40:0|Green:40:0|Counter Height:40:0|White:40:0|Bar Height:40:0|Orange:80:0|Bar Height:80:0|Green:80:0|Bar Height:80:0|

The delimiter "|" separates the unique values of an item (in this example they are ******* stools of differing heights and colors), I need to only keep the lower value and remove the larger one.

The example above contains both White:0:0 and White:40:0 as two different values associated with the same ******* stool. I need to only have White:0:0 remain and White:40:0 to go away.

Tried various methods but nothing seems to work. Any help is appreciated.

Thanks,
-EB

 

[VBA Geek]

hi

i see White:0:0|Counter Height:0:0 and White:40:0|Bar Height:40:0, it doesnt look like the same stool?

 

[eonblue2]

Thanks for the Reply. They are the same product, however this is the way the data spits out right now. The white option should not have an additional charge, nor should counter height. Bar height should have a $40 additional charge. With this configuration, a white stool will charge an additional $40 which is not correct, the only way to remove that option is to get rid of "White:40:0" - it's proving to be difficult.

 

[VBA Geek]

For instance if i look at Orange

you should keep the entire string "Orange:40:0|Counter Height:40:0" and remove "Orange:80:0|Bar Height:80:0"?

 

[eonblue2]

The color and height should be considered separate. They are not together in any way. The delimiter is "|" and the values in between them shouldn't be paired with another value. The larger value needs to be deleted. So in this case Orange:80:0 needs to go between the two Orange options. Any help on how to remove the duplicate color or height options with higher values? The syntax is <Option>:<Price Increase>:<Sort Order> - therefore if there are any options that repeat, they need to be evaluated and the option with the higher price increase needs to go from the string.