detect pattern in excel sheet

**DanSir** · 08-25-2013, 04:52 AM

Hi everyone!

Before I start I want you to know that I posted this "problem" already in an german excel forum. So please dont get mad at me because of "cross-posting" etc.

Iam currently an intern at a big company, where they gave me the following task:

We have a huge excel-sheet, containing disputes with customers. Everytime there is a dispute, the customers data will be transferred into the sheet. The sheet is organized like this:

DATE / NAME / ID / COMMENT / FREQUENCY (how often one customer appears in the list) /ID2 (ID combination of the date + ID) /Weekday

And here comes the task:

Find all customers who appear 10 work-days(Mo-Fr) in a row. So if a customer appears Mo-Fr and keeps on being in the list the next Monday etc. it still counts as "in a row". Same with official holidays. If a customer appears at a certain date he is "booked" and it doesnt matter if a appears a second time on that specific date.

Attached to this post you find an example in which you can see the structure of my excel sheet!
example.xls

Note: I had to use a german version of excel when I made this little example - thats why the formula in the collum "Frequency" says "Zählenwenn" its the german version of COUNTIF

At work I use an english version of Excel 2003

It would be great if someone had an ideo how to solve this! Right now I dont

Thanks!!

**DavidJAllen** · 08-25-2013, 01:54 PM

DanSir,

I solved this problem in two steps (see attached)

1) create a pivot table, rows as customer, column as date, data as count of date
2) use a custom function to count the 10 consecutive days (I am assuming no Saturdays or Sundays in your data list)

The function returns true or false, you need to give it the range to test (in this case the line in the pivot for a customer with all the dates. It simply steps along the range and adds one to the counter if there is a 1 in the cell, if not the counter is reset. If the counter get to 10 the true is returned, otherwise false.

Please Login or Register  to view this content.

I could write a macro to sort the data by customer then date and loop through and output the clients where there are 10 consecutive days. Probably neater but I think the one here is easier to understand and you can eyeball to check!

Hope that helps.

Regards

David

**DanSir** · 08-26-2013, 03:33 AM

Hi David!

Big Thanks!

I'll try to put this in my "real" sheet and will give you some feedback if it works!!

Cheers!!

It all works fine! You really saved my day!

**DavidJAllen** · 08-27-2013, 08:50 AM

Glad I could help.

Regards

David

**DanSir** · 09-01-2013, 07:33 AM

Hi again - do you think there is a way to count how often the "10 consecutive day" rule applies for eacht customer? Iam not sure if I have to change the vba module or if I can solve this with a countif formula?!

regards
DanSir

**DavidJAllen** · 09-01-2013, 02:41 PM

DanSir,

This is not a problem or difficult but I need to know precisely how you want it.

Lets say 2 clients have the following pattern:

1-1-1-x-x-1-1-1-1-1-1-1-1-1-1-1-1-x-x-1-1-1-1-1-1-1-1-1-1-1-1-x-x (3,12,12)
1-1-1-1-1-1-1-1-1-1-1-1-1-1-1-1-1-1-1-1-1-1-1-1-1-1-1-1-1-1-1-1-1 (27)

Would you want the result to be 2 and 1 or something else?

Regards

David

**DanSir** · 09-02-2013, 02:17 AM

Hi David! "2 and 1" would be fine!

Regrads,

DanSir

**DavidJAllen** · 09-02-2013, 02:51 AM

DanSir,

I have amended the custom function to return the number of groups of 10 or more rather than a true or false.

Regards

David

detect pattern in excel sheet

LinkBack

Thread Tools

Rate This Thread

Display

detect pattern in excel sheet

Re: detect pattern in excel sheet

Re: detect pattern in excel sheet

Re: detect pattern in excel sheet

Re: detect pattern in excel sheet

Re: detect pattern in excel sheet

Re: detect pattern in excel sheet

Re: detect pattern in excel sheet

Thread Information

Users Browsing this Thread

Similar Threads

[SOLVED] Formula to detect anomaly in data entered in excel sheet

Detect sheet protection as it occurs

[SOLVED] detect and delete existing sheet

Automatically detect sheet name

How to detect if sheet is hidden?

Tags for this Thread

Bookmarks

Bookmarks

Posting Permissions