Excel - Match positive and negative numbers

[Stuart]

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

 

[Mike H]

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

 

[Bob Phillips]

Do it with conditional formatting.

Select all target cells starting at A1

menu Format>Conditional 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

 

[macropod]

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.

 

[Stuart]

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.

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]

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 -

 

[Bob Phillips]

In B1: =ABS(A1)

copy down then sort by column B

--
__________________________________
HTH

Bob

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.

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]

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 -

 

[Stuart]

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

 

[Bob Phillips]

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

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

 

[macropod]

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]


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

 

[macropod]

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]

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]


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

In B1: =ABS(A1)

copy down then sort by column B

--
__________________________________
HTH

Bob


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.













- Show quoted text -