Question description:
This user has given permission to use the problem statement for this
blog.
I have got multiple columns with info. I would like to know if there's a way that I can make questions that can reduce the columns into the criteria I am looking for. For example, Each column have different vehicles with all their specs in. Now I want to ask questions like price, year etc and eliminate the vehicles that does not fit the criteria? Thanks!
Solved by K. L. in 46 mins
This is the chat thread from the real Excelchat help session. It contains no private user
information.
Excelchat Expert
23/04/2018 - 09:13
Hi welcome!
User
23/04/2018 - 09:13
Hi
Excelchat Expert
23/04/2018 - 09:14
You have a question on search criteria from different columns- is that correct?
User
23/04/2018 - 09:14
Yes. I'd like to know if there's a way to filter through columns by way of elimination
User
23/04/2018 - 09:15
For example>>
Excelchat Expert
23/04/2018 - 09:15
Yes, depending on how things are set up.
User
23/04/2018 - 09:16
So now I have got 2 questions>
User
23/04/2018 - 09:17
So the in this example the bmw and the Toyota should be removed and the Mercedes only one left?
User
23/04/2018 - 09:17
Is this possible?
Excelchat Expert
23/04/2018 - 09:17
Let me review your example.
User
23/04/2018 - 09:17
The options would of course be a drop down menu
Excelchat Expert
23/04/2018 - 09:18
Ok, the criteria requires both conditions = white and 2001-correct?
User
23/04/2018 - 09:18
Yes
Excelchat Expert
23/04/2018 - 09:19
And the drop down selection results are shown in B7 and B8-correct?
User
23/04/2018 - 09:19
Yes
Excelchat Expert
23/04/2018 - 09:21
Are we allowed to add columns? What I think is the easy way to go and maintain- is a column that concats B, C, D, in this exmple and the same with critera,
Excelchat Expert
23/04/2018 - 09:23
But nor sure what you mean by only the Mercedes is left. Not really a way to delete data, Do you mean just show the selection in another part of the sheet ?
User
23/04/2018 - 09:24
I think ideally I would want to ask the questions on a different sheet and then the columns that fits in the criteria either gets highlighted or maybe shown on a separate sheet?
Excelchat Expert
23/04/2018 - 09:25
ok, I can start with some ideas and we can go from there, time permitting.
User
23/04/2018 - 09:26
Please
Excelchat Expert
23/04/2018 - 09:26
I need to take this example to desktop , viewer is not really for construction. Please stand by.
User
23/04/2018 - 09:26
Ok
Excelchat Expert
23/04/2018 - 09:40
Ok, sorry it took a while
User
23/04/2018 - 09:40
No worries
Excelchat Expert
23/04/2018 - 09:40
[Uploaded an Excel file]
Excelchat Expert
23/04/2018 - 09:40
Here is a simple way to get started.
Excelchat Expert
23/04/2018 - 09:41
Create a key value by concat cells and use a lookup.
Excelchat Expert
23/04/2018 - 09:43
there are some other more complex ways to do this and maybe a macro or two, but in a basic chat session, I can only answer specific formula questions. Hard to give a complete solution without your actual sheet and some needs assessment.
Excelchat Expert
23/04/2018 - 09:44
I hope you understand.
User
23/04/2018 - 09:45
This is a great help. I have two more questions. The result cell, what if more there's, like for instance in this example, more than one car that match the criteria. And secondly, is there a way to make the answer in a downward row instead of horizontal?
Excelchat Expert
23/04/2018 - 09:47
Let me review
Excelchat Expert
23/04/2018 - 09:50
Yes, both are possible, but will take some time, since you now have multiple functions and needs and a bit more complex.
User
23/04/2018 - 09:51
Any recommendations where I can find these formulas?
Excelchat Expert
23/04/2018 - 09:51
I think what would be needed is your other idea of color coding
Excelchat Expert
23/04/2018 - 09:51
Use the concat functions "&" sign to create a key
Excelchat Expert
23/04/2018 - 09:52
on each row of your data
Excelchat Expert
23/04/2018 - 09:52
Then create a cell to hold the criteria to match the key, similar to what we have in this simple example
Excelchat Expert
23/04/2018 - 09:54
Then use CONDITIONAL FORMATTING- with a user defined formula. where key on each row matches the criteria. Choose your color.
Excelchat Expert
23/04/2018 - 09:54
This will identify all the possible matches in a column/row by color code.
Excelchat Expert
23/04/2018 - 09:56
Or you can add a column = FLAG. IF key = criteria ,then Y , else N. and sort desending, Y will be on top,
User
23/04/2018 - 09:57
Makes sense. I'll definitely give it a try. You have been a great help. Thank you so much!
Excelchat Expert
23/04/2018 - 09:57
Ok, thanks, agian hard to do a full solution here, but hopefully this gives you some ideas.
User
23/04/2018 - 09:58
Definitely. Thanks!
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.
