Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Highlight the debit and Credit ( positive and negative) numbers within a column
"David McRitchie" wrote in message ...
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...tFormatExample 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). --- HTH, David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001] My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm Search Page: http://www.mvps.org/dmcritchie/excel/search.htm "Sandip Shah" wrote... 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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Highlight the debit and Credit ( positive and negative) numbers within a column
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: http://www.mvps.org/dmcritchie/excel/condfmt.htm 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 --- HTH, David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001] My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm Search Page: http://www.mvps.org/dmcritchie/excel/search.htm "Sandip Shah" wrote... 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 original 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. "David McRitchie" wrote 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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Highlight the debit and Credit ( positive and negative) numbers within a column
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 ----------------------------------------------- ~~ Message posted from http://www.ExcelTip.com ~~View and post usenet messages directly from http://www.ExcelForum.com |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
I have a column of numbers, some positive, some negative. | Excel Worksheet Functions | |||
I have a column of numbers, some negative, some positive | Excel Worksheet Functions | |||
Excel, change column of negative numbers to positive numbers? | New Users to Excel | |||
Highlight the debit and Credit ( positive and negative) numbers within a column | Excel Programming | |||
Highlight the debit and Credit ( positive and negative) numbers within a column | Excel Programming |