#1
|
||||
|
||||
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. |
#2
|
|||
|
|||
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"))) |
#3
|
||||
|
||||
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. |
#4
|
||||
|
||||
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.
|
#5
|
|||
|
|||
Quote:
Nice solution p45cal! |
#6
|
||||
|
||||
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. |
#7
|
||||
|
||||
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. |
#8
|
||||
|
||||
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. |
#9
|
||||
|
||||
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.
|
#10
|
||||
|
||||
In the RegsNotRenewedThisYear, how is the last step done? The #1?
|
#11
|
||||
|
||||
I'm not sure which step you're referring to; there's no #1. Can you copy the code for the step here?
|
#12
|
||||
|
||||
See attached, thanks.
|
#13
|
||||
|
||||
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. |
#14
|
||||
|
||||
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. |
#15
|
||||
|
||||
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. |
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 |