Hi,
I am having a database in excel of more than 1 lakh rows. Suppose i am having a data from Jan,19 to July,19 data. I am having a unique id with me.
Now i want to identify whether the unique id exists in July,19 month are available in June,19 database or not.
Any suggestion to sort out?
Welcome to the forum
Please attach a sample workbook (not a picture or pasted copy). Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are shown, mock them up manually if necessary.
Remember to desensitize the data.
Click on GO ADVANCED and then scroll down to Manage Attachments to open the upload window.
Hi,
Attachment 639169
I want to create a formula with which i can check the Unique ID 19766/106100003167 with Creation Date 22-08-2019 2:51:37 PM. The mentioned Unique ID is available with in past 30days creation date or not.
Attached is the sample database.
Please upload a workbook or a representative cut down copy, anonymised if necessary. It is always easier to advise if we can see your request in its context. Pictures re rarely much use.
Show a before and after situation with manually calculated results, explaining which information is data and which is results, and if it's not blindingly obvious how you have arrived at your results some explanatory notes as well.
To upload a file click the Go Advanced button at the foot of your post, look underneath the post area for the Manage Attachments section and take it from there.
Richard Buttrey
RIP - d. 06/10/2022
If any of the responses have helped then please consider rating them by clicking the small star iconbelow the post.
Please find the data.
Creation Date Unique ID Date substract (30 Days) Checking Unique ID Result to obtain Remark
22-08-19 14:51 19766/106100003167 22-08-19 23-07-19 19766/106100003167 FALSE Unique ID not present with in 30 days
19-08-19 15:37 21829/105100109932 19-08-19 20-07-19 21829/105100109932 TRUE Unique ID present with in 30 days
13-08-19 20:08 19070/104100049125
13-08-19 11:56 19766/106100003167
12-08-19 18:54 16940/380055285
09-08-19 11:35 21829/105100054156
06-08-19 11:16 16940/380055285
17-07-19 18:27 21829/105100109932
16-07-19 19:18 19766/106100011184
25-07-19 12:22 19766/106100003167
I am unable to attached as the window is not opening.
did you Edit your post and choose Go Advanced then look for the Manage Attachments?Originally Posted by Sid_2987
Yes....please find in the manage attachment. it is named as "Sample Data"
Would you add the True / False results for the other examples and explain the results with reference to cells used to calculate the results. The simple description 'not present within 30 days is not clear enough.
Do you mean the next 30 days, or the previous 30 days.
In addition when you say next Unique ID do you mean the next occurrence of the ID in the table, or the next occurrence of the eariest next date for that ID?
At the moment your table is unsorted by date and hence leads to this last question.
Dear Richard,
Thank you for attending my request.
I don't understand why you are facing this much problem to understand my simple ENGLISH.
For more understanding, I have attached the excel sheet and that excel sheet has a calculation.
I request you to please check the formula associated with it.
Also, it doesn't matter whether it is next or previous. If you are unable to understand. Based on the requirement it can be changed to next or previous also.
MY SIMPLE QUERY IS " IF ANY OF THE SALES ID IS PRESENT ON 28TH AUGUST 2019 THEN I HAVE TO IDENTIFY WHETHER SAME SALES ID HAS OCCURED IN PREVIOUS 30 DAYS". Hope I am clear.
Based on my understanding if a professional is working in an organization. A person has to come out with approach first then a discussion on final conclusion.
if a person is asking doubt it means he has less clarity so its mentor responsibility to make it simpler by providing a solution. The solution doesn't matter it will be correct or wrong.
If you are able to provide a solution then OK else let the other person can try to sort the query.
Thank you for your support
As I said, would you add all the results you expect for the example table in your workbook. This is necessary so that we can test a proposed solution against what you EXPECT to see.
My long experience tells me that just two results are really not sufficient to be certain of picking up any smll variations or anomalies.
It may also be more efficient to ue a list of data sorted by date, or possibly ID and date. Is that something your system permits.
I don't idly ask for clarification. If clarification is sought it's because I don't understand stuff like
"I am having a unique id with me. Now i want to identify whether the unique id exists in July,19 month are available in June,19 database or not."
Remember you are totally au fait with your system and understand almost without saying what results you expect. We have no prior knowledge of the overall goal of the system nor how variations in layout etc. may impact on a solution. We ask because we're not clear.
perhaps
=IF(COUNTIFS(B:B,">"&INT(B3)-30,B:B,"<="&B3,C:C,C3)>1,"Unique ID not present within 30 days","Unique ID present within 30 days")
depending on your 30 days you may wish to change the 30 to 31
Hi,
I have used the formula as =not(iserror(Match(Cell reference, July(named data which appears in July month),0))).
With the help of this formula, I am able to get the result. But it has become static. I want to make it dynamic.
For Example. If Creation date is 29-Aug-19 then I want to verify for past 30 days i.e till 28-Jul-19. My formula should check the Unique ID or Sales Person ID has appeared in past 30 days or not. Similarly, if the creation date is 28-Aug-19 then my formula should check again for past 30 days i.e till 27-Jul-19.
Please find the formula associated with the data in excel sheet. I am not getting idea to make it dynamic. I want based on the creation date it will automatic check for past 30 days data.
what doesn't work with what I gave you?
when the 30 days are dynamic, I can not see the logic of named ranges around which month the data appears in.
See post #10.
You were asked to add specific results for the good reasons I gave.
Since the results vary with the current date show us what the results would be assuming two different current dates stating what the 'current date' is for each of the two examples - and set the current dates a few days apart.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks