Hello to all! I am a new member to this forum, but have been an avid reader for some time now. Here is the challenge I am facing. Please forgive me for the length...I tried to make it as short as possible while still making sense. I would like to write a VBA statement in Excel 2013 to work with the following codes below. Each of the three (3) categories are made up of a Parent Category and several Subcategories, three (3) Subcategories for each Parent Category in this example.

Categories 1, 2, and 3 are separate, yet equivalent, Parent Categories and are each hierarchical.

Category 1 (Model Years) is a “Non-Part” Parent Category and is different than Category 2 and Category 3 in the sense that they are “Part” Parent Categories.

CATEGORY 1 (Model Years: Non-Part Category)
12345 = Model Years = Parent Category: Level 1
23456 = Year of 1950 = Subcategory: Level 2
34567 = Year of 1951 = Subcategory: Level 2
45678 = Year of 1952 = Subcategory: Level 2

CATEGORY 2 (Widgets: Part Category)
98765 = Widgets = Parent Category: Level 1
87654 = Widgets, Large = Subcategory: Level 2
76543 = Widgets, Medium = Subcategory: Level 2
65432 = Widgets, Small = Subcategory: Level 2

CATEGORY 3 (Gadgets: Part Category)
246 = Gadgets = Parent Category: Level 1
468 = Gadgets, Long = Subcategory: Level 2
680 = Gadgets, Regular = Subcategory: Level 2
802 = Gadgets, Short = Subcategory: Level 2

There is one field named “CategoryID” in the spreadsheet that holds the codes from the categories and subcategories listed above. The codes are separated by a single comma without any spaces, e.g. 98765,65432,34567,680. The codes are not in any order in accordance with a category path. Also, codes from each of the 3 main categories (Parent or Sub) are in this “CategoryID” field.
 
What Needs to Happen:
Problem Example with Codes from Above BEFORE VBA Statement:

Item Number CategoryID
ABC-1 76543,12345,34567,680,45678
ABC-2 468,23456,802,12345,34567,87654

By looking at the codes in this example table (worksheet):

- Item ABC-1 is categorized as a medium widget (76543), a model years item (12345), a year 1951 item (34567), a regular gadget (680), and a year 1952 item (45678).
- Item ABC-2 is categorized as a long gadget (468), a year 1950 item (23456), a short gadget (802), a model years item (12345), a year 1951 item (34567), and a large widget (87654).

The correct category paths are listed below beginning with the deepest subcategory level code and working left to right up to the highest parent category. I know this seems reverse, but this is how the paths must be (as well as separated by commas without spaces):

12345 = 12345
23456 = 23456,12345
34567 = 34567,12345
45678 = 45678,12345
98765 = 98765
87654 = 87654,98765
76543 = 76543,98765
65432 = 65432,98765
246 = 246
468 = 468,246
680 = 680,246
802 = 802,246

What needs to happen is that each record (Item) needs to be associated with only one “Part” Parent Category out of the two that are available, Widgets and Gadgets. Right now, there are codes from both the Widgets category as well as the Gadgets category, but each part needs to be in only one. The actual file contains thousands of records, so some type of automation is necessary. Also, the codes for any of the years from the “Non-Part” Category called Model Years need to stay in the field.
 
First Challenge:
Basically, I need to search the string in the CategoryID field for each of the records and determine which of the records are in more than one single “Part” Category (can be Parent or Sub, but can NOT have codes from separate “Part” Categories (Parent or Sub). I’d like to receive the returned result in another new field with a value of “CHECK” if any given part is in more than one single “Part” Parent Category.

Second Challenge:
After each of the records has been checked for being in either one or more than one single “Part” Category resulting in a new field for the items in more than one “Part” Category with a value of “CHECK”, I need to take out any of the incorrect “Part” Category codes while leaving in the Model Years codes using the correct Category Codes and Category Paths (listed above). Then I need to replace all of the codes that pertain to the single correct “Part” Category codes that are currently not in order with the correct Category Path (listed above) exactly as they are listed above AND concatenate them to the beginning of the string which at that point should only be codes for the Model Years.

When looking at the Problem Example table above, we would want the item ABC-1 to be only a medium widget, not a gadget. We would want the item ABC-2 to be only a long gadget, not a widget. We want any of the years codes to be left in any order so long as the “Part” Category codes adhere to the code paths listed above and are concatenated to the beginning of the string with the Model Years codes following. Again, the final result should have all codes separated by commas with no spaces.

Again here is the Problem Example with Codes from Above BEFORE VBA Statement(s):

Item Number CategoryID
ABC-1 76543,12345,34567,680,45678
ABC-2 468,23456,802,12345,34567,87654

Here is what the data should look like AFTER VBA Statement(s):

Item Number CategoryID
ABC-1 76543,98765,12345,34567,45678
ABC-2 468,246,12345,34567

I know this is somewhat long winded and I do apologize. I tried my best to keep it as short as possible. If anyone can help me with this, I would truly and greatly appreciate it very, very much. Obviously, I need some help, suggestions, recommendations, or anything of a constructive nature. Thank you, more than words can explain, in advance.