Looking For A Formula

  • Thread starter Thread starter qcan
  • Start date Start date
Q

qcan

I have pondered this for a while now. I cannot think of a proper
formula to do the job.

Simply put - I have about 20,000 rows with 2 columns per set. I want
to somehow identify when a number greater than zero first appears - at
which point, I would like to denote it with an X in column A.. There
would be a maximum of 30 columns in each set. The way it would search
for the number per set, is to start in cell B1. If B1 is greater than
zero, then X is to be place in. If not, it would go down to B2. If not
again, then back up to C1.... then C2 right to a maximum of cell AE2.
it will eventually encounter a number greater than zero. After it
encounters is it would then go to the next set directly below which
would be B3 and B4 etc....

Here are a few examples (I have shortened the columns to 7 for
illustaration purposes only):


A B C D E
F G H
X 0 0 0 0 2 3 0
0 0 0 0 0 0 1

0 0 0 0 0 0 0
X 0 0 1 0 0 1 0

X 0 0 0 0 0 0 1
0 0 0 0 0 0 0

X 0 3 0 3 1 0 0
0 2 0 1 0 0 0

0 0 0 0 0 0 0
X 1 0 0 3 3 0 0

X 0 0 1 1 1 0 0
0 0 0 0 0 0 0

Thanks in advance !
 
Try...

Sub DenoteCellsGreaterTanZero()
Dim r
For r = 1 To ActiveSheet.UsedRange.Rows.Count
If Application.WorksheetFunction.CountIf(Rows(r), ">0") > 0 _
Then Cells(r, 1) = "x"
Next 'r
End Sub
 
GS explained on 9/2/2011 :
Try...

Sub DenoteCellsGreaterTanZero()
Dim r
For r = 1 To ActiveSheet.UsedRange.Rows.Count
If Application.WorksheetFunction.CountIf(Rows(r), ">0") > 0 _
Then Cells(r, 1) = "x"
Next 'r
End Sub
Click to expand...

Note that this is a programming group!
BUT
Since you asked for a formula...

=IF(COUNTIF(B1:AE1,">0"),"x","")
 
GS explained on 9/2/2011 :



Note that this is a programming group!
BUT
Since you asked for a formula...

  =IF(COUNTIF(B1:AE1,">0"),"x","")

--
Garry

Free usenet access athttp://www.eternal-september.org
ClassicVB Users Regroup! comp.lang.basic.visual.misc
Click to expand...

Hey Gary,

I tried both the formula and the macro that you created. Maybe I am
doing something wrong, but it is always returning an "X" in every row
as it will eventually find a number greater than zero. I don't think I
was clear enough in my explanation. What I wanted was an "X" in either
one row or another only once it encounters the number greater than
zero. Therefore, only each set of two rows will contain an "X".... not
both - and of course it must search in the pattern that I previouisly
stipulated.
 
qcan formulated on Friday :
Hey Gary,

I tried both the formula and the macro that you created. Maybe I am
doing something wrong, but it is always returning an "X" in every row
as it will eventually find a number greater than zero. I don't think I
was clear enough in my explanation. What I wanted was an "X" in either
one row or another only once it encounters the number greater than
zero. Therefore, only each set of two rows will contain an "X".... not
both - and of course it must search in the pattern that I previouisly
stipulated.
Click to expand...

Ok, so you want it to place an x in the first row of the pair that it
finds a number greater than zero, AND if the 1st row is that row then
ignore the 2nd row.[?] Otherwise, put the x in the 2nd row.[?]

Is Row1 the first row? (ie: sets= 1,2; 3,4; 5,6; ...) OR does the data
start in Row2?

Try...

Sub DenoteCellsGreaterThanZero2()
Dim r As Long
Dim b1 As Boolean, b2 As Boolean
For r = 1 To ActiveSheet.UsedRange.Rows.Count Step 2
With Application.WorksheetFunction
b1 = (.CountIf(Rows(r), ">0") > 0)
b2 = (.CountIf(Rows(r).Offset(1), ">0") > 0)
End With 'Application.WorksheetFunction
If b1 Then Cells(r, 1) = "x" Else Cells(r, 1).Offset(1) = "x"
Next 'r
End Sub
 
