Page 2 of 3 FirstFirst 123 LastLast
Results 11 to 20 of 22

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

  1. #11
    Board Regular sykes's Avatar
    Join Date
    May 2002
    Location
    Cornwall,England
    Posts
    1,561
    Post Thanks / Like
    Mentioned
    4 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Wondering whether a formula could assist or is even possible ...

    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.
    Sykes
    Windows 10 / XL 2016
    Members don't have crystal balls, so PLEASE describe your situation, code,
    and requirements ACCURATELY in your first post. It saves hair!
    Better still - upload your spreadsheet (and formulae!) with this great bit of kit.


    - forum use guidelines, forum rules and terms of use
    - Try searching for your answer first, see how
    - Read the FAQs
    - List of BB codes

  2. #12
    MrExcel MVP Eric W's Avatar
    Join Date
    Aug 2015
    Location
    Bountiful, UT
    Posts
    8,718
    Post Thanks / Like
    Mentioned
    45 Post(s)
    Tagged
    5 Thread(s)

    Default Re: Wondering whether a formula could assist or is even possible ...

    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!
    Cheers,
    Eric

    When you eliminate the impossible, whatever remains, however improbable, must be the truth.

    -Posting guidelines, forum rules, terms of use, FAQs, BB codes, See how to search the forum
    -Post a screen shot with the HTML Maker

  3. #13
    New Member
    Join Date
    Jul 2019
    Location
    Hobart, Tasmania
    Posts
    11
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Wondering whether a formula could assist or is even possible ...

    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

  4. #14
    New Member
    Join Date
    Jul 2019
    Location
    Hobart, Tasmania
    Posts
    11
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Wondering whether a formula could assist or is even possible ...

    I sure will sykes, once things quieten down a bit I'd love to learn more. Thank you ever so much again

  5. #15
    MrExcel MVP Eric W's Avatar
    Join Date
    Aug 2015
    Location
    Bountiful, UT
    Posts
    8,718
    Post Thanks / Like
    Mentioned
    45 Post(s)
    Tagged
    5 Thread(s)

    Default Re: Wondering whether a formula could assist or is even possible ...

    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!
    Cheers,
    Eric

    When you eliminate the impossible, whatever remains, however improbable, must be the truth.

    -Posting guidelines, forum rules, terms of use, FAQs, BB codes, See how to search the forum
    -Post a screen shot with the HTML Maker

  6. #16
    New Member
    Join Date
    Jul 2019
    Location
    Hobart, Tasmania
    Posts
    11
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Wondering whether a formula could assist or is even possible ...

    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

  7. #17
    New Member
    Join Date
    Jul 2019
    Location
    Hobart, Tasmania
    Posts
    11
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Wondering whether a formula could assist or is even possible ...

    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

  8. #18
    Board Regular sykes's Avatar
    Join Date
    May 2002
    Location
    Cornwall,England
    Posts
    1,561
    Post Thanks / Like
    Mentioned
    4 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Wondering whether a formula could assist or is even possible ...

    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.
    Sykes
    Windows 10 / XL 2016
    Members don't have crystal balls, so PLEASE describe your situation, code,
    and requirements ACCURATELY in your first post. It saves hair!
    Better still - upload your spreadsheet (and formulae!) with this great bit of kit.


    - forum use guidelines, forum rules and terms of use
    - Try searching for your answer first, see how
    - Read the FAQs
    - List of BB codes

  9. #19
    New Member
    Join Date
    Jul 2019
    Location
    Hobart, Tasmania
    Posts
    11
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Wondering whether a formula could assist or is even possible ...

    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

  10. #20
    Board Regular sykes's Avatar
    Join Date
    May 2002
    Location
    Cornwall,England
    Posts
    1,561
    Post Thanks / Like
    Mentioned
    4 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Wondering whether a formula could assist or is even possible ...

    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!
    Sykes
    Windows 10 / XL 2016
    Members don't have crystal balls, so PLEASE describe your situation, code,
    and requirements ACCURATELY in your first post. It saves hair!
    Better still - upload your spreadsheet (and formulae!) with this great bit of kit.


    - forum use guidelines, forum rules and terms of use
    - Try searching for your answer first, see how
    - Read the FAQs
    - List of BB codes

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •