Excel 2010: Formula to Detect Data Repetition
Hello Experts!
I have a pivot table that contains an IP address then below it the m/dd/yyyy hh:mm i.e.
xxx.xx.xxx.xxx
m/dd/yyyy hh:mm
m/dd/yyyy hh:mm
m/dd/yyyy hh:mm
m/dd/yyyy hh:mm
and so on.
I think I figured out a way to detect patterns but am not sure of the formula I would need to use to capture this. My huge world changing ephiany came as I was going through each entry looking for patterns and noticed repeted entries. For example:
Column A Column B
xx.xxx.xxx.xx Detected Pattern
8/27/2012 10:44
8/27/2012 10:44
8/27/2012 10:44
8/27/2012 10:44 4
8/27/2012 10:45
8/27/2012 10:45 2
8/27/2012 10:46 1
8/27/2012 10:47
8/27/2012 10:47
8/27/2012 10:47
8/27/2012 10:47 4
8/27/2012 10:48
8/27/2012 10:48 2
8/27/2012 10:49 1
and so on
With the example above, you can see a visible pattern in Column B based off of Column A's data. By counting the repetition, I'm able target patterns better in my data. Is there a formula that can do this for me? This would save us so much time in being able to detect these patterns. Any help is GREATLY APPRECIATED
I have a pivot table that contains an IP address then below it the m/dd/yyyy hh:mm i.e.
xxx.xx.xxx.xxx
m/dd/yyyy hh:mm
m/dd/yyyy hh:mm
m/dd/yyyy hh:mm
m/dd/yyyy hh:mm
and so on.
I think I figured out a way to detect patterns but am not sure of the formula I would need to use to capture this. My huge world changing ephiany came as I was going through each entry looking for patterns and noticed repeted entries. For example:
Column A Column B
xx.xxx.xxx.xx Detected Pattern
8/27/2012 10:44
8/27/2012 10:44
8/27/2012 10:44
8/27/2012 10:44 4
8/27/2012 10:45
8/27/2012 10:45 2
8/27/2012 10:46 1
8/27/2012 10:47
8/27/2012 10:47
8/27/2012 10:47
8/27/2012 10:47 4
8/27/2012 10:48
8/27/2012 10:48 2
8/27/2012 10:49 1
and so on
With the example above, you can see a visible pattern in Column B based off of Column A's data. By counting the repetition, I'm able target patterns better in my data. Is there a formula that can do this for me? This would save us so much time in being able to detect these patterns. Any help is GREATLY APPRECIATED
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
@ssaqibh - Nicely done. I was trying to think of how this could be done in a single column/formula. This will help me with a couple of projects I have in the works as well
ASKER
Example attached.
Bookexample1.xlsx
Bookexample1.xlsx
Would need an example of the pivot table, as would plan to get the pivot table to output in the format required, without need for formula.
For example add coulmn to the table with a count of the dates.
Or to use the right click and group by function.
So if a file with the full pivot table could be posted (or mailed to me if sensitive) that would help. Ta.
For example add coulmn to the table with a count of the dates.
Or to use the right click and group by function.
So if a file with the full pivot table could be posted (or mailed to me if sensitive) that would help. Ta.
ASKER
Thanks Barman. I may try that if I can't get this to work. Being able to do this without a formula would be absolutely ideal with this.
Mark - Your spreadsheet was rather interesting. I just can't get it work as it keeps putting "1's" in both column B and D. I've matched my main spreadsheet with your example i.e. same cells, same columns but not getting desired output.
Capture.JPG
Mark - Your spreadsheet was rather interesting. I just can't get it work as it keeps putting "1's" in both column B and D. I've matched my main spreadsheet with your example i.e. same cells, same columns but not getting desired output.
Capture.JPG
Can you post your example in xls vs. xlsx? I'm on an old system today and can't open the xlsx.
Curious concerning the result. Did you try ssaqibh's method?
Curious concerning the result. Did you try ssaqibh's method?
Seriously Vic, mail me the pivot table or try adding a coulmn for a count of the dates.
Can then get this to output far easier than the formula method, as the formula will only work for one address at a time.
Can then get this to output far easier than the formula method, as the formula will only work for one address at a time.
ASKER
I see, we'll do. Thanks S. Let me try adding the column for a count of the dates as you recommended and we'll take it from there. Well send email if that doesn't work. Thanks.
ASKER
Hi S - check your email here in a few. Thanks.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Fantastic input, thank you all!
You do not have to award points to all participants who attempt to help you. You should award "The answer" which is relvant to the question and the one which you actually used. Assist points are meant for cases where other answers are also relevant to your question. In this case only the last one really solved your problem and you should have awarded all points to that comment.
ASKER
ssaqibh, I just try and do the right thing when it comes to points. I apologize if points weren't dispersed properly.
No need to apologize. Just a guideline for future so that whoever deserves the full points gets the full points.
If you post an example file can show easier than explain.