Keyword extraction formula

**Ninja2k** · 2014-01-08, 12:59 PM

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?

**NBVC** · 2014-01-08, 02:08 PM

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.

**Ninja2k** · 2014-01-08, 02:53 PM

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.

**NBVC** · 2014-01-08, 04:28 PM

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

**Ninja2k** · 2014-01-08, 04:40 PM

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?