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

Sandip,

An easy way to group opposite numbers is to use a helper column. Lets' say you numbers are in column C. In cell D2, use the
formula =ABS(C2) and copy it down to match your numbers. Then sort your entire table based on column D to bring "opposites"
together. If you want to find pairs of opposites, you could then use another helper column: in E2, use the formula
=OR(C1+C2=0,C2+C3=0)
and copy down to match your data. Then filter your entire table on column E, showing only TRUE.

HTH,
Bernie
Excel MVP


"Sandip Shah" wrote in message om...
Hi,

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.

Is there a way through a macro or a function where I can highlight one
set of postive and negative number appearing in the list. Thus all the
numbers with the offset entry ( opposite sign ) should be highlighted.

For eg. If 100 is there on one row, and -100 on some other row (same
coloumn), both these numbers should get highlighted. Incase there is
another 100 with no offseting negative entry, it should remain
unhighlighted.

Your help would be appreciated.

Thanks
Sandip

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

Bernie, maybe you can help me out - I've been trying to get some code
together for this one, but it falls over, and I can't figure out
why.In column A I've been using the following numbers.A fair test I
reckon.:

100
-100
100
20
-20
50
-50
1.2
-1.2
1.2
1.2
1.2
-1.2
3
5
67
8
9
-9

The code I've got is as follows.It is supposed to make all matching
pairs of numbers bold and leave all unmatched numbers with a regular
font thickness.I would have prefered not to have used an 'On Error
Goto' statement but can't think of a way round it.Anyway it still
doesn't work and as you'll see it throws an error when it hits the
number 3.How come this error doesn't just move the routine to the
label "NoMatch:" ??

Dim myVar
Dim x As Integer
Dim y As Integer
Dim mycell

Sub FindMatch()

'assuming the numbers are in column A starting from A1 and that
there is
'a continuous column of numbers from top to bottom
'i.e no blank cells on the way down, just blanks after the data
has finished

[A1].Select 'to change to whichever column needed
y = [A1].End(xlDown).Row
Do Until IsEmpty(ActiveCell)
myVar = ActiveCell
x = ActiveCell.Row
If ActiveCell 0 Then
Set mycell = Range("A" & x & ":A" & y).Find(myVar * -1)
On Error GoTo NoMatch:
If mycell.Font.FontStyle < "Bold" Then
mycell.Font.FontStyle = "Bold"
ActiveCell.Font.FontStyle = "Bold"
End If
NoMatch:
End If
ActiveCell.Offset(1).Select
Loop

End Sub

Help greatly appreciated

Jason.

"Bernie Deitrick" wrote in message ...
Sandip,

An easy way to group opposite numbers is to use a helper column. Lets' say you numbers are in column C. In cell D2, use the
formula =ABS(C2) and copy it down to match your numbers. Then sort your entire table based on column D to bring "opposites"
together. If you want to find pairs of opposites, you could then use another helper column: in E2, use the formula
=OR(C1+C2=0,C2+C3=0)
and copy down to match your data. Then filter your entire table on column E, showing only TRUE.

HTH,
Bernie
Excel MVP


"Sandip Shah" wrote in message om...
Hi,

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.

Is there a way through a macro or a function where I can highlight one
set of postive and negative number appearing in the list. Thus all the
numbers with the offset entry ( opposite sign ) should be highlighted.

For eg. If 100 is there on one row, and -100 on some other row (same
coloumn), both these numbers should get highlighted. Incase there is
another 100 with no offseting negative entry, it should remain
unhighlighted.

Your help would be appreciated.

Thanks
Sandip

Highlight the debit and Credit ( positive and negative) numberswithin a column
 

How about a Format|conditional Formatting approach:

I pasted your numbers into A1:A19. I selected the range and with A1 active, I
did format|conditional formatting.
I used this as my formula:
=COUNTIF($A$1:$A$19,A1)1
and set the font to bold when it was true.

You can use countif inside your code, too:

Option Explicit
Sub testme01()

