#1
|
|||
|
|||
Check multiple cafeterias
Greetings,
I've a list of over 100 thousand records with ID, name, code etc. What I'm trying to identify is if a certain code i.e. H0004 appears along with any other codes for the same ID# as we do not allow the H0004 code to exist with any other codes for the same person. In the attached file sheet1 is the sample list, and sheet2 is how I envision the result should look like. TIA Regards, |
#2
|
|||
|
|||
So ... Sheet2 is the problem and Sheet1 is the corrected version ?
So long as the other cells in the row have the number 4 somewhere, it is ok ? |
#3
|
|||
|
|||
Logit, thanks.
No, it's the other way around. sheet1 is the source data, and sheet2 is what I expect to see. I produced sheet2 by manually: filtered out those individuals who have H0004 codes along with any other codes. So, my goal is to apply formula so that I get sheet2. Simply put, H0004 code is not allowed with any other codes 1. an individual can have all H0004 codes 2. an individual can have any other codes, but NOT along with H0004 code Hope this helps TIA Regards, |
#4
|
|||
|
|||
OCM :
I have to admit that I am stumped with this one. My gray matter hasn't a clue how to approach an answer. My apologies. If there is anyone else out there that can provide a solution PLEASE do so. |
#5
|
|||
|
|||
Two possible solutions attached - Table formula (I defined table on 1st sheet as Table for this!) or regular worksheet formula.
|
#6
|
|||
|
|||
Thank you both.
ArviLaanemets, I started implementing your method in a very big excel workbook. I was approached by the requester to clarify further. Here is the clarification: Everything remains the same what changed is that we are looking for individuals with H0004 and then getting the other codes within the same week (within 7 days). I think this will reduce the result tremendously. I modified & attached a sample workbook. TIA Regards, |
#7
|
|||
|
|||
Quote:
Some details are missing for me! Are you meaning week as calendary unit, or as 7-day time period? When week is calendary unit, then do you use US calendary (the week starts with Sunday) or ISO Calendary (the week starts with Monday)? When week is 7-day time period, then does it apply to dates before and after H0004 (13 days total), or only for dates after H0004 (7 days total)? Must all conditions apply for all H0004 codes for same ID, or only for latest one (e.g. what happens when in year 2018 was H0004 with another code in next day, but in year 2019 was H0004 without any other code less than 7 days apart)? How must rows with no dates handled? |
#8
|
|||||
|
|||||
ArviLaanemets, thanks.
Quote:
e.g. individual gets H0004 code on 5/1/20 and then gets G0400 on 5/6/20, then the way it’s done manually is to get the difference i.e. 6-1= 5 days. Then we flag this as it is <= 7 days and not allowed. But, say if individual gets H0004 on 4/7/19 and gets G0401 on 4/18/19 then it’s allowed (18-7=11 days) over 7 days intervals and we do not need to flag this ID/individual. Quote:
Quote:
Quote:
H0004 (main code) G0400 G0401 80008 Simply put, H0004 code cannot be followed or combined with any of the other 3 codes within 7 days. But, H0004 code by itself can be provided daily or at any time (no restrictions) In your example, a. if in year 2018 H0004 with another code the next day= not allowed b. in year 2019 H0004 without any other code less than 7 day apart = allowed Quote:
|
#9
|
||||
|
||||
In the attached there's a table which lists only the disallowed entries with their respective H0004 entry. Blank dates have been completely ignored since there's no way of knowing whether they're in the seven day limit or not.
Currently it only returns disallowed entries after the H0004 dates, but I can tweak it to include within seven days either side if you want. All you need to do is refresh (right-click and choose Refresh) the table at cell L1 each time the data in Table1 changes. |
#10
|
|||
|
|||
Thanks P45cal,
I did change dates, refreshed the table and works nicely. 1. Any other codes AFTER the H0004 code is not allowed. Yes, if you can tweak it to show within 7 days after the H0004 dates, it will be great. 2. Can you please explain what you used as a solution for this problem? I would like to use the same method for future projects. TIA Regards, |
#11
|
||||
|
||||
Quote:
Quote:
refs: Power Query (Get & Transform) & M code Microsoft Power Query for Excel Help - Excel |
#12
|
|||
|
|||
p45cal,
Thank you very much, it worked beautifully. Is it possible to get the filtered records (not allowed) in a separate sheet by itself? If not, what you provided is suffice. Also, at this time no need to filter out 7 days before the H code. Thank you for the resource links for the power query as I'm not familiar with it before. Regards, |
#13
|
||||
|
||||
Quote:
It's better to cut rather than copy because you'll get a copy of the query too which is unnecessary. |
#14
|
||||
|
||||
Correction. My last offering missed out a row in the results, so I corrected that and while doing so put blanks in the MostRecentH4 column where the row was for a H0004 code so as not to duplicate info and make it easier for the eye to navigate:
Click to view image File: |
#15
|
|||
|
|||
p45cal,
Thanks for the correction. As you suggested, I did cut/paste to display the result in a separate sheet and it worked. Based on the sample file everything is working as intended. Now, my actual workbook contains over 3000 rows and columns A – X to include ID, Name, Code, Address, Amount etc. What steps do I need to take to apply your method from scratch on this and similar projects in future? TIA Regards, |
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
Select multiple check boxes at once. | Batchook | Word | 0 | 09-08-2016 03:44 PM |
Excel vba to check to check if two columns are empty | subspace3 | Excel Programming | 5 | 07-09-2015 04:45 PM |
Multiple Check In/check Out Times | big0 | Excel | 4 | 09-19-2013 05:02 AM |
cannot check/uncheck check box but added check box | learn2office | Word | 1 | 11-27-2012 02:02 AM |
Link word check box to access check box | Mrkieth | Word | 4 | 01-30-2012 06:43 AM |