Recognizing, counting, and displaying duplicates

**bish8282** · 01-12-2009, 05:23 PM

I work in the fitness center of a large senior community that keeps a strict reservation schedule for a certain piece of equipment.
The users sign up with their address on a sheet of paper early in the week and when they arrive they highlight their names to signify they were there.
My boss has been noticing an increasing number of no shows.

She would like me to keep track of the addresses that aren't showing up on a consistent basis.

I would like to create one Excel sheet in which data can be entered on a daily basis.
For example here are the dates and their numbered addresses:
12/1/2008 12/2/2008 12/3/2008
1944 1944 1944
2770 2770 2770
868 868 868
2030 2030 2030
1944 1944 1944
2770 2770 2770
868 868 868
2030 2030 2030
445 445 445

On the next sheet I would like to perform an analysis of these numbers.
I would title every two columns with the week and the date.
The first column in each week would display the duplicate address #s.
The second column would display how many times the address came up as a no show that week.

I would like to organize it this way:
Week 1 (Date-Date) Week 2 (Date-Date) Week 3 etc
#s Freq #s Freq #s Freq
1944 3 1927 2 361 5
2009 4 1492 5 1254 7

My question is:
Is this type of data analysis too complicated for simple functions?
If not, what kinds of functions can I use?

I am not versed in Macros or Basic programming.

**mewingkitty** · 01-12-2009, 05:29 PM

I'm sure you can see by looking at your post, it's very difficult to decipher the data as you've listed it. Posting it that way also means that if one of us is going to work on it, we have to copy everything over to get started.

Please post an example workbook.

mew!

**bish8282** · 01-19-2009, 02:44 PM

Here is an example workbook.

The first sheet is the raw data which would be inputted on a daily basis.
The numbers indicate the address numbers of gym members who did not show up for their appointments.

The second sheet is where I would like the data analyzed.
I would like the first column of each Week section to list each duplicate address number and the second column would list how many duplicates there were for each number.

Any ideas?

**mewingkitty** · 01-19-2009, 08:00 PM

Maybe...

Please Login or Register  to view this content.

Your information is the same for all three weeks, confused the hell out of me when I was trying to figure out why all three weeks worth of answers came out the same.

EDIT:
Could change this so that it looped around week to week if you have a pile more weeks worth of data, but I set it up this way cause it's easier to understand, and because you didn't say your sheet would be bigger than this.

**bish8282** · 01-19-2009, 11:20 PM

How do I apply all of that code?
Is that Basic?

**mewingkitty** · 01-20-2009, 01:22 AM

Yup, that's VBA.

Check out the attachment, see if it returns the results correctly. I generally write this stuff in my spare time at work, so I don't have a lot of time at that point to check for possible glitches. I do however have a couple of my IM addresses linked to this site, so if you'd like a hand getting it running on your spreadsheet, feel free to send me a "mew" and I'll help you out.

mew!

EDIT:
Also please let me know if this is going to be limited to a 3 week schedule of if it needs to run for an indefinite number of weeks until encountering blanks.

mew mew!

**bish8282** · 01-20-2009, 01:42 AM

I'll try it tomorrow morning.
Thanks Lady Mew!

**mewingkitty** · 01-20-2009, 01:45 AM

So yeah, your welcome,
and let me know if you need anything changed on that, cause I only set it to go through three weeks worth. I could make it have one loop and loop it until there are blank cells found, but it sounded like you only needed the three weeks analyzed, so I made three loops that are all hard written in with co-ordinates.

and

I'm a kitty.
Not all kitties are ladies.

mew!

**mewingkitty** · 01-20-2009, 02:03 PM

Wowzers.

K so made some revisions.

Added a counter at the beginning to determine how many weeks worth of data are present.

Cut the rest of the formula down to one loop, which increments the integers each time a loop completes, so that it goes from week to week without fixed numbers for each.

Included a couple notes in the code to hopefully make it a little clearer how it works, if you need to change anything later.

Please Login or Register  to view this content.

EDIT:
There's a couple numbers in there you may have to change. There's a pair of "4 to 15" loops, those are based on the information being in rows 4 to 15. If you have information in rows farther down than that, you'll need to change the "15"s to the row number you need checked to. Also with that, there's a loop with an 84 in it. That is based on checking 84 times for a duplicate number, so if you're going to increase the number of cells copied, you'll need to increase it too.

It doesn't have to be exact, you could change the 15's and the 84 to 10000, it'd just take a lot longer to run.

There's a way to make Ranges such as those dependant upon whether or not there is information in them, I just don't really get how to do it yet.

mew.