Vlookup value after comma+space & return each value seperated by comma+space

sandeepsandeep · Dec 21st 2017

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]

[/tr]

[tr]

[td]

3

[/td]

[td]

991121QR5, 991121XSX, 991234SSC

[/td]

[td][/td]

[/tr]

[tr]

[td]

4

[/td]

[td]

991121QR5, 991121XSX, 991234SSC, 991121SDF

[/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.

**NBVC** · Dec 21st 2017

First add this VB function to your VB Editor (Alt+F11, Insert >> Module).

Code

Function aconcat(a As Variant, Optional sep As String = "") As String
' Harlan Grove, Mar 2002
Dim y As Variant
If TypeOf a Is Range Then
For Each y In a.Cells
aconcat = aconcat & y.Value & sep
Next y
ElseIf IsArray(a) Then
For Each y In a
aconcat = aconcat & y & sep
Next y
Else
aconcat = aconcat & a & sep
End If
aconcat = Left(aconcat, Len(aconcat) - Len(sep))
End Function

Display More

Then back in the sheet, in B2 enter this Array* Formula:

=SUBSTITUTE(TRIM(aconcat(IF(ISNUMBER(MATCH("*"&Sheet2!$A$2:$A$5&"*",Sheet1!A2,0)),Sheet2!$B$2:$B$5,"")," "))," ",", ")

Adjust ranges to suit and copied down.

In C2 enter this normal formula:

=SUMPRODUCT(--(ISNUMBER(MATCH("*"&Sheet2!$A$2:$A$5&"*",A2,0))),Sheet2!$B$2:$B$5)

adjust ranges to suit and copy down

[arf]*[/arf]

sandeepsandeep · Dec 21st 2017

Quote from NBVC

First add this VB function to your VB Editor (Alt+F11, Insert >> Module).

Code

Function aconcat(a As Variant, Optional sep As String = "") As String

' Harlan Grove, Mar 2002

Dim y As Variant

If TypeOf a Is Range Then

For Each y In a.Cells

aconcat = aconcat & y.Value & sep

Next y

ElseIf IsArray(a) Then

For Each y In a

aconcat = aconcat & y & sep

Next y

Else

aconcat = aconcat & a & sep

End If

aconcat = Left(aconcat, Len(aconcat) - Len(sep))

End Function

Display More

Then back in the sheet, in B2 enter this Array* Formula:

=SUBSTITUTE(TRIM(aconcat(IF(ISNUMBER(MATCH("*"&Sheet2!$A$2:$A$5&"*",Sheet1!A2,0)),Sheet2!$B$2:$B$5,"")," "))," ",", ")

Adjust ranges to suit and copied down.

In C2 enter this normal formula:

=SUMPRODUCT(--(ISNUMBER(MATCH("*"&Sheet2!$A$2:$A$5&"*",A2,0))),Sheet2!$B$2:$B$5)

adjust ranges to suit and copy down

[arf]*[/arf]

Display More

Thats Excellent but what if the code is present but it has no value or the code is not there in sheet2. Then in this case the sum should show an error. Can this be included as well.

sandeepsandeep · Dec 21st 2017

Quote from NBVC

First add this VB function to your VB Editor (Alt+F11, Insert >> Module).

Code

Function aconcat(a As Variant, Optional sep As String = "") As String

' Harlan Grove, Mar 2002

Dim y As Variant

If TypeOf a Is Range Then

For Each y In a.Cells

aconcat = aconcat & y.Value & sep

Next y

ElseIf IsArray(a) Then

For Each y In a

aconcat = aconcat & y & sep

Next y

Else

aconcat = aconcat & a & sep

End If

aconcat = Left(aconcat, Len(aconcat) - Len(sep))

End Function

Display More

Then back in the sheet, in B2 enter this Array* Formula:

=SUBSTITUTE(TRIM(aconcat(IF(ISNUMBER(MATCH("*"&Sheet2!$A$2:$A$5&"*",Sheet1!A2,0)),Sheet2!$B$2:$B$5,"")," "))," ",", ")

Adjust ranges to suit and copied down.

In C2 enter this normal formula:

=SUMPRODUCT(--(ISNUMBER(MATCH("*"&Sheet2!$A$2:$A$5&"*",A2,0))),Sheet2!$B$2:$B$5)

adjust ranges to suit and copy down

[arf]*[/arf]

Display More

I want the final outcome as below in case code is present but has no value or the code is not present at all. [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]

[TABLE="border: 0, cellpadding: 0, cellspacing: 0"]

[tr]

[TD="width: 202"]100100, 888888, #NA, 121212

[/tr][/td][tr]

[/tr]

[/TABLE]
[/TD]

[td]

ERROR

[/td]

[/tr]

[tr]

[td]

3

[/td]

[td]

991121QR5, 991121XSX, 991234SSC

[/td]

[td]

100100, 888888, #NA

[/td]

[td]

ERROR

[/td]

[/tr]

[tr]

[td]

4

[/td]

[td]

991121QR5, 991121XSX, 991234SSC, 991121XYZ

[/td]

[td]

100100, 888888, #NA, #NA

[/td]

[td]

ERROR

[/td]

[/tr]

[/TABLE]

Please note I have added new code 991121XYZ which is not there in Sheet2's table. This has to throw an error.

**NBVC** · Dec 21st 2017

Try this Array formula:

=SUBSTITUTE(TRIM(aconcat(IF(ISNUMBER(SEARCH("*"&Sheet2!$A$2:$A$5&"*",Sheet1!A2)),Sheet2!$B$2:$B$5,"#N/A")," "))," ",", ")

sandeepsandeep · Dec 21st 2017

Quote from NBVC

Try this Array formula:

=SUBSTITUTE(TRIM(aconcat(IF(ISNUMBER(SEARCH("*"&Sheet2!$A$2:$A$5&"*",Sheet1!A2)),Sheet2!$B$2:$B$5,"#N/A")," "))," ",", ")

Your new formula is adding 4th value i.e. #NA in B4 even though there are just 3 codes.

**NBVC** · Dec 21st 2017

i'll have to think about it....

**AliGW** · Dec 21st 2017

This query has been cross-posted on at least one other forum. Please provide links to all other instances of the same query.

sandeepsandeep · Dec 22nd 2017

Quote from NBVC

i'll have to think about it....

Your VB works only when table in sheet2 has exact number of coloumns. When I add a new CODE it shows an extra 0 in Sheet1 B2.

**AliGW** · Dec 22nd 2017

Please add the link(s) I requested in post #8 before continuing - please do NOT ignore requests and instructions from moderators. Thanks.

sandeepsandeep · Dec 22nd 2017

Quote from AliGW

Please add the link(s) I requested in post #8 before continuing - please do NOT ignore requests and instructions from moderators. Thanks.

Here you go.

https://chandoo.org/forum/thre…l-the-last-comma-s.36769/

https://www.mrexcel.com/forum/…ast-comma-space-cell.html

https://www.excelforum.com/exc…-space-in-the-series.html

**AliGW** · Dec 22nd 2017

Thank you. Please do not cross-post again without being honest at the start and providing links - it is disrespectful to all those trying to help you.

sandeepsandeep · Dec 23rd 2017

Quote from AliGW

Thank you. Please do not cross-post again without being honest at the start and providing links - it is disrespectful to all those trying to help you.

[INDENT]Query Solved. This thread needs to be closed.

https://www.excelforum.com/excel-fo...es-till-last-comma-space-in-the-series-3.html[/INDENT]

Share