How to pull through a list while not including rows with no matches

[Accipiterformes]

Good day,

I am trying to pull through a list of birds per location (sheet 2) from a mixed list (sheet 1). I have used a VLOOKUP plus a IF/OR but I am obviously getting unmatched results where that bird is not found in that location. Is there a way to pull the matches through for each location column but have no rows in between where the values do not match? The location list must also look for a match in all 3 location columns in sheet 1. My first post on a forum of any sort so my apologies if I have done something wrong.

[dflak]

Please confirm which is sheet 1 and which is sheet 2. Are you trying to populate the Sites List from the information on the species list?

[Accipiterformes]

Hi dflak, thanks for the response. Apologies, yes sheet 1 is Species List and sheet 2 is Site list and yes, I am trying to populate the site lists from the species list :-)

[JohnTopley]

Try in E2

=IF(OR('Species list'!$C2=E$1,'Species list'!$D2=E$1,'Species list'!$E2=E$1), A2, "No")

Drag across and down column

Removed intermediate columns and added locations in alphabetical order "Site List"

[dflak]

Here's a VB implementation with a slight twist.

First I converted the Species List and Location Validation tables to tables. The main reason is because tables know how big they are, and they also copy down formulas, formats and validations automatically. So you can add and delete data and you don't have to change formulas.

I overlaid a named dynamic range on the locations table and used it for the data validation for all the locations in the species list. The dynamic range means you can add locations and the validation will expand to cover them.

Next I made a pivot table against each of these tables in the Working sheet. These ranges are alphabetized (that's what pivot tables do), and I copy and paste them to the Location Sheet as row and column headings.

From there, I go down the list of species I get the bird name and the location and find it on the grid on the locations page. If there is a location, then it plots a "Y".

There's a button on the working page that runs the macro.

I noticed that you had additional columns on your grid that were hidden. I don't know what purpose these serve. If we have to work them back in, let me know.

[Accipiterformes]

Hi John and dflak. Thank you for your time. This is not quite what I want though. The attached has a sheet called "Example of desired result". You will see that there is a column for each location but only species found in that location (whether as a primary, secondary or tertiary location) pull through. This means someone doesnt have to scan through a lot of spaces of "No" to see which species are found in that location (do not want to have to use filters manually on each column to determine this). Also, if these lists can be updated automatically whether a species is inserted between others or at the bottom of the list on the Species list sheet (think this will require a dynamic range?).

[JohnTopley]

VBA code:

Worksheet event code

To add this code: click tab "Sites List", "View code", copy and paste

Please Login or Register  to view this content.

To add this code: Alt+F11, "Insert" == >"Module" == > copy and paste

Please Login or Register  to view this content.

Added named range "Location" which is your list of (sorted) sites

Data Validation changed to list / =Location

Selecting from DV will initiate code.

[Glenn Kennedy]

..or if you do want a formula solution, try this. Two array formulae. One in A2, copied down:

Please Login or Register  to view this content.

and another in B2, copied across and down:

Please Login or Register  to view this content.

[Glenn Kennedy]

Oops. I forgot to add:

Array Formulae are a little different from ordinary formulae in that they MUST be confirmed by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER.

You will know the array is active when you see curly brackets { } - or "curly braces" for those of you in the USA, or "flower brackets" for those of you in India - appear around the outside of your formula. If you do not use CTRL+SHIFT+ENTER you will (almost always) get an error message or an incorrect answer. Press F2 on that cell and try again.

Don't type the curly brackets yourself - it won't work...

[Accipiterformes]

Thank you John! Working a dream! I just made the data validation list a table so that it would auto update and include any additional locations added between or at the end of the locations currently on the list. Thanks for everyone's help

[Accipiterformes]

Thanks Glenn. John, is there a way to have the columns in the site list sheet automatically receive the data as you put a new site into the validation list. So lets say there are 4 locations, 1, 2, 3 and 4. That this would automatically be allocated to columns A, B, C and D in the site list. Then, if I add a 5th location, that would automatically be allocated to column E in the site list? (i.e. not having to select a site from a validation in the first row on the site list?) - I know that this has implications on the code, as the location selection is the command for running the code :-(

[JohnTopley]

Please Login or Register  to view this content.

Add tab "Dashboard" and moved "Location" list to this tab: named range "Location" is dynamic so range automatically changes if locations are added/deleted.

Recommend you insert in sorted order as we naturally like lists in a known sequence!

Output on tab "Sites Lists"

Click "RUN" to execute macro

Oriiginal code with drp-down still available.

[Accipiterformes]

Thank you! Apologies for constantly shifting the goal posts!

[JohnTopley]

Thanks for the rep.

If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED. Thanks.