verify keyword exists in range

[NW30075]

Greetings All!
I have keywords in the attached xlsx sheet2 cells (c2:g105) that i need to verify they exist in the sheet1 range b2:b121.
if exists, return the sheet1 corresponding col A value.
if not found return "not found"

My problem is the sheet1 col b values are sentences and I can't get index/match to work.

What is the best way to get this done?

Many thanks!

 

[NARAYANK991]

Hi ,

An interesting application.

However , there are a few comments I would like to make :

1. You need to run spellcheck on both worksheets.

2. Suppose we isolate the first 4 words in column B in both sheets ; this will give us the starting point to check for the words in the metadata range. But here we run into a problem ; the keywords are few and too common e.g. the rows 35 , 36 , 37 , 41 and 44 all have the keyword reports ; how are we going to do a match ? Another such keyword is report.

You need to ensure that the keywords are of significance , and if possible unique. If this is not possible , include more keywords so that the combination is unique.

I have done some preliminary work in this direction ; can you comment ?

Narayan

 

[NW30075]

Hello Narayan,
Thank you for your reply.

BACKGROUND:
This effort won't be 100% solution, just trying to find common words between the two sets of requirements in order to match them up (group similar to similar requirements where they can then be reviewed by an analyst.)

I was thinking we could dump the sheet1 range b2:b121 into an array, and then parse the array with the meta data in sheet2. Where the keyword is found, return the corresponding sheet1 col a value. Problem is I'm not yet skilled enough to make this happen!

Any more assistance you can provide is greatly appreciated!

Best Regards,
Nick

 

[NARAYANK991]

Hi Nick ,

Your reply does not address the problems I have pointed out. Can you do that ?

Narayan

 

[NW30075]

spell check no problem.
Keywords are common in order to be able to see similarities between the requirements. All the reports will be come report. Will be a one to many relationship, one metadata term can have more than one find.
This effort won't be 100% solution, just trying to find common words between the two sets of requirements in order to match them up (group similar to similar requirements where they can then be reviewed by an analyst.)

Thanks!

 

[NARAYANK991]

Hi ,

Please see this file and comment.

Narayan

 

[NW30075]

Hi ,

Please see this file and comment.

Narayan

Greetings Narayan,
Thanks so much for your efforts to this point. I am deeply grateful.

If we can get one more piece, it'll should be ready for use.

On sheet2:
if the # in col K >1, then list the matching sheet1 values in col m, then N, then O etc.
for example if K2 = 3, there would be different sheet1 values in Col L, M, N

Great work so far, thank you so much!

 

[NARAYANK991]

Hi ,

I will try , but the task seems more complex.

For example , suppose the 3 keywords are :

create , user , account

The first word create itself could have multiple matches ; these matches may or may not contain the second keyword user , or the third keyword account ; the same will hold true for the other two keywords.

Thus , the overall list of matching IDs may total even 20 !

Narayan