Results 1 to 7 of 7

Thread: Keyword extraction formula

  1. #1

    Unhappy Keyword extraction formula



    Register for a FREE account, and/
    or Log in to avoid these ads!

    My Spreadsheet contains printer specifications in blocks that I need to add in to my inventory
    A formula reads these blocks and extracts each attribute.

    My Formula does the following:


    • Grabs a keyword from a cell
    • Finds the first instance of that keyword and returns the value in the opposite cell



    The problem is some of my specification blocks do not have a certain keyword such as Power

    Block 1 - Power Keyword
    Block 2 - No power
    Block 3 - Power Keyword

    My formula is returning the second instance of power but it belongs to block 3

    My Solution:


    • Make the formula smarter
    • Before each block put a keyword such as Main Specification so formula can count the blocks, block 1,2,3 etc
    • Formula then finds block 1 keyword 1, block 2 keyword 2 etc


    Ideal Result

    Block 1 Found Power keyword so return adjacent cell value of AC 230V
    Block 2 Did not find power so return blank
    Block 3 Found Power keyword so return adjacent cell value of AC 110/230 V ( 50/60Hz )

    Is there a way to modify my formula to make it return the correct values?
    Attached Files Attached Files

  2. #2
    Super Moderator NBVC's Avatar
    Join Date
    May 2011
    Location
    Mississauga, Canada
    Posts
    1,489
    Articles
    0
    Excel Version
    Excel 2016
    Perhaps you can use this suggestion.

    First, name each Block. Select Block1 (A1:B19) and type Block1 in the Name Box (just to the left of the Formula bar).

    Repeat for each block, using consistent and consecutive naming convention.

    Now in the Spec sheet, in R6 (for Power), enter formula:

    =IFERROR(INDEX(INDIRECT("Block"&ROWS($A$6:$A6)),MATCH(R$4,INDEX(INDIRECT("Block"&ROWS($A$6:$A6)),0,1),0),2),"")

    copied down. You can also copy left/right for the other categories.


  3. #3
    The problem is I need to repeat the process again and again, the block will have different sizes so I need to rely on a piece of text rather than a cell range of a specified size.

  4. #4
    Super Moderator NBVC's Avatar
    Join Date
    May 2011
    Location
    Mississauga, Canada
    Posts
    1,489
    Articles
    0
    Excel Version
    Excel 2016
    I am not sure if this is going to be the most efficient way... but based on assumption that the first line of each block is "Product Description", see if this works:

    Code:
    =IFERROR(INDEX(INDEX('Main Specs'!$B$1:$B$3989,SMALL(IF('Main Specs'!$A$1:$A$3989="Product Description",ROW('Main Specs'!$A$1:$A$3989)-ROW('Main Specs'!$A$1)+1),ROWS($A$1:$A1)),0):INDEX('Main Specs'!$B$1:$B$3989,IFERROR(SMALL(IF('Main Specs'!$A$1:$A$3989="Product Description",ROW('Main Specs'!$A$1:$A$3989)-ROW('Main Specs'!$A$1)+1),ROWS($A$1:$A2)),3989),0),SMALL(IF(INDEX('Main Specs'!$A$1:$A$3989,SMALL(IF('Main Specs'!$A$1:$A$3989="Product Description",ROW('Main Specs'!$A$1:$A$3989)-ROW('Main Specs'!$A$1)+1),ROWS($A$1:$A1)),0):INDEX('Main Specs'!$A$1:$A$3989,IFERROR(SMALL(IF('Main Specs'!$A$1:$A$3989="Product Description",ROW('Main Specs'!$A$1:$A$3989)-ROW('Main Specs'!$A$1)+1),ROWS($A$1:$A2)),3989),0)=R$4,ROW(INDEX('Main Specs'!$B$1:$B$3989,SMALL(IF('Main Specs'!$A$1:$A$3989="Product Description",ROW('Main Specs'!$A$1:$A$3989)-ROW('Main Specs'!$A$1)+1),ROWS($A$1:$A1)),0):INDEX('Main Specs'!$B$1:$B$3989,IFERROR(SMALL(IF('Main Specs'!$A$1:$A$3989="Product Description",ROW('Main Specs'!$A$1:$A$3989)-ROW('Main Specs'!$A$1)+1),ROWS($A$1:$A2)),3989),0))-MIN(ROW(INDEX('Main Specs'!$B$1:$B$3989,SMALL(IF('Main Specs'!$A$1:$A$3989="Product Description",ROW('Main Specs'!$A$1:$A$3989)-ROW('Main Specs'!$A$1)+1),ROWS($A$1:$A1)),0):INDEX('Main Specs'!$B$1:$B$3989,IFERROR(SMALL(IF('Main Specs'!$A$1:$A$3989="Product Description",ROW('Main Specs'!$A$1:$A$3989)-ROW('Main Specs'!$A$1)+1),ROWS($A$1:$A2)),3898),0)))+1),1)),"")
    confirmed with CTRL+SHIFT+ENTER and copied down.

    Note: formula assumes row 3989 is last row. You will need to replace all occurances of 3989 in formula to last row of your database (do not use larger than necessary, due to declined efficiency).


  5. #5
    This seems to work for Power only but I can't seem to make it work for the other keywords, my old formula grabbed the keywords from G4, H4, I4 etc, can something similar be done here?

  6. #6
    Sorry yes that worked I just didn't copy and paste correctly

  7. #7
    Super Moderator NBVC's Avatar
    Join Date
    May 2011
    Location
    Mississauga, Canada
    Posts
    1,489
    Articles
    0
    Excel Version
    Excel 2016
    Ok great. You're welcome.


Posting Permissions

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