Conditional Format With SUMIF
 

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

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

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

Hey Frank,

I guess this post got lost over the weekend.

Anyone have any ideas as to how 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 all of 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 all of
the rows for that day-account to be pink <Pink.

Any help would be appreciated.

TIA

-Minitman


On Fri, 29 Oct 2004 18:12:50 -0500, Minitman
wrote:

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 all of 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 all of 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