Need script for removing duplicate numbers which are separated by comma

Question

In this google sheet I have a set of data which I get from salesforce. The "Input" sheet is the raw data that I get and the "Output" sheet is what I make manually.

The "Input" sheet has 3 columns. Column A has primary case(single value), column B has secondary case(single value) and column C has Number IDs(multiple values which are separated by commas).

Primary cases will have multiple secondary cases and each secondary case will have either a single ID or multiple IDs associated with it. The numbers under Column C will always start with [ and will always be separated with a comma but will never end with ]

Secondary cases(in column B) can have same Number IDs(in column C) or different Number IDs e.g. once secondary case will have 4 numbers IDs associated with it [549759806189, 549756061030, 549760099925, 549757060706 and another secondary case will have 8 numbers IDs associated with it [549759806189, 549756061030, 549760099925, 549757060706, 549757046913, 549760100018, 549756537904, 549755938202

If you notice, there are duplicates in these. In the "Output" sheet, I need to put the Primary case numbers and against those I need to add all the unique number IDs associated with each secondary case under it. So if Primary case has 4 secondary cases associated and those 4 secondary cases have 7 unique number ids associated with them in "Output" sheet, I need to add the primary case number and against that add the 7 unique number ids. There is no need to import secondary cases in the "Output" sheet, just the Primary cases and unique Number IDs that are associated with it.

This needs to be done on all primary cases in the "Input" sheet.

Is there a way to get this automated with a script ?

Any help is highly appreciated!

Thank you!

Answer 1

try formula:

=ARRAYFORMULA({SORT(UNIQUE(1*FILTER(Input!A2:A, Input!A2:A<>""))), 
 "["&SUBSTITUTE(TRIM(TRANSPOSE(QUERY(TRANSPOSE(QUERY(
 IF(QUERY(SPLIT(UNIQUE(FLATTEN(IF(IFERROR(SPLIT(Input!C2:C, ","))="",,Input!A2:A&"♦"&
 SPLIT(SUBSTITUTE(Input!C2:C, "[", ), ",")))), "♦"), 
 "select count(Col1) where Col1 is not null group by Col1 pivot Col2")=1, 
 INDEX(QUERY(SPLIT(UNIQUE(FLATTEN(IF(IFERROR(SPLIT(Input!C2:C, ","))="",,Input!A2:A&"♦"&
 SPLIT(SUBSTITUTE(Input!C2:C, "[", ), ",")))), "♦"), 
 "select count(Col1) where Col1 is not null group by Col1 pivot Col2"), 1), ),
 "offset 1", 0)),,99^99))), " ", ", ")})