Uh, here's a revision that takes into account neither row of a set has
a number >0...

Sub DenoteCellsGreaterThanZero2()
Dim r As Long
Dim b1 As Boolean, b2 As Boolean
For r = 1 To ActiveSheet.UsedRange.Rows.Count Step 2
With Application.WorksheetFunction
b1 = (.CountIf(Rows(r), ">0") > 0)
b2 = (.CountIf(Rows(r).Offset(1), ">0") > 0)
End With 'Application.WorksheetFunction
If b1 Then Cells(r, 1) = "x": GoTo nextset
If b2 Then Cells(r, 1).Offset(1) = "x"
nextset:
Next 'r
End Sub
 
Uh, here's a revision that takes into account neither row of a set has
a number >0...

Sub DenoteCellsGreaterThanZero2()
  Dim r As Long
  Dim b1 As Boolean, b2 As Boolean
  For r = 1 To ActiveSheet.UsedRange.Rows.Count Step 2
    With Application.WorksheetFunction
      b1 = (.CountIf(Rows(r), ">0") > 0)
      b2 = (.CountIf(Rows(r).Offset(1), ">0") > 0)
    End With 'Application.WorksheetFunction
    If b1 Then Cells(r, 1) = "x": GoTo nextset
    If b2 Then Cells(r, 1).Offset(1) = "x"
nextset:
  Next 'r
End Sub

--
Garry

Free usenet access athttp://www.eternal-september.org
ClassicVB Users Regroup! comp.lang.basic.visual.misc
Click to expand...

Hey Gary,

Close, but not quite. I tried your macro. The macro runs, but it is
not returning the correct info. To answer your questions.....

Ok, so you want it to place an x in the first row of the pair that it
finds a number greater than zero, AND if the 1st row is that row then
ignore the 2nd row.[?]

*** Correct. The same applies if it encounters a number greater than
zero for the first time in the second row, if it still has not
encountered a number greater than zero in the previous first row. In
other words an "X" will be placed in either the first row or second
row - but never both.

Example:

00000174090
X 00004645000

Is Row1 the first row? (ie: sets= 1,2; 3,4; 5,6; ...)

*** Yes, it starts in row 1, and yes, the sets are 1,2; 3,4; 5,6
etc....
 
qcan brought next idea :
Uh, here's a revision that takes into account neither row of a set has
a number >0...

Sub DenoteCellsGreaterThanZero2()
  Dim r As Long
  Dim b1 As Boolean, b2 As Boolean
  For r = 1 To ActiveSheet.UsedRange.Rows.Count Step 2
    With Application.WorksheetFunction
      b1 = (.CountIf(Rows(r), ">0") > 0)
      b2 = (.CountIf(Rows(r).Offset(1), ">0") > 0)
    End With 'Application.WorksheetFunction
    If b1 Then Cells(r, 1) = "x": GoTo nextset
    If b2 Then Cells(r, 1).Offset(1) = "x"
nextset:
  Next 'r
End Sub

--
Garry

Free usenet access athttp://www.eternal-september.org
ClassicVB Users Regroup! comp.lang.basic.visual.misc
Click to expand...

Hey Gary,

Close, but not quite. I tried your macro. The macro runs, but it is
not returning the correct info. To answer your questions.....

Ok, so you want it to place an x in the first row of the pair that it
finds a number greater than zero, AND if the 1st row is that row then
ignore the 2nd row.[?]

*** Correct. The same applies if it encounters a number greater than
zero for the first time in the second row, if it still has not
encountered a number greater than zero in the previous first row. In
other words an "X" will be placed in either the first row or second
row - but never both.
Click to expand...

That's EXACTLY what the macro does!!!
Example:

00000174090
X 00004645000
Click to expand...

This example contradicts your explanation! CLEARLY, the 1st row
contains a number >0 and so the code would put the x in that row and
ignore the 2nd row.

Example:
00000
00001 '//this row gets the x

00001 '//this row gets the x
11111 '//this row gets ignored because above row is 1st row >0
 
I have pondered this for a while now. I cannot think of a proper
formula to do the job.

