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
16
Medium Priority
?
813 Views
Last Modified: 2012-08-29
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
Comment
Question by:itsmevic
  • 6
  • 4
  • 3
  • +1
16 Comments
 
LVL 43

Assisted Solution

by:Saqib Husain, Syed
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

by:Steve
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

by:mark_harris231
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
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 10

Expert Comment

by:mark_harris231
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

by:itsmevic
ID: 38338293
Example attached.
Bookexample1.xlsx
0
 
LVL 24

Expert Comment

by:Steve
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

by:itsmevic
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

by:mark_harris231
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

by:Steve
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

by:itsmevic
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

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

Accepted Solution

by:
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)
This additional column is then added to the pivot table data.
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

by:itsmevic
ID: 38345570
Fantastic input, thank you all!
0
 
LVL 43

Expert Comment

by:Saqib Husain, Syed
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

by:itsmevic
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

by:Saqib Husain, Syed
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

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

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…

580 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question