Lookup to find and output multiple values in different worksheet

[harsh2209]

Hi,
I’m trying to use Lookup to generate a traceability matrix. For each requirement in worksheet A, I want to input what all test cases are present in Worksheet 2. By using the lookup, it just returns the first occurrence and not the others. Attached is that example sheet. Any help would be highly appreciated.
Thanks

[NBVC]

Add this udf to your VB Editor (ALT+F11, Insert|Module)

Please Login or Register  to view this content.

Then use formula in E2:

=SUBSTITUTE(TRIM(aconcat(IF('Test Cases'!$A$2:$A$34=B2,'Test Cases'!$B$2:$B$34,"")," "))," ",",")

and confirm it with CTRL+SHIFT+ENTER not just ENTER and copy it down

[harsh2209]

Thanks NVBC!

However, I'm trying to understand why I'm getting all test case numbers which do not have any requirement mapped against some requirements..Could you please see what happens if some of the test cases do not have a requirement id?

[NBVC]

Are you confirming the formula with CTRL+SHIFT+ENTER so that you get { } brackets around the formula?

[harsh2209]

Thanks Again, Yes I'm doing it. It works very well.

One more thing. So, is it also possible that the logic picks up more then one requirements separated by comma from one cell.
Please see the example in the attached file - 'Example2.xlsm'

[NBVC]

Did you attach another file?

[harsh2209]

Sorry, I did now..

[NBVC]

Try:

=SUBSTITUTE(TRIM(aconcat(IF(ISNUMBER(SEARCH(B2,'Test Cases'!$A$2:$A$34)),'Test Cases'!$B$2:$B$34,"")," "))," ",",")

confirmed with CTRL+SHIFT+ENTER and copied down.

[harsh2209]

Again, works like a charm!

Thanks a lot!

[NBVC]

Great!

Can you remember to please mark your thread as Solved.

How to mark a thread Solved
Go to the first post
Click edit
Click Go Advanced
Just below the word Title you will see a dropdown with the word No prefix.
Change to Solved
Click Save

[harsh2209]

Sure! but, what if I have some more questions..do I open a new thread?

[NBVC]

If it is directly related to this actual question, then no Mark it back to No Prefix and then ask..

... if is not directly related, then start a new thread.. you can add link to here if you feel it necessary...

[harsh2209]

Hi NBVC,

The solution you proved work well. Here is one discrepancy that I have come across.

The formula & macro cannot differentiate between requirement '6.2.0' and '16.2.0'. So, the test cases mapped to 16.2.0, 16.2.1 are also getting automatically mapped to 6.2.0 and 6.2.1...

Attached example2.xlsm with the scenario..

Any thought/ way out..

[harsh2209]

The formula & macro cannot differentiate between requirement '6.2.0' and '16.2.0'. So, the test cases mapped to 16.2.0, 16.2.1 are also getting automatically mapped to 6.2.0 and 6.2.1...

Attached example2.xlsm with the scenario..

Any thought/ way out..

[harsh2209]

Could you please help...

[Huron]

Try

=SUBSTITUTE(TRIM(aconcat(IF(B2='Test Cases'!$A$2:$A$40,'Test Cases'!$B$2:$B$40,"")," "))," ",",")

entered as an array formula.

[NBVC]

Try:

Please Login or Register  to view this content.

CSE confirmed