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 .................................................................................
[TABLE="width: 500"]
<tbody>[TR]
[TD="align: center"]A[/TD]
[TD="align: center"]B[/TD]
[TD="align: center"]C[/TD]
[TD="align: center"]D[/TD]
[TD="align: center"]E[/TD]
[TD="align: center"]F[/TD]
[TD="align: center"]G[/TD]
[TD="align: center"]F[/TD]
[TD="align: center"]G[/TD]
[/TR]
[TR]
[TD]HV Switchgear[/TD]
[TD]MV Panels[/TD]
[TD]MV Switchgear[/TD]
[TD]11 KV Switchgear[/TD]
[TD]RMU[/TD]
[TD]MVS[/TD]
[TD]MLT[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Access control[/TD]
[TD]Card Reader[/TD]
[TD]Push button for exit[/TD]
[TD]Door contact/holder[/TD]
[TD]Mortise Lock[/TD]
[TD]Input Module[/TD]
[TD]Door controller[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Fire alarm[/TD]
[TD]Heat Detector[/TD]
[TD]Multi Sensor [/TD]
[TD]Smoke detector[/TD]
[TD]Duct Smoke Detector[/TD]
[TD]Sounder horn[/TD]
[TD]Strobe Light[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]PAVA[/TD]
[TD]remote paging microphone[/TD]
[TD]emergency panel[/TD]
[TD]digital event recorder[/TD]
[TD]CD player[/TD]
[TD]tuner[/TD]
[TD]overriding relay[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Active components[/TD]
[TD]IP Telephony Handsets[/TD]
[TD]PABX[/TD]
[TD]Wireless controller[/TD]
[TD]-[/TD]
[TD]-[/TD]
[TD]-[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]


SHEET 2

[TABLE="width: 500"]
<tbody>[TR]
[TD]DESCRIPTION[/TD]
[TD]RESULT[/TD]
[/TR]
[TR]
[TD]HIGH VOLTAGE DISTRIBUTION INSTALLATION; EQUIPMENT; Containment including all fittings, couplers, supports, fixings and ancillary work as necessary; HV cable Ladder, MV Panels[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]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 [/TD]
[TD][/TD]
[/TR]
[TR]
[TD]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[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]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[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

===================================================
SAMPLE FINAL RESULT (REFERENCE ONLY)
[TABLE="width: 500"]
<tbody>[TR]
[TD]DESCRIPTION[/TD]
[TD]RESULT[/TD]
[/TR]
[TR]
[TD]HIGH VOLTAGE DISTRIBUTION INSTALLATION; EQUIPMENT; Containment including all fittings, couplers, supports, fixings and ancillary work as necessary; HV cable Ladder, MV Panels[/TD]
[TD]HV Switchgear[/TD]
[/TR]
[TR]
[TD]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 [/TD]
[TD]Access control[/TD]
[/TR]
[TR]
[TD]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[/TD]
[TD]Fire alarm[/TD]
[/TR]
[TR]
[TD]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[/TD]
[TD]PAVA[/TD]
[/TR]
</tbody>[/TABLE]

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

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:

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD="align: center"]DESCRIPTION[/TD]
[TD="align: center"]RESULT (COMPONENT NAME )[/TD]
[/TR]
[TR]
[TD]HIGH VOLTAGE DISTRIBUTION INSTALLATION; EQUIPMENT; Containment including all fittings, couplers, supports, fixings and ancillary work as necessary; HV cable Ladder, MV Switchgear[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]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[/TD]
[TD][/TD]
[/TR]
[TR]
[TD] 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[/TD]
[TD][/TD]
[/TR]
[TR]
[TD] 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[/TD]
[TD][/TD]
[/TR]
[TR]
[TD] 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)[/TD]
[TD][/TD]
[/TR]
[TR]
[TD] 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[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]



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

[TABLE="class: cms_table, width: 1024"]
<tbody>[TR]
[TD]
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.


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

 

[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?