Expert Solution - Excel COLUMN Problems

Question description:
This user has given permission to use the problem statement for this blog.

I filtered data in column A (it is labeled ID #) of a data set. On my next worksheet, column A is also the ID #, but it is a different data set. I want to filter the ID #'s the same for the two sheets, but how do I do that being that I am working with two different sets of data?

Solved by V. H. in 22 mins

This is the chat thread from the real Excelchat help session. It contains no private user information.

Excelchat Expert 15/02/2018 - 07:12

Hello

Excelchat Expert 15/02/2018 - 07:12

Welcome to Excel chat.

Excelchat Expert 15/02/2018 - 07:16

Ok. I can see the data in two sheets.

User 15/02/2018 - 07:17

Ok, so I just filtered the data within column one. Do you follow?

User 15/02/2018 - 07:17

column one of sheet one

Excelchat Expert 15/02/2018 - 07:17

Ok. I can see that. You have filtered them on some criteria.

User 15/02/2018 - 07:18

Yes, the criteria is all year numbers that are equal to two.

Excelchat Expert 15/02/2018 - 07:18

i.e. where Year# = 2

Excelchat Expert 15/02/2018 - 07:18

Ok.

User 15/02/2018 - 07:18

So my filtered results are 1, 3, 6, 8,13

Excelchat Expert 15/02/2018 - 07:18

Yes.

User 15/02/2018 - 07:19

How do I apply those results and filter column A within sheet two so it only shows those 5 numbers?

Excelchat Expert 15/02/2018 - 07:20

Would you like to have a formula in column C of sheet 2?

Excelchat Expert 15/02/2018 - 07:20

That can solve your problem.

Excelchat Expert 15/02/2018 - 07:20

Then you can apply the filter on column C.

User 15/02/2018 - 07:20

What would the formula be?

Excelchat Expert 15/02/2018 - 07:20

Let me put that formula for you in column C of sheet 2.

User 15/02/2018 - 07:22

Excelchat Expert 15/02/2018 - 07:23

Now you can filter your column C.

Excelchat Expert 15/02/2018 - 07:23

If you are satisfied with the solution, I can explain the formula to you and then you can modify it for your use.

User 15/02/2018 - 07:24

How do I filter that?

Excelchat Expert 15/02/2018 - 07:25

Please see the sheet 2 now

Excelchat Expert 15/02/2018 - 07:25

You have to apply text filter.

User 15/02/2018 - 07:26

Text filter --> contains --> true? is that what you do

Excelchat Expert 15/02/2018 - 07:26

Also, you don't have to use "True" or "False", these can be any other text or number of your choice. And then you can filter on that.

User 15/02/2018 - 07:26

Can you explain the formula to me please?

Excelchat Expert 15/02/2018 - 07:26

I used Text Filters > Equals > True

Excelchat Expert 15/02/2018 - 07:26

Sure.

Excelchat Expert 15/02/2018 - 07:27

So I have used a VLOOKUP formula to find the matching value in sheet 1

Excelchat Expert 15/02/2018 - 07:28

VLOOKUP has 3 main parameters, 1st is the value we want to search. In this case it was value in column A.

Excelchat Expert 15/02/2018 - 07:28

2nd is where we want to search, the table where data is, in this case it was Sheet1!A:B

Excelchat Expert 15/02/2018 - 07:28

It's important that 1st column of data contains the value that we want to search.

Excelchat Expert 15/02/2018 - 07:29

in this case ID.

Excelchat Expert 15/02/2018 - 07:29

Now, I have used a value 2 as 3rd parameter,

Excelchat Expert 15/02/2018 - 07:29

because I want to check if my value is matched then it must return corresponding value in column B, which has a index of 2 in my data set.

Excelchat Expert 15/02/2018 - 07:30

Then I used, FALSE, which is optional parameter, and it means that I want exact search.

Excelchat Expert 15/02/2018 - 07:31

Now if I applied VLOOKUP formula to all the cells in column C, all will be matched with corresponding values from B.

Excelchat Expert 15/02/2018 - 07:32

Hence I used an IF function wrapping the VLOOKUP result.

Excelchat Expert 15/02/2018 - 07:33

Now if any VLOOKUP formula gives result of 2, it will put "TRUE", else it will put "FALSE" in the column C.

User 15/02/2018 - 07:33