display only the unique row of data

[jeffmack]

Hello all, what I would like to do is display or highlight (whichever is easier) the entire row that contains bad data represented below as 9999 in the "Location" column. This then would alert the end user that they have entered the wrong data for that field (should be 0000).

Is this doable ?

Thank you for any help. Sample data below.

[pre]

Student ID	Dist Code	Location	Year     	Dis      Table
1040653316	104101252	0000	6/30/2013	2131	Student
1040653316	104101252	0000	6/30/2013	2131	Student Snap
1040653316	104101252	9999	6/30/2013	2131	Special Ed
1770889647	104101252	0000	6/30/2013	2127	Student
1770889647	104101252	0000	6/30/2013	2127	Student Snap
1844254658	104101252	0000	6/30/2013	2124	Student Snap
1844254658	104101252	9999	6/30/2013	2124	Special Ed
1844254658	104101252	0000	6/30/2013	2124	Student
1943453233	104101252	0000	6/30/2013	2127	Student
2015876324	104101252	0000	6/30/2013	2123	Student
2130091601	104101252	0000	6/30/2013	2127	Student
2130091601	104101252	0000	6/30/2013	2127	Student Snap
2198006405	104101252	0000	6/30/2013	2123	Student
2417151545	104101252	0000	6/30/2013	2123	Student
2515359469	104101252	0000	6/30/2013	2131	Student
2672955018	104101252	0000	6/30/2013	2127	Student
2825827851	104101252	0000	6/30/2013	2123	Student
3030451186	104101252	0000	6/30/2013	2127	Student
3030451186	104101252	0000	6/30/2013	2127	Student Snap
3030451186	104101252	9999	6/30/2013	2127	Special Ed
3083414099	104101252	0000	6/30/2013	2127	Student Snap
3083414099	104101252	9999	6/30/2013	2127	Special Ed
3083414099	104101252	0000	6/30/2013	2127	Student
3098797398	104101252	0000	6/30/2013	2123	Student
3147968553	104101252	0000	6/30/2013	2123	Student
3171189747	104101252	0000	6/30/2013	2123	Student

[/pre]

 

[SirJB7]

Hi, jeffmack!


First of all welcome to Chandoo's website Excel forums. Thank you for your joining us and glad to have you here.


As a starting point I'd recommend you to read the green sticky topics at this forums main page. There you'll find general guidelines about how this site and community operates (introducing yourself, posting files, netiquette rules, and so on).


Among them you're prompted to perform searches within this site before posting, because maybe your question had been answered yet.


Feel free to play with different keywords so as to be led thru a wide variety of articles and posts, and if you don't find anything that solves your problem or guides you towards a solution, you'll always be welcome back here. Tell us what you've done, consider uploading a sample file as recommended, and somebody surely will read your post and help you.


And about questions in general...


If you haven't performed yet the search herein, try going to the topmost right zone of this page (Custom Search), type the keywords used in Tags field when creating the topic or other proper words and press Search button. You'd retrieve many links from this website, like the following one(s) -if any posted below-, maybe you find useful information and even the solution. If not please advise so as people who read it could get back to you as soon as possible.


And about this question in particular...


You can do it at least in 2 ways:

a) Filter

Select the whole worksheet or data columns, go to Data tab, Sort & Filter group, click on Filter.

An arrow should have appeared at right bottom of each cell in 1st row. Then click on Location arrow, uncheck Select All, check 9999. This will show you only bad data rows.

Select All for remove applied filter.

b) Highlight

Select data columns, go to Start tab, Styles group, click on Conditional Formatting.

Select New Rule, Use Formula.

Place this formula in the text box: =$C1=9999

Select the proper format, Accept.


Regards!

 

[jeffmack]

Thank you for the quick response. I had looked at the Filter option however I would like to somehow have the display come up automatically, without any User intervention.


And for the conditional formatting, I apologize but the "Bad Data" may not always be "9999". It could be anything so I can't really hardcode a cell value in.


Thank you again.

 

[SirJB7]

Hi, jeffmack!

That doesn't have much sense. The conditions (Location=9999 or any other comparison that you might do using any combination of your data) may be set in the CF formula to get the desired result. That's to say whatever formula you could place in an empty column to decide if a row has bad data o good data, it'd be replicated in the CF formula.

If this method doesn't work for you, would you mind telling us how would you determinate bad data? Thank you.

Regards!

 

[Luke M]

To clarify what SirJB7 said, you could make the CF rule be:

=$C1<>0

or

=$C1<>"0000"

if col C is formatted as text (not likely, but just in case).

 

[jeffmack]

SirJB7 thank you for the suggestion and Luke M for the clarification of Sir JB7's suggestion. That suggestion will work !! and it it seems so easy. Thank you both.


Let me ask this, can I expand upon that formula to check other columns ?


Thank you again.

 

[Luke M]

Sure thing. You'll want to use the OR function to check for independent conditions (ie, this or that).

Example:

=OR($C1<>0,$E1="Failure")