Excel: prune by list

September 7, 2017 7:32 AM

I have a list of values I'd like to use to prune another sheet. If those values are found in the user_id column, then I'd like to delete the entire row where they occur. Filtering would work too. I feel like there should be a relatively easy way to do this, but Googling turns up solutions that don't work for me.

There will be multiple instances of some user_id. So 504 is on the prune list, they have 16 lines in the data-set assigned to their user_id, and I'd like to get rid of each of those instances.

I'm fine using another program if it can easily do this, but working natively to Excel would be preferable.

posted by codacorolla to Computers & Internet (6 answers total)

Can you add a column? If so do MATCH($value, $user_id, 0), then filter on the #N/A in the added match column to only show those that have no corresponding other in the user_id column, copy, paste to a new sheet?
posted by fatfrank at 7:43 AM on September 7, 2017 [1 favorite]

Came here to say the same thing as fatfrank though I would probably have said use vlookup and delete anything that was not an error result. Generically just add a column to your data-set that contains any lookup formula into the prune list, and then remove the records that do have a result.
posted by buildmyworld at 7:54 AM on September 7, 2017 [1 favorite]

COUNTIF will work as well. You count how many times the user id from the data set shows up in the prune list. If you have no duplicates in the prune list, every record will have a 1 or 0. You delete the records with 1.
posted by soelo at 8:56 AM on September 7, 2017

Use an Advanced Filter.

1. Put your list of IDs to be pruned in a column on a new sheet and give it a column header user_id
2. Select the new column and give that range a name, like FilterIDs or something
3. On the main sheet, select Advanced Filter from the ribbon's Data tab
4. Select the Filter in place radio button, make sure the List range is set to all of your data, uncheck Unique records only, and type the name you used in step 2 in the Criteria range field
5. Click OK
6. Your data will now be filtered to show only rows that have an ID in your prune list. Select those rows and delete.
7. Click the Clear button on the ribbon's Data tab to remove your filter
8. You can also now delete the new sheet created in step 1

You should also be able to Google some good info on Advanced Filters. Contextures and Chandoo are useful resources.
posted by roosterboy at 9:49 AM on September 7, 2017 [1 favorite]

I'd do the VLOOKUP thing that buildmyworld suggested.
posted by kevinbelt at 12:06 PM on September 7, 2017

Excel PowerQuery is absolutely made for this type of job. Depending on your Excel version it is either built in or a free download from Microsoft. It allows you to load data from excel, text files, sql databases,etc, transform & filter it and then put it back into another excel sheet. It is a pretty magical tool.
posted by mmascolino at 5:53 AM on September 8, 2017