clients database\/ billing database

Post Reply
Guest

clients database\/ billing database

Post by Guest » Tue Dec 20, 2011 10:09 pm

I have a clients database that has more people than my billing database. I'm trying to figure out who isn't paying by revealing the people on the clients database list that aren't in the billing database.

I've sorted them alphabetically and tried to use this example: https://discussions.apple.com/thread/2364208?start=0&tstart=0 from Badunit but I'm not sure if that is the correct route.

 

 

any advice on this?


Guest

Re:clients database\/ billing database

Post by Guest » Tue Dec 20, 2011 11:18 pm

Charlie,

 

Without seeing your data, it's difficult to prescribe an expression, but we can speak generally here.

 

This seems to me a very simple case for using COUNTIF. If I count the number of occurances of each member of the Client Database in the Billing Database, I should get all zeros and ones. The zeros are the people who are missing from the biling database and the ones are the people who exist in both databases. A sort on the count will organize the names for your examination.

 

Jerry


Guest

Re:clients database\/ billing database

Post by Guest » Wed Dec 21, 2011 12:24 am

Hi Jerrold -

 

thanks for the info/advice.

 

here is what my current setup looks like: http://chrle.us/9nwD

 

can you give me a formula that I could use like Badunit has here: https://discussions.apple.com/thread/2364208?start=0&tstart=0


Guest

Re:clients database\/ billing database

Post by Guest » Wed Dec 21, 2011 1:07 am

Charlie,

 

Here's an example:

 

Screen Shot 2011-09-01 at 4.03.54 PM.png

 

The formula in column B of Table 1 is: =COUNTIF(Table 2 :: A,A)

 

Pretty simple. You can sort and get this:

 

Screen Shot 2011-09-01 at 4.04.26 PM.png

 

In both cases I used a Conditional Format to color the Fill of the zeros.

 

Jerry


Post Reply