Unique List When Adjacent Cell Contains a list of Words

[Audiogasm]

Hello all,

I recently watched https://www.youtube.com/watch?v=Tq3B168qT5E and have a question regarding this. My list for example contains these 4 words that i need to use to search for a similar words in a column.
admin
List 1
unix
hrms
quality

The cells that i am searching for any of these are in contain all these with many other non matching words.
When i used the method shown in the video i was able to get the results. However, i hit a roadblock when some of the words have extra letters in them. For example,
List 2
rsa, dba
unix, admin
unixprd
hrmsprd
qualitysys

Now, i wanted to match exactly the word unix since its common in both but i don't want it to show unixprd which is a different word but contains the word unix in it. How do i go about resolving this issue since i need exactly what is in my list.

Appreciate any help i can get.

 

[Yongle]

It looks as though all your terms are delimited by commas

Simple fix

1. add a comma as the final character in all cells in that column ... by creating another column and using this formula, copied down
=A1&","
2. either copy and paste those values to the original column or point your you tube formula at the new column (hide the column if you prefer)
3. add a comma to all the search words
searching for unix, will only find unix

It may be worth running search and replace on your whole column searching for " ," (space followed by comma) and replacing with "," (comma only)

 

[Audiogasm]

Thank you for the reply, I tried and ran into this problem. So here is what i did, i added ',' to every entry in the list and every entry in the search field.
So in the end my list was something like this
unix,
hsms,
dbalerts,
at,

The column where i had to search these had many entries including (Note that the comma between the 2 words already existed since that the format that i get the data in, the comma i added is only the one in the end)
unix,
staff,unix,
hsmsprd,database,
wasuat,
db,

Everything was fine until it showed positive results for the word 'wasuat,' since there is a word called 'at,' in the column. I want it to show only the exact match. Any idea please.

Thank you !

 

[Aladin Akyurek]

What is the end result you want to obtain? Show what keyword each item in List2 contains or just "unix", that's keywords which are available in List2?

 

[Yongle]

The secret is to find a way to make everything consistent - which keeps life simple

Again there is a simple fix which uses exactly the same technique as before
Search instead for " Word," (space Word comma)
In your example search for " at," (space "at")
Insert a space before all search words

BUT the first value in the searched cell is not prefixed by a space
Fix is to insert space as the first character in each cell

The formula to do that is
=" "&A1

Or you can apply both fixes at the same time with
=" "&A1&","

 

[Audiogasm]

What is the end result you want to obtain? Show what keyword each item in List2 contains or just "unix", that's keywords which are available in List2?

So i have a list of words,
1. Unix
2. Linux
3. adm
4. admin

I need to find these words in another column which has 1 or more words separated by a comma, example below.
1. rsa
2. adm
3. saadmin,staff
4. work
5. staff
6. padm,unix

Now the issue with the formula is that it will find all the words which have a partial match like number 3, 6 including 2. So 2 is correct since its an exact match, while 3 (saadmin) and 6(padm) are partial matches since they contain admin and and adm. So i want only the exact match.

 

[Audiogasm]

The secret is to find a way to make everything consistent - which keeps life simple

Again there is a simple fix which uses exactly the same technique as before
Search instead for " Word," (space Word comma)
In your example search for " at," (space "at")
Insert a space before all search words

BUT the first value in the searched cell is not prefixed by a space
Fix is to insert space as the first character in each cell

The formula to do that is
=" "&A1

Or you can apply both fixes at the same time with
=" "&A1&","

Alright, let me try this again.

 

[Aladin Akyurek]

Please just state the end result...

 

[Audiogasm]

Hello,

I think i get it now, using both the space and a comma i am able to get the desired result. Thank you for your kind help. I have a few more data sets to compare. I will get back to you here if i run into any more trouble.

 

[Aladin Akyurek]

Hello,

I think i get it now, using both the space and a comma i am able to get the desired result. Thank you for your kind help. I have a few more data sets to compare. I will get back to you here if i run into any more trouble.

Try also to address the questions you get...

Was the end result this?

Book1
	A	B	C
1	List 2		Results
2	rsa, dba
3	unix, admin		unix
4	unixprd
5	hrmsprd
6	qualitysys
Sheet2

That is, blank, unix, blank, blank, blank

or just this:

Book1
	A	B	C
1	List 2		Result
2	rsa, dba		unix
3	unix, admin
4	unixprd
5	hrmsprd
6	qualitysys
Sheet2 (2)

That is, a single result.

Or something else?