All solutions COLUMNS Expert Solution – Excel COLUMN Problems

Excel - COLUMN Function Problem - Expert Solution

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
Ok
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
ok
Excelchat Expert 15/02/2018 - 07:34
To make it simpler, you can just use VLOOKUP, and it will give the values of B from your sheet1, and then you can filter on column C.
User 15/02/2018 - 07:34
ok thank you for the help
Excelchat Expert 15/02/2018 - 07:34
And you won't need IF function.
Excelchat Expert 15/02/2018 - 07:34
I am glad that I could help you.
Excelchat Expert 15/02/2018 - 07:34
Hope to see you again soon. Please end this session.
Excelchat Expert 15/02/2018 - 07:34
Bye!

This is the output file from the real Excelchat help session:
This is an example of the expert help you can get. It contains no private user information.

Get instant expert help with Excel and Google Sheets

Post your problem and you’ll get expert help in seconds.

Your message must be at least 40 characters
Your privacy is guaranteed. Your session will not be used for blog unless you give us persmission.

Click here to get your free Excelchat help session

Subscribe to Excelchat.co
Trusted by people who work at
Amazon.com, Inc
Facebook, Inc
Accenture PLC
Siemens AG
Macy's
The Allstate Corporation
United Parcel Service
Dell Inc