Simply put - I have about 20,000 rows with 2 columns per set. I want
to somehow identify when a number greater than zero first appears - at
which point, I would like to denote it with an X in column A.. There
would be a maximum of 30 columns in each set. The way it would search
for the number per set, is to start in cell B1. If B1 is greater than
zero, then X is to be place in. If not, it would go down to B2. If not
again, then back up to C1.... then C2 right to a maximum of cell AE2.
it will eventually encounter a number greater than zero. After it
encounters is it would then go to the next set directly below which
would be B3 and B4 etc....

Here are a few examples (I have shortened the columns to 7 for
illustaration purposes only):

A      B        C                 D                E
F                 G                 H
X      0        0       0       0       2      3       0
         0      0       0       0      0       0       1

         0      0       0       0      0       0       0
X      0        0       1       0       0      1       0

X      0        0       0       0       0      0       1
         0      0       0       0      0       0       0

X      0        3       0       3       1      0       0
         0      2       0       1      0       0       0

         0      0       0       0      0       0       0
 X     1        0       0       3       3      0       0

 X     0        0       1       1       1      0       0
         0      0       0       0      0       0       0

Thanks in advance !
Click to expand...

Insert a row at the top and put this in row2 and copy down
=IF(AND(COUNTIF(B2:H2,">"&0),a1<>"x"),"x","")
 
qcan brought next idea :




Hey Gary,
Click to expand...
Close, but not quite. I tried your macro. The macro runs, but it is
not returning the correct info. To answer your questions.....
Click to expand...
Ok, so you want it to place an x in the first row of the pair that it
finds a number greater than zero, AND if the 1st row is that row then
ignore the 2nd row.[?]
Click to expand...
*** Correct. The same applies if it encounters a number greater than
zero for the first time in the second row, if it still has not
encountered a number greater than zero in the previous first row. In
other words an "X" will be placed in either the first row or second
row - but never both.
Click to expand...

That's EXACTLY what the macro does!!!
Example:
Click to expand...
  00000174090
X 00004645000
Click to expand...

This example contradicts your explanation! CLEARLY, the 1st row
contains a number >0 and so the code would put the x in that row and
ignore the 2nd row.

Example:
  00000
  00001  '//this row gets the x

  00001  '//this row gets the x
  11111  '//this row gets ignored because above row is 1st row >0
Is Row1 the first row? (ie: sets= 1,2; 3,4; 5,6; ...)
Click to expand...
*** Yes, it starts in row 1, and yes, the sets are 1,2; 3,4; 5,6
etc....
Click to expand...

--
Garry

Free usenet access athttp://www.eternal-september.org
ClassicVB Users Regroup! comp.lang.basic.visual.misc- Hide quoted text -

- Show quoted text -
Click to expand...

Hi Gary,

No contradiction. I guess I am guilty of not being clear enough.

As for your two examples. Yes, the first is correct. However, The
second is not. The "X" would go to the second row.

The search for the first number greater than zero works in a kind of a
zig zag pattern. Keeping in mind that the search starts with B1 then
(if it finds a zero) it goes DOWN to B2.. (if it again finds a zero)
the search goes to C1..(if it again finds a zero) it goes to C2 and so
on and so forth.
 
qcan brought next idea :
Uh, here's a revision that takes into account neither row of a set has
a number >0...
Sub DenoteCellsGreaterThanZero2()
  Dim r As Long
  Dim b1 As Boolean, b2 As Boolean
  For r = 1 To ActiveSheet.UsedRange.Rows.Count Step 2
    With Application.WorksheetFunction
      b1 = (.CountIf(Rows(r), ">0") > 0)
      b2 = (.CountIf(Rows(r).Offset(1), ">0") > 0)
    End With 'Application.WorksheetFunction
    If b1 Then Cells(r, 1) = "x": GoTo nextset
    If b2 Then Cells(r, 1).Offset(1) = "x"
nextset:
  Next 'r
