Question description:
This user has given permission to use the problem statement for this
blog.
I have three columns on a spreadsheet. The first two columns I need sorted so that it includes everything 80% and greater. The third column I also need 80% or greater but this column is a result of a VLOOKUP formula.
Solved by M. H. in 60 mins
This is the chat thread from the real Excelchat help session. It contains no private user
information.
Excelchat Expert
16/05/2018 - 11:21
Welcome, thank you for choosing Got It Pro-Excel! I'll be glad to assist you with your concern.
Excelchat Expert
16/05/2018 - 11:22
I understand that you need help in sorting your data. May you please share the document you have so I can help you with your concern?
User
16/05/2018 - 11:22
Well, the document is huge with multiple tabs. Not something easily shared
User
16/05/2018 - 11:23
I have two columns and need to include everything that is greater than 80%
User
16/05/2018 - 11:23
in both coulmns
User
16/05/2018 - 11:23
columns
Excelchat Expert
16/05/2018 - 11:23
Alright, can you share a screenshot so I would have an idea on how your data looks like?
User
16/05/2018 - 11:23
Yes
Excelchat Expert
16/05/2018 - 11:23
Or you can share just the tabs that you need help with..
Excelchat Expert
16/05/2018 - 11:24
You can paste the values here if the file has formulas
User
16/05/2018 - 11:25
=IF(ISNA(VLOOKUP([@[Hub/Path/Circuit]],'Thompson One List - May'!A:F,6,FALSE)),"--",(VLOOKUP([@[Hub/Path/Circuit]],'Thompson One List - May'!A:F,6,FALSE)))
User
16/05/2018 - 11:25
That formula results in the last column giving me another percent
User
16/05/2018 - 11:25
percentage
User
16/05/2018 - 11:26
I need something that will give me a report listing all three columns that have values greater than 80%
Excelchat Expert
16/05/2018 - 11:26
Ok just to verify, you need to filter out everything that is greater than 80% for all 3 columns correct?
User
16/05/2018 - 11:27
I need everything that is greater than 80%
User
16/05/2018 - 11:27
80 or greater
Excelchat Expert
16/05/2018 - 11:27
in any of the 3 columns?
User
16/05/2018 - 11:27
yes
User
16/05/2018 - 11:28
if one column has 80 and another has 82, that row needs to be displayed
User
16/05/2018 - 11:28
if all three have it, then that row needs to be displayed
User
16/05/2018 - 11:29
if only one has it, then that row needs to be displayed
Excelchat Expert
16/05/2018 - 11:29
ok got it
User
16/05/2018 - 11:30
That's right
Excelchat Expert
16/05/2018 - 11:30
ok here's the formula that you can use: =IF(OR(B5>80%,C5>80%,D5>80%),"YES","")
User
16/05/2018 - 11:30
like that
Excelchat Expert
16/05/2018 - 11:30
just change the cell reference to the correct cell reference you have on your data
User
16/05/2018 - 11:30
where do I put that? Do I highlight all the rows and columns
Excelchat Expert
16/05/2018 - 11:31
on the last available column you have, first row with data then drag it to the last row
User
16/05/2018 - 11:31
ok
Excelchat Expert
16/05/2018 - 11:32
Then afterwards, you can filter all of your data, and show only those with YES in the column you have added
User
16/05/2018 - 11:32
Can I change it to =>
User
16/05/2018 - 11:32
or just put 79
Excelchat Expert
16/05/2018 - 11:33
so you also need to include 80%?
Excelchat Expert
16/05/2018 - 11:33
Do you mean greater than or equal to 80%?
Excelchat Expert
16/05/2018 - 11:34
If greater than or equal to 80% then yes you can use >79%
Excelchat Expert
16/05/2018 - 11:34
=IF(OR(B5>79%,C5>79%,D5>79%),"YES","")
Excelchat Expert
16/05/2018 - 11:34
Do you have any questions regarding the solution provided?
User
16/05/2018 - 11:34
But that last column has that big formula
Excelchat Expert
16/05/2018 - 11:35
how many columns you have right now? 3?
User
16/05/2018 - 11:35
yes
Excelchat Expert
16/05/2018 - 11:35
Then add a new column to add the formula and name it FILTER
User
16/05/2018 - 11:35
ok
Excelchat Expert
16/05/2018 - 11:36
Same with what's showing on the document preview
User
16/05/2018 - 11:36
ok4
Excelchat Expert
16/05/2018 - 11:36
Did it work?
User
16/05/2018 - 11:36
Checking
Excelchat Expert
16/05/2018 - 11:38
Ok, let me know if you are satisfied with the solution provided.
Excelchat Expert
16/05/2018 - 11:43
Hi, did it work on your end?
User
16/05/2018 - 11:44
Perfect!
Excelchat Expert
16/05/2018 - 11:44
Awesome!
Excelchat Expert
16/05/2018 - 11:45
Is there anything else that I can assist you with regarding this issue?
User
16/05/2018 - 11:45
Hold on one sec
User
16/05/2018 - 11:47
I am good!
User
16/05/2018 - 11:47
Thanks!
Excelchat Expert
16/05/2018 - 11:47
Perfect! Thanks for using Got It Pro-Excel. Please give your kind feedback for our service. Have a good day! :)
Excelchat Expert
16/05/2018 - 11:50
Feel free to end this session as soon as you are ready for us to continue helping other customers. Thank you.
User
16/05/2018 - 11:52
Help!
User
16/05/2018 - 11:52
I need to exclude dashes!
User
16/05/2018 - 11:52
in the last column
Excelchat Expert
16/05/2018 - 11:53
what is the result showing in column 4?
Excelchat Expert
16/05/2018 - 11:53
for those with dashes? shouldn't it be BLANK not YES?
User
16/05/2018 - 11:54
It is showing YES when there is a dash and the other columns are below 80%
Excelchat Expert
16/05/2018 - 11:54
ok let me check
User
16/05/2018 - 11:55
=IF(OR(H276>79,I276>79%,J276>79%),"YES","")
User
16/05/2018 - 11:56
That is the exact filter and it is working except it is showing YES for last column that has --
Excelchat Expert
16/05/2018 - 11:56
can you show an example of the exact value? dash like - or two --?
User
16/05/2018 - 11:56
two
User
16/05/2018 - 11:56
--
Excelchat Expert
16/05/2018 - 11:58
ok let me double check the formula for you
User
16/05/2018 - 11:59
ok
Excelchat Expert
17/05/2018 - 12:07
can you show the formula for cells with dashes?
User
17/05/2018 - 12:08
=IF(ISNA(VLOOKUP([@[Hub/Path/Circuit]],'Thompson One List - May'!A:F,6,FALSE)),"--",(VLOOKUP([@[Hub/Path/Circuit]],'Thompson One List - May'!A:F,6,FALSE)))
Excelchat Expert
17/05/2018 - 12:08
Can you remove the dashes inside "--"?
Excelchat Expert
17/05/2018 - 12:09
=IF(ISNA(VLOOKUP([@[Hub/Path/Circuit]],'Thompson One List - May'!A:F,6,FALSE)),"",(VLOOKUP([@[Hub/Path/Circuit]],'Thompson One List - May'!A:F,6,FALSE)))
Excelchat Expert
17/05/2018 - 12:10
just put blank instead of double dashes since excel is reading double dashes as numbers
Excelchat Expert
17/05/2018 - 12:12
Hello did it work>
User
17/05/2018 - 12:12
It is still calculating
Excelchat Expert
17/05/2018 - 12:13
ok, let me know if you're good with the resolution
User
17/05/2018 - 12:17
It is giving me that inconsistent formula error
Excelchat Expert
17/05/2018 - 12:18
did you drag down the formula where you change "--" to ""?
User
17/05/2018 - 12:18
Yes
User
17/05/2018 - 12:18
Even the first cell is giving me that error
Excelchat Expert
17/05/2018 - 12:18
whats the formula you have in column a first row?
User
17/05/2018 - 12:18
It is calculating correctly
User
17/05/2018 - 12:19
The old percentages are still the same
Excelchat Expert
17/05/2018 - 12:19
I need to see the formula to check
User
17/05/2018 - 12:19
=IF(ISNA(VLOOKUP([@[Hub/Path/Circuit]],'Thompson One List - May'!A:F,6,FALSE)),"",(VLOOKUP([@[Hub/Path/Circuit]],'Thompson One List - May'!A:F,6,FALSE)))
Excelchat Expert
17/05/2018 - 12:19
ok, how about the 2nd column?
Excelchat Expert
17/05/2018 - 12:19
first row
User
17/05/2018 - 12:20
Everything else is fine
User
17/05/2018 - 12:20
all the data is fine
User
17/05/2018 - 12:20
I am just getting that little green pointer
Excelchat Expert
17/05/2018 - 12:20
except for what?
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.
