CODEWITHSUNDEEP
excelfilterexcel-formulamultiple-columns
Thomas A Mathew
Thomas A Mathew

Reputation: 159

Formula for filter column in Excel

Please suggest a formula for the following requirement:

My requirement: In Sheet 1, I want to filter out the 100+ users in column A and these 100+ users are present in Sheet 2 (I could paste the 100+ users from Sheet 2 to say Column E of Sheet 1 such that only Sheet 1 need to be considered)

So I want to filter 100+ in comparison to column E 100+ names (after copying as above)

Then I can fill in Column C & D after filter.

Note : I don't want to delete rest of the names in Column A, just want to apply filter according to Column E such that only the 100+ names appear in column A so I can fill the corresponding data in Column C & D

enter image description here

Upvotes: 0

Views: 225

Answers (1)

Gabriel Pierce
Gabriel Pierce

Reputation: 406

You can whitelist / blacklist values via the COUNTIF function in a filter.

=FILTER( range to return, COUNTIF( needle, haystack "range to return"))

So if you wanted to create a third sheet and paste those columns as you shown above you could paste this formula in cell A2...

=FILTER( 'Sheet 1'!A2:D2801, COUNTIF('Sheet 2'!A2:101, 'Sheet 1'!A2:A2801))

And it will auto-populate columns A-D with your desired data. This formula is assuming your 100 desired names are on column A starting on A2. I'm sure you can tweak this a little to fit your needs

Upvotes: 1

Related Questions