End Sub
--
Garry
Free usenet access athttp://www.eternal-september.org
ClassicVB Users Regroup! comp.lang.basic.visual.misc
Hey Gary,
Close, but not quite. I tried your macro. The macro runs, but it is
not returning the correct info. To answer your questions.....
Ok, so you want it to place an x in the first row of the pair that it
finds a number greater than zero, AND if the 1st row is that row then
ignore the 2nd row.[?]
*** Correct. The same applies if it encounters a number greater than
zero for the first time in the second row, if it still has not
encountered a number greater than zero in the previous first row. In
other words an "X" will be placed in either the first row or second
row - but never both.
Click to expand...
Click to expand...
That's EXACTLY what the macro does!!!
Click to expand...
This example contradicts your explanation! CLEARLY, the 1st row
contains a number >0 and so the code would put the x in that row and
ignore the 2nd row.
Click to expand...
Example:
  00000
  00001  '//this row gets the x
Click to expand...
  00001  '//this row gets the x
  11111  '//this row gets ignored because above row is 1st row >0
Free usenet access athttp://www.eternal-september.org
ClassicVB Users Regroup! comp.lang.basic.visual.misc- Hide quoted text -
Click to expand...
- Show quoted text -
Click to expand...

Hi Gary,

No contradiction. I guess I am guilty of not being clear enough.

As for your two examples. Yes, the first is correct. However, The
second is not. The "X" would go to the second row.

The search for the first number greater than zero works in a kind of a
zig zag pattern. Keeping in mind that the search starts with B1 then
(if it finds a zero) it goes DOWN to B2.. (if it again finds a zero)
the search goes to C1..(if it again finds a zero) it goes to C2 and so
on and so forth.
Click to expand...

Did you try what I offered?
 
qcan has brought this to us :
qcan brought next idea :




Uh, here's a revision that takes into account neither row of a set has
a number >0...
Click to expand...
Sub DenoteCellsGreaterThanZero2()
  Dim r As Long
  Dim b1 As Boolean, b2 As Boolean
  For r = 1 To ActiveSheet.UsedRange.Rows.Count Step 2
    With Application.WorksheetFunction
      b1 = (.CountIf(Rows(r), ">0") > 0)
      b2 = (.CountIf(Rows(r).Offset(1), ">0") > 0)
    End With 'Application.WorksheetFunction
    If b1 Then Cells(r, 1) = "x": GoTo nextset
    If b2 Then Cells(r, 1).Offset(1) = "x"
nextset:
  Next 'r
End Sub
Free usenet access athttp://www.eternal-september.org
ClassicVB Users Regroup! comp.lang.basic.visual.misc
Hey Gary,
Click to expand...
Close, but not quite. I tried your macro. The macro runs, but it is
not returning the correct info. To answer your questions.....
Ok, so you want it to place an x in the first row of the pair that it
finds a number greater than zero, AND if the 1st row is that row then
ignore the 2nd row.[?]
Click to expand...
*** Correct. The same applies if it encounters a number greater than
zero for the first time in the second row, if it still has not
encountered a number greater than zero in the previous first row. In
other words an "X" will be placed in either the first row or second
row - but never both.
Click to expand...

That's EXACTLY what the macro does!!!
Example:
Click to expand...
  00000174090
X 00004645000
Click to expand...

This example contradicts your explanation! CLEARLY, the 1st row
contains a number >0 and so the code would put the x in that row and
ignore the 2nd row.

Example:
  00000
  00001  '//this row gets the x

  00001  '//this row gets the x
  11111  '//this row gets ignored because above row is 1st row >0
Is Row1 the first row? (ie: sets= 1,2; 3,4; 5,6; ...)
*** Yes, it starts in row 1, and yes, the sets are 1,2; 3,4; 5,6
etc....
Click to expand...

--
Garry

Free usenet access athttp://www.eternal-september.org
ClassicVB Users Regroup! comp.lang.basic.visual.misc- Hide quoted text -

- Show quoted text -
Click to expand...

Hi Gary,

No contradiction. I guess I am guilty of not being clear enough.

As for your two examples. Yes, the first is correct. However, The
second is not. The "X" would go to the second row.

The search for the first number greater than zero works in a kind of a
zig zag pattern. Keeping in mind that the search starts with B1 then
(if it finds a zero) it goes DOWN to B2.. (if it again finds a zero)
the search goes to C1..(if it again finds a zero) it goes to C2 and so
on and so forth.
Click to expand...

And this is exactly what the code does!!! It will only put an x in the
1st find <0 per pair. If no find then no x in either! Not sure what
you're doing that it doesn't work that way for you but that's how it
works here!
 
