Extract multiple keywords from text string

[HughT]

I have searched the forum but I can't find anything that quite matches this.

This is for a healthy eating project. Students record their meals in a daily diary. This is random unformatted text in a single cell (B1, B2 etc). There is a keyword list of healthy foods, each item being in a separate cell. What I would like to do is search the random text for occurrences of the keywords and return the keywords in another cell adjacent to the text cell. I would then like to be able to search the returned cells by the keyword list.

So:

Keywords (each in a separate cell, but doesn't have to be in Column A):

A1 Apple
A2 Fries
A3 Salad
A4 Burger
etc

Text (in B1)
Today I ate a burger with fries, and had an apple afterwards.

Result (in C1)
Apple Fries Burger [order is not important]

C1 to C20 (etc) will be the searchable data. I want to be able to search this by each keyword in the range A1:A4, ie 'Apple', 'Fries', 'Salad' etc so I can see who has been eating Apples, Fries, etc. Using column filters will display the contents of every cell, so if some comedian enters the whole range A1:A4 (which will actually be much larger) the filter will also return the whole range, so I need an alternative method.

I possible I would like to do this by a formula rather than VBA as I have to hand this over to someone who will not understand VBA, and can add to or alter the contents of the lookup range (A1:A4) simply by adding to it or overtyping the existing contents.

Thank you for your help.

 

[Peter_SSs]

Sorry, I don't understand this example

 

[iazeemkhan]

Sir, please ignore my last message, there is some changes in question, please avoid above message. Thanks a lot, will join soon with modified question.

 

[iazeemkhan]

Dear Sir,

Request you yo please send me a solution for below;

Sheet 1

1) There is a List of Component Names in A column
2) From column B to column XDZ there are item namesin each column which comes under Column A.

Sheet 2

Column A content Descriptions, Column B will Result Column

Now What I need

I want to search Item Names in Description (Column A Sheet 2),if found get the Component Name of that Item Name in Result column B (Sheet 2)


SHEET 1

COMPONENT NAMES IN COLUMN A
ITEM NAMES (which comes under Column A) FROM COLUMN B to XDZ (Remaining Item names will be added in main data, below is just a sample)



Component
Name I T E M N A M E S .................................................................................

A	B	C	D	E	F	G	F	G
HV Switchgear	MV Panels	MV Switchgear	11 KV Switchgear	RMU	MVS	MLT
Access control	Card Reader	Push button for exit	Door contact/holder	Mortise Lock	Input Module	Door controller
Fire alarm	Heat Detector	Multi Sensor	Smoke detector	Duct Smoke Detector	Sounder horn	Strobe Light
PAVA	remote paging microphone	emergency panel	digital event recorder	CD player	tuner	overriding relay
Active components	IP Telephony Handsets	PABX	Wireless controller	-	-	-

<tbody>
</tbody>

SHEET 2

