Code to display "text containing other text"

[ellison]

Good morning, what a great site!

I wonder if you could help…
I have to sort through different reports from different departments on a monthly basis & turn them into something which (hopefully!) makes sense.
One issue we have is that I have to go through the different (unconfirmed) “descriptions” that people give to different enquiries & boil them down to a set of “approved descriptions” (the approved set is now about 20,000 but is added to all of the time)
I have suggested to the powers-that-be that they could use some data validation or drop-down menus in order to get people to use a set of “approved descriptions”. But apparently the different departments “NEED” this free text type option (& yup, they answered in capitals!)
I’m wondering if there’s a piece of code which would allow me to something like this:
1. Show the incoming list of “unconfirmed descriptions” in one column (generally 100,000 different descriptions but can be more or less)
2. Display on each row the different “approved descriptions” which are contained in that text.

NB I should have mentioned that the “text” can also contain digits, slashes, dashes etc)

So for example, Column A contains the set of “approved descriptions”, Column C contains the set of “unconfirmed descriptions” and then Columns D, E, F, G etc contain the “approved descriptions” which are contained inside that text.

And no entries if there it contains zero "approved!
EG:
[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]approved[/TD]
[TD][/TD]
[TD]unconfirmed descriptions[/TD]
[TD]approved 1[/TD]
[TD]approved 2[/TD]
[/TR]
[TR]
[TD]nut[/TD]
[TD][/TD]
[TD]2 inch nut with special thread[/TD]
[TD]nut[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]plate[/TD]
[TD][/TD]
[TD]bolt - TBC[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]insert[/TD]
[TD][/TD]
[TD]backplate[/TD]
[TD]plate[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]insert assembly with nut[/TD]
[TD]insert[/TD]
[TD]nut[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]nut[/TD]
[TD]nut[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

If anybody could point me in the right direction, I’d be eternally grateful. And may even stop thinking terrible thoughts about the people that type in “25 massive nails” one week, then “26 massive nails” the next, then 27, then blah blah the following week lol!!!!

Many thanks
Neil.

 

[Oscar Cronquist]

Re: Code to display "text containing other text" https://www.mrexcel.com/forum/images/smilies/icon_eek.gif

[Table="width:, class:head"][tr=bgcolor:#E0E0F0][th] [/th][th]

A​

[/th][th]

B​

[/th][th]

C​

[/th][th]

D​

[/th][th]

E​

[/th][th]

F​

[/th][/tr]
[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

1​

[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

2​

[/td][td]approved[/td][td][/td][td]unconfirmed descriptions[/td][td]approved 1[/td][td]approved 2[/td][td][/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

3​

[/td][td]nut[/td][td][/td][td]2 inch nut with special thread[/td][td]nut[/td][td][/td][td][/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

4​

[/td][td]plate[/td][td][/td][td]bolt - TBC[/td][td][/td][td][/td][td][/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

5​

[/td][td]insert[/td][td][/td][td]backplate[/td][td]plate[/td][td][/td][td][/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

6​

[/td][td][/td][td][/td][td]insert assembly with nut[/td][td]nut[/td][td]insert[/td][td][/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

7​

[/td][td][/td][td][/td][td]nut[/td][td]nut[/td][td][/td][td][/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

8​

[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]
[/table][Table="width:, class:grid"][tr][td]Sheet: Sheet22[/td][/tr][/table]

Array formula in cell D3:

=INDEX($A$3:$A$5,SMALL(IF(COUNTIF($C3,"*"&$A$3:$A$5&"*")>0,MATCH(ROW($A$3:$A$5),ROW($A$3:$A$5)),""),COLUMNS($A$1:A1)))

 

[ellison]

Re: Code to display "text containing other text" https://www.mrexcel.com/forum/images/smilies/icon_eek.gif

Sir, you are a genius - that works beautifully!!!!!

 

[Peter_SSs]

Re: Code to display "text containing other text" https://www.mrexcel.com/forum/images/smilies/icon_eek.gif

Sir, you are a genius - that works beautifully!!!!!

So, you are comfortable with that sample list in column A, that if a cell in column B contained the text "insert minute assembly", then column C would return "nut"?

 

[ellison]

Re: Code to display "text containing other text" https://www.mrexcel.com/forum/images/smilies/icon_eek.gif

That is a very good point (& one I hadn't considered!!!)

Is there a way of dealing with that?!

Thanks
Neil

 

[Peter_SSs]

Re: Code to display "text containing other text" https://www.mrexcel.com/forum/images/smilies/icon_eek.gif

Is there a way of dealing with that?!

Hmmm, I'm not sure.
Do you really have up to 20,000 words in column A & up to 100,000 descriptions in column C?

 

[ellison]

Re: Code to display "text containing other text" https://www.mrexcel.com/forum/images/smilies/icon_eek.gif

yes ... and it grows month by month grrrrrrrrrrrrr

It's okay though Peter, the formula above gets rid of a huge amount of the checking. So we can mop the rest up with a bit of good old fashioned filters, lookups and deleting!

Thanks for your help though

All the best

Neil

 

[Peter_SSs]

Re: Code to display "text containing other text" https://www.mrexcel.com/forum/images/smilies/icon_eek.gif

I think this solves the "nut" type problem (see row 8) but I suspect it will struggle with a range of 20,000 cells in column A. Anyway, you could give it a try.
Formula in D3 (array formula again) is copied across and down.
Note that this lists the words in the order they appear in the column C cell, not the order they appear in column A

Excel Workbook
	A	B	C	D	E
2	approved		unconfirmed descriptions	approved 1	approved 2
3	nut		2 inch nut with special thread	nut
4	plate		bolt - TBC
5	insert		backplate
6			insert assembly with nut	insert	nut
7			nut	nut
8			insert minute assembly plate	insert	plate
ellison

 

[Oscar Cronquist]

Re: Code to display "text containing other text" https://www.mrexcel.com/forum/images/smilies/icon_eek.gif

So, you are comfortable with that sample list in column A, that if a cell in column B contained the text "insert minute assembly", then column C would return "nut"?

Thank you Peter_SSs, I didn't see that as a problem. Look at backplate in cell C5 and cell D5 returns plate in the first post.

 

[Peter_SSs]

Re: Code to display "text containing other text" https://www.mrexcel.com/forum/images/smilies/icon_eek.gif

Look at backplate in cell C5 and cell D5 returns plate in the first post.

Yes, I noticed that when I made my last post but hadn't when I was manufacturing my "nut" example earlier.
So now I'm not really sure what the desired outcome for 'partial match' examples like that is.