qcan expressed precisely :
qcan brought next idea :




Uh, here's a revision that takes into account neither row of a set has
a number >0...
Click to expand...
Sub DenoteCellsGreaterThanZero2()
  Dim r As Long
  Dim b1 As Boolean, b2 As Boolean
  For r = 1 To ActiveSheet.UsedRange.Rows.Count Step 2
    With Application.WorksheetFunction
      b1 = (.CountIf(Rows(r), ">0") > 0)
      b2 = (.CountIf(Rows(r).Offset(1), ">0") > 0)
    End With 'Application.WorksheetFunction
    If b1 Then Cells(r, 1) = "x": GoTo nextset
    If b2 Then Cells(r, 1).Offset(1) = "x"
nextset:
  Next 'r
End Sub
Free usenet access athttp://www.eternal-september.org
ClassicVB Users Regroup! comp.lang.basic.visual.misc
Hey Gary,
Click to expand...
Close, but not quite. I tried your macro. The macro runs, but it is
not returning the correct info. To answer your questions.....
Ok, so you want it to place an x in the first row of the pair that it
finds a number greater than zero, AND if the 1st row is that row then
ignore the 2nd row.[?]
Click to expand...
*** Correct. The same applies if it encounters a number greater than
zero for the first time in the second row, if it still has not
encountered a number greater than zero in the previous first row. In
other words an "X" will be placed in either the first row or second
row - but never both.
Click to expand...

That's EXACTLY what the macro does!!!
Example:
Click to expand...
  00000174090
X 00004645000
Click to expand...

This example contradicts your explanation! CLEARLY, the 1st row
contains a number >0 and so the code would put the x in that row and
ignore the 2nd row.

Example:
  00000
  00001  '//this row gets the x

  00001  '//this row gets the x
  11111  '//this row gets ignored because above row is 1st row >0
Is Row1 the first row? (ie: sets= 1,2; 3,4; 5,6; ...)
*** Yes, it starts in row 1, and yes, the sets are 1,2; 3,4; 5,6
etc....
Click to expand...

--
Garry

Free usenet access athttp://www.eternal-september.org
ClassicVB Users Regroup! comp.lang.basic.visual.misc- Hide quoted text -

- Show quoted text -
Click to expand...

Hi Gary,

No contradiction. I guess I am guilty of not being clear enough.

As for your two examples. Yes, the first is correct. However, The
second is not. The "X" would go to the second row.

The search for the first number greater than zero works in a kind of a
zig zag pattern. Keeping in mind that the search starts with B1 then
(if it finds a zero) it goes DOWN to B2.. (if it again finds a zero)
the search goes to C1..(if it again finds a zero) it goes to C2 and so
on and so forth.
Click to expand...

The only possible reason the code could fail is if there's blank rows
between the sets. (Not what you stated)
 
Here's the result of running the code on the sample data you posted...

x 0 0 0 0 2 3 0 '//1st >0
0 0 0 0 0 0 1 '//ignored
--
0 0 0 0 0 0 0 '//ignored
x 0 0 1 0 0 1 0 '//1st >0
--
x 0 0 0 0 0 0 1 '//1st >0
0 0 0 0 0 0 0 '//ignored
--
x 0 3 0 3 1 0 0 '//1st >0
0 2 0 1 0 0 0 '//ignored
--
0 0 0 0 0 0 0 '//ignored
x 1 0 0 3 3 0 0 '//1st >0
--
x 0 0 1 1 1 0 0 '//1st >0
0 0 0 0 0 0 0 '//ignored
----'//test set
0 0 0 0 0 0 0 '//ignored
0 0 0 0 0 0 0 '//ignored

...as rendered by this code...

Sub DenoteCellsGreaterThanZero2()
Dim r As Long
Dim b1 As Boolean, b2 As Boolean
For r = 1 To ActiveSheet.UsedRange.Rows.Count Step 2
With Application.WorksheetFunction
b1 = (.CountIf(Rows(r), ">0") > 0)
b2 = (.CountIf(Rows(r).Offset(1), ">0") > 0)
End With 'Application.WorksheetFunction
If b1 Then Cells(r, 1) = "x": GoTo nextset
If b2 Then Cells(r, 1).Offset(1) = "x"
nextset:
Next 'r
End Sub
 
