CODEWITHSUNDEEP
excel-formulaconcatenation
JimS-W
JimS-W

Reputation: 101

Building large complex lists in excel, using multiple parameters to determine list content

I have a spreadsheet which I use to record species presence and abundance within a number of woodland zones (see excerpt below). The spreadsheet also contains a lot of other data about each species.

enter image description here

I choose from a current list of c.1100 species (Column F), selecting the letter that corresponds to their abundance (I-N), using the DAFOR Scale (Dominant; Abundant; Frequent; Occasional; Rare) or “Present”, if I do not yet know their abundance, or “?” if I am unsure about the species. There are 25 Zones in total in each sheet.

Numbers of species per zone, and total numbers of species, are then easily calculated. “?”s are excluded from the count.

However I also need to be able to output various lists which accumulate the species found, together with their abundance in some cases, by Zone, and by various other parameters, e.g. species types (Column FN), invasive species (AO), and so on.

Separator would be “; “, with a “.” at the end of each list. Blanks should not be included.

e.g. in this example:

In reality the lists often run to hundreds of species.

I need to be able to use this out in the field, on a mobile device (ruggedised laptop) with limited battery and processing power, and often for the best part of a day. I therefore need the process to be as efficient as possible.

So my question is: is there a quick/efficient way to build some or all of this?

I have managed to achieve most of this, mostly in a separate tab, using TEXTJOIN, and various other standard functions. However it is achieved in such a hugely convoluted manner, using so many formulae and cells (literally thousands) that it would be very hard to demonstrate it here.

It kind of works, but runs very slowly, and freezes up completely on occasion, presumably because each entry I make then necessitates a large amount of resource for each output, even though many of the outputs will be NULL. The formulae also break very easily.

Thank you

Upvotes: 0

Views: 73

Answers (1)

P.b
P.b

Reputation: 11628

This requires M365, but as you mention you use a mobile. I wrote this using the mobile M365 app, so it should work:

=LET(LIST,
LAMBDA(zone,[Abundance],[Invasive],
LET(x,XLOOKUP(zone,I2:AI2,I5:AI13),
    y,2-IF(ISOMITTED(Abundance),0,1-Abundance),
TEXTJOIN(TAKE({" ","; "},,-y),
         ,
         TOCOL(
               IFS(LEN(x)*IF(ISOMITTED(Invasive),
                             1,
                             IF(Invasive,AO5:AO13="Y",1),
                   HSTACK(F5:F13,
                          IF(y=2,x,""))),
               2))&".")),
LIST(2,1,1))

This creates a function called LIST with the following inputs:

  1. Zone (mandatory input)

Zone a number resembling the Zone number you want your info from.

  1. Abundance (optional input)

If 0, or FALSE Abundance will exclude the Abundance value from selected Zone.

If 1, TRUE or omitted Abundance will include the Abundance value from selected Zone.

  1. Invasive

If 0, or FALSE Invasive will ignore values in AO5:AO13 and include all Abundance values from selected Zone

If 1, TRUE or omitted Invasive will include all Abundance values from selected Zone where the rows in values from AO5:AO13 equal "Y" only

You can use it as is and change the last part of the formula LIST(2,0,1) to your needs,

Or create a named lambda by creating name LIST refers to: =LAMBDA(zone,[Abundance],[Invasive],LET(x,XLOOKUP(zone,I2:AI2,I5:AI13),y,2-IF(ISOMITTED(Abundance),0,1-Abundance),TEXTJOIN(TAKE({" ","; "},,-y),,TOCOL(IFS(LEN(x)*IF(ISOMITTED(Invasive),1,IF(Invasive,AO5:AO13="Y",1)),HSTACK(F5:F13,IF(y=2,x,""))),2))&"."))

You could than use LIST as a function:

=LIST(2,0,1) will return values from Zone 2, excluding Abundance values, filtering on being Invasive.

=List(1) will return all values from Zone 1 including Abundance values, not filtering on being Invasive. (Could also be written as LIST(1,1,0))

Link to file (F15 currently holds the formula; F17 the named lambda version).

Upvotes: 0

Related Questions