Dim myRange As Range
Dim myCell As Range
Dim wks As Worksheet

Set wks = ActiveSheet
With wks
Set myRange = .Range("a1", .Range("a1").End(xlDown))
End With
For Each myCell In myRange.Cells
If Application.CountIf(myRange, myCell.Value) 1 Then
myCell.Font.Bold = True
Else
myCell.Font.Bold = False
End If
Next myCell

End Sub


jason wrote:

Bernie, maybe you can help me out - I've been trying to get some code
together for this one, but it falls over, and I can't figure out
why.In column A I've been using the following numbers.A fair test I
reckon.:

100
-100
100
20
-20
50
-50
1.2
-1.2
1.2
1.2
1.2
-1.2
3
5
67
8
9
-9

The code I've got is as follows.It is supposed to make all matching
pairs of numbers bold and leave all unmatched numbers with a regular
font thickness.I would have prefered not to have used an 'On Error
Goto' statement but can't think of a way round it.Anyway it still
doesn't work and as you'll see it throws an error when it hits the
number 3.How come this error doesn't just move the routine to the
label "NoMatch:" ??

Dim myVar
Dim x As Integer
Dim y As Integer
Dim mycell

Sub FindMatch()

'assuming the numbers are in column A starting from A1 and that
there is
'a continuous column of numbers from top to bottom
'i.e no blank cells on the way down, just blanks after the data
has finished

[A1].Select 'to change to whichever column needed
y = [A1].End(xlDown).Row
Do Until IsEmpty(ActiveCell)
myVar = ActiveCell
x = ActiveCell.Row
If ActiveCell 0 Then
Set mycell = Range("A" & x & ":A" & y).Find(myVar * -1)
On Error GoTo NoMatch:
If mycell.Font.FontStyle < "Bold" Then
mycell.Font.FontStyle = "Bold"
ActiveCell.Font.FontStyle = "Bold"
End If
NoMatch:
End If
ActiveCell.Offset(1).Select
Loop

End Sub

Help greatly appreciated

Jason.

"Bernie Deitrick" wrote in message ...
Sandip,

An easy way to group opposite numbers is to use a helper column. Lets' say you numbers are in column C. In cell D2, use the
formula =ABS(C2) and copy it down to match your numbers. Then sort your entire table based on column D to bring "opposites"
together. If you want to find pairs of opposites, you could then use another helper column: in E2, use the formula
=OR(C1+C2=0,C2+C3=0)
and copy down to match your data. Then filter your entire table on column E, showing only TRUE.

HTH,
Bernie
Excel MVP


"Sandip Shah" wrote in message om...
Hi,

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.

Is there a way through a macro or a function where I can highlight one
set of postive and negative number appearing in the list. Thus all the
numbers with the offset entry ( opposite sign ) should be highlighted.

For eg. If 100 is there on one row, and -100 on some other row (same
coloumn), both these numbers should get highlighted. Incase there is
another 100 with no offseting negative entry, it should remain
unhighlighted.

Your help would be appreciated.

Thanks
Sandip

--

Dave Peterson

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

Hi Jason,

I have read though your code and realise that the stated error
happened when the Find function fails ie. at 3 because there is no -3
to match. If by no mistakes of mine, the error handler (NoMatch) is
not useful in catching this problem and actually affects the
syntax/flow of your code.

To avoid the error, you can try making slight changes to the following
portion :

If ActiveCell 0 Then
If Not (Range("A" & x & ":A" & y).Find(myVar * -1) Is Nothing) Then
Set mycell = Range("A" & x & ":A" & y).Find(myVar * -1)
If mycell.Font.FontStyle < "Bold" Then
mycell.Font.FontStyle = "Bold"
ActiveCell.Font.FontStyle = "Bold"
End If
End If
End If

Another pointed noted is that the above codes will only run ideally in
your stimulated data when the negative counter lies below the
positive. Try re-sorting the data in ascending/descending order, the
code will not run correctly.