Don Guillett used his keyboard to write :
Insert a row at the top and put this in row2 and copy down
=IF(AND(COUNTIF(B2:H2,">"&0),a1<>"x"),"x","")
Click to expand...

This does not place x for set#3 nor set#6 of the OP's sample data. It
needs to test both rows to determine the action to take on one or the
other.
 
Don Guillett used his keyboard to write :




This does not place x for set#3 nor set#6 of the OP's sample data. It
needs to test both rows to determine the action to take on one or the
other.

--
Garry

Free usenet access athttp://www.eternal-september.org
ClassicVB Users Regroup! comp.lang.basic.visual.misc
Click to expand...

Did you insert a row at the top. I can send a file or you can send me
yours
dguillett1 @gmail.com
 
Don Guillett explained on 9/3/2011 :
Did you insert a row at the top. I can send a file or you can send me
yours
dguillett1 @gmail.com
Click to expand...

Yes, I inserted a row at the top as you instructed. Are you saying you
get 1 x for each set of rows? Here are the results I get...
A-- B-- C-- D-- E-- F-- G-- H
1--
2-- x 0 0 0 0 2 3 0_set1/row1
3-- 0 0 0 0 0 0 1_set1/row2
4-- 0 0 0 0 0 0 0_set2/row1
5-- x 0 0 1 0 0 1 0_set2/row2
6-- 0 0 0 0 0 0 1_set3/row1 '//no x here
7-- 0 0 0 0 0 0 0_set3/row2
8-- x 0 3 0 3 1 0 0_set4/row1
9-- 0 2 0 1 0 0 0_set4/row2
10- 0 0 0 0 0 0 0_set5/row1
11- x 1 0 0 3 3 0 0_set5/row2
12- 0 0 1 1 1 0 0_set6/row1 '//no x here
13- 0 0 0 0 0 0 0_set6/row2

Why do you think your file would be set up different than mine, since I
set mine up as per your instructions?
 
Don Guillett explained on 9/3/2011 :







Yes, I inserted a row at the top as you instructed. Are you saying you
get 1 x for each set of rows? Here are the results I get...
    A-- B-- C-- D-- E-- F-- G-- H
1--
2-- x   0       0       0       0       2       3       0_set1/row1
3--     0       0       0       0       0      0       1_set1/row2
4--     0       0       0       0       0      0       0_set2/row1
5-- x   0       0       1       0       0       1       0_set2/row2
6--     0       0       0       0       0      0       1_set3/row1  '//no x here
7--     0       0       0       0       0      0       0_set3/row2
8-- x   0       3       0       3       1       0       0_set4/row1
9--     0       2       0       1       0      0       0_set4/row2
10-     0       0       0       0       0      0       0_set5/row1
11- x   1       0       0       3       3       0       0_set5/row2
12-     0       0       1       1       1      0       0_set6/row1  '//no x here
13-     0       0       0       0       0      0       0_set6/row2

Why do you think your file would be set up different than mine, since I
set mine up as per your instructions?

--
Garry

Free usenet access athttp://www.eternal-september.org
ClassicVB Users Regroup! comp.lang.basic.visual.misc- Hide quoted text -

- Show quoted text -
Click to expand...

Garry / Don,

Don,

I tried your formula. Sorry, It does not work properly.

Garry,

With regards to above with the results that you get - All is correct
on your small sample with the exception of set 3 and set 6. Why
wouldn't an "X" be returned on both of these sets ?
 
qcan has brought this to us :
Garry / Don,

Don,

I tried your formula. Sorry, It does not work properly.

Garry,

With regards to above with the results that you get - All is correct
on your small sample with the exception of set 3 and set 6. Why
wouldn't an "X" be returned on both of these sets ?
Click to expand...

My sample DOES return an x for all 6 sets. Don's formula (return sample
above) DOES NOT return an x for sets 3/6 (as I stated here at the top).

Sounds to me like you're confused about who's/what return samples are
being provided. My code returns an x for all 6 sets as noted in my
reply (which also includes the macro I used to get those results)!
 
Back
Top