Microsoft Office Forums

Go Back   Microsoft Office Forums > >

Reply
 
Thread Tools Display Modes
  #1  
Old 05-14-2020, 06:33 PM
OCM OCM is offline Check multiple cafeterias Windows 7 32bit Check multiple cafeterias Office 2000
Novice
Check multiple cafeterias
 
Join Date: Mar 2013
Posts: 17
OCM is on a distinguished road
Default 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,
Attached Files
File Type: xlsx Sample.xlsx (13.6 KB, 13 views)
Reply With Quote
  #2  
Old 05-14-2020, 06:52 PM
Logit Logit is offline Check multiple cafeterias Windows 10 Check multiple cafeterias Office 2007
Expert
 
Join Date: Jan 2017
Posts: 529
Logit is a jewel in the roughLogit is a jewel in the roughLogit is a jewel in the rough
Default

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 ?
Reply With Quote
  #3  
Old 05-15-2020, 04:14 AM
OCM OCM is offline Check multiple cafeterias Windows 7 32bit Check multiple cafeterias Office 2000
Novice
Check multiple cafeterias
 
Join Date: Mar 2013
Posts: 17
OCM is on a distinguished road
Default

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,
Reply With Quote
  #4  
Old 05-15-2020, 07:16 PM
Logit Logit is offline Check multiple cafeterias Windows 10 Check multiple cafeterias Office 2007
Expert
 
Join Date: Jan 2017
Posts: 529
Logit is a jewel in the roughLogit is a jewel in the roughLogit is a jewel in the rough
Default

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.
Reply With Quote
  #5  
Old 05-17-2020, 09:22 AM
ArviLaanemets ArviLaanemets is offline Check multiple cafeterias Windows 8 Check multiple cafeterias Office 2016
Expert
 
Join Date: May 2017
Posts: 869
ArviLaanemets has much to be proud ofArviLaanemets has much to be proud ofArviLaanemets has much to be proud ofArviLaanemets has much to be proud ofArviLaanemets has much to be proud ofArviLaanemets has much to be proud ofArviLaanemets has much to be proud ofArviLaanemets has much to be proud ofArviLaanemets has much to be proud of
Default

Two possible solutions attached - Table formula (I defined table on 1st sheet as Table for this!) or regular worksheet formula.
Attached Files
File Type: xlsx OCMSample.xlsx (14.7 KB, 8 views)
Reply With Quote
  #6  
Old 05-18-2020, 11:43 AM
OCM OCM is offline Check multiple cafeterias Windows 7 32bit Check multiple cafeterias Office 2000
Novice
Check multiple cafeterias
 
Join Date: Mar 2013
Posts: 17
OCM is on a distinguished road
Default

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,
Attached Files
File Type: xlsx Sample1.xlsx (12.7 KB, 8 views)
Reply With Quote
  #7  
Old 05-19-2020, 10:55 PM
ArviLaanemets ArviLaanemets is offline Check multiple cafeterias Windows 8 Check multiple cafeterias Office 2016
Expert
 
Join Date: May 2017
Posts: 869
ArviLaanemets has much to be proud ofArviLaanemets has much to be proud ofArviLaanemets has much to be proud ofArviLaanemets has much to be proud ofArviLaanemets has much to be proud ofArviLaanemets has much to be proud ofArviLaanemets has much to be proud ofArviLaanemets has much to be proud ofArviLaanemets has much to be proud of
Default

Quote:
Originally Posted by OCM View Post
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).
From your workbook example, it looks more like you want to get all codes for ID when in week around/after of H0004 aren't any other codes, not codes for one week

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?
Reply With Quote
  #8  
Old 05-20-2020, 06:07 AM
OCM OCM is offline Check multiple cafeterias Windows 7 32bit Check multiple cafeterias Office 2000
Novice
Check multiple cafeterias
 
Join Date: Mar 2013
Posts: 17
OCM is on a distinguished road
Default

