Wondering whether a formula could assist or is even possible ...

sykes

Well-known Member
Joined
May 1, 2002
Messages
1,561
Morning Sue (from the other side of the world!!)

That's great news. Well done!

As Eric alluded to, I'm sure the 6-digit student ID format could be sorted at the same time, for you. I wouldn't be surprised if he couldn't write it into the same conditional formatting formula, for you - or if not, add another one.
Certainly my coded solution could easily do this, just by adding another line or two, so do come back to me if you need to do this - or just fancy trying out the code, anyway.

In the meantime, please, keep being verbose when posting problems - I'd guess that at least half of the initial questions on this site, start with a short post, then soon descend into a long diatribe of ping-pong posting, whilst we rub our crystal balls - and try and get to the bottom of the poster's workbook set-up, and try to prise out of them what the desired result really is!
Another huge gripe of mine, is that 15 to 20 posts down the line, they're actually after a lot more - and if only they'd been clear from the outset, it would have saved loads of nugatory effort!

Anyway, enjoy deploying Eric's solution when you get to work, and as I said, come back here if you fancy a bash at coding.
All the best.
Sykes.
 

Eric W

MrExcel MVP
Joined
Aug 18, 2015
Messages
8,972
I'm very glad it works for you! Thanks for the interesting problem, and for the follow-up on it. If you ever have another question, let us know. Next time I'm in Tasmania, I'll look you up! :biggrin:
 

eskimo85

New Member
Joined
Jul 12, 2019
Messages
11
Hi Eric, yes you must! :)

Funny you should say that (another question) as I thought of one overnight - although it still relates to the original question. It should have occurred to me earlier - I don't know why it didn't. In doing my dummy test sheet I only populated 16 or so rows which made it clear at a glance which ones were incorrect, however given that there is up to 300 rows on the real sheets I thought it might be quicker if I could filter somehow the rows with errors after the conditional formatting has highlighted them so that I can take a screen capture of only the ones that have problems to email back to the relevant Course Coordinator to follow up with the respective lecturers. Does that make sense. Still quite early here on Monday morning and I don't know that I'm fully awake yet LOL. Is that something that would be achievable do you think?

Sue :)
 

eskimo85

New Member
Joined
Jul 12, 2019
Messages
11
I sure will sykes, once things quieten down a bit I'd love to learn more. Thank you ever so much again :)
 

Eric W

MrExcel MVP
Joined
Aug 18, 2015
Messages
8,972
Sure, you can filter the rows. Just take the original formula and put it in E1. Then copy it down the column to the end of your data. At this point, you might want to put in a header row, since the Filter tool expects one. But if you don't, you'll just see row 1 whether or not it should show. Then just select column E, click on Sort & Filter > Filter. Then in the drop-down box that appears, check the TRUE box and uncheck the others. All done!

Hopefully, your Tuesday is a bit better! (Although I'm just starting my Monday :()

Good luck! :biggrin:
 

eskimo85

New Member
Joined
Jul 12, 2019
Messages
11
Thanks Eric, that's terrific - will make things that much easier rather than having to note down all the errors (of which I hope there aren't too many) in an email. A visual screen capture can be emailed instead. Anything to save a bit of time in what has been in my view an unnecessarily long process from go to wo. You're all champions, really you are :)

I hope your Monday proved to be a good one. And might I say, if ever I come to the States I really must visit your town - what a lovely sounding place to live in!!

Sue :)
 

eskimo85

New Member
Joined
Jul 12, 2019
Messages
11
Hi Eric, just a quick question if I can? The Exams Office have added in a number of new columns to the results spreadsheets along two initial rows which has resulted in my data now being in columns P & Q, and commencing on Row 3 rather than Row 1. Would I be correct in assuming that if I change all instances of C & D in your provided formula to P & Q, and amend to 3 rather than 1 (eg C1 would become P3) that it should all still work correctly. Just wanted to make sure before I break everything LOL. Ta :)
 

sykes

Well-known Member
Joined
May 1, 2002
Messages
1,561
Hi Sue

Could be that Eric's busy / offline etc; in his absence, I suggest that you make a copy ("SaveAs") and mess around on that, if you're concerned about compromising the original data.
You can either copy the whole workbook (or .CSV file) or just right-click the sheet's name tab, and check the "Create copy" checkbox - then work on that copy of the worksheet in your file. If it all goes to plan, implement the changes into the real sheet/book.
Another way, if the extra columns/rows that the Exams Office have added, aren't needed for what you're doing, is to just delete them again (on a copy, perhaps), so your data's back to it's original rows & columns. Easiest way to delete a whole row/column, is to click on either the number to the far left (row) or letter at the very top (column) then right-click, and select "Delete." Sorry If I'm teaching Granny to suck eggs, as we say in England!
In the end, it all depends in what state you have to send things back out to other people.
 

eskimo85

New Member
Joined
Jul 12, 2019
Messages
11
Thanks so much sykes; lovely to hear from you and ta for your response. As it turns out, I ended up doing the very thing you suggested not long after the last tome of a post lol - great minds think alike it seems; well one great and one not so great and a wee bit tired :) It's been a doozy of a week so far. So all good in that regard now. All is working a treat.

Can relate to your English saying - my Mum, her siblings and parents came over to Australia in 1956 as '10 pound Poms' - Mum's words not mine, so I reckon I've probably heard every English saying there is over the years. One day I'll get there - certainly on the bucket list!

Sue :)
 

sykes

Well-known Member
Joined
May 1, 2002
Messages
1,561
Glad you're sorted, Sue.
You should be over here now - first summer for years, when it's not rained most days! I'm even thinking of dusting off the old BBQ - just need to remember how to use it!
 

Forum statistics

Threads
1,081,929
Messages
5,362,182
Members
400,671
Latest member
Tommy00836

Some videos you may like

This Week's Hot Topics

  • populate from drop list with multiple tables
    Hi All, i have a drop list that displays data, what i want is when i select one of those from the list to populate text from different tables on...
  • Find list of words from sheet2 in sheet1 before a comma and extract text vba
    Hi Friends, Trying to find the solution on my task. But did not find suitable one to the need. Here is my query and sample file with details...
  • Dynamic Formula entry - VBA code sought
    Hello, really hope one of you experts can help with this - i've spent hours on this and getting no-where. .I have a set of data (more rows than...
  • Listbox Header
    Have a named range called "AccidentsHeader" Within my code I have: [CODE]Private Sub CommandButton1_Click() ListBox1.RowSource =...
  • Complex Heat Map using conditional formatting
    Good day excel world. I have a concern. Below link have a list of countries that carries each country unique data. [URL...
  • Conditional formatting
    Hi good morning, hope you can help me please, I have cells P4:P54 and if this cell is equal to 1 then i want row O to say "Fully Utilised" and to...
Top