Re: Highlight the debit and Credit ( positive and negative) numbers within a column

[Sandip Shah]

Hi Sandip,
When you indicate highlight do you really need the background
to be highlighted, if so then the Conditional Formatting solutions
already provided simplify this.
http://www.mvps.org/dmcritchie/excel/condfmt.htm

But if you just want to more conventionally change the FONT color then
you can use normal cell formatting. #,##0.00_);[Red](#,##0.00)
Since you have numbers and not converting between text and numbers
the change of format is immediate.
http://www.mvps.org/dmcritchie/excel/formula.htm#GetFormatExample

By the way the newsgroup
microsoft.public.excel.worksheetfunctions
was changed years ago on the Microsoft newsservers to
microsoft.public.excel.worksheet.functions
the old one hangs around in UseNet. Those of us that attach directly
to the Microsoft newsservers would never see your posting except for
the fact that you cross-posted. (cross-posting is not a good practice).

I have dowloaded an account details from my accounting package into
excel. The number of transactions runs into over 10000 lines. All the
debits and credits ( positive and negative ) numbers are in the same
column.

David,

I have gone through the website on conditional formatting. I had also
gone through CPearsons website prior to posting this message. The
problem I see in conditional formatting is that it highlights the
duplicate entry but not the orginal entry.

For eg. If I have 4500 on row 1 and -4500 on row 12, I would want both
these numbers to be highlighted so that I can verify it and remove it
from the list. Hence in a column which has hundreds of positive and
negative numbers, the end result of all the highlighted numbers would
be zero since the positive is matched with a negative number.

Let me know if I am wrong in my understanding about conditional
formatting.

Regards
Sandip

 

[David McRitchie]

Hi Sandip,
Duplicates was not mentioned in your original questions about
identifying negative numbers. Don't know if these are to be tied
in together. The cells you select when you define a C.F. are the
ones that get colored. In the formula below since $A is
absolute you can select any column(s) you want and not necessarily
the column where the duplicate occurs.

Directly from my page on Conditional Formatting:
Duplicated Anywhere in Column: Need not be sorted (includes first of duplicates)
Formula is: =COUNTIF($A:$A,$A1)>1

For Conditional Formatting the object of the formula is to return True or False.
False is zero, everything else is True.

So your problem is really on the formula. You can read more about
identification of duplicates on Chip Pearson's pages look for
duplicates in his topic index.
http://www.cpearson.com/excel/topic.htm
and more about use of COUNTIF in
Cell Counting Techniques -- John Walkenbach
- Worksheet formula examples that demonstrate various
- ways to count cells that match a criteria.
http://www.j-walk.com/ss/excel/tips/tip52.htm

A reference to download and refer to on your computer
Excel Function Dictionary -- by Peter Noneley,
http://homepage.ntlworld.com/noneley
workbook with 157+ sheets, each with an explanation and
example of an Excel function.

Looks like you are looking for outstanding balances, so you might
want to use AutoFilter where SUMIF on a helper column is not
zero for a specific description/account. Use of autofilter in
Debra Dalgleish's http://www.contextures.com

 

[Lefty_180]

I had the same issue, and got around it by adding 2 extra columns to m
worksheet. The first new column (Column W) finds the absolute value o
the positive or negative of the current row (=ABS(E6) - assumin
original data in E).
The second column (Column X) contains my initial matching criteria.
Here is the formula in its simpliest form.

=IF(ABS(SUMIF($W$6:$W$10000,$W6,$E$6:$E$10000))<0.005,"Matched","No
Yet")

The concept here is to "match" all lines with the same absolute valu
and sum the original (debits and credits) - if the result is less tha
.005 (with massive amounts of data, XL isn't perfect) call the lin
"Matched".

Hints: After copying the formulas and allow XL to calculate th
results, I copy and paste the values to allow XL to work faster.

I currently perform this initial type of reconciliation on account
with anywhere from 10 to 40,000 lines and it is able to weed out a lo
of the mondane matches for me