I will suggest the following codes instead :

Sub ModifyFindMatch()

Dim myVar
Dim x As Integer
Dim y As Integer
Dim mycell

ActiveSheet.Range("A1").Select 'to change to whichever start cell
needed
y = ActiveSheet.Range("A1").End(xlDown).Row - ActiveCell.Row
Do Until IsEmpty(ActiveCell)
myVar = ActiveCell
For i = 1 To y
If ActiveCell.Offset(i).Value = -1 * myVar Then
Set mycell = ActiveCell.Offset(i)
If mycell.Font.FontStyle < "Bold" Then
mycell.Font.FontStyle = "Bold"
ActiveCell.Font.FontStyle = "Bold"
Exit For
End If
End If
Next i
ActiveCell.Offset(1).Select
Loop

End Sub

Hopefully, the above is of help to ya. ^_^

Regards
Jo Lee

-----------------------------------------------------------------------------
(jason) wrote in message . com...
Bernie, maybe you can help me out - I've been trying to get some code
together for this one, but it falls over, and I can't figure out
why.In column A I've been using the following numbers.A fair test I
reckon.:

100
-100
100
20
-20
50
-50
1.2
-1.2
1.2
1.2
1.2
-1.2
3
5
67
8
9
-9

The code I've got is as follows.It is supposed to make all matching
pairs of numbers bold and leave all unmatched numbers with a regular
font thickness.I would have prefered not to have used an 'On Error
Goto' statement but can't think of a way round it.Anyway it still
doesn't work and as you'll see it throws an error when it hits the
number 3.How come this error doesn't just move the routine to the
label "NoMatch:" ??

Dim myVar
Dim x As Integer
Dim y As Integer
Dim mycell

Sub FindMatch()

'assuming the numbers are in column A starting from A1 and that
there is
'a continuous column of numbers from top to bottom
'i.e no blank cells on the way down, just blanks after the data
has finished

[A1].Select 'to change to whichever column needed
y = [A1].End(xlDown).Row
Do Until IsEmpty(ActiveCell)
myVar = ActiveCell
x = ActiveCell.Row
If ActiveCell 0 Then
Set mycell = Range("A" & x & ":A" & y).Find(myVar * -1)
On Error GoTo NoMatch:
If mycell.Font.FontStyle < "Bold" Then
mycell.Font.FontStyle = "Bold"
ActiveCell.Font.FontStyle = "Bold"
End If
NoMatch:
End If
ActiveCell.Offset(1).Select
Loop

End Sub

Help greatly appreciated

Jason.

"Bernie Deitrick" wrote in message ...
Sandip,

An easy way to group opposite numbers is to use a helper column. Lets' say you numbers are in column C. In cell D2, use the
formula =ABS(C2) and copy it down to match your numbers. Then sort your entire table based on column D to bring "opposites"
together. If you want to find pairs of opposites, you could then use another helper column: in E2, use the formula
=OR(C1+C2=0,C2+C3=0)
and copy down to match your data. Then filter your entire table on column E, showing only TRUE.

HTH,
Bernie
Excel MVP


"Sandip Shah" wrote in message om...
Hi,

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.

Is there a way through a macro or a function where I can highlight one
set of postive and negative number appearing in the list. Thus all the
numbers with the offset entry ( opposite sign ) should be highlighted.

For eg. If 100 is there on one row, and -100 on some other row (same
coloumn), both these numbers should get highlighted. Incase there is
another 100 with no offseting negative entry, it should remain
unhighlighted.

Your help would be appreciated.

Thanks
Sandip

Highlight the debit and Credit ( positive and negative) numberswithin a column
 

I read Jason's post and missed the part about matching the opposites.

the worksheet CF formula would change to:
=COUNTIF($A$1:$A$19,-A1)1

This line in code could change:
If Application.CountIf(myRange, -myCell.Value) 1 Then

Dave Peterson wrote:

How about a Format|conditional Formatting approach:

