I have a query which I am unable to solve.
I have certain Codes in Sheet1 as below [TABLE="align: left, border: 1, cellpadding: 1, width: 600"]
[tr][td][/td][td]A
[/td][td]B
[/td][td]C
[/td][/tr][tr][td]1
[/td][td]CODES
[/td][td]FINAL OUTPUT
[/td][td]TOTAL
[/td][/tr][tr][td]2
[/td][td]991121QR5, 991121XSX, 991234SSC, 991121SDF
[/td][td][/td][td][/td][/tr][tr][td]3
[/td][td]991121QR5, 991121XSX, 991234SSC
[/td][td][/td][td][/td][/tr][tr][td]4
[/td][td]991121QR5, 991121XSX, 991234SSC, 991121SDF
[/td][td][/td][td][/td][/tr]
[/TABLE]
Sheet2 is as follows- [TABLE="align: left, border: 1, cellpadding: 1, width: 400"]
[tr][td][/td][td]A
[/td][td]B
[/td][/tr][tr][td]1
[/td][td]CODES
[/td][td]VALUES
[/td][/tr][tr][td]2
[/td][td]991121QR5
[/td][td]100100
[/td][/tr][tr][td]3
[/td][td]991121XSX
[/td][td]888888
[/td][/tr][tr][td]4
[/td][td]991234SSC
[/td][td]123456
[/td][/tr][tr][td]5
[/td][td]991121SDF
[/td][td]121212
[/td][/tr]
[/TABLE]
The Final Outcome in Sheet 1 should be as follows:- [TABLE="align: left, border: 1, cellpadding: 1, width: 600"]
[tr][td][/td][td]A
[/td][td]B
[/td][td]C
[/td][/tr][tr][td]1
[/td][td]CODES
[/td][td]FINAL OUTPUT
[/td][td]TOTAL
[/td][/tr][tr][td]2
[/td][td]991121QR5, 991121XSX, 991234SSC, 991121SDF
[/td][td]100100, 888888, 123456, 121212
[/td][td]1233656
[/td][/tr][tr][td]3
[/td][td]991121QR5, 991121XSX, 991234SSC
[/td][td]100100, 888888, 123456
[/td][td]1112444
[/td][/tr][tr][td]4
[/td][td]991121QR5, 991121XSX, 991234SSC, 991121SDF
[/td][td]100100, 888888, 123456, 121212
[/td][td]1233656
[/td][/tr]
[/TABLE]
How can I get the above OUTCOME using one single formula to vlookup each value from cell A2 after comma+space from Sheet1 with the table in Sheet2 and place the output in same format as in cell A2 of sheet 1 in cell B2 of Sheet 1. This should repeat till the last comma+space is there in the series. Also how to add up all the values of B2 in C2.