Finding matching rows on two worksheets

[Wire323]

I'm trying to compare rows on 2 worksheets. If there are matches then I want to take the rows off of the 1st worksheet and move it to a 3rd one.

My 1st worksheet consists of "admin calls." Column A is the phone number, H is the date, and F is the start time.

My 2nd worksheet consists of "all calls." Column I is the phone, B is the date, and C is the start time.

I want to look at each row of the 1st worksheet, and try to find a matching row on the 2nd. A match will mean the phone number, date, and start time are all the same. If it finds a match, then I want to copy the value of Column B on the 1st worksheet to COlumn M on the 2nd. I'll then take the row off of the 1st worksheet and move it to a 3rd worksheet (called "matches").

Any help will be greatly appreciated. This is the last piece of a project I'm working on to make our billing girl's life a lot easier.


If I need to explain anything better then please let me know. I'm posting this question in a few different forums.

Thanks.

 

[HalfAce]

Hello Wire323,
Before running the code are any of your time values formatted as anything other than 1234? The code (as it's written) depends on all times being entered as regular numeric values either 3 or 4 digits long. (I understood you were changing numeric values to times and then back again. (yes?)

If you have times entered (as numeric values) that will be less than 3 digits long then you should be able to just change these two lines from:
Format(c1, "##:##")
Format(c2, "##:##")
To:
Format(c1, "00:##")
Format(c2, "00:##")

With all times left as simple numbers to start with, does this work?

 

[Wire323]

Yeah, some are less than 3 digits. The cells that have times between 12:00 and 12:55 am will have 1 or 2 digits. "1" would be 12:01 am, "44" would be 12:44 am, etc.

I'll try it out.

 

[HalfAce]

Yup, my mistake.
I didn't realize you guys would be working all night.

Those format changes should fix you right up.

 

[Wire323]

So close to being complete.

It didn't consider exactly 15 minutes before or after to be a match. I'm guessing the value 0.010117 may be off my a minute.

 

[HalfAce]

Yes, again my mistake. (It's off by 26 seconds.)
0.010117 actually equals 14 minutes, 34 seconds.
An even 14 minutes would be 0.009722222
An even 15 minutes would be 0.010416667
And an even 16 minutes would be 0.011111111

I believe if you replace 0.010117 with 0.010416667 you'll get exactly 15 minutes.

 

[Wire323]

It works perfectly now. Thanks a lot!

If I ever make it up to Alaska again I'll buy you a beer, or a snowboard or something.

Seriously though, thanks for all the help. I had a deadline of the 20th, and I would have cut it pretty close if you hadn't volunteered your time and talent.

Thanks. again.

 

[HalfAce]

You're very welcome.
If we can't give each other a hand then what good are we, right?

I didn't realize you had any deadline at all. I thought you were
just doing her a favor. (In which case I was thinking if I ever
made it to your area, it would be she that owes us both a beer!

If you have a boss or someone expecting it by the 20th, then the proper
"programmer's etiquette" would be to keep mentioning how much work is involved,
how you're not sure it can be done in that amount of time, (etc. etc.)
and then come through with it on the 18th or 19th. :wink:

Hope it all works out for you.