How to group rows in Excel while taking into account "daisy-chaining"?

Question

I have some data in an excel sheet that looks something like below:

ID	Group
A	1
B	1
B	2
C	2
D	3

Each group says that all IDs in that group are related to one another. Notice that, through B, A and C are related to each other despite being in separate groups. A and C are "daisy-chained" together through B. I want to find all groups, taking into account daisy-chaining, in this data set. The ideal output table would look like below:

ID	Super-Group
A	1,2
B	1,2
C	1,2
D	3

I've tried working this problem through power query but I could only get so far on my minimal experience. From what I could garner online, I should be able to get the result I want through some clever joining but I can't wrap my head around the logistics of it.

Answer 1

Since you just ask the logic, so I will just propose one way to do it, and I trust you can find out the correct Excel formulae or Power Query to implement it yourself.

Assuming group data look like below:

First step would be grouping IDs by Group Number:

The formula I used here is FILTER() and TRANSPOSE()

The next step would be finding out common IDs for different combinations of group pairs.

Below table means B is in both Group1 and Group2, E is in both Group2 and Group3, E is in both Group2 and Group4, D,E are in both Group3 and Group4.

So following group pairs have daisy chain between them: (1,2) (2,3) (2,4), (3,4).

The formula I used to find out common elements between two groups is this: extract common values from two lists in excel

The final step is to list all GroupIds for group pairs that have "daisy chain" between them.

e.g, since (1,2) has daisy chain between them, we can use FILTER() or XLOOKUP() to find out all GroupIds that are in Group1 or Group2.