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

sykes

Well-known Member
Joined
May 1, 2002
Messages
1,645
Office Version
365
Platform
Windows
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.
 

Some videos you may like

Excel Facts

Remove leading & trailing spaces
Save as CSV to remove all leading and trailing spaces. It is faster than using TRIM().

Eric W

MrExcel MVP
Joined
Aug 18, 2015
Messages
9,592
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
9,592
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,645
Office Version
365
Platform
Windows
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,645
Office Version
365
Platform
Windows
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!
 

Watch MrExcel Video

Forum statistics

Threads
1,096,240
Messages
5,449,189
Members
405,556
Latest member
dougbohr

This Week's Hot Topics

Top