How can I exclude blank cells from from INDEX COLLECT criteria?

l.gann

Hello,

I've got a sheet with over 13,000 rows. My goal is to have end users enter "search criteria" into several cells to narrow the list to a handful of options. The criteria will be Speaker First Name, Speaker Last Name, Specialty, and State.

So far, I've been able to pull the information from my source sheet based on the criteria, but the end users may not have all four pieces of criteria.

Is there a way I can modify my formula to filter my results as long as at least one piece of data is entered in the criteria cells?

Current Formula:

=IFERROR(IF([Speaker First Name]$1 <> "", INDEX(DISTINCT(COLLECT({AttendeesConcat},{AttendeeFN}, {AttendeeFN} = [Speaker First Name]$1)), [Primary Column]@row), IF([Speaker Last Name]$1 <> "", INDEX(DISTINCT(COLLECT({AttendeesConcat}, {AttendeeLN}, {AttendeeLN} = [Speaker First Name]$1)), [Primary Column]@row), IF(Specialty$1 <> "", INDEX(DISTINCT(COLLECT({AttendeesConcat}, {Specialty}, {Specialty} = Specialty$1)), [Primary Column]@row), IF(State$1 <> "", INDEX(DISTINCT(COLLECT({AttendeesConcat},{State}, {State} = State$1)), [Primary Column]@row))))), ")

This formula works if I only need to evaluate one criteria cell at a time, but doesn't have the waterfall effect I'm looking for.

Any advice would be greatly appreciated!!

The "search" criteria will be entered in the highlighted cells.

Paul Newcome

My suggestion would involve some helper column in the source sheet. First you would insert helper columns for each piece of search criteria (1st Name, Last Name, Specialty, and State). In these columns you would have formulas to pull over the search results.

Next you would insert a checkbox column with a formula to check boxes when your row data has at least one match to the source data.

=IF(OR(AND([1st Name Helper]@row <> "", CONTAINS([1st Name Helper]@row, [Speaker First Name]@row), AND(...........), AND(.............), AND(.............)), 1)

You would continue those AND combinations until you have covered each of the different search parameters. Then you can simplify your INDEX/COLLECT to only pull those rows where the boxes are checked.

Paul Newcome

My suggestion would involve some helper column in the source sheet. First you would insert helper columns for each piece of search criteria (1st Name, Last Name, Specialty, and State). In these columns you would have formulas to pull over the search results.

Next you would insert a checkbox column with a formula to check boxes when your row data has at least one match to the source data.

=IF(OR(AND([1st Name Helper]@row <> "", CONTAINS([1st Name Helper]@row, [Speaker First Name]@row), AND(...........), AND(.............), AND(.............)), 1)

You would continue those AND combinations until you have covered each of the different search parameters. Then you can simplify your INDEX/COLLECT to only pull those rows where the boxes are checked.

l.gann

@Paul Newcome - thanks so much for this reply!

I was able to use this line of thought (slightly modified) to achieve my goal, however, it takes too long to populate. Would you be able to suggest any thoughts on optimizing performance? Ideally, the results would populate automatically/within seconds versus having to refresh/wait minutes for the cell links to update across sheets.

Current setup:

Source Sheet(s): 32,684 rows and 15 columns of data between two source sheets

• Match Column - Indicates if row matches search criteria.

=IF(AND([Helper FN]@row = "", [Helper LN]@row = "", [Helper Specialty]@row = "", [Helper State]@row = ""), "", IF(CONTAINS("False", [Helper FN]@row:[Helper State]@row), "", "Match"))

• Helper Columns - Return "True" if row contains a match, "False" if no match, and "" if no search criteria entered on target sheet. Cross sheet references changed for each search criteria.

=IF({FNSearch} = "", "", IF(CONTAINS({FNSearch}, FIRSTNAME@row), "True", "False"))

Target Sheet:

• Results Column - pulls concatenated field from source sheet if row contains "Match" in the match column

=IFERROR(INDEX(COLLECT({AttendeesConcat}, {Match}, {Match} = "Match"), [Primary Column]@row), "")

Paul Newcome

The performance challenge is coming from the shear amount of data. Unfortunately there isn't really any way to make this process any more efficient.

l.gann

I appreciate the follow up 😊

Paul Newcome

Happy to help. 👍️

Jamika J.

Hello I am having a similar issue. I am trying to pull in data from 2 different criteria without it collecting any blank cells, only cells with values.

=COUNTIF(IFERROR(INDEX(COLLECT({RBT/ABAT Quarterly Review Form Range 28}, {RBT/ABAT Quarterly Review Form Range 2}, [RBT/ABAT]@row, {RBT/ABAT Quarterly Review Form Range 3}, [Review_Quarter]@row, <>""), 1), ""))

I am not sure where I am going wrong. It worked fine when pulling in the first rows of data. It does not work when pulling over from additional rows.

Would anyone have any suggestions?

Paul Newcome

@Jamika J. the INDEX function will only ever pull one value. To pull multiple values, you would need a JOIN function.

Having said that... What is the purpose of the COUNTIF funtion?

Jamika J.

Hello @Paul Newcome, I am attempting to pull over the data in a cell from a column based on it matching the staffs name and review quarter. For instance, I have 4 quarters of staff entries and I need to only pull in the most recent entry for quarter 1, which in the image below would be the 12 points. Since there is data for all 4 quarters entered, I tried to place my formula to the following:

=IFERROR(INDEX(COLLECT({Points on 2nd Sheet}, {Staff Name on Second Sheet}, [Staff Name on Primary Sheet]@row, {Review Quarter on 2nd Sheet}, [Review Quarter on Primary Sheet]@row), 1), "")

Can you please let me know if I am missing a step? I would really appreciate any advice that can possibly help!