I am looking for a macro that will traverse down the row of values (some might be empty), and for each value in column B (in the form of a comma separates string), parse the string, removing the comma and space, and insert each parsed value in a new cell along the row.
If the parsed value, begins with a B- then the macro should go to Sheet2 and collect all values from column B that belong to that B- value in column A. If no values are found in sheet2, then the B- value should be used to be inserted into the next cell in the row on sheet1
e.g. my data looks like this
Sheet1
| A | B | C | D |
1 | ID_1 | PRD, LFT, TGB, DEC | | |
2 | ID_1 | DEC, STF, B-FTABG, B-ABGDD | | |
3 | ID_1 | DEC, PRD, B-ABGSYS, LFT | | |
4 | ID_2 | B- FTPRD, LYU, TGB, DEC | | |
5 | ID_2 | PRD, TFL, TGB, DEC | | |

Sheet2
A B
1 B-FTABG DEC
2 B-FTABG PEL
3 B-FTABG WER
4 B-FTABG TSL
6 B-ABGDD LPO
7 B-ABGDD VGT

Result
Sheet1
| A | B | C | D | E | F | G | H | I |
1 | ID_1 | PRD | LFT | TGB | DEC | | | | |
2 | ID_1 | DEC | STF | DEC | PEL | WER | TSL | LPO | VGT |
3 | ID_1 | DEC | PRD | B-ABG | LFT | | | | |
4 | ID_2 | LPO | VGT | LYU | TGB | DEC | | | |
5 | ID_2 | PRD | TFL | TGB | DEC | | | | |

Thanks