Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Excel - Match positive and negative numbers
Hello
I hope someone can assist me on this one. I was hoping it would be fairly easy for someone to write a macro which can look down a column U and find a positive number and then match this against a negative number of the same amount. Basically there should always be a positive and negative number. Where there is a match with say 34.28 and -34.28 then both rows should be highlighted. I want to basically find the rows where there is only either a positive number or a negative number with no match to the other side. There may well be two rows containing 34.28 and only one containing -34.28. I dont know how you can factor this in to the equation. Sorry if this is a rather complex bit of code....... Stuart |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Excel - Match positive and negative numbers
Stuart
How many +/- matches do you expect to find in these 2 columns? What if there's more than 1 pair, do you want them different colours? There may well be two rows containing 34.28 and only one containing -34.28. I dont know how you can factor this in to the equation. You have to tell us how to factor this in to equation, these are your numbers not ours and we can't decide what to do in this instance. Mike "Stuart" wrote: Hello I hope someone can assist me on this one. I was hoping it would be fairly easy for someone to write a macro which can look down a column U and find a positive number and then match this against a negative number of the same amount. Basically there should always be a positive and negative number. Where there is a match with say 34.28 and -34.28 then both rows should be highlighted. I want to basically find the rows where there is only either a positive number or a negative number with no match to the other side. There may well be two rows containing 34.28 and only one containing -34.28. I dont know how you can factor this in to the equation. Sorry if this is a rather complex bit of code....... Stuart |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Excel - Match positive and negative numbers
Do it with conditional formatting.
Select all target cells starting at A1 menu FormatConditional Formatting change Condition 1 To Formula Is enter a formula of =COUNTIF(A:A,-A1)=0 select the pattern tab choose a colour OK out -- __________________________________ HTH Bob "Stuart" wrote in message ... Hello I hope someone can assist me on this one. I was hoping it would be fairly easy for someone to write a macro which can look down a column U and find a positive number and then match this against a negative number of the same amount. Basically there should always be a positive and negative number. Where there is a match with say 34.28 and -34.28 then both rows should be highlighted. I want to basically find the rows where there is only either a positive number or a negative number with no match to the other side. There may well be two rows containing 34.28 and only one containing -34.28. I dont know how you can factor this in to the equation. Sorry if this is a rather complex bit of code....... Stuart |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Excel - Match positive and negative numbers
Hi Stuart,
You don't really need a macro if all you want is for the non-matching cell pairs to be highlighted - all you need is a conditional format based in the equation: =$A1+$B1<0 where A1 & B1 represent the addresses of the cell pair being tested. Simply enter this formula in the conditional format for the first to be tested, using the 'Formula is' option from the conditions dropdown and choose an appropriate pattern highlighting format, then copy this cell and paste over the whole range using Edit|Paste Special|Format. -- Cheers macropod [MVP - Microsoft Word] "Stuart" wrote in message ... Hello I hope someone can assist me on this one. I was hoping it would be fairly easy for someone to write a macro which can look down a column U and find a positive number and then match this against a negative number of the same amount. Basically there should always be a positive and negative number. Where there is a match with say 34.28 and -34.28 then both rows should be highlighted. I want to basically find the rows where there is only either a positive number or a negative number with no match to the other side. There may well be two rows containing 34.28 and only one containing -34.28. I dont know how you can factor this in to the equation. Sorry if this is a rather complex bit of code....... Stuart |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Excel - Match positive and negative numbers
Hi
Thanks for your responses There are several hundred rows. The £34.28 could be matching the -£34.28 on the 500th row. The rows are not in positive and negative order. They come in any order. I want to find pairs in the rows. Does this make sense? I am sure someone put together a macro a while back but cannot find the thread. Sorry to be a pain. Stuart They are not in any particular order. On Feb 26, 10:04*am, "macropod" wrote: Hi Stuart, You don't really need a macro if all you want is for the non-matching cell pairs to be highlighted - all you need is a conditional format based in the equation: =$A1+$B1<0 where A1 & B1 represent the addresses of the cell pair being tested. Simply enter this formula in the conditional format for the first to be tested, using the 'Formula is' option from the conditions dropdown and choose an appropriate pattern highlighting format, then copy this cell and paste over the whole range using Edit|Paste Special|Format. -- Cheers macropod [MVP - Microsoft Word] "Stuart" wrote in ... Hello I hope someone can assist me on this one. I was hoping it would be fairly easy for someone to write a macro which can look down a column U and find a positive number and then match this against a negative number of the same amount. Basically there should always be a positive and negative number. Where there is a match with say 34.28 and -34.28 then both rows should be highlighted. I want to basically find the rows where there is only either a positive number or a negative number with no match to the other side. There may well be two rows containing 34.28 and only one containing -34.28. *I dont know how you can factor this in to the equation. Sorry if this is a rather complex bit of code....... Stuart- Hide quoted text - - Show quoted text - |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Excel - Match positive and negative numbers
In B1: =ABS(A1)
copy down then sort by column B -- __________________________________ HTH Bob "Stuart" wrote in message ... Hi Thanks for your responses There are several hundred rows. The £34.28 could be matching the -£34.28 on the 500th row. The rows are not in positive and negative order. They come in any order. I want to find pairs in the rows. Does this make sense? I am sure someone put together a macro a while back but cannot find the thread. Sorry to be a pain. Stuart They are not in any particular order. On Feb 26, 10:04 am, "macropod" wrote: Hi Stuart, You don't really need a macro if all you want is for the non-matching cell pairs to be highlighted - all you need is a conditional format based in the equation: =$A1+$B1<0 where A1 & B1 represent the addresses of the cell pair being tested. Simply enter this formula in the conditional format for the first to be tested, using the 'Formula is' option from the conditions dropdown and choose an appropriate pattern highlighting format, then copy this cell and paste over the whole range using Edit|Paste Special|Format. -- Cheers macropod [MVP - Microsoft Word] "Stuart" wrote in ... Hello I hope someone can assist me on this one. I was hoping it would be fairly easy for someone to write a macro which can look down a column U and find a positive number and then match this against a negative number of the same amount. Basically there should always be a positive and negative number. Where there is a match with say 34.28 and -34.28 then both rows should be highlighted. I want to basically find the rows where there is only either a positive number or a negative number with no match to the other side. There may well be two rows containing 34.28 and only one containing -34.28. I dont know how you can factor this in to the equation. Sorry if this is a rather complex bit of code....... Stuart- Hide quoted text - - Show quoted text - |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Excel - Match positive and negative numbers
I am sorry everyone.....maybe I am not explaining correctly.
I have an account in which it has been debited with a positive number £34.28. I might later pay this in which case I would have a negative figure of -£34.28. I need the row containing the £34.28 and -£34.28 to be formatted in green if there is a match. If there is only the £34.28 then this will remain unhighlighted. I hope your able to assist me on this. Stuart On Feb 26, 11:14*am, "Bob Phillips" wrote: In B1: =ABS(A1) copy down then sort by column B -- __________________________________ HTH Bob "Stuart" wrote in message ... Hi Thanks for your responses There are several hundred rows. The £34.28 could be matching the -£34.28 on the 500th row. *The rows are not in positive and negative order. They come in any order. I want to find pairs in the rows. Does this make sense? I am sure someone put together a macro a while back but cannot find the thread. Sorry to be a pain. Stuart They are not in any particular order. On Feb 26, 10:04 am, "macropod" wrote: Hi Stuart, You don't really need a macro if all you want is for the non-matching cell pairs to be highlighted - all you need is a conditional format based in the equation: =$A1+$B1<0 where A1 & B1 represent the addresses of the cell pair being tested. Simply enter this formula in the conditional format for the first to be tested, using the 'Formula is' option from the conditions dropdown and choose an appropriate pattern highlighting format, then copy this cell and paste over the whole range using Edit|Paste Special|Format. -- Cheers macropod [MVP - Microsoft Word] "Stuart" wrote in ... Hello I hope someone can assist me on this one. I was hoping it would be fairly easy for someone to write a macro which can look down a column U and find a positive number and then match this against a negative number of the same amount. Basically there should always be a positive and negative number. Where there is a match with say 34.28 and -34.28 then both rows should be highlighted. I want to basically find the rows where there is only either a positive number or a negative number with no match to the other side. There may well be two rows containing 34.28 and only one containing -34.28. I dont know how you can factor this in to the equation. Sorry if this is a rather complex bit of code....... Stuart- Hide quoted text - - Show quoted text -- Hide quoted text - - Show quoted text - |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Excel - Match positive and negative numbers
Another suggestion
in column B add a formula of =SUMPRODUCT(--(ABS($A$1:$A$100)=ABS(A1)))1 copy that down and then sort columns A and B by column B. That way all the singlestons will be grouped, and all the multiples. -- __________________________________ HTH Bob "Stuart" wrote in message ... I am sorry everyone.....maybe I am not explaining correctly. I have an account in which it has been debited with a positive number £34.28. I might later pay this in which case I would have a negative figure of -£34.28. I need the row containing the £34.28 and -£34.28 to be formatted in green if there is a match. If there is only the £34.28 then this will remain unhighlighted. I hope your able to assist me on this. Stuart On Feb 26, 11:14 am, "Bob Phillips" wrote: In B1: =ABS(A1) copy down then sort by column B -- __________________________________ HTH Bob "Stuart" wrote in message ... Hi Thanks for your responses There are several hundred rows. The £34.28 could be matching the -£34.28 on the 500th row. The rows are not in positive and negative order. They come in any order. I want to find pairs in the rows. Does this make sense? I am sure someone put together a macro a while back but cannot find the thread. Sorry to be a pain. Stuart They are not in any particular order. On Feb 26, 10:04 am, "macropod" wrote: Hi Stuart, You don't really need a macro if all you want is for the non-matching cell pairs to be highlighted - all you need is a conditional format based in the equation: =$A1+$B1<0 where A1 & B1 represent the addresses of the cell pair being tested. Simply enter this formula in the conditional format for the first to be tested, using the 'Formula is' option from the conditions dropdown and choose an appropriate pattern highlighting format, then copy this cell and paste over the whole range using Edit|Paste Special|Format. -- Cheers macropod [MVP - Microsoft Word] "Stuart" wrote in ... Hello I hope someone can assist me on this one. I was hoping it would be fairly easy for someone to write a macro which can look down a column U and find a positive number and then match this against a negative number of the same amount. Basically there should always be a positive and negative number. Where there is a match with say 34.28 and -34.28 then both rows should be highlighted. I want to basically find the rows where there is only either a positive number or a negative number with no match to the other side. There may well be two rows containing 34.28 and only one containing -34.28. I dont know how you can factor this in to the equation. Sorry if this is a rather complex bit of code....... Stuart- Hide quoted text - - Show quoted text -- Hide quoted text - - Show quoted text - |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
Excel - Match positive and negative numbers
Hi Stuart,
That is a quite different problem. It also raise other issues. How would you propose to treat: .. two debits of the same amount, where there is only one credit of the same amount (ie only one debit has been paid off) .. payment of an amount by instalments .. payment of two or more debts in a single payment? -- Cheers macropod [MVP - Microsoft Word] "Stuart" wrote in message ... I am sorry everyone.....maybe I am not explaining correctly. I have an account in which it has been debited with a positive number £34.28. I might later pay this in which case I would have a negative figure of -£34.28. I need the row containing the £34.28 and -£34.28 to be formatted in green if there is a match. If there is only the £34.28 then this will remain unhighlighted. I hope your able to assist me on this. Stuart On Feb 26, 11:14 am, "Bob Phillips" wrote: In B1: =ABS(A1) copy down then sort by column B -- __________________________________ HTH Bob "Stuart" wrote in message ... Hi Thanks for your responses There are several hundred rows. The £34.28 could be matching the -£34.28 on the 500th row. The rows are not in positive and negative order. They come in any order. I want to find pairs in the rows. Does this make sense? I am sure someone put together a macro a while back but cannot find the thread. Sorry to be a pain. Stuart They are not in any particular order. On Feb 26, 10:04 am, "macropod" wrote: Hi Stuart, You don't really need a macro if all you want is for the non-matching cell pairs to be highlighted - all you need is a conditional format based in the equation: =$A1+$B1<0 where A1 & B1 represent the addresses of the cell pair being tested. Simply enter this formula in the conditional format for the first to be tested, using the 'Formula is' option from the conditions dropdown and choose an appropriate pattern highlighting format, then copy this cell and paste over the whole range using Edit|Paste Special|Format. -- Cheers macropod [MVP - Microsoft Word] "Stuart" wrote in ... Hello I hope someone can assist me on this one. I was hoping it would be fairly easy for someone to write a macro which can look down a column U and find a positive number and then match this against a negative number of the same amount. Basically there should always be a positive and negative number. Where there is a match with say 34.28 and -34.28 then both rows should be highlighted. I want to basically find the rows where there is only either a positive number or a negative number with no match to the other side. There may well be two rows containing 34.28 and only one containing -34.28. I dont know how you can factor this in to the equation. Sorry if this is a rather complex bit of code....... Stuart- Hide quoted text - - Show quoted text -- Hide quoted text - - Show quoted text - |
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
Excel - Match positive and negative numbers
Hi Stuart,
In my last reply, I took your clarification to imply that the £34.28 and -£34.28 might be on different rows. Correct? If they're on the same row, my applying the solution in my previous reply will work just fine. If not, you could take the same approach, but using the formula: =COUNTIF($B$1:$B$100,-$A1)0 in A1, and: =COUNTIF($A$1:$A$100,-$B1)0 in B1, where you're comparing data in A1:A100 against data in B1:B100. The caveats in my previous reply still apply, however, and you're liable to get false matches. This can partially be overcome by changing the '0' in both formulae to '=1' and setting up a second conditional format based on the same formula, but with '1' and a different colour to highlight any many-to-one matches. -- Cheers macropod [MVP - Microsoft Word] "macropod" wrote in message ... Hi Stuart, That is a quite different problem. It also raise other issues. How would you propose to treat: . two debits of the same amount, where there is only one credit of the same amount (ie only one debit has been paid off) . payment of an amount by instalments . payment of two or more debts in a single payment? -- Cheers macropod [MVP - Microsoft Word] "Stuart" wrote in message ... I am sorry everyone.....maybe I am not explaining correctly. I have an account in which it has been debited with a positive number £34.28. I might later pay this in which case I would have a negative figure of -£34.28. I need the row containing the £34.28 and -£34.28 to be formatted in green if there is a match. If there is only the £34.28 then this will remain unhighlighted. I hope your able to assist me on this. Stuart On Feb 26, 11:14 am, "Bob Phillips" wrote: In B1: =ABS(A1) copy down then sort by column B -- __________________________________ HTH Bob "Stuart" wrote in message ... Hi Thanks for your responses There are several hundred rows. The £34.28 could be matching the -£34.28 on the 500th row. The rows are not in positive and negative order. They come in any order. I want to find pairs in the rows. Does this make sense? I am sure someone put together a macro a while back but cannot find the thread. Sorry to be a pain. Stuart They are not in any particular order. On Feb 26, 10:04 am, "macropod" wrote: Hi Stuart, You don't really need a macro if all you want is for the non-matching cell pairs to be highlighted - all you need is a conditional format based in the equation: =$A1+$B1<0 where A1 & B1 represent the addresses of the cell pair being tested. Simply enter this formula in the conditional format for the first to be tested, using the 'Formula is' option from the conditions dropdown and choose an appropriate pattern highlighting format, then copy this cell and paste over the whole range using Edit|Paste Special|Format. -- Cheers macropod [MVP - Microsoft Word] "Stuart" wrote in ... Hello I hope someone can assist me on this one. I was hoping it would be fairly easy for someone to write a macro which can look down a column U and find a positive number and then match this against a negative number of the same amount. Basically there should always be a positive and negative number. Where there is a match with say 34.28 and -34.28 then both rows should be highlighted. I want to basically find the rows where there is only either a positive number or a negative number with no match to the other side. There may well be two rows containing 34.28 and only one containing -34.28. I dont know how you can factor this in to the equation. Sorry if this is a rather complex bit of code....... Stuart- Hide quoted text - - Show quoted text -- Hide quoted text - - Show quoted text - |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Excel - Adding positive or negative numbers in a column | Excel Discussion (Misc queries) | |||
Excel, change column of negative numbers to positive numbers? | New Users to Excel | |||
Excel 2002 : Convert Positive Numbers to Negative Numbers ? | Excel Discussion (Misc queries) | |||
How to switch negative numbers to positive numbers in Excel ? | Excel Worksheet Functions | |||
Convert a column of numbers from positive to negative in Excel | Excel Discussion (Misc queries) |