Excel Function to Exclude rows based on certain values

0 votes

I excluded rows in an excel table based on certain values

For example:

enter image description here

I need to exclude all rows if column A is equal to any of these numbers ( 5840,4302,4432, and so on)

The table data will be huge to filter only the data I want.

Jan 23, 2023 in Others by Kithuzzz
• 38,010 points
6,715 views

1 answer to this question.

0 votes

One method is to combine the FILTER() spreadsheet function with the Excel Table feature. NB. To do this, you'll need a recent version of Excel. Utilizing a Table adds some additional practical usefulness (such as automatically adding rows and allowing reference by column name).

enter image description here

The OP's input data may already be a Table, if so, this first step can be skipped.

  1. Put the input and filter list into tables. Excel help page. After the table has been created I have used the Table Design menu (which appears in the menu bar when a cell in the table is selected) to turn off the row banding format and header filters. This is also where you can rename the Tables. I have named them "Input" and "Exclude"

  2. For the filtered output, choose where you want the output to start (cell H3 in my example), and enter a formula to copy the headers: =Input[#Headers]. Of course you can copy and paste the headers manually if you like. Here I've used the Format Painter to copy across the cell formats for the headers.

  3. In the next cell down (H4 in my example), enter this formula: =FILTER(Input,(LEN(Input[ID])>0) * ISERROR(MATCH(Input[ID],Exclude[IDs to exclude],0))).

You should be able to add or delete new rows (right-click in the Table and choose Delete) in both the Input and Exclude tables, and the output should react (if you have Calculation set to Automatic).

answered Jan 23, 2023 by narikkadan
• 63,700 points

Related Questions In Others

0 votes
0 answers

Merge rows based on value (pandas to excel - xlsxwriter)

I'm attempting to use xlsxwriter to output ...READ MORE

Nov 2, 2022 in Others by Kithuzzz
• 38,010 points
2,256 views
0 votes
1 answer

Excel formula to average selected cells based on certain criteria

Try: =AVERAGE(IF(X:X=A1,Z:Z)) With Ctrl+Shift+Enter. READ MORE

answered Nov 13, 2022 in Others by narikkadan
• 63,700 points
355 views
0 votes
1 answer

Excel VBA search based on cell values into folders and sub-folders to get the file path and data

This will create a listing of all ...READ MORE

answered Jan 19, 2023 in Others by narikkadan
• 63,700 points
1,751 views
0 votes
1 answer
0 votes
0 answers

Excel: How to Sort or filter text by specific word or words?

Even if the term is used in ...READ MORE

Nov 27, 2022 in Others by Kithuzzz
• 38,010 points
498 views
0 votes
1 answer

Excel formula to calculate MIN in table filtered

Try this: =SUBTOTAL(105;B2:B7) READ MORE

answered Jan 30, 2023 in Others by narikkadan
• 63,700 points
362 views
0 votes
1 answer

Filter outlook sent items in vba failing for emails with multiple recipients

The PR DISPLAY TO parameter provides a ...READ MORE

answered Feb 21, 2023 in Others by narikkadan
• 63,700 points
524 views
0 votes
1 answer

Copy data with filter applied using Excel VBA

Try this: Private Sub CommandButton1_Click() Dim ...READ MORE

answered Mar 19, 2023 in Others by Kithuzzz
• 38,010 points
326 views
0 votes
1 answer

Excel function to dynamically SUM UP data based on matching rows and columns

Excel 365 for MAC should have the BYCOL function, Given: Your ...READ MORE

answered Jan 21, 2023 in Others by narikkadan
• 63,700 points
675 views
0 votes
1 answer

Repeated excel rows based on a cell with multiple values

You can use this query: let ...READ MORE

answered Oct 20, 2022 in Others by narikkadan
• 63,700 points
1,178 views
webinar REGISTER FOR FREE WEBINAR X
REGISTER NOW
webinar_success Thank you for registering Join Edureka Meetup community for 100+ Free Webinars each month JOIN MEETUP GROUP