How to give the output of duplicates from different columns in one row

I am having trouble with an inventory of 65000 chemicals with duplicates.

As shown in the example attachment, 'A3 to A11', 'A12 to A15' and 'A16 to A21' are all the same C-A-S (column B) but have different H-Codes that are classified with an "if analysis" to give an output of "1" if the "Chemical H-Code" (Column C) matches with any of the numbers from "D2 to BW2".

My problem is that I can not figure out how to get all the H-Codes for a single chemical in a single row. ROW 27 shows the kind of output I am looking for with a chemical.

Is there a better way to set up the Inventory? PLEASE HELP.....

Perhaps this will serve your purpose. Done with Subtotal, on the Data tab of Ribbon, Outline section. For original data, click on plus [+] button at extreme left.
You can delete the Grand Total Row after complete. Don't know why it doesn't add borders to the last two rows.

Originally Posted by jhren

Perhaps this will serve your purpose. Done with Subtotal, on the Data tab of Ribbon, Outline section. For original data, click on plus [+] button at extreme left.
You can delete the Grand Total Row after complete. Don't know why it doesn't add borders to the last two rows.

Hey thanks so much for helping. Unfortunately I have more than 60000 chemical in my excel inventory. Please see the newer attached list that has more of the chemicals. Please let me know if you could help in any way. thanks so much.CAS Duplicate HCode Output.xlsx

is this what you are looking for? You will have to fill down column A and B of the first sheet and the second worksheet is set up for about 400 chemicals.

CAS Duplicate HCode Output.xlsx

You could also go this route if you want to avoid Array Formulas.It has the benefit of Sheet 2 being easily expandable. If you need to separate the text string on sheet 2 you can copy and paste it as values to a new sheet and do text to column delimited by a comma.

CAS Duplicate HCode Output (2).xlsx