+ Reply to Thread
Results 1 to 3 of 3

Extract info based on criteria (INDEX?)

  1. #1
    Registered User
    Join Date
    01-19-2012
    Location
    England
    MS-Off Ver
    Excel for Mac 2019
    Posts
    25

    Extract info based on criteria (INDEX?)

    I'm investigating the potential for humane slaughter of farmed fish, which typically requires stunning them (either electrically or percussively) prior to killing. For some species (marked 'y' in the table), a suitable method has already been developed. For others, there is no known method (blank cell). For a third category, there has been no research on that specific species, but it is closely related to a species with a known method, so I've marked those '?' and put the name of the related species in a separate column. I've done this for about 270 species so far.

    So, in order to prioritise species for further research, I want to know the total population ('count') of the groups of species that all may be able to use the same stunning methods. Ideally, I'd be able to see the populations of the individual species (as in 'Desired table 1' in the linked sheet) but also be able to sort the groups by total count. If this isn't feasible, something like table 2 would be good, with just the names and totals for the groups.

    I could probably figure out how to do this using VLOOKUP and SUMIF if I manually typed in the names of the species with known methods, but those may change so ideally I want it all to automatically update as the table changes. I tried using INDEX but couldn't figure out the ROW bit of the formula. Some help would be much appreciated.

    EDIT: It seems I can't post a link to the sheet, so I've uploaded a much-simplified version in Excel.
    Attached Files Attached Files

  2. #2
    Forum Expert dflak's Avatar
    Join Date
    11-24-2015
    Location
    North Carolina
    MS-Off Ver
    2013 / 2016 / 365
    Posts
    6,947

    Re: Extract info based on criteria (INDEX?)

    You have too much cross-referencing of the data. I streamlined it.

    A fish species is similar to itself, so that's one record. I may also be similar to other species as well, and I created a row of data for each species it is similar to. I did not duplicate mappings. For example, once I associated Atlantic Salmon with Sea Trout, I removed the entry that associates Sea Trout from Atlantic Salmon.

    I kept the original table to look up the stun method.

    So I built the table to look like the blue table on Sheet 2.
    Attached Files Attached Files
    One spreadsheet to rule them all. One spreadsheet to find them. One spreadsheet to bring them all and at corporate, bind them.

    A picture is worth a thousand words, but a sample spreadsheet is more likely to be worked on.

  3. #3
    Registered User
    Join Date
    01-19-2012
    Location
    England
    MS-Off Ver
    Excel for Mac 2019
    Posts
    25

    Re: Extract info based on criteria (INDEX?)

    Many thanks dflak

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1