I have a table as below,
| Column A | Column B |
|---|---|
| Tom | 12,45 |
| Kenny | 1,4,6 |
| Jude | 1,4,5,7 |
| Benji | 15,48 |
Need it like as below
| Column A | Column B |
|---|---|
| Tom | 12 |
| Tom | 45 |
| Kenny | 1 |
| Kenny | 4 |
| Kenny | 6 |
| Jude | 1 |
| Jude | 4 |
| Jude | 5 |
| Jude | 7 |
I have tried using the FILTER function, however it is not providing what I need.
CodePudding user response：
Office 365. assuming a range of A1:B4 (change as required within the formula):
=LET(ζ,A1:B4,κ,INDEX(ζ,,1),λ,INDEX(ζ,,2),α,"<a><b>",β,"</b><b>",γ,"</b></a>",δ,"//b",ξ,FILTERXML(α&TEXTJOIN(β,,SUBSTITUTE(λ,",",β))&γ,δ),IF(SEQUENCE(,2,0),ξ,INDEX(FILTERXML(α&CONCAT(REPT(κ&β,1 LEN(λ)-LEN(SUBSTITUTE(λ,",",""))))&γ,δ),SEQUENCE(COUNT(ξ)))))
It is assumed that there are no names in column A with a corresponding blank in column B.