I pasted your numbers into A1:A19. I selected the range and with A1 active, I
did format|conditional formatting.
I used this as my formula:
=COUNTIF($A$1:$A$19,A1)1
and set the font to bold when it was true.

You can use countif inside your code, too:

Option Explicit
Sub testme01()

Dim myRange As Range
Dim myCell As Range
Dim wks As Worksheet

Set wks = ActiveSheet
With wks
Set myRange = .Range("a1", .Range("a1").End(xlDown))
End With
For Each myCell In myRange.Cells
If Application.CountIf(myRange, myCell.Value) 1 Then
myCell.Font.Bold = True
Else
myCell.Font.Bold = False
End If
Next myCell

End Sub

jason wrote:

Bernie, maybe you can help me out - I've been trying to get some code
together for this one, but it falls over, and I can't figure out
why.In column A I've been using the following numbers.A fair test I
reckon.:

100
-100
100
20
-20
50
-50
1.2
-1.2
1.2
1.2
1.2
-1.2
3
5
67
8
9
-9

The code I've got is as follows.It is supposed to make all matching
pairs of numbers bold and leave all unmatched numbers with a regular
font thickness.I would have prefered not to have used an 'On Error
Goto' statement but can't think of a way round it.Anyway it still
doesn't work and as you'll see it throws an error when it hits the
number 3.How come this error doesn't just move the routine to the
label "NoMatch:" ??

Dim myVar
Dim x As Integer
Dim y As Integer
Dim mycell

Sub FindMatch()

'assuming the numbers are in column A starting from A1 and that
there is
'a continuous column of numbers from top to bottom
'i.e no blank cells on the way down, just blanks after the data
has finished

[A1].Select 'to change to whichever column needed
y = [A1].End(xlDown).Row
Do Until IsEmpty(ActiveCell)
myVar = ActiveCell
x = ActiveCell.Row
If ActiveCell 0 Then
Set mycell = Range("A" & x & ":A" & y).Find(myVar * -1)
On Error GoTo NoMatch:
If mycell.Font.FontStyle < "Bold" Then
mycell.Font.FontStyle = "Bold"
ActiveCell.Font.FontStyle = "Bold"
End If
NoMatch:
End If
ActiveCell.Offset(1).Select
Loop

End Sub

Help greatly appreciated

Jason.

"Bernie Deitrick" wrote in message ...
Sandip,

An easy way to group opposite numbers is to use a helper column. Lets' say you numbers are in column C. In cell D2, use the
formula =ABS(C2) and copy it down to match your numbers. Then sort your entire table based on column D to bring "opposites"
together. If you want to find pairs of opposites, you could then use another helper column: in E2, use the formula
=OR(C1+C2=0,C2+C3=0)
and copy down to match your data. Then filter your entire table on column E, showing only TRUE.

HTH,
Bernie
Excel MVP


"Sandip Shah" wrote in message om...
Hi,

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.

Is there a way through a macro or a function where I can highlight one
set of postive and negative number appearing in the list. Thus all the
numbers with the offset entry ( opposite sign ) should be highlighted.

For eg. If 100 is there on one row, and -100 on some other row (same
coloumn), both these numbers should get highlighted. Incase there is
another 100 with no offseting negative entry, it should remain
unhighlighted.

Your help would be appreciated.

Thanks
Sandip

--

Dave Peterson


--

Dave Peterson

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

Cheers Jo Lee - that sorts my logic out.Should be able to adapt the
new code to a couple of things I've been meaning to do

Thanks again
Jason

(Jo Lee) wrote in message . com...
Hi Jason,

I have read though your code and realise that the stated error
happened when the Find function fails ie. at 3 because there is no -3
to match. If by no mistakes of mine, the error handler (NoMatch) is
not useful in catching this problem and actually affects the
syntax/flow of your code.

To avoid the error, you can try making slight changes to the following
portion :

