Alternative to HLOOKUP?

Hi papalapapp,

I think you will do better with a simple IF().

B2= IF(Table 1::B2="x",Table 1::B$1,"")

this is filled down and across.

quinn

HI papalapapp,

Quinn's solution using IF considers only the contents of columns B through E in the same row as the formula. It will give your desired result if the list in column A of both table remains the same (as in your posted example).

Here's the result you'd get if you entered "Pancake" into the first row of Quinn's Table 1 - 1, and left the rest of column A empty. The formula shown is from Quinn's example. placed in Table 1-1::B1, and filled down and right from there.

My reading of your question is that you want a table that will return results more like the example in Table 1-2 and 1-2a below. Notes on the error triangles follow the image.

Table 1-2 contains this formula in cell B1, filled down and right to the rest of the table:

Table 1-2::B1: =IF(VLOOKUP($A,Table 1 :: $A:$E,COLUMN(),FALSE)="x",Table 1::B$1,"")

The formula returns an error message when VLOOKUP cannot find a match for the search-value from column A in the first column of the search-where table, columns A to E of Table 1. There are several ways to make the error triangles not appear. Table 1-2a shows the results using the 'brute force' method—wrapping the main formula in an error trap: (original formula in bold)

Table 1-2a::B1: =IFERROR(IF(VLOOKUP($A,Table 1 :: $A:$E,COLUMN(),FALSE)="x",Table 1::B$1,""),"")

As can be seen, the trap catches all errors and presents a 'blank' cell.

But you may want to distinguish between errors caused by searching for 'nothing' (rows 2 and 6) and errors caused by searching for something not yet included in the lookup table, Table 1 ("Green salad" in row 5). The result would be something like this:

Table 1-2b::B1: =IFERROR(IF(VLOOKUP($A,Table 1 :: $A:$E,COLUMN(),FALSE)="x",Table 1::B$1,""),IF(AND(LEN($A)>0,COLUMN()=2),"item not found",""))

The part in bold replaces the empty string ( "" ) in the formula in Table 1-2a, above.

Regards,

Barry

So what you have is a set of three tables.

Table 1: A column listing Meals and several columns listing ingredients.

In each row, a mealname in column A, and an x in each column containing the ingredient listed in row 1 of that column.

Table 2: A copy of Table 1 with column label substituted for each x, and "("&string&") "&column label substituted for each string other tha

And with an added column holding a string consisting of the concatenated values in all cells of that row, separated by commas.

Table 3: A two column table. Column A accepts entry of a mealname; column B returns the ingredients list from Table 2.

Questions to consider:

Once the data for a meal has been entered into Table 1, how often will that line require editing?

