Sir,
Thank you very much for the reply.
I did try your formula originally and again today.
Here is a visual depiction of what your formula results in,
on a blank worksheet:
Row Col F Col H Col S Col X Col Y Col Z
1 Larry John A Larry 0
2 Mary Mary A 0 0
3 Mary Larry B 3 0
4 John Larry C 4 0
5 Mary John C 0 -4
6 John Mary A 6 0
A) Cell X1 contains Larry
B) Cell X2 contains: =OR(F2=$F$1,H2=$X$1)*
OR(S2={"A","B","C"})*ROW()
C) Cells X3 to X6, contain the "X2" formula, copied down
D) Cell Y2 contains =X2-MAX($X$2:X2)
E) Cell Z1 contains =MAX(Y:Y)
Thus:
A) Your 1st formula does depict the row number of rows
containing both "Larry" and or A, B or C.
However, the results I'm looking for, must be separated for
each of A, B or C, as you would have read in my 1st reply to
Mr. Coderre.
This means that instead of using only column X for your first
formula, I'd have to use columns X, Y and Z with a similar
formula, looking for specifically, both Larry and A in col X,
both Larry and B in col Y and both Larry and C in col X.
B) Secondly, your second formula does not produce the desired result.
i.e. the formula you entered in cell Y2 and copied down,
doesn't count the maximum number of absences between rows
containing say, Larry and A.
(Even if I were to use 3 columns total: X, Y and Z containing
your first formula)
C) Also, if I were to copy your first formula down to row 12.000,
I would create a column containing ever increasing #'s.
The problem isn't naming the rows containing both Larry and A,
but rather counting the maximum number of rows existant,
between such rows. i.e. counting the maximum absence of
such a row re-appearing.
D) About array formulas
I made a refference to an array formula being present in column
Y, such as the one provided by Mr. Coderres' 1st reply, because
a similar formula would be needed in order to produce the required
result. (Instead of your 2nd formula)
If you were to try this, you'd find how yes, a powerful machine does
halt.
[Nevermind using 3 columns, containing such a formula (Mr. Coderres')
* 800 rows (one row for each name)]
The most powerful machine available to us, uses an
Athlon FX 60 CPU and 2 gb of RAM.
Even this machine (nevermind notebooks- which are the target)
halt after the 1st 200 rows.
Remember that as I stated in my last reply, after JMBs' 2nd response,
the target sheet I'm using contains a total of 22 columns, 18 of which
have been filled already, with other, large formulas, such as
Mr. Coderres'.
800 rows (names)* 22 such columns, is just too much.
E) Re: The macro you provided
Certainly, a smart concept. However, again, your 2nd formula
doesn't work. (In order for the macro to work)
Also, I would have to assume that you haven't tried
executing a macro on 800 names, through 12.000 rows, (3 col's)
to fill: 800 rows (names) * 3 results (A, B, C)= 2.400 cells.
If you were to try this, you'd find how a "Command too long"
message appears. When it doesn't (because of tweaking VB)
"autorecover" encounters a problem in the process.
E) I would assume that you may haven't read or understood my 1st
reply to Mr. Coderre. This reply, contains the complete question.
In this reply, I explain how the desired results must be displayed
in three, different cells, one for each of A, B and C.
(Remember: my initial post was a simplified version of the question)
F) Lastly and most importantly:
My last reply to this post (before this one), explains how there's no
point in -my- further researching into the problem, because:
a) A UDF, similar to JMB's was used, to succesfully produce the
needed results.
b) However, that is of no importance, as no practically available
pc can handle the workload, regardless of the aforementioned UDF.
That means that the report I was trying to create, cannot be
created
efficiently enough, so as to be operated by practically available
pc's
(notebooks), whether we were to use this, new UDF or not.
Once again, I would like to thank you and all others
having participated in this inquiry.
Roger Govier said:
Hi Vasilis
There was a mistake with my solution, in that the second formula would
have produced a circular reference.
Instead of =MAX(Y:Y) it should be =MAX(Y2:Y65536) or alternatively the
formula =MAX(Y:Y) should be entered in Z1 in place of Y1. In the latter
case the macro would need amending to write results from Range("Z1") not
Range("Y1")
However, I don't think you can have tried it, or you are not
understanding what I am saying.
Firstly
=OR(F2=$F$1,H2=$X$1)*OR(S2={"A","B","C"})*ROW()
will return a row number only if
the name appearing in X1 appears in either column F or H, AND there is
a either a Letter A OR B OR C in column S
and not as you say, just if the name appears in the row.
I believe my formula does return the answer as per your
- Note that the count must ommit rows which do not contain exactly:
(Ex) both: "Larry" and: ("A", or "B" or "C")
Secondly
[1/2 of which will contain array formulas (your 2nd formula)]
Neither of the formulae posted are array formulae. They are very simple
formulae which can be calculated quickly down a sheet of 12000 rows or
more and would not cause even a relatively low powered computer any
problem.
Thirdly
My solution does not require the addition of more than 2 columns.
It would be extremely poor design (and not possible even on XL2007) to
have to have 2 extra columns for each of 800 employees!!
As I have said, there would be a list of all employees in a single
column on Sheet2.
The macro, would take each name in turn from column A, and write it to
cell X1 on Sheet1, whereupon, Y1 would reflect the answer pertinent to
that employee.
It would then take the result from Y1 and write that back to column 2 on
Sheet2 alongside the employee's name.
It would then iterate through each name in the list on sheet2 until the
task was complete.
Sheet 2 would thus be 800 rows by 2 columns (and not a formula in sight)
Fourthly
Lastly, others seem to agree on this problem requiring a UDF.
They may be right.
I don't disagree that a UDF can be written to solve this problem but it
is not the only solution.
I prefer to use very simple worksheet formulae (which are very efficient
and faster than most UDF's) to carry out the task of finding the result
for a single employee, and then utilise an uncomplicated macro to cycle
through the task of changing the name in cell X1 for me.
None of these formulae would cause any computer undue stress in their
calculation, and should run fairly quickly.
--
Regards
Roger Govier
Vasilis Tergen said:
Sir,
Thank you for the reply.
The formula you initially provided me with doesn't work.
What it does instead is provide the row number of any row containing
the name "Larry", or whatever the user enters in cell X1.
In a simpler manner it provides:
=IF($A$1:$A$12000="Larry",ROW($A$1:$A$12000),"")
Secondly, yes you would need to add an exorbitant number of cells
(columns & rows) even if did work. This, because two new "helper"
columns must be added for each name, to contain the 2 formulas you
provided & autofill down to row 12000.
Thus, 2 new columns (containing your 2 formulas)
*12000 rows (containing "Larry", "A","B","C"...) each=
24.000 new rows/ name.
Those rows will cover 2 columns => 48.000 cells.
(Remember that you entered a name, "Larry" into cell X1)
Lastly, to calculate all of the 800 names, you'd need:
800 (names)* 48.000 (cells containing formulas for each name)=
38.400.000 cells,
[1/2 of which will contain array formulas (your 2nd formula)]
Also, the macro you presented in your 2nd reply would be quite
practical,
if the original formula worked.
Lastly, others seem to agree on this problem requiring a UDF.
They may be right.
Roger Govier said:
Hi Vasilis
Assuming that my solution works i.e. does produce the correct answer
for
Larry, there is no need to add 800 columns to the sheet at all.
On Sheet2, starting with A1, create a list of all 800 names going
down
to cell A800
Then, run the following trivial piece of code to obtain the result
for
all 800 people
Sub FindLarry()
Dim i As Long, lrow As Long
lrow = Sheets("Sheet2").Cells(Rows.Count, 1).End(xlUp).Row
For i = 1 To lrow
Sheets("Sheet1").Range("X1") = Sheets("Sheet2").Cells(i, "A")
Sheets("Sheet2").Cells(i, "B") = Sheets("Sheet1").Range("Y1")
Next
End Sub
--
Regards
Roger Govier
message Sir,
As stated in the initial post (question), helper columns cannot be
used
because
I'm analyzing 800 names such as "Larry", "John"... over 12000 rows.
This, would require using: 800* 2 additional helper columns= 1600 *
12000*
22 columns (the width of the report table)= 422.400.000 cells
containing
formulas.
(Data-rows- are also added daily, to the existing, >12.000...)
:
Hi
If I understand you correctly, then with the use of 2 helper
columns
you
could do the following.
I used columns X and Y
In X1 I entered Larry and in X2
=OR(F2=$F$1,H2=$X$1)*OR(S2={"A","B","C"})*ROW()
In Y2
=X2-MAX($X$2:X2)
In both cases, fill down as required
in Y1
=MAX(Y:Y) returns the maximum you are seeking
--
Regards
Roger Govier
in
message Sir,
Thank you very much for taking the time to write that formula.
I tried it repeatedly and these are its' results:
I) It works if the target column (Column A in this case) has
less
than
7 blank rows
at its' beginning. (Rows 1-7)
II) It may "jam" a high-powered machine if copied and pasted
onto
other
cells.
III) The function certainly works on the problem I posted,
however
as it would've been too complicated to state in my
initial
inquiry,
I didn't explain the complete version of the
problem -which
follows-
and for which, the afforementioned formula does not
suffice:
The Problem
A) Instead of having 1 column to work with, I have 3.
Larry, John or any other name, may appear either under
columns
F
or H,
yet never
simultaneously, under both columns F & H of the same row.
B) Additionally, in order to count the number of absences,
criteria
must be
used,
from yet another column, column S.
Column S will always contain 1 of either of 3 values: A, B or
C.
C) A visual table depiction:
Column F Column H Column S
Larry John A
Mary Mary A
Mary Larry B
John Larry C
Mary John C
Larry Mary A
D) The actual, exact data results needed are as follows:
I must count the maximum absence of:
1) "Larry" appearing under either column F or H, while
simultaneously,
having "A" appear under column S (on the same row).
2) "Larry" appearing under either column F or H, while
simultaneously,
having "B" appear under column S (on the same row).
3) "Larry" appearing under either column F or H, while
simultaneously,
having "C" appear under column S (on the same row).
- Obviously, 3 similar formulas will be used to
calculate
the
afforementioned,
entered into 3 separate cells.
- Note that the count must ommit rows which do not
contain
exactly:
(Ex) both: "Larry" and: ("A", or "B" or
"C")
The formula you kindly provided me with, includes
rows
in
its'
results, which do
not meet the specified criteria. It simply provides
the
maximum
absence between
such rows. Ex: In the target workbook, it resulted
in a
1438
maximum row absence
between "Larry" appearing under column "F".
-To give you a better idea, "Larry" actually appears
a
total of
51 times in 12000 rows.
If I were to filter the 12000 rows for "Larry", I
would
find
that the maximum
absence of both "Larry" and "A" appearing on the
same
row,
would be= 15 times.
Not 1438, which is the maximum absence of "Larry"
simply
appearing under column F.
F) Lastly, should it be of any help:
I recently posted a related question in this discussion
forum.
It is to be found under: Excel worksheet functions/ "A
rather
difficult .........."
I managed to answer that question on my own, succesfully.
If you were to help me say, define just one the
aforementioned
rows
(in