Page 5 of 10 FirstFirst ... 34567 ... LastLast
Results 41 to 50 of 95

Thread: Extract multiple keywords from text string
Thanks Thanks: 0 Likes Likes: 0

  1. #41
    MrExcel MVP
    Moderator
    Peter_SSs's Avatar
    Join Date
    May 2005
    Location
    Macksville, Australia
    Posts
    41,404
    Post Thanks / Like
    Mentioned
    100 Post(s)
    Tagged
    21 Thread(s)

    Default Re: Extract multiple keywords from text string

    Sorry, I don't understand this example
    Hope this helps, good luck.
    Peter
    Excel 365 - Windows 10
    - Want to help your helpers by posting a small, copyable, screen shot directly in your post? Look here
    - If posting vba code, please use Code Tags - like this [code]Paste code here[/code] - or use the # key in the Reply window
    - Read: Forum Rules, Forum Use Guidelines, & FAQ

  2. #42
    New Member
    Join Date
    Jun 2012
    Posts
    50
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Extract multiple keywords from text string

    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.

  3. #43
    New Member
    Join Date
    Jun 2012
    Posts
    50
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Extract multiple keywords from text string

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


    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

    ===================================================
    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

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

  4. #44
    MrExcel MVP
    Moderator
    Peter_SSs's Avatar
    Join Date
    May 2005
    Location
    Macksville, Australia
    Posts
    41,404
    Post Thanks / Like
    Mentioned
    100 Post(s)
    Tagged
    21 Thread(s)

    Default Re: Extract multiple keywords from text string

    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?
    Hope this helps, good luck.
    Peter
    Excel 365 - Windows 10
    - Want to help your helpers by posting a small, copyable, screen shot directly in your post? Look here
    - If posting vba code, please use Code Tags - like this [code]Paste code here[/code] - or use the # key in the Reply window
    - Read: Forum Rules, Forum Use Guidelines, & FAQ

  5. #45
    New Member
    Join Date
    Jun 2012
    Posts
    50
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Extract multiple keywords from text string

    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

    Thank you Sir

  6. #46
    MrExcel MVP
    Moderator
    Peter_SSs's Avatar
    Join Date
    May 2005
    Location
    Macksville, Australia
    Posts
    41,404
    Post Thanks / Like
    Mentioned
    100 Post(s)
    Tagged
    21 Thread(s)

    Default Re: Extract multiple keywords from text string

    Quote Originally Posted by iazeemkhan View Post
    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.
    Hope this helps, good luck.
    Peter
    Excel 365 - Windows 10
    - Want to help your helpers by posting a small, copyable, screen shot directly in your post? Look here
    - If posting vba code, please use Code Tags - like this [code]Paste code here[/code] - or use the # key in the Reply window
    - Read: Forum Rules, Forum Use Guidelines, & FAQ

  7. #47
    New Member
    Join Date
    Jun 2012
    Posts
    50
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Extract multiple keywords from text string

    Dear Sir,

    You are right. Please consider below;

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

    Thank you Sir.

  8. #48
    MrExcel MVP
    Moderator
    Peter_SSs's Avatar
    Join Date
    May 2005
    Location
    Macksville, Australia
    Posts
    41,404
    Post Thanks / Like
    Mentioned
    100 Post(s)
    Tagged
    21 Thread(s)

    Default Re: Extract multiple keywords from text string

    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.

    Code:
    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
    Hope this helps, good luck.
    Peter
    Excel 365 - Windows 10
    - Want to help your helpers by posting a small, copyable, screen shot directly in your post? Look here
    - If posting vba code, please use Code Tags - like this [code]Paste code here[/code] - or use the # key in the Reply window
    - Read: Forum Rules, Forum Use Guidelines, & FAQ

  9. #49
    New Member
    Join Date
    Jun 2012
    Posts
    50
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Extract multiple keywords from text string

    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



    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

    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.


  10. #50
    MrExcel MVP
    Moderator
    Peter_SSs's Avatar
    Join Date
    May 2005
    Location
    Macksville, Australia
    Posts
    41,404
    Post Thanks / Like
    Mentioned
    100 Post(s)
    Tagged
    21 Thread(s)

    Default Re: Extract multiple keywords from text string

    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?
    Last edited by Peter_SSs; Aug 5th, 2019 at 05:40 AM.
    Hope this helps, good luck.
    Peter
    Excel 365 - Windows 10
    - Want to help your helpers by posting a small, copyable, screen shot directly in your post? Look here
    - If posting vba code, please use Code Tags - like this [code]Paste code here[/code] - or use the # key in the Reply window
    - Read: Forum Rules, Forum Use Guidelines, & FAQ

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •