Find text in one column = add "#, " to different column?

[Jennifre]

Hello Excel experts... I thank you in advance!

I need to find words in column U, and if found, add comma-space-assigned number in column R (in same row).
(without disturbing content already in column R. I've begun doing this manually.)

Example of what I'm trying to do: (Would love to have #s added in order, but if can't, that's okay)
worksheet "tags" (partial)
[TABLE="class: cms_table_head"]
<tbody>[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=E0E0F0]#E0E0F0[/URL] "]
[TH="align: center"][/TH]
[TH="align: center"]A
[/TH]
[TH="align: center"]B
[/TH]
[/TR]
[TR]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=E0E0F0]#E0E0F0[/URL] "]

[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]1​

​

[/TD]
[TD]cat[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=E0E0F0]#E0E0F0[/URL] "]

[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]2[/COLOR]​

[/TD]
[TD]sat[/TD]
[TD]2[/TD]
[/TR]
[TR]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=E0E0F0]#E0E0F0[/URL] "]

[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]3[/COLOR]​

[/TD]
[TD]on[/TD]
[TD]3[/TD]
[/TR]
[TR]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=E0E0F0]#E0E0F0[/URL] "]

[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]4[/COLOR]​

[/TD]
[TD]mouse[/TD]
[TD]4[/TD]
[/TR]
[TR]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=E0E0F0]#E0E0F0[/URL] "]

[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]5[/COLOR]​

[/TD]
[TD]the[/TD]
[TD]5[/TD]
[/TR]
</tbody>[/TABLE]


[/COLOR] worksheet "join"
Existing #s , #s added via this new trick
(It's okay if the numbers are added to column R at the end of what's there already, esp if I'm able to order
them later?)
[TABLE="class: cms_table_head"]
<tbody>[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=E0E0F0]#E0E0F0[/URL] "]
[TH="align: center"][/TH]
[TH="align: center"][/TH]
[TH="align: center"][COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]R[/COLOR]
[/TH]
[TH="align: center"][COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]U[/COLOR]
[/TH]
[/TR]
[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] "]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=E0E0F0]#E0E0F0[/URL] "]

[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]1[/COLOR]​

[/TD]
[TD][/TD]
[TD]1, 2, 4, 5, 21, [/TD]
[TD]more... the cat sat on the mouse ...more[/TD]
[/TR]
[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] "]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=E0E0F0]#E0E0F0[/URL] "]

[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]2[/COLOR]​

[/TD]
[TD][/TD]
[TD]1, 4, 5, 21, 28, [/TD]
[TD]more... cat ate the mouse ...more[/TD]
[/TR]
[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] "]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=E0E0F0]#E0E0F0[/URL] "]

[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]3[/COLOR]​

[/TD]
[TD][/TD]
[TD]1, 2, 4, 5, 21, 22, [/TD]
[TD]more... the mouse sat under the cat ...more[/TD]
[/TR]
</tbody>[/TABLE]



​Thank you!!

 

[Jennifre]

Hi @Shakehips!

Any tips about where I make the substitutions in what you've sent, regarding the col A rows, and col B and C contents? Thank you, Tony!

 

[Snakehips]

@Jennifur !

I'm not sure that I know what you mean about substitutions.
The code should run on the sample data you posted / I think I replicated.
Bedtime here now. If you / I / both are still confused tomorrow then I will need further clarification of your real data set.

 

[Jennifre]

Okay, @Snakehips Tony -- nothing happened at all. ... I'm sure I did it wrong. What is "it"?
Thank you!!

 

[Jennifre]

Okay, sleeping @Snakehips, there was a column between "tags" col 1 and col 2. After deleting that and trying again, I get this:

Run-time error '13':

Type mismatch

*I hope this is somehow an easy fix. I am so excited, if this can work! Thank you!!

 

[Snakehips]

Good Morning!
OK, I think my signature just about sums it up. I need clarification of how your data is set up.
The options are...
You provide a specific verbal description
You post representative screenshots
You post a link to a sample file on a third party host such as Dropbox
You PM me and we arrange to exchange a file by email
You resign your current job and get yourself another that does not involve spreadsheets
I 'get a life' and maybe, take up golf instead

Please advise.

 

[Jennifre]

@Snakehips, good morning!

I have an alternative, which I can use just the same, it'll just take longer but not terrible. (Better than manually!)

Here is my data, almost literally:
Columns and data (ea column is 100px wide, like almost, below!), in NOW state:
A--------B--------C--------D--------
word

Columns and data, in DESIRED state:
A--------B--------C--------D--------
word x


Is that easy, or possible?

Thank you, Tony!! BOTH your options are perfect!

 

[Jennifre]

SHOOT! THAT did not work at all!! I'll try again, data is this:

Columns and data (ea column is 100px wide, like almost, below!), in NOW state:
A--------B--------C--------D--------
word

Columns and data, in DESIRED state:
A--------B--------C--------D--------
word--------------x


Is that easy, or possible for some formula / function / magic to:
if a certain "word" is in A1, Excel will add a letter x in C1?
The only thing I'd need is that the x be placed on the same row as the "word", but in column C.

Please let me know, @Snakehips, if you are still with your first coffee and toast! Thank you so much!!

 

[Jennifre]

Here is my data, almost literally:
Columns and data (ea column is 100px wide, like almost, below!), in NOW state:
A--------B--------C--------D--------
word

Columns and data, in DESIRED state:
A--------B--------C--------D--------
word-------------- x


This seems so easy, fingers crossed it actually is!!

 

[Dan Waterloo]

Hi Jennifre,

Try the following formula in cell C2 (I assume there is a header row):

=if(ISNUMBER(SEARCH("word",A2)), "x", "")

And copy this formula down column C as far as you need it.