DESCRIPTION	RESULT
HIGH VOLTAGE DISTRIBUTION INSTALLATION; EQUIPMENT; Containment including all fittings, couplers, supports, fixings and ancillary work as necessary; HV cable Ladder, MV Panels
SECURITY SYSTEM INSTALLATION; EQUIPMENT; CCTV - Switchgear and equipment, Card Reader, including supports and ancillary work as necessary; Hotel security control room - (including video wall monitors, anpr workstation, operator/supervisor workstation, badge
LOW VOLTAGE DISTRIBUTION INSTALLATION; EQUIPMENT; Switch gear and equipment, including supports,Multi Sensor final point-wiring, containment and ancillary work as necessary; MDB-P-GF-03(1459.54kW)Heat Detector
LOW VOLTAGE DISTRIBUTION INSTALLATION; EQUIPMENT; Switch gear and equipment, including supports, final point-wiring, containment and ancillary work as necessary; MDB-P-GF-04(1440.8kW)built-in dual channel digital source card, CD player

<tbody>
</tbody>

===================================================
SAMPLE FINAL RESULT (REFERENCE ONLY)

DESCRIPTION	RESULT
HIGH VOLTAGE DISTRIBUTION INSTALLATION; EQUIPMENT; Containment including all fittings, couplers, supports, fixings and ancillary work as necessary; HV cable Ladder, MV Panels	HV Switchgear
SECURITY SYSTEM INSTALLATION; EQUIPMENT; CCTV - Switchgear and equipment, Card Reader, including supports and ancillary work as necessary; Hotel security control room - (including video wall monitors, anpr workstation, operator/supervisor workstation, badge	Access control
LOW VOLTAGE DISTRIBUTION INSTALLATION; EQUIPMENT; Switch gear and equipment, including supports,Multi Sensor final point-wiring, containment and ancillary work as necessary; MDB-P-GF-03(1459.54kW)Heat Detector	Fire alarm
LOW VOLTAGE DISTRIBUTION INSTALLATION; EQUIPMENT; Switch gear and equipment, including supports, final point-wiring, containment and ancillary work as necessary; MDB-P-GF-04(1440.8kW)built-in dual channel digital source card, CD player	PAVA

<tbody>
</tbody>

Thank you Sir, I tried to explain but If you have any query, please let me know.

 

[Peter_SSs]

16,000+ columns is a lot of columns!!
I'm not sure if I would have a suggestion for data that big but lets see after the following questions are resolved.

1a. Sheet1. Can an item name appear in more than 1 row?
1b. If so, does that mean that the result column in Sheet2 could contain more than 1 component name?

2. Sheet1. In any given row can there be data then blank cells then more data? For example, in your sample the 'Active components' row has items in columns B, C & D then some blanks in columns E, F & G. Could there be another item in that row in, say, column P?

3. Sheet1. About how many rows are likely?

4. Sheet2. About how many rows are likely?

 

[iazeemkhan]

Dear Sir,

1a. Sheet1. Can an item name appear in more than 1 row?
Ans: No
1b. If so, does that mean that the result column in Sheet2 could contain more than 1 component name?
Ans: There will be only one Component name.

2. Sheet1. In any given row can there be data then blank cells then more data? For example, in your sample the 'Active components' row has items in columns B, C & D then some blanks in columns E, F & G. Could there be another item in that row in, say, column P?
Ans: Blank suggests that the item is done for that component. (In future, we can add new item after last item name).

3. Sheet1. About how many rows are likely?
Ans: Say Around 50000

4. Sheet2. About how many rows are likely?
Ans: Say Around 50000


Below is My Suggestion Only (Please Advice)
Can we do vice versa (Transpose) for Sheet1 -Example below

HV Switchgear	Access control	Fire alarm	PAVA
MV Panels	Access control panel	Heat Detector	microprocessor based digital public address matrix system
MV Switchgear	Card Reader	Heat Detector with sounder	remote paging microphone
11 KV Switchgear	Push button for exit	Multi Sensor	emergency panel
RMU	Door contact/holder	Multi Sensor detector with sounder	digital event recorder
MVS	Electro magnetic door lock and door contact	Smoke detector	built-in dual channel digital source card
MLT	Mortise Lock	Smoke dector with sounder	control/signal from fire alarm system
	Input Module	Smoke detector with remote indicator	CD player
	Door controller	Duct Smoke Detector	tuner
	Breakglass	Munual call break point	line monitoring/line supervisory panel
	Intrusion Detection	Sounder horn	paging/line selector
	Gate barrier system	Strobe Light	network power amplifier
	Door position switch	Sounder & Strobe Flasher	maintenance-free lead-acid battery complete with charger
	ACS Server	Fire Telephone Jack	box speaker 6W/15W wall mounted at 2500 AFFL
	Guard Tour System	Fire Telephone Handset	ceiling speaker 6W/15W
		Fireman Panel	horn speaker 15W wall mounted at 2500 AFFL
		Wall Beam Receiver	overriding relay
		Wall Beam Transmitter	Pa rack
		Module	P & S Panel
		Interface Unit	PWD Main Panel
			BGM Controller

<colgroup><col><col><col><col></colgroup><tbody>
</tbody>

Thank you Sir

 

[Peter_SSs]

3. Sheet1. About how many rows are likely?
Ans: Say Around 50000


Below is My Suggestion Only (Please Advice)
Can we do vice versa (Transpose) for Sheet1 -Example below

I don't see how you could transpose Sheet1. You said it could have about 50,000 rows. If you transpose, that would make 50,000 columns.
However, a worksheet only has 16,000+ columns so it couldn't be done.

I will consider the problem with Sheet1 as it was in post 43 unless you advise that the 50,000 rows was a significant over-estimate, in which case you would need to provide a more accurate/realistic value for the number of Component Names in that worksheet.

 

[iazeemkhan]

Dear Sir,

You are right. Please consider below;

3. Sheet1. About how many rows are likely?
Ans: Say Around 10000

Thank you Sir.

 

[Peter_SSs]

This seems to work on the small sample data provided (with Sheet1 arranged as per post 45) but I'm not too whether it will handle the size data you actually have.
I would suggest testing on smallish samples to start with and gradually increase the size if it works as expected.

Sub Components()
  Dim RX As Object
  Dim aResults As Variant
  Dim c As Long, i As Long, ubaResults As Long
  Dim sComp As String
  
  Set RX = CreateObject("VBScript.RegExp")
  RX.Ignorecase = True
  With Worksheets("Sheet2")
    aResults = .Range("A2", .Range("A" & .Rows.Count).End(xlUp)).Resize(, 2).Value
  End With
  ubaResults = UBound(aResults)
  With Sheets("Sheet1")
    For c = 1 To .Cells(1, .Columns.Count).End(xlToLeft).Column
      sComp = .Cells(1, c).Value
      RX.Pattern = "\b" & Join(Application.Transpose(.Range(.Cells(2, c), .Cells(.Rows.Count, c).End(xlUp))), "|") & "\b"
      For i = 1 To ubaResults
        If IsEmpty(aResults(i, 2)) Then
          If RX.Test(aResults(i, 1)) Then aResults(i, 2) = sComp
        End If
      Next i
    Next c
  End With
  Sheets("Sheet2").Range("A2:B2").Resize(ubaResults).Value = aResults
End Sub

 

[iazeemkhan]

Dear Sir,

Thank you very much for your post. Actually, I need Component Name in Result colum in Sheet 1

Sheet 1:

DESCRIPTION	RESULT (COMPONENT NAME )
HIGH VOLTAGE DISTRIBUTION INSTALLATION; EQUIPMENT; Containment including all fittings, couplers, supports, fixings and ancillary work as necessary; HV cable Ladder, MV Switchgear
SECURITY SYSTEM INSTALLATION; EQUIPMENT; CCTV - Switchgear and equipment, Card Reader, including supports and ancillary work as necessary; Hotel security control room - (including video wall monitors, anpr workstation, operator/supervisor workstation, badge Push button for exit
LOW VOLTAGE DISTRIBUTION INSTALLATION; EQUIPMENT; Switch gear and equipment, including supports, final point-wiring, containment and ancillary work as necessary; MDB-P-GF-03(1459.54kW)Heat Detector
LOW VOLTAGE DISTRIBUTION INSTALLATION; EQUIPMENT; Switch gear and equipment, including supports, final point-wiring, containment and ancillary work as necessary; MDB-P-GF-04(1440.8kW)built-in dual channel digital source card
LOW VOLTAGE DISTRIBUTION INSTALLATION; EQUIPMENT; Switch gear and equipment, including supports, final point-wiring, containment and ancillary work as necessary; MDB-P-GF-05(1620.2kW)Active component - Security System (CCTV & ACS)
LOW VOLTAGE DISTRIBUTION INSTALLATION; EQUIPMENT; Switch gear and equipment, including supports, final point-wiring, containment and ancillary work as necessary; MDB-P-GF-06(1465.5kW) ATS40A

<tbody>
</tbody>

SHEET 2

Below are the Component mentioned (BOLD and RED) under each Components there are Item mentioned

What is the Requirement;

If we found any one Item in Description (Sheet 1) then we have to get its Component name display in Result column (Sheet 1) next to Description column. NOTE: COMPONENT Name to be display not the ITEM Name.

HV Switchgear	Access control	Fire alarm	PAVA	Access control system	Active components
MV Panels	Access control panel	Heat Detector	microprocessor based digital public address matrix system	Active component - Security System (CCTV & ACS)	ATS40A
MV Switchgear	Card Reader	Heat Detector with sounder	remote paging microphone	Active component - (ICT Network)	ATS63A
11 KV Switchgear	Push button for exit	Multi Sensor	emergency panel	IP Telephony Handsets	ATS80A
RMU	Door contact/holder	Multi Sensor detector with sounder	digital event recorder	PABX	ATS100A
MVS	Electro magnetic door lock and door contact	Smoke detector	built-in dual channel digital source card	Wireless controller	ATS125A
MLT	Mortise Lock	Smoke dector with sounder	control/signal from fire alarm system		ATS160A
	Input Module	Smoke detector with remote indicator	CD player		ATS250A
	Door controller	Duct Smoke Detector	tuner		ATS400A
	Breakglass	Munual call break point	line monitoring/line supervisory panel		ATS630A
	Intrusion Detection	Sounder horn	paging/line selector		ATS800A
	Gate barrier system	Strobe Light	network power amplifier		ATS1000A
	Door position switch	Sounder & Strobe Flasher	maintenance-free lead-acid battery complete with charger		ATS1200A
	ACS Server	Fire Telephone Jack	box speaker 6W/15W wall mounted at 2500 AFFL		ATS1600A
	Guard Tour System	Fire Telephone Handset	ceiling speaker 6W/15W		ATS2000A
		Fireman Panel	horn speaker 15W wall mounted at 2500 AFFL		ATS2500A
		Wall Beam Receiver	overriding relay
		Wall Beam Transmitter	Pa rack
		Module	P & S Panel
		Interface Unit	PWD Main Panel
			BGM Controller

<colgroup><col><col><col><col><col><col></colgroup><tbody>
</tbody>

RANGE FOR DATA Sheet1 Description data will be aproximately maximum 10000 rows. Sheet2 1) Component data (In Column wise data ) will not be morethen 6000 Columns 2) Item Data will also be Say 6000 to 9000 rows maximum. Thank you Sir, Hope I explain correctly. If you have any query please let me know.

<tbody>
</tbody>

 

[Peter_SSs]

Have you swapped your sheets?

In post 43 Sheet1 contained the Component Names and the items that related to those components. Sheet2 contained the text that needed to be scanned and the results.
Post 49 seems to be the opposite way around

If that is so, have you tried the code after swapping each Sheet1 for Sheet2 and vice-versa?