View Single Post
  #3   Report Post  
Minitman
 
Posts: n/a
Default

Hey Frank,

Ok, here goes....

|______A_____|______B_____|______C______|______D__ ___|
1 |____Date____|___Account___|__BS Amount_|__DT Amount_|
2 |__01/15/98___|_900460123-8_|$______120.00|_____________| <OK
3 |__01/15/98___|_900460123-8_|_____________|$_______70.00| <OK
4 |__01/15/98___|_900460123-8_|_____________|$_______50.00| <OK
5 |__01/15/98___|__15006254___|$_______75.00|_____________| <OK
6 |__01/15/98___|__15006254___|____________|$________35.00| <OK
7 |__01/15/98___|__15006254___|____________|$________40.00| <OK
8 |__01/18/98___|__15006254___|$______205.00|_____________| <OK
9 |__01/18/98___|__15006254___|____________|$_______120.00| <OK
10|__01/18/98___|__15006254___|____________|$________85.00| <OK
11|__01/18/98___|__64012813___|____________|$________45.00| <Pink
12|__01/21/98___|_900460123-8_|$_______80.00|_____________| <OK
13|__01/21/98___|_900460123-8_|$______120.00|_____________| <OK
14|__01/21/98___|_900460123-8_|_____________|$_______25.00| <OK
15|__01/21/98___|_900460123-8_|_____________|$_______54.00| <OK
16|__01/21/98___|_900460123-8_|_____________|$_______42.00| <OK
17|__01/21/98___|_900460123-8_|_____________|$_______26.00| <OK
18|__01/21/98___|_900460123-8_|_____________|$_______53.00| <OK
19|__01/25/98___|_900460123-8_|$_______21.00|_____________| <L Bl
20|__01/25/98___|_900460123-8_|_____________|$_____-179.00| <L Bl
21|__01/25/98___|_900460123-8_|_____________|$_______93.00| <L Bl
22|__01/25/98___|_900460123-8_|_____________|$_______87.00| <L Bl

I am looking to see if the sum of BS Amount per day per account is the
same as the sum of DT Amount for the same day and account. If the DT
Amount is smaller then the BS Amount I need for the rows for that
day-account to be light blue <L Bl. If the BS Amount is smaller then
the DT Amount, then I need for the rows for that day-account to be
pink <Pink.

If a BS Amount is missing, there is no way knowing how many DT Amounts
are supposed to match up with it, so some of them could be missing
also. If A BS Amount is missing, it is missing for all of that month
for that account number. For example, any DT Amounts with an account
number of 64012813 are going to be missing a BS Amount for that entire
month. Which is why that row is colored pink.

I have a conditional format on these cells now (except row 1) which
colors them beige and removes the borders if =$An="" which is why I
don't think I can use conditional formatting but I will need a vba
solution.

Thank you for looking at this. I hope this gives you enough to work
with.

I appreciate any help that you can give.

-Minitman


On Fri, 29 Oct 2004 19:20:53 +0200, "Frank Kabel"
wrote:

Hi
not really sure. Best would be if you could post some example rows of
data (plain text please) and explain your expected result

--
Regards
Frank Kabel
Frankfurt, Germany


Minitman wrote:
Greetings,

I have to see if one three conditions exists:

1) Missing BS items for existing DT items - <Pink
2) Missing DT items from existing BS items - <Light Blue
3) Both have some missing - <Light Purple

The first Conditional Format will be in A1, B1, C1 and D1. These are
the 4 columns that have information:

A) Date (BS & DT)
B) Account (BS & DT)
C) BS Amount
D) DT Amount

I need to turn the contents of A and B into some kind of transaction
ID. The date need only be in dd format. The Account has either
xxxxxxxx or xxxxxxxxx-x format, they are both present.

BS entries have Date, Account and Amount
DT entries also have Date, Account and Amount, only different

amounts.
DT Amounts for the same Date-Account added together will be equal to
BS Amounts for the same Date-Account added together. This is the
default condition.

I think SUMIF is involved, but I am not sure how.

Anyone have any ideas?

Any help would be appreciated.

TIA

-Minitman