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 in Others by Kithuzzz
• 27,740 points
29 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 by narikkadan
• 51,240 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
• 27,740 points
551 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
• 51,240 points
70 views
0 votes
1 answer
0 votes
0 answers

Convert Rows to Columns with values in Excel using custom format

1 I having a Excel sheet with 1 ...READ MORE

Feb 17, 2022 in Others by Edureka
• 13,640 points
165 views
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
• 27,740 points
66 views
0 votes
1 answer

Excel formula to calculate MIN in table filtered

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

answered 1 day ago in Others by narikkadan
• 51,240 points
19 views
0 votes
2 answers

How to remove rows with missing values (NAs) in a data frame?

Hi, The below code returns rows without ...READ MORE

answered Aug 20, 2019 in Data Analytics by anonymous
• 33,050 points
13,880 views
0 votes
1 answer

Is it possible to apply filter just to one of the measures

Hey @Ghost, Why don't you use Level of Detail ...READ MORE

answered Apr 17, 2018 in Tableau by Atul
• 10,230 points
733 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 in Others by narikkadan
• 51,240 points
41 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
• 51,240 points
100 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