Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
Solved

# Excel 2010:  Formula to Detect Data Repetition

Posted on 2012-08-27
Medium Priority
813 Views
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
0
Question by:itsmevic
• 6
• 4
• 3
• +1

LVL 43

Assisted Solution

Saqib Husain, Syed earned 200 total points
ID: 38338123
This should work if the data is in cells A4:A16

=IF(ISNA(MATCH(A3,A4:\$A\$17,0)),COUNTIF(\$A\$3:\$A\$16,A3),"")
0

LVL 24

Expert Comment

ID: 38338144
Use the pivot table to create a count column for the times

If you post an example file can show easier than explain.
0

LVL 10

Assisted Solution

mark_harris231 earned 200 total points
ID: 38338264
I'd recommend using a helper column.  In the attached, I placed a helper column in Column D with the following formula in Cell D3:

=IF(A3<>A2,1,D2+1)     (Copy down as far as data in Column A)

Then, in Cell B3, paste the following formula:

=IF(D4>D3,"",D3)  (Copy down as far as data in Column A)
Pattern.xls
0

LVL 10

Expert Comment

ID: 38338290
@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
0

Author Comment

ID: 38338293
Example attached.
Bookexample1.xlsx
0

LVL 24

Expert Comment

ID: 38338322
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.
0

Author Comment

ID: 38338506
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
0

LVL 10

Expert Comment

ID: 38338549
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?
0

LVL 24

Expert Comment

ID: 38338554
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.
0

Author Comment

ID: 38338660
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.
0

Author Comment

ID: 38339124
Hi S - check your email here in a few.  Thanks.
0

LVL 24

Accepted Solution

Steve earned 1600 total points
ID: 38344869
OK, so to get this going we have added an aditional column next to the original data which has a formula to remove the seconds portion of the time/date:
=DATE(YEAR(A2),MONTH(A2),DAY(A2))+TIME(HOUR(A2),MINUTE(A2),0)
(where A2 is the original time/date to seconds)
A count of the new column is added to a pivot table of IP and the new field.
The Grouping sensitivity of the pivot table can then be adjusted by changing the formula of the additional "Helper Coulmn"
0

Author Closing Comment

ID: 38345570
Fantastic input, thank you all!
0

LVL 43

Expert Comment

ID: 38346801
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.
0

Author Comment

ID: 38347755
ssaqibh, I just try and do the right thing when it comes to points.  I apologize if points weren't dispersed properly.
0

LVL 43

Expert Comment

ID: 38347780
No need to apologize. Just a guideline for future so that whoever deserves the full points gets the full points.
0

## Featured Post

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In Part II of this series, I will discuss how to identify all open instances of Excel and enumerate the workbooks, spreadsheets, and named ranges within each of those instances.
Microsoft's Excel has many features that most people will never need nor take advantage of.  Conditional formatting is one feature that you may find a necessity once you start using it.
This Micro Tutorial demonstrates how to create Excel charts: column, area, line, bar, and scatter charts. Formatting tips are provided as well.
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaacâ€¦
###### Suggested Courses
Course of the Month15 days, 9 hours left to enroll