If this is data that is entered once, then never changed, there is no need to recalculate its translation into words every time the document is opened (and every time any change is made to the document. Selecting the data that will stay the same on Table 2, Copying, then using Paste Results will replace the formulas in those cells with the last calculated results, and remove those calculation from the load.

When is it necessary to INSERT a row on Table 1, or to REMOVE a row from Table 1?

Could new meals be always added to the end of the table, and 'deleted' meals be simply marked with with a leading "ZZZ" on Table 2 to place them out of reach to any LOOKUP alphabetically before "ZZZ "

Is there a need for the individual ingredient names to appear in individual columns in Table 2? Is this table for viewing, or only for calculations on the way to Table 3? If there's no need for humans to view this table, the necessary concatenation might be possible to achieve without the excess commas, and with a trim needed only to remove the final comma and a trailing space.

Regards,

Barry

An illustration of part of the suggestions above:

Column B on each table is a header column containing ="" in each cell. This is necessary to the concatenation formula in the rest of the row in Table 2.

Other than this column, all data on Table 1 is entered directly to the cell.

Column A on Table 2 contains a straight cell reference to the paired cell in column A of Table 1. This can be replaced with the formula you are using for the same task.

Columns C, D, E and F of Table 2 contain a formula that copies the content of the cell in the same row of the column to its left, followed by one of three things:

• If the paired cell on Table 1 contains (only) an x, adds the ingredient name from Table 1, followed by a comma and a space.
• If the paired cell on table 1 contains any text other than a single x, inserts a left parenthesis, followed by the text in the paired cell, followed by a right parenthesis and a space, followed by the ingredient name from row 1, followed by a comma and a space.
• otherwise, adds a null string.

Column G contains a formula that removes the final comma and space.

Table 2::C2: =B1&IF(Table 1::C2="x",Table 1::C$1&", ",IF(LEN(Table 1::C2)>0,"("&Table 1::C2&") "&Table 1::C$1&", ",""))

Fill right to F2, and down to the last row of the table.

Table 2::G2: =LEFT(F1,LEN(F1)-2)

Observations:

1. Once the calculations Table 2 are done, the formulas in rows where calculation has been completed may be replaced with the last values calculated.
   An extra row at the bottom of the table, containing a dummy 'meal' provides a place to store the formula from C2.
2. The formula in C2 is scalable, as it can be filled right to as many columns as needed and down as many rows as needed, provided it is not filled over the formula in the final column. Note that Table 2 cannot have more columns that the number of columns in Table 1 plus the final column. To add columns to Table 2, drag the column control handle right, then fill the final column's formulas right to fill the new columns, then fill the last column of the 'retrieve and add' formula right to the column before the last new column.
3. As the calculations for Table 2 have to be done only after a change (or set of changes) in Table 1, an AppleScript might be a better tool than a set of formulas. A script could likely go through Table 1, and produce the contents of Table 3 directly, bypassing the need for Table 2. Once run, the contents of the lookup table would be stable and remain unchanged until the next time the script was run.

Regards,

Barry

Hello

If your goal is to enumerate ingredients for every meal given in column A, you might try the following table. Columns F:M are auxiliary and may be hidden.

INGREDIENTS (excerpt) A1 Meal A2 Pasta A3 Hamburger A4 Pancake B1 Sugar B2 B3 B4 x C1 Salt C2 x C3 x C4 x D1 Milk D2 D3 D4 x E1 Meat E2 E3 x E4 F1 F2 =IF(B2="x",B$1,"") F3 =IF(B3="x",B$1,"") F4 =IF(B4="x",B$1,"") G1 G2 =IF(C2="x",C$1,"") G3 =IF(C3="x",C$1,"") G4 =IF(C4="x",C$1,"") H1 H2 =IF(D2="x",D$1,"") H3 =IF(D3="x",D$1,"") H4 =IF(D4="x",D$1,"") I1 I2 =IF(E2="x",E$1,"") I3 =IF(E3="x",E$1,"") I4 =IF(E4="x",E$1,"") J1 J2 J3 J4 K1 K2 =J2&IF(LEN(J2)*LEN(F2)>0,", ","")&F2 K3 =J3&IF(LEN(J3)*LEN(F3)>0,", ","")&F3 K4 =J4&IF(LEN(J4)*LEN(F4)>0,", ","")&F4 L1 L2 =K2&IF(LEN(K2)*LEN(G2)>0,", ","")&G2 L3 =K3&IF(LEN(K3)*LEN(G3)>0,", ","")&G3 L4 =K4&IF(LEN(K4)*LEN(G4)>0,", ","")&G4 M1 M2 =L2&IF(LEN(L2)*LEN(H2)>0,", ","")&H2 M3 =L3&IF(LEN(L3)*LEN(H3)>0,", ","")&H3 M4 =L4&IF(LEN(L4)*LEN(H4)>0,", ","")&H4 N1 Ingredients N2 =M2&IF(LEN(M2)*LEN(I2)>0,", ","")&I2 N3 =M3&IF(LEN(M3)*LEN(I3)>0,", ","")&I3 N4 =M4&IF(LEN(M4)*LEN(I4)>0,", ","")&I4

Notes.

Formula in F2 can be filled down and right across F2:I5.

Formula in K2 can be filled down and right across K2:N5.

Column J needs to be blank for uniform formulae in K:N to work.

Table is built with Numbers v2.

Regards,

H

If you are using Numbers 3 a script is also good at something like this:

1. Copy-paste the script below into Script Editor (in Applications > Utilities)
2. Click anywhere in the source table (the one with the x's)
3. Click the run button in Script Editor.
4. Click once in B2 of the second table.
5. Command-v or Edit > Paste and Match Style.

SG

tell application "Numbers"

tell front document to tell active sheet

tell (first table whose selection range's class is range) ¬

to set lstOfLsts to rows's cells's value

end tell

end tell

set allIngredients to lstOfLsts'sitem 1's items 2 thru -1

set pasteStr to ""

set text item delimiters to ", "

repeat with i from 2 to count lstOfLsts

tell lstOfLsts'sitemi

set thisMeal to item 1

--> "Pancake"

set ingredientsXd to items 2 thru -1

--> {"x", "x", "x", missing value, missing value, missing value}

set theseIngredients to my getNames(ingredientsXd, allIngredients)

--> {"Flour, Sugar, Milk"}

set pasteStr to pasteStr & thisMeal & tab & (theseIngredients as string) & return

end tell

end repeat

set text item delimiters to ""

set the clipboard topasteStr

return pasteStr

to getNames(theseIngredients, allIngredients)

set ingredientNames to {}

repeat with i from 1 to count theseIngredients

tell theseIngredients'sitemi

if it is not missing value then ¬

copy allIngredients'sitemi to end of ingredientNames

end tell

end repeat

return ingredientNames

end getNames

I like Hiroto's method of inserting commas before all but the first ingredient in the growing list.

As written, his formula does not handle the use of comments to be added to the ingredient name, as well as "x" which places the name only in the ingredient boxes in columns F to I.

Revising Hiroto's formula in F2 as shown below, will add this capability, mentioned in the OP's post of March 14: "Neat addition: If you enter text instead of an x, for example "may contain traces", it will append it to the ingedient. => "Peanuts (may contain traces)""

Original: =IF(B2="x",B$1,"")

Revised: =IF(B2="x",B$1,IF(LEN(B2)>0,B$1&" ("&B2&")",""))

As in Hiroto's example, Fill down to F4, Fill right to column I.

Regards,

Barry

SG wrote:

"If you are using Numbers 3 a script is also good at something like this:"

Definitely! The OP stated earlier "The only downside (to a solution he'd come up with) is the massive processing power needed…Opening the file takes about 30 seconds of calculating even on a 2,3 GHz 16GB MBP. Table 1 has approx 100 rows and 35 columns, Table 2 double the amount to account for more entries in the future."

Ridding Numbers of the necessity of repeatedly doing all that calculation makes a script a better than 'good' tool for the job. The script does all the calculations to build the lookup table (Table 2) just once each time it is run, and is run once in each session where Table 1 has been edited. The rest of the time (when the document is being used), the only calculations left for Numbers to do are those done by the Lookup formula in Table 3.

Regards,

Barry

If you're fine with scripting solution, you might try the following AppleScript script which is a wrapper of bash/ruby script.

To use it, select the source range for ingredients matrix as shown in INGREDIENTS table below, copy it to the clipboard, run the script and it will put TSV text representing enumerated ingredients in the clipboard, which you can paste to destination range as shown in SUMMARY table below. Error is reported in result pane/window of (Apple)Script Editor.

* source range is selected

--APPLESCRIPT do shell script "/bin/bash -s <<'HUM' - { export LC_CTYPE=UTF-8 /usr/bin/ruby -w <<'EOF' - <(pbpaste) | pbcopy def array2text(aa, opts = {}) # array aa : 2d array # hash opts : {:fs => fs, :rs => rs} # string fs : field separator # string rs : record separator fs, rs = {:fs => %[\\t], :rs => %[\\n]}.merge(opts).values_at(:fs, :rs) return aa.map {|a| a.join(fs) }.join(rs) + rs end def text2array(t, opts = {}) # string t : text representation of 2d array # hash opts : {:fs => fs, :rs => rs} # string fs : field separator # string rs : record separator fs, rs = {:fs => %[\\t], :rs => %[\\n]}.merge(opts).values_at(:fs, :rs) return t.split(rs).map {|a| a.split(fs, -1)} end JOINED_FIELD_NAME = 'Ingredients' JOINER = ', ' aa, *bb = text2array(ARGF.read, :rs => /\\r\\n|\\r|\\n/) cc = [] bb.each do |b| i = 0 cc << [b.shift, b.inject([]) { |ee, e| i += 1; ee << aa[i] if e == 'x'; ee }.join(JOINER)] end aa1 = [aa[0], JOINED_FIELD_NAME] puts array2text([aa1, *cc]) EOF } 2>&1 exit 0 HUM" --END OF APPLESCRIPT

Briefly tested with Numbers v2 under OS X 10.6.8. It should work with any spreadsheet programs which support TSV text in the clipboard for data exchange.

Good luck,

H

I've tested Hiroto's script with Numbers 3 and Numbers 2 under El Cap. It does essentially the same thing as my script above. But instead of just click a cell, run, click, paste (4 steps) you have to select, copy to clipboard, run, click, paste (5 steps) and the select step may not be so convenient with 100 rows and 35 columns. For those still using Numbers 2, my script above will work (Numbers 3 should not be open at the same time) after being modified in two places to the following:

SG

tell application "Numbers"

tell front document to tell (first sheet whose tables's selection range's class contains range)

tell (first table whose selection range's class is range) ¬

to set lstOfLsts to rows's cells's value

end tell

end tell

set allIngredients to lstOfLsts'sitem 1's items 2 thru -1

set pasteStr to ""

set text item delimiters to ", "

repeat with i from 2 to count lstOfLsts

tell lstOfLsts'sitemi

set thisMeal to item 1

--> "Pancake"

set ingredientsXd to items 2 thru -1

--> {"x", "x", "x", missing value, missing value, missing value}

set theseIngredients to my getNames(ingredientsXd, allIngredients)

--> {"Flour, Sugar, Milk"}

set pasteStr to pasteStr & thisMeal & tab & (theseIngredients as string) & return

end tell

end repeat

set text item delimiters to ""

set the clipboard topasteStr

return pasteStr

to getNames(theseIngredients, allIngredients)

set ingredientNames to {}

repeat with i from 1 to count theseIngredients

tell theseIngredients'sitemi

if it is not 0 then ¬

copy allIngredients'sitemi to end of ingredientNames

end tell

end repeat

return ingredientNames

end getNames

papalapapp wrote:

I would love to use Applescript but considering that someone of unknown skill levels might use this document, it might cause problems down the road. Not the script itself but the method of handling. To be safe here, the script would have to be stored inside the file and should be triggered from a button on the sheet. But afaik that's not possible.

I'm always curious when I see someone leery of using an existing AppleScript because it is too hard to use for people of unknown skills.

There may be reasons not to use a script here. But difficulty of use is clearly not one of them!

All you have to do is:

1. Click in a cell in the table.
2. Click the run button
3. Click a destination cell.
4. Type command-c to paste.

Virtually no skills required!

And if you expand the table in the future no skills are needed to adjust formulas, etc. The script still works.

As for it being "safe" if a script is stored within a file and launched by a button on a sheet, I'm not so sure about that. That would be (slightly) more convenient if you have to use the script often. But I doubt if it would not be safer. Curious users of unknown skills clicking a button on a sheet are much more likely to cause problems.

Anyway, glad you've found a solution that works for you.

SG