Compare two excel sheets w/different headers to return rows according to criteria

Hi guys,

I tried asking about this yesterday, but it was REALLY complicated and no one bit. So I created a simpler file with essentially the same info needing the same thing.

Let's say my company rents out parking spaces to people at a monthly rate, to be paid on the anniversary day of your start date. (If you started renting 7/3/12, you'd pay on 8/3/12, 9/3/12, etc. for as long as you're renting the space). Within the first 36 months of rent (3 yrs), we will fill your gas tank for free (I know that's ludicrous, just an example). Since we're tracking the gas, we still make an order in our billing software every time we fill your tank, just at $0.00. Starting in the 37th month (any time past 3 yrs), tenants need to start paying whenever they want tanks.

So, I attached two sheets (in one workbook) with:
-Anyone renting a parking space and the date they started renting
-Any order for gas fillups

I need to find a way to identify parkers who've filled up after their 3 year limit, so that I can bill for them.

Can someone help me?! Basically, I need to find common names in both lists, and separate them according to specific criteria.

Any assistance or direction or suggestions are much appreciated...

Thanks in advance!

This is rather quick and crude but it might get you going. The values over 3 in the results are what I think you are looking for.

Is this what you mean?

I will try to get to your actual worksheet and see how it actually ties into what the original problem was.

Today isn't a good day but I will see what I can do.

This formula "=IF(COUNTIFS(ORDERS!$G$2:$G$15,RENTAL!H2,ORDERS!$D$2:$D$15,"Bill")=0,"",COUNTIFS(ORDERS!$G$2:$G$15,RENTAL!H2,ORDERS!$D$2:$D$15,"Bill"))" means: If the first part in brackets starting with COUNTIFS is equal to 0 enter "" (a blank cell) and if it isn't equal to 0 perform the COUNTIFS formula and give the result.

The COUNTIFS formula says to go to tab ORDERS and in the range G2:G15 count the records that match H2 on the RENTAL tab THEN on the ORDERS tab in the range D2:D15, count the entries that match "Bill". The two ranges with the two criteria counts the records that match both criteria. In this case it would count the matches for Hendrix Jimi and Bill and count the occurrences of that combination. If Hendrix Jimi had 14 Bills then the COUNTIFS would return the number 14. The COUNTIFS doesn't "return" records it only counts records according to the criteria set.

This might help you out somewhat.

This will count the occurrences in Already billing. Enter on the Rentals tab and copy down.

Formula:

Please Login or Register  to view this content.

This compares ALREADY BILLING with the ORDERS tab. Enter on the ALREADY BILLING TAB to get the number of orders for each ID.

Formula:

Please Login or Register  to view this content.

This will give the total number of orders for each ID on the RENTALS tab.

Formula:

Please Login or Register  to view this content.

The file has become too large to upload.

Thanks for the feedback.

Glad to have been of some help.