Microsoft Office Forums

Go Back   Microsoft Office Forums > >

Reply
 
Thread Tools Display Modes
  #1  
Old 09-23-2020, 07:53 PM
Marcia's Avatar
Marcia Marcia is offline Highlight values not registered Windows 7 32bit Highlight values not registered Office 2013
Expert
Highlight values not registered
 
Join Date: May 2018
Location: Philippines
Posts: 526
Marcia has a spectacular aura aboutMarcia has a spectacular aura aboutMarcia has a spectacular aura about
Default Highlight values not registered

Hi. I have a table of registration numbers with dates of registration. In monitoring the numbers that failed to register this year, what I do is to copy the Registration sheet and delete all numbers registered prior to 2020 in the new sheet. Then I apply a conditional formatting in the Registration sheet to highlight those numbers that are not present in the 2020 sheet.


How do I effect the formatting right there in the Registration sheet without the hassle of creating another 2020 of current year sheet?
Thank you and sample file attached.
Attached Files
File Type: xlsx Registration.xlsx (11.9 KB, 11 views)
Reply With Quote
  #2  
Old 09-24-2020, 07:17 AM
Purfleet Purfleet is offline Highlight values not registered Windows 10 Highlight values not registered Office 2019
Expert
 
Join Date: Jun 2020
Location: Essex
Posts: 345
Purfleet is a splendid one to beholdPurfleet is a splendid one to beholdPurfleet is a splendid one to beholdPurfleet is a splendid one to beholdPurfleet is a splendid one to beholdPurfleet is a splendid one to beholdPurfleet is a splendid one to behold
Default

There must be a better way, possibly with advanced filter or PQ. But this seems to work for a formula

=IF(C2<>"","",IF(COUNTIFS($A$2:A2,A2)<COUNTIFS(Nam e,A2),"",IF(LARGE(--(Name=Registration!$A2)*(Date_of_Registration),1)> $F$1,"","Check")))
Attached Files
File Type: xlsx Registration_purfleet.xlsx (15.8 KB, 8 views)
Reply With Quote
  #3  
Old 09-25-2020, 12:47 AM
Marcia's Avatar
Marcia Marcia is offline Highlight values not registered Windows 7 32bit Highlight values not registered Office 2013
Expert
Highlight values not registered
 
Join Date: May 2018
Location: Philippines
Posts: 526
Marcia has a spectacular aura aboutMarcia has a spectacular aura aboutMarcia has a spectacular aura about
Default

