Link to home
Create AccountLog in
Avatar of Lia Nungaray
Lia NungarayFlag for United States of America

asked on

How to exclude a large amount of rows in Excel

I have a very large worksheet, over 5000 rows, and I want to exclude around 4000. I of course, have this list in another worksheet, and this other worksheet only contains one column. Is there any way of doing this in Excel? Thanks!
Avatar of Patrick Matthews
Patrick Matthews
Flag of United States of America image

horalia,

It is not at all clear what you are trying to do.

Patrick
Avatar of Lia Nungaray

ASKER

Sorry, didn't read what I submitted... Anyhow, let's hope this is a better explanation :-)

I have Sheet1, which contains the following columns:
UserID, Contract Name, Date.
There are around 5000 rows in Sheet1.

I then have Sheet2, which contains only one column, UserID. This table contains around 4000 rows.
What I would like to do, is exclude the rows in Sheet1 where the UserID match the UserID from Sheet2. Kind of a SELECT statement with a NOT IN. Was wondering if this can be done directly in Excel: SELECT * FROM Sheet1 WHERE UserID NOT IN (SELECT UserID from Sheet2)

Thanks!
Could you create this by adding a hidden column to sheet 1?

My rational for this, is then you could maintain a single sheet, and in that column, grant a value of "X" or "1", or something to items you wished to exclude.  Then you can change Sheet2 to have a column of =if(Sheet1!D4="X",Sheet1!D4,"").  This would allow you to dynamically generate a list of exclusions.

I know what I stated requires you to essentially redo, the entire workbook... however, the other option would be to skip down to say Row 15000 (or whatever seems to be a number below where you would want to go, or out to a column beyond where you think you will use), and set up a mirror of the data that you enter into Sheet2 for your exclusions list via an =Sheet2!A2 type reference to pull the column of values that you are looking to exclude.  For explanation purposes, we will say that this mirror table is beginning on Sheet1 in cell AA10, we will also say that your core exclusions list is on Sheet2 starting in cell A2.  

the formula to enter in Sheet1 cell AA10 would be =Sheet2!A2.  Once this formula has been entered, you would fill down as far as you want/need.

To be able to use this value reliably, however, you will need one more thing.  In a column, with the same starting Row as the data you wish to conditionally sum, based on the values you have entered on Sheet2.  (We will say that this is Sheet1, Column R, Row 5)  You will enter the following formula =VLOOKUP(C5,$AA$10:$AB$,5505,2,FALSE).

Then in the column adjacent to where you just ran your VLOOKUP, enter the following formula =iferror(R5,"0")

Now you have a grouping of information you can use to run SUMIF calculations against

One example of this would be to perform a sumif, based on values in R5:S5005 being 0, or only the items that are NOT included on your Sheet2 exclusion list.  To do so, go to your desired cell, in this case let us say it is Sheet1 Cell C5000.  (Assuming that the value you wish to sum is in Column C starting with row 5 - sorry rather arbitrary...)  =sumif(C5:C4995,0,S10:S5005).  Please note if you wanted to perform the reverse and sum everything <>0 you would need to enter the formula as =sumif(C5:C4995,"<>0",R5:S4995).  Note the "s" are necessary if you are NOT pulling a static value, and want to use a range or <>.

I hope this helps a little bit, I know its a little, cumbersome, however, it should allow you to accomplish what you are trying to do.

Sorry, just realized, we can possibly simply this further, if you wanted to attach an empty template of your file, that contains only placeholder data, I should be able to populate it w/ an example of what I tried to explain above.
SOLUTION
Avatar of dlmille
dlmille
Flag of United States of America image

Link to home
membership
Create an account to see this answer
Signing up is free. No credit card required.
Create Account
ASKER CERTIFIED SOLUTION
Link to home
membership
Create an account to see this answer
Signing up is free. No credit card required.
Create Account
Thank you teylyn.

I feel the solution given by will work. Sample file is also attached.

Sid
Mine will too.  I think the OP may have had other priorities.
This question has been classified as abandoned and is being closed as part of the Cleanup Program. See my comment at the end of the question for more details.