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
Last edited by harsh2209; 03-08-2010 at 05:22 PM.
Add this udf to your VB Editor (ALT+F11, Insert|Module)
Then use formula in E2: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
=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
Microsoft MVP - Excel
Where there is a will there are many ways. Pick One!
Please read the Forum Rules
If you are happy with the results, please add to the contributor's reputation by clicking the reputation icon (star icon) below
Please also mark the thread as Solved once it is solved. Check the FAQ's to see how.
Preferred Charities: Lupus Canada and Sick Kids Foundation.
Feel Free to Donate if you want to, for the assistance you received today.
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?
Are you confirming the formula with CTRL+SHIFT+ENTER so that you get { } brackets around the formula?
Microsoft MVP - Excel
Where there is a will there are many ways. Pick One!
Please read the Forum Rules
If you are happy with the results, please add to the contributor's reputation by clicking the reputation icon (star icon) below
Please also mark the thread as Solved once it is solved. Check the FAQ's to see how.
Preferred Charities: Lupus Canada and Sick Kids Foundation.
Feel Free to Donate if you want to, for the assistance you received today.
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'
Last edited by harsh2209; 03-03-2010 at 03:25 PM.
Did you attach another file?
Microsoft MVP - Excel
Where there is a will there are many ways. Pick One!
Please read the Forum Rules
If you are happy with the results, please add to the contributor's reputation by clicking the reputation icon (star icon) below
Please also mark the thread as Solved once it is solved. Check the FAQ's to see how.
Preferred Charities: Lupus Canada and Sick Kids Foundation.
Feel Free to Donate if you want to, for the assistance you received today.
Sorry, I did now..
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.
Microsoft MVP - Excel
Where there is a will there are many ways. Pick One!
Please read the Forum Rules
If you are happy with the results, please add to the contributor's reputation by clicking the reputation icon (star icon) below
Please also mark the thread as Solved once it is solved. Check the FAQ's to see how.
Preferred Charities: Lupus Canada and Sick Kids Foundation.
Feel Free to Donate if you want to, for the assistance you received today.
Again, works like a charm!
Thanks a lot!
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
Microsoft MVP - Excel
Where there is a will there are many ways. Pick One!
Please read the Forum Rules
If you are happy with the results, please add to the contributor's reputation by clicking the reputation icon (star icon) below
Please also mark the thread as Solved once it is solved. Check the FAQ's to see how.
Preferred Charities: Lupus Canada and Sick Kids Foundation.
Feel Free to Donate if you want to, for the assistance you received today.
Sure! but, what if I have some more questions..do I open a new thread?
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...
Microsoft MVP - Excel
Where there is a will there are many ways. Pick One!
Please read the Forum Rules
If you are happy with the results, please add to the contributor's reputation by clicking the reputation icon (star icon) below
Please also mark the thread as Solved once it is solved. Check the FAQ's to see how.
Preferred Charities: Lupus Canada and Sick Kids Foundation.
Feel Free to Donate if you want to, for the assistance you received today.
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..
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..
Could you please help...
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
Reply With Quote
Bookmarks