## Perform thousands of conditions to a data set

This article explains how to filter a data set based on extremely many conditions in an Excel defined Table, in fact, this example has 1500 conditions. So many that it is tedious and time-consuming to enter or press with left mouse button on checkboxes in a Filter / Excel Table.

I have all conditions entered in a worksheet and I am going to utilize a formula to apply all conditions at once. This article was created to answer the following question:

**Mohsin Ali Raziq asks:**

I have problem, and o don't know how to solve it, I have data of almost 10000 forms, from which I have to find 1500, so it is very difficult to dig out 1500 one by one through Ctrl+F, is there any way to to put all the forms number at once and then find all those just by press with left mouse button oning. the primary key is Form Number.

The "Data" worksheet contains the form numbers in column A, the remaining columns contain dummy data.

The next sheet "Search values" contains the 1500 form numbers you want to use as conditions.

### Convert data set to an Excel defined Table

I will convert the data, in this example, to an Excel defined Table. You can also use an Excel Filter if you prefer that.

- Select any cell in your data set.
- Press Ctrl + T which is a shortcut for converting data to an Excel defined Table.

- Press OK button.

Excel now applies formatting to your data set indicating it is now an Excel defined Table.

### Search form numbers

It is now time to enter the formula, it will be automatically copied to cells below by the Excel defined Table. This is great saving us a little bit of time.

Formula in cell G2:

Make sure you use absolute cell references. The COUNTIF function counts the number of values in cell range 'Search values'!$A$2:$A$1501 that is equal to the value in column #. Here is the function syntax:

COUNTIF(*range*,Â *criteria*)

It returns 1 if the value in column # is found in the cell range containing conditions and 0 (zero) if not found. If there is more than one instance of a value in 'Search values'!$A$2:$A$1501 the formula may return a value greater than 1.

### Filter table

The Excel defined Table lets you select which values you want to filter, there is now only 1 or 0 (zero) to choose from which is much easier to base a filter on than 1500 different conditions.

- Press with left mouse button on the black arrow at the top in column G which is the same column you entered the formula in.
- Make sure only 1 is selected since this indicates that the value on the same row in column # is equal to one of the 1500 conditions specified in cell range 'Search values'!$A$2:$A$1501.

- Press OK button to apply the filter.

Below is a picture of the filtered Excel defined Table containing the 1500 form numbers,

### Recommended articles

Recommended articles

VLOOKUP a multi-column range and return multiple values.

Recommended articles

Question: I have a list and I want to filter out all rows that have a value (Column C) that […]

Recommended articles

This article demonstrates how to extract records/rows based on two conditions applied to two different columns, you can easily extend […]

Recommended articles

This article describes how to filter duplicate rows with the use of a formula.Â It is, in fact, an array […]

Recommended articles

Table of contents Filter unique distinct row records Filter unique distinct row records but not blanks Filter unique distinct row […]

### Table category

This article demonstrates different ways to reference an Excel defined Table in a drop-down list and Conditional Formatting. There are […]

This article demonstrates two formulas that extract distinct values from a filtered Excel Table, one formula for Excel 365 subscribers […]

This article demonstrates a formula that counts unique distinct values filtered from an Excel defined Table. Debra Dalgleish described in […]

This article demonstrates how to populate a drop down list with filtered values from an Excel defined Table. The animated […]

This article demonstrates how to filter records occurring in only one out of two Excel defined tables. It also shows […]

The image above demonstrates a conditional formatting formula applied to an Excel Table containing random data. The Excel Table has […]

This article describes a macro that hides specific columns automatically based on values in two given cells. I am also […]

The image above demonstrates a macro linked to a button. Press with left mouse button on the button and the […]

This article demonstrates how to filter duplicate records using a simple formula and an Excel defined table.

I will in this article demonstrate a macro that copies criteria from one Excel Table and applies them to another […]

In this tutorial, I am going to demonstrate how to filter an ExcelÂ define Table through a VBA macro. How it […]

This blog post demonstrates how to filter unique distinct values from an Excel table dynamically. When you change or add […]

Today I am going to demonstrate how amazing pivot tables are! Take a look at this time sheet. You can […]

I will in this article demonstrate three different techniques to sort a data set in Excel. The first method sorts […]

This article demonstrates how to insert and use a scroll bar (Form Control) in Excel. It allows the user to […]

An Excel table allows you to easily sort, filter and sum values in a data set where values are related.

This article demonstrates a macro that automatically applies a filter to an Excel defined Table based on the result from […]

The filter feature in Excel won't allow you to do OR logic between columns, however, you can if you allow […]

This article explains how to calculate the largest and smallest number based on a condition which is if the number […]

## Functions in this article

More than 1300 Excel formulas

## How to use Excel Tables

## Excel categories

### 2 Responses to “Perform thousands of conditions to a data set”

### Leave a Reply

### How to comment

**How to add a formula to your comment**

<code>Insert your formula here.</code>

**Convert less than and larger than signs**

Use html character entities instead of less than and larger than signs.

< becomes < and > becomes >

**How to add VBA code to your comment**

[vb 1="vbnet" language=","]

Put your VBA code here.

[/vb]

**How to add a picture to your comment:**

Upload picture to postimage.org or imgur

Paste image link to your comment.

**Contact Oscar**

You can contact me through this contact form

what isthe [@['#]] that you type inside the countif formula ?

Douglas Rezende

It is a structured reference used in Excel defined Tables.

The @ character references cells on the same row as the formula.

The '# is the name of the column, not the greatest column name I admit, considering that # characters are used in structured references as well.

https://www.ablebits.com/office-addins-blog/2019/02/06/structured-references-excel-tables/