First thing you must fill blank cell in sheet 2
Select b column then press control+G->click on special ->check mark on blank->all the blank cell now highlighted -> give = and left cell then press control+enter.
Now in sheet1
E2
Formula:
=IFERROR(IF(COLUMNS($E2:E2)>LEN($A2)-LEN(SUBSTITUTE($A2,",",""))+1,"",INDEX(Sheet2!$B$2:$B$5,SMALL(IFERROR(MATCH(INDEX(TRIM(MID(SUBSTITUTE($A2,",",REPT(" ",LEN($A2))),(ROW((INDIRECT("1:"&LEN($A2)-LEN(SUBSTITUTE($A2,",",""))+1)))-1)*LEN($A2)+1,LEN($A2))),),Sheet2!$A$2:$A$5,0),10^10),COLUMNS($E2:E2)))),"#NA")
...confirmed by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER. You will know the array is active when you see curly braces { } appear around your formula. If you do not CTRL+SHIFT+ENTER you will get an error or a clearly incorrect answer.
Copy towards the cells and down. There are helper columns.
B2
Formula:
=LEFT(SUBSTITUTE(E2&","&F2&","&G2&","&H2&","&I2&","&J2&","&K2,",",REPT(" ",LEN(A2)),COUNTA(E2:L2)-COUNTBLANK(E2:L2)),LEN(A2))
Copy down.
Row\Col |
A |
B |
C |
D |
E |
F |
G |
H |
I |
J |
1 |
CODES |
OUTPUT |
SUM |
Helper column |
Helper column |
Helper column |
Helper column |
Helper column |
Helper column |
Helper column |
2 |
991121QR5, 991121XSX, 991234SSC, 991121SDF |
100100,888888,991234SSC,121212 |
1110200 |
100100 |
888888 |
991234SSC |
121212 |
|
|
|
3 |
991121QR5, 991121XSX, 991234SSC |
100100,888888,991234SSC |
#NA |
100100 |
888888 |
991234SSC |
|
|
|
|
4 |
991121QR5, 991121XSX, 991234SSC, 991121XYZ |
100100,888888,991234SSC,#NA |
#NA |
100100 |
888888 |
991234SSC |
#NA |
|
|
|
Sum part in column C I could not understand.
Bookmarks