Thank you Purfleet. It seems a helper column is needed but no problem with that. I changed the $F$1 to DATE(YEAR(TODAY(),1,1) and the "" before the "NO" to "YES". What I'm trying to do now is to transfer the "NO" to the earliest registration not the latest.
I will get back if I could not get the desired result.
Reply With Quote
  #4  
Old 09-25-2020, 04:31 AM
p45cal's Avatar
p45cal p45cal is online now Highlight values not registered Windows 10 Highlight values not registered Office 2019
Expert
 
Join Date: Apr 2014
Posts: 863
p45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant future
Default

Quote:
Originally Posted by Purfleet View Post
possibly with advanced filter or PQ
In the attached is a Power Query offering at cell F1 of the Registration sheet. It just needs right-clicking and refreshing. It lists only those registrations not renewed in the current year without a comment attached to the latest registatrion (it will still list a non-renewal if there's no comment attached to the latest renewal) and only lists the latest registration for each registration.
Attached Files
File Type: xlsx msofficeforums45701Registration.xlsx (22.2 KB, 10 views)
Reply With Quote
  #5  
Old 09-25-2020, 10:54 AM
Purfleet Purfleet is offline Highlight values not registered Windows 10 Highlight values not registered Office 2019
Expert
 
Join Date: Jun 2020
Location: Essex
Posts: 345
Purfleet is a splendid one to beholdPurfleet is a splendid one to beholdPurfleet is a splendid one to beholdPurfleet is a splendid one to beholdPurfleet is a splendid one to beholdPurfleet is a splendid one to beholdPurfleet is a splendid one to behold
Default

Quote:
Originally Posted by p45cal View Post
In the attached is a Power Query offering at cell F1 of the Registration sheet. It just needs right-clicking and refreshing. It lists only those registrations not renewed in the current year without a comment attached to the latest registatrion (it will still list a non-renewal if there's no comment attached to the latest renewal) and only lists the latest registration for each registration.

Nice solution p45cal!
Reply With Quote
  #6  
Old 09-25-2020, 02:38 PM
Marcia's Avatar
Marcia Marcia is offline Highlight values not registered Windows 7 32bit Highlight values not registered Office 2013
Expert
Highlight values not registered
 
Join Date: May 2018
Location: Philippines
Posts: 526
Marcia has a spectacular aura aboutMarcia has a spectacular aura aboutMarcia has a spectacular aura about
Default

Thank you p45cal. My first PQ solution involved 2 queries, yours is just 1. I will open and see on Monday how you did it.
Please help me understand the step by step process so I could apply it to the actual data. Where did the Table (CurrentYearRegistration) that was merged to the Source come from?
3 pm: I was able to follow through the steps until the fnLastReg. The error message says that the PQ is of lower version. Google says I must update my Excel to later version than 2013 but travel restriction is still in effect in this part of the planet.

Last edited by Marcia; 09-26-2020 at 12:05 AM.
Reply With Quote
  #7  
Old 09-26-2020, 02:49 AM
Marcia's Avatar
Marcia Marcia is offline Highlight values not registered Windows 7 32bit Highlight values not registered Office 2013
Expert
Highlight values not registered
 
Join Date: May 2018
Location: Philippines
Posts: 526
Marcia has a spectacular aura aboutMarcia has a spectacular aura aboutMarcia has a spectacular aura about
Default

Purfleet, in another sheet, I changed your formula to:
=IF(C2<>"","",IF(COUNTIFS($A$2:A2,A2)<COUNTIFS(Nam e,A2),"",IF(LARGE(--(Name=Registration!$A2)*(Date_of_Registration),1)> (YEAR(TODAY()),1,1),"NO","YES")))
I would like the formula in this instance to attach the NO or YES to the earliest date of registration not the latest. The column heading is "CURRENT" so either the answer is YES or NO. I tried to change the LARGE to SMALL but the result is the same with the LARGE function.
Thank you.
Reply With Quote
  #8  
Old 09-26-2020, 04:51 AM
p45cal's Avatar
p45cal p45cal is online now Highlight values not registered Windows 10 Highlight values not registered Office 2019
Expert
 
Join Date: Apr 2014
Posts: 863
p45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant future
Default

Quote:
Originally Posted by Marcia View Post
Google says I must update my Excel to later version than 2013
Often when you get the warning that you have an earlier version of Power Query than what the workbook was developed in you can just dismiss the warning and carry on. However, I attached a diagnostic version of fnLastReg which I'd like you to go in as if to edit it and select the steps one by one until it complains, then tell me which step it complained at and what the error message says. I might be able to write a work-around.
Link to picture:
Image at Box

ps. The CurrentYearRegistration is just your table filtered for the current year and all but the Registration number columns removed.
Attached Files
File Type: xlsx Diagnosticmsofficeforums45701Registration.xlsx (22.0 KB, 5 views)
Reply With Quote
  #9  
Old 09-26-2020, 06:27 AM
Marcia's Avatar
Marcia Marcia is offline Highlight values not registered Windows 7 32bit Highlight values not registered Office 2013
Expert
Highlight values not registered
 
Join Date: May 2018
Location: Philippines
Posts: 526
Marcia has a spectacular aura aboutMarcia has a spectacular aura aboutMarcia has a spectacular aura about
Default

Thanks p45cal. I just finished going through all the steps and ended up with the same result. I felt so overwhelmed. I will try them in the actual data and see how they behave.
Reply With Quote
  #10  
Old 09-26-2020, 07:23 AM
Marcia's Avatar
Marcia Marcia is offline Highlight values not registered Windows 7 32bit Highlight values not registered Office 2013
Expert
Highlight values not registered
 
Join Date: May 2018
Location: Philippines
Posts: 526
Marcia has a spectacular aura aboutMarcia has a spectacular aura aboutMarcia has a spectacular aura about
Default

In the RegsNotRenewedThisYear, how is the last step done? The #1?
Reply With Quote
  #11  
Old 09-26-2020, 09:29 AM
p45cal's Avatar
p45cal p45cal is online now Highlight values not registered Windows 10 Highlight values not registered Office 2019
Expert
 
Join Date: Apr 2014
Posts: 863
p45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant future
Default

Quote:
Originally Posted by Marcia View Post
In the RegsNotRenewedThisYear, how is the last step done? The #1?
I'm not sure which step you're referring to; there's no #1. Can you copy the code for the step here?
Reply With Quote
  #12  
Old 09-26-2020, 09:45 AM
Marcia's Avatar
Marcia Marcia is offline Highlight values not registered Windows 7 32bit Highlight values not registered Office 2013
Expert
Highlight values not registered
 
Join Date: May 2018
Location: Philippines
Posts: 526
Marcia has a spectacular aura aboutMarcia has a spectacular aura aboutMarcia has a spectacular aura about
Default

See attached, thanks.
Attached Images
File Type: png Regn.png (7.1 KB, 21 views)
Reply With Quote
  #13  
Old 09-26-2020, 10:27 AM
p45cal's Avatar
p45cal p45cal is online now Highlight values not registered Windows 10 Highlight values not registered Office 2019
Expert
 
Join Date: Apr 2014
Posts: 863
p45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant future
Default

That's in the diagnostic file. Delete the whole file, it's been mangled by me to get you to tell me where things might be going wrong (that file also contains a mangled fnLastReg).
FYI that line is created by clicking on one of the words 'Table' in the grp column of the previous step.
Reply With Quote
  #14  
Old 09-26-2020, 04:45 PM
Marcia's Avatar
Marcia Marcia is offline Highlight values not registered Windows 7 32bit Highlight values not registered Office 2013
Expert
Highlight values not registered
 
Join Date: May 2018
Location: Philippines
Posts: 526
Marcia has a spectacular aura aboutMarcia has a spectacular aura aboutMarcia has a spectacular aura about
Default

P45cal,
1. What should be the source of fnLastReg?
2. I noticed that your workbook contains the same number of sheets, whereas in my practice workbook, there are as many sheets as there are queries so the workbook keeps on expanding.
Thank you again.
Reply With Quote
  #15  
Old 09-26-2020, 04:50 PM
p45cal's Avatar
p45cal p45cal is online now Highlight values not registered Windows 10 Highlight values not registered Office 2019
Expert
 
Join Date: Apr 2014
Posts: 863
p45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant future
Default

1.fnLastReg is a function; its 'source' gets passed to it in the Invoke function line.
2.The default loading destination of a new query is a new sheet; you can either cut and paste the new table to another location or right-click on the query's name in the query list and choose where to Load to… there.
Reply With Quote
Reply

Thread Tools
Display Modes


Similar Threads
Thread Thread Starter Forum Replies Last Post
Highlight Duplicate Values Across Multiple Sheets Bo_Knows_04 Excel 1 07-11-2020 11:41 PM
VBA to highlight specific values in the spreadsheet daiwuliz Excel 6 05-23-2018 10:30 AM
Getting pop-up saying that my copy of Office is not registered. dzuk Office 0 05-28-2015 05:09 PM
find - reading highlight - highlight all / highlight doesn't stick when saved bobk544 Word 3 04-15-2009 03:31 PM
Interface not registered dezwakke2 Outlook 0 12-03-2006 12:27 AM

Other Forums: Access Forums

All times are GMT -7. The time now is 04:28 AM.


Powered by vBulletin® Version 3.8.11
Copyright ©2000 - 2024, vBulletin Solutions Inc.
Search Engine Optimisation provided by DragonByte SEO (Lite) - vBulletin Mods & Addons Copyright © 2024 DragonByte Technologies Ltd.
MSOfficeForums.com is not affiliated with Microsoft