Using Vlookup and Concatenate to compare columns across different sheets

[phantomx013]

Hi

I have 2 sheets in an excel file.

Sheet 1 has 2 columns as below:

[TABLE="width: 309"]
<colgroup><col><col></colgroup><tbody>[TR]
[TD]#47094[/TD]
[TD]#37919[/TD]
[/TR]
[TR]
[TD]#47073[/TD]
[TD]#45258[/TD]
[/TR]
[TR]
[TD]#47175, #47178, #47179[/TD]
[TD]#44610[/TD]
[/TR]
[TR]
[TD]N/A[/TD]
[TD]#36090[/TD]
[/TR]
</tbody>[/TABLE]

Sheet 2 has 1 column

[TABLE="width: 58"]
<colgroup><col></colgroup><tbody>[TR]
[TD]#37919[/TD]
[/TR]
[TR]
[TD]#45258[/TD]
[/TR]
[TR]
[TD]#44610[/TD]
[/TR]
[TR]
[TD]#36090
[/TD]
[/TR]
</tbody>[/TABLE]

I want to compare sheet1.column2 with sheet1.column1 and put the corresponding results in sheet2.column2

What I did was I select the data in sheet1 and gave it name range "values". Next I used the formula =VLOOKUP(CONCATENATE("*",A1,"*"),Values,2,FALSE) for sheet2.column2

This give me only single corresponding value. e.g. it would give me below result

#37919 #47094
#45258 #47073
#44610 #47175
#36090 #N/A

Whereas I would like to have it in the below format:

#37919 #47094
#45258 #47073
#44610 #47175, #47178, #47189 <<<<<<<<<<<<<<
#36090 #N/A

Can someone help me please? I have about 6000 rows to compare !

Regards

MB

 

[phantomx013]

Rick

I don't know how to thank you enough for helping me out and guiding me. The code works. I really appreciate your help.

Regards

MB

 

[phantomx013]

Just a quick question. If my sheets has like 7000 entries, do I need to edit the formula "=LookupConcat(A1,Sheet1!A$1:A$4,Sheet1!B$1:B$4,", ",FALSE)"

 

[phantomx013]

I am going to write an very embarrassing question.

The first time everything worked. Now for some reasons, when ever I try the formula it gives me this #NAME? error in all the cells in column Sheet2.B2

I seriously don't know what is wrong may be I messed up some setting. But asking this question is really embarrassing

If you want I can share the workbook with you via dropbox or some thing.

Can you help the kid?

 

[Rick Rothstein]

Just a quick question. If my sheets has like 7000 entries, do I need to edit the formula "=LookupConcat(A1,Sheet1!A$1:A$4,Sheet1!B$1:B$4,", ",FALSE)"

Yes. A UDF, once installed, is no different than any other Excel function... range arguments must be for all the cells that argument is to process. I used Sheet1!A$1:A$4 because those were the cells on Sheet1 of your posted example data which contained the value to be searched when looking up values on Sheet2 (represented by the function's first argument, A1) and I used Sheet1!B$1:B$4 because those results you wanted reported when the function's first argument (A1) was located. Since you have 7000 values on Sheet1, the formula would be modified to this...

=LookupConcat(A1,Sheet1!A$1:A$7000,Sheet1!B$1:B$7000,", ",FALSE)

 

[phantomx013]

Sorry, It worked again

Instead of inserting module, I was coping the code in the macro window!!!

Regards !!!

 

[Rick Rothstein]

I am going to write an very embarrassing question.

The first time everything worked. Now for some reasons, when ever I try the formula it gives me this #NAME? error in all the cells in column Sheet2.B2

I seriously don't know what is wrong may be I messed up some setting.

The #NAME? error means one of two things...

1) You meant to include text in quote marks for an argument to a function but forgot to put the quote marks around it. In that situation, Excel thinks the unquoted text is the name of a Function or Defined Name but cannot find such a function or Defined Name anywhere within Excel or the Workbook.

2) You properly used the name of a function or Defined Name, but it no longer exists in the Workbook. In your case, that could mean you deleted the LookUpConcat function from the module or you saved the Workbook, but forgot to save it as a XLSM file, so the macro code was deleted making it unavailable for use the next time the Workbook was opened (see the last paragraph that I posted in Message #10).

 

[phantomx013]

I have one more request and I promise I will not bug you after that

Some of the entries in Sheet1 has some text against them such as below:

Sheet 1 is as below:

#47094 #37919
#47073 #45258
#47175, #47178, #47179 #44610
#47175,#47178 #36090
N/A #41903
PTS #36446

Sheet 2 should be as below


#47094 #37919
#47073 #45258
#47175 #44610, #36090
#47178 #44610, # 36090
#47179 #44610
N/A #41903
PTS #36446

Is it possible to reflect that in the calculation?

Thanks

 

[Rick Rothstein]

I have one more request and I promise I will not bug you after that

Some of the entries in Sheet1 has some text against them such as below:

Sheet 1 is as below:

#47094 #37919
#47073 #45258
#47175, #47178, #47179 #44610
#47175,#47178 #36090
N/A #41903
PTS #36446

Sheet 2 should be as below


#47094 #37919
#47073 #45258
#47175 #44610, #36090
#47178 #44610, # 36090
#47179 #44610
N/A #41903
PTS #36446

Is it possible to reflect that in the calculation?

Thanks

The UDF that I posted works fine, as is, for those entries. What is happening on your end that made you post your question? Are you, perhaps, getting blank values returned to you on Sheet2 in cells B6 and B7? If so, it is probably be cause you forgot to change the range references for Sheet1 to include Rows 5 and 6 where you added additional values to look up.

 

[phantomx013]

Sorry, It works fine. No more questions. I confused myself again.

Thanks a lot for all the help ! You been really helpful.