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

Question

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.

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:

A list of herbs found within Zone 2 with abundance would return: “Alexanders ?; Allseed D; American willowherb P.”
A list of all invasive species without abundance would return just: “American willowherb.”
A list of all species without abundance would return: “Alexanders; Allseed; Almond willow; Alpine currant; American willowherb.”

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

hi - would be good to have sample data to work with - cannot reproduce the screeshot you have provided - also - where do you envisage selecting the 3 bullet points? try pivot table perhaps — JB-007
– JB-007, Commented Feb 15 at 15:51
May I ask why you want the result joined in a cell? TEXTJOIN has a limit of 32767 characters. Apart from this, how readable would it become? — P.b
– P.b, Commented Feb 15 at 20:59
Hi @JB-007, not sure how I share sample data on here - please explain. Pivot tables look like they might work, but can they output text lists, instead of just tallies? — JimS-W
– JimS-W, Commented Feb 16 at 6:47
Hi @P.b, If I understand your question correctly: I need to output lists/tables of lists, to put into reports to show survey results. I had assumed these need to be joined in a cell, but is there another way to do this? — JimS-W
– JimS-W, Commented Feb 16 at 6:53
I don't know what I don't know, that's why I asked. Apparently you use the end result of the calculation into a report. I still wonder the notation is that handy. Why not use a different separator between the name and abundance. A name could contain multiple words, which are separated by the same separator we use to separate the name and abundance. — P.b
– P.b, Commented Feb 16 at 8:34

P.b · Accepted Answer · 2025-02-15 16:19:25Z

0

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:

Zone (mandatory input)

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

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.

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

edited Feb 15 at 16:19

answered Feb 15 at 15:50

P.b

12k2 gold badges15 silver badges34 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

JimS-W Feb 16 at 6:29

Thank you. I should have explained - the 'mobile device' is a ruggedised laptop, running Office 365 - would it still work on this?

P.b Feb 16 at 8:27

Yes, Office365 (M365) should work. But you find out soon enough if you try.

Collectives™ on Stack Overflow

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

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related