Fill Cells Red if Duplicate Category Split Group

[BillySpivy]

Hello,

Can there be conditional format to fill category cells red on the Transactions sheet if there are duplicate categories for Split group same Account, same Date, and same Payee?

I manually filled the cells G33 and G34 because they are duplicates that have these conditions.

Thank you

[AliGW]

CF rule:

=AND(B11<>"",COUNTIFS(B$11:B$5000,B11,C$11:C$5000,C11,E$11:E$5000,E11,G$11:G$5000,G11,K$11:K$5000,K11)>1)

Applied to:

=$G$11:$G$5000

[BillySpivy]

Thank you very much. Does the formula expand with the table or does the rule have to go to 5000?

[AliGW]

You just need to set it longer than the table will ever be.

[BillySpivy]

I am just trying to learn is why I ask. I thought that when more rows to the table are added the new row takes on the formatting from the previous row. Is this true?

[BillySpivy]

There doesn't seem to be consistency to the conditional formatting when adding rows. So I made the table with 500 rows beyond the initial balances so the ranges go to row 505. I adjusted all conditional formatting formulas to upper limits of 505. This will keep things tidy.

500 rows of transactions are more than enough for the year. Next year I will start a new workbook with the initial balances carried over from this year and clear the transactions.

Thank you again for help.

[JohnTopley]

500 TXs equates to 10 per week .. so perhaps your figure is too low. Based on many years of using Excel to manage my finances (home grown application!) !

[BillySpivy]

Okay I will expand to 1000 then thanks for your suggestion.

[BillySpivy]

Mr. Topley you mentioned that you have been using Excel for finances for years. How do you update from credit cards and banks? Do you manually enter each transaction?

[JohnTopley]

I record all my Credit/Debit card Transactions (in this mainly cashless society).

I have a sheet per month where I have all my regular payments (Standing Orders/ Direct Debits which can be monthly, yearly or limited months per year)), all my income (retired!) and I record the Credit cards in separate table. I keep a running monthly balance (including Credit Card TXs) so I know exactly the state of my finances

On a monthly basis, I get an Excel (CSV) file download from my bank and run a macro to check my records versus the bank and hence I can do a monthly reconciliation. The checking is simply based on matching amounts (as bank statement descriptions rarely match mine!)but I have never found this a problem (unless I missed/incorrectly added in my record!).

Some regular payments e.g telephone bill vary by a small amount each month so there are some manual adjustments My Credit card is a manual check but that is not to onerous. Equally, some month-end TXs appear on the following month's bank statement: yes, it would be possible to write a more sophisticated macro to do this check but "it isn't broke" so I leave well alone.

I can always reconcile to the penny! This has served me well for many years.

. i

[BillySpivy]

It sounds like a very good system. Have you ever thought of making a template perhaps with donations? I would likely donate if it works. I am currently trying out YNAB but it will require a paid subscription after the 34 day trial period.

[JohnTopley]

Given the amount of time you have spent "tailoring" Money Manager, I don't think it would meet your needs. Everyone's financial affairs and how they are managed ,are different and you have spent considerable effort on areas which have no place in my affairs.

It is simple, unsophisticated using basic Debit/Credit columns to record my finances: you could easily produce your own now that you have a template with "Money Manager". You appear to want a level detail (judging my your posts) which I do not require and cannot provide.