ArviLaanemets, thanks.

Quote:
From your workbook example, it looks more like you want to get all codes for ID when in week around/after of H0004 aren't any other codes, not codes for one week
Correct, the goal is to identify if a given ID/individual has H0004 and then gets any other codes within 7 days intervals.
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:
Are you meaning week as calendary unit, or as 7-day time period?
7-day time period
Quote:
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)?
Only, dates after H0004 (7 days total)
Quote:
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)?
To clarify & simplify things, let’s say we have 4 codes:
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:
How must rows with no dates handled?
Usually, the date field is almost always populated
Reply With Quote
  #9  
Old 05-25-2020, 02:01 PM
p45cal's Avatar
p45cal p45cal is offline Check multiple cafeterias Windows 10 Check multiple cafeterias 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

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.
Attached Files
File Type: xlsx msofficeforums44954Sample1.xlsx (22.9 KB, 5 views)
Reply With Quote
  #10  
Old 05-26-2020, 05:46 PM
OCM OCM is offline Check multiple cafeterias Windows 7 32bit Check multiple cafeterias Office 2000
Novice
Check multiple cafeterias
 
Join Date: Mar 2013
Posts: 17
OCM is on a distinguished road
Default

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,
Reply With Quote
  #11  
Old 05-27-2020, 03:01 AM
p45cal's Avatar
p45cal p45cal is offline Check multiple cafeterias Windows 10 Check multiple cafeterias 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 OCM View Post
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
That's what it does now. I was offering to add 7 days before H0004.
Quote:
Originally Posted by OCM View Post
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.
It uses Power Query; go to the Data tab of the ribbon, then in the Queries & Connections section click on Queries & Connections and a pane should show up, not surprisingly with the header 'Queries & Connections'. In the Queries section you should see two queries, one called Table1 which is the result table on the sheet, and another called fnMiniTbl. The Table1 query calls fnMiniTbl. If you right-click Table1 in the new pane you'll get the option to Edit, which will let you step through what it does.



refs:
Power Query (Get & Transform) & M code


Microsoft Power Query for Excel Help - Excel
Reply With Quote
  #12  
Old 05-27-2020, 03:04 PM
OCM OCM is offline Check multiple cafeterias Windows 7 32bit Check multiple cafeterias Office 2000
Novice
Check multiple cafeterias
 
Join Date: Mar 2013
Posts: 17
OCM is on a distinguished road
Default

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,
Reply With Quote
  #13  
Old 05-28-2020, 03:15 AM
p45cal's Avatar
p45cal p45cal is offline Check multiple cafeterias Windows 10 Check multiple cafeterias 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 OCM View Post
Is it possible to get the filtered records (not allowed) in a separate sheet by itself? If not, what you provided is suffice.
Select the whole table, the Cut (Ctrl+x) and paste to the new location (Ctrl+v).
It's better to cut rather than copy because you'll get a copy of the query too which is unnecessary.
Reply With Quote
  #14  
Old 05-28-2020, 05:15 PM
p45cal's Avatar
p45cal p45cal is offline Check multiple cafeterias Windows 10 Check multiple cafeterias 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

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:
Attached Files
File Type: xlsx msofficeforums44954Sample1_correction.xlsx (23.2 KB, 5 views)
Reply With Quote
  #15  
Old 05-29-2020, 05:57 PM
OCM OCM is offline Check multiple cafeterias Windows 7 32bit Check multiple cafeterias Office 2000
Novice
Check multiple cafeterias
 
Join Date: Mar 2013
Posts: 17
OCM is on a distinguished road
Default

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,
Reply With Quote
Reply

Thread Tools
Display Modes


Similar Threads
Thread Thread Starter Forum Replies Last Post
Select multiple check boxes at once. Batchook Word 0 09-08-2016 03:44 PM
Check multiple cafeterias 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
Check multiple cafeterias 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

Other Forums: Access Forums

All times are GMT -7. The time now is 04:06 PM.


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