Identifying and merging matching ID's within range automatically

If the second table is your expected results, I don't understand the logic. Please give some worked examples to explain your rationale.

Originally Posted by TMS

If the second table is your expected results, I don't understand the logic. Please give some worked examples to explain your rationale.

You will see in the updated attachment how the Coloured Activity Ids (Blue, Green and Pink) in the relationships columns (C-P) are also present in the main Activity ID Start Column (B).
This is because they share relationships. All the other Activity ID's in the relationship columns are surplus to requirement.
Effectively this is a data cleaning tool.

Any Activity ID within Col C-P that isn't also present in column B needs to be removed automatically with the cleaned results put into the second table

I can add more worked examples but it will just provide more noise without adding anything. I am hoping that the colouring, together with the expected results and the further details provided here will assist.

Effectively the second data table is just a trimmed version of the top table (the top table being results of a TEXTSPLIT formula). The concept is simple though my explanation may be lacking. I am not sure I can explain it any other way.

My initial thoughts were some INDEX, MATCH, FILTER
or
COUNTIF FILTER to scrub the data, removing all the grey activities in col C - P eg A451700 FF

Essentially I need an ARRAY Filter based on a fixed column B lookup value (nothing in the RED Relationships section should move from its respective Row in the lower "trim" table)

Followed buy TEXTJOIN to recombine the remaining ID's (for example bringing ENPRE0001 and FF together to make ENPRE0001FF) this can be done separately though.


Thank you for the time spent looking so far.

maybe something like this with Power Query
btw. you have duplicated entry in ID column

Index	Activity ID Start	List
1	ENGSCM00001
2	ENGSCM00002
3	ENGDET001
4	ENGDET002	ENPRE0001FF
5	ENGDET003
6	ENGDET004	ENGSCM00002FF,ENGDET002FF
7	SCPCSR001
8	SCPCSR003	ENGSCM00002FF
9	SCPCSR002
10	SCPCSR004	ENPRE0002FF
11	ENGSCM00002

1. DON'T QUOTE WHOLE POSTS !
2. check this one:

Index	Activity ID Start	List
1	ENGSCM00001
2	ENGSCM00002
3	ENGDET001
4	ENGDET002
5	ENGDET003
6	ENGDET004	ENGSCM00002FF,ENGDET002FF
7	SCPCSR001
8	SCPCSR003	ENGSCM00002FF
9	SCPCSR002
10	SCPCSR004
11	ENGSCM00002

---

if the problem is solved, make me happy and hit Add Reputation (bottom left corner next to the post that was helpful)
and then mark the thread as SOLVED (top above your first post - Thread Tools)

As per your message to me, please do NOT open a new thread on this - simply continue here. Thanks.