If ActiveCell 0 Then
If Not (Range("A" & x & ":A" & y).Find(myVar * -1) Is Nothing) Then
Set mycell = Range("A" & x & ":A" & y).Find(myVar * -1)
If mycell.Font.FontStyle < "Bold" Then
mycell.Font.FontStyle = "Bold"
ActiveCell.Font.FontStyle = "Bold"
End If
End If
End If

Another pointed noted is that the above codes will only run ideally in
your stimulated data when the negative counter lies below the
positive. Try re-sorting the data in ascending/descending order, the
code will not run correctly.

I will suggest the following codes instead :

Sub ModifyFindMatch()

Dim myVar
Dim x As Integer
Dim y As Integer
Dim mycell

ActiveSheet.Range("A1").Select 'to change to whichever start cell
needed
y = ActiveSheet.Range("A1").End(xlDown).Row - ActiveCell.Row
Do Until IsEmpty(ActiveCell)
myVar = ActiveCell
For i = 1 To y
If ActiveCell.Offset(i).Value = -1 * myVar Then
Set mycell = ActiveCell.Offset(i)
If mycell.Font.FontStyle < "Bold" Then
mycell.Font.FontStyle = "Bold"
ActiveCell.Font.FontStyle = "Bold"
Exit For
End If
End If
Next i
ActiveCell.Offset(1).Select
Loop

End Sub

Hopefully, the above is of help to ya. ^_^

Regards
Jo Lee

-----------------------------------------------------------------------------
(jason) wrote in message . com...
Bernie, maybe you can help me out - I've been trying to get some code
together for this one, but it falls over, and I can't figure out
why.In column A I've been using the following numbers.A fair test I
reckon.:

100
-100
100
20
-20
50
-50
1.2
-1.2
1.2
1.2
1.2
-1.2
3
5
67
8
9
-9

The code I've got is as follows.It is supposed to make all matching
pairs of numbers bold and leave all unmatched numbers with a regular
font thickness.I would have prefered not to have used an 'On Error
Goto' statement but can't think of a way round it.Anyway it still
doesn't work and as you'll see it throws an error when it hits the
number 3.How come this error doesn't just move the routine to the
label "NoMatch:" ??

Dim myVar
Dim x As Integer
Dim y As Integer
Dim mycell

Sub FindMatch()

'assuming the numbers are in column A starting from A1 and that
there is
'a continuous column of numbers from top to bottom
'i.e no blank cells on the way down, just blanks after the data
has finished

[A1].Select 'to change to whichever column needed
y = [A1].End(xlDown).Row
Do Until IsEmpty(ActiveCell)
myVar = ActiveCell
x = ActiveCell.Row
If ActiveCell 0 Then
Set mycell = Range("A" & x & ":A" & y).Find(myVar * -1)
On Error GoTo NoMatch:
If mycell.Font.FontStyle < "Bold" Then
mycell.Font.FontStyle = "Bold"
ActiveCell.Font.FontStyle = "Bold"
End If
NoMatch:
End If
ActiveCell.Offset(1).Select
Loop

End Sub

Help greatly appreciated

Jason.

"Bernie Deitrick" wrote in message ...
Sandip,

An easy way to group opposite numbers is to use a helper column. Lets' say you numbers are in column C. In cell D2, use the
formula =ABS(C2) and copy it down to match your numbers. Then sort your entire table based on column D to bring "opposites"
together. If you want to find pairs of opposites, you could then use another helper column: in E2, use the formula
=OR(C1+C2=0,C2+C3=0)
and copy down to match your data. Then filter your entire table on column E, showing only TRUE.

HTH,
Bernie
Excel MVP


"Sandip Shah" wrote in message om...
Hi,

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.

Is there a way through a macro or a function where I can highlight one
set of postive and negative number appearing in the list. Thus all the
numbers with the offset entry ( opposite sign ) should be highlighted.

For eg. If 100 is there on one row, and -100 on some other row (same
coloumn), both these numbers should get highlighted. Incase there is
another 100 with no offseting negative entry, it should remain
unhighlighted.

Your help would be appreciated.

Thanks
Sandip