I have a Sheet with Column A being City and Column B being District, like so:
| City | Districts |
|---|---|
| Accord | 103 |
| Albany | 109,110,108 |
| Altamont | 109 |
I would like to transform this into the "opposite":
| District | Cities |
|---|---|
| 103 | Accord |
| 108 | Albany |
| 109 | Albany, Altamont |
| 110 | Albany |
I'm pretty sure it's simple. Thanks !
CodePudding user response：
use:
=ARRAYFORMULA(QUERY(SPLIT(REGEXREPLACE(TRIM(FLATTEN(
QUERY(QUERY(SPLIT(FLATTEN(SPLIT(B2:B, ",")&"¤×"&A2:A&","), "×"),
"select max(Col2) group by Col2 pivot Col1"),,9^9))),
",$", ), "¤ ", 0), "where Col2 is not null", 0))
