Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
COUNTIF in between rows
How would I find the maximum amount of intervals between
"Larry" re- appearing in column A? I must use a formula- not a macro or a filter. The correct answer in this example= 4. That means that in column A, the maximum absence (or intervals) of "Larry" re- appearing was 4 times, which occured between rows 2 and 7. (Ex: As opposed to rows: 7 - 9, where he was only absent once) Rows Column A 1 John 2 Larry 3 John 4 John 5 John 6 Mary 7 Larry 8 Mary 9 Larry 10 John |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
COUNTIF in between rows
I can do it with a helper column
With the names in A1:A7 In B1 enter: =--(A1="Larry") In B2 enter: =(B1+(A2<"Larry"))*(A2<"Larry") Copy this down the column =MAX(B1:B7) returns the value 4 So experimenting with an array formula, this seems to work =MAX((B1:B6+(A2:A7<"Larry"))*(A2:A7<"Larry")) entered with CTRL+SHIFT+ENTER best wishes -- Bernard V Liengme www.stfx.ca/people/bliengme remove caps from email "Vasilis Tergen" wrote in message ... How would I find the maximum amount of intervals between "Larry" re- appearing in column A? I must use a formula- not a macro or a filter. The correct answer in this example= 4. That means that in column A, the maximum absence (or intervals) of "Larry" re- appearing was 4 times, which occured between rows 2 and 7. (Ex: As opposed to rows: 7 - 9, where he was only absent once) Rows Column A 1 John 2 Larry 3 John 4 John 5 John 6 Mary 7 Larry 8 Mary 9 Larry 10 John |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
COUNTIF in between rows
Sir,
Thank you for the reply, it is greatly appreciated. However, the data table I'm using, contains 12.000 rows of data and 800 names such as "Larry", "John"... I did something similar to what you've suggested previously, which unfortunately resulted in "freezing" the computer, as I needed: 800 names* 12.000 rows containing a "helper" column to analyze the data. Thus, I'm looking for a formula to be entered in a single cell. "Bernard Liengme" wrote: I can do it with a helper column With the names in A1:A7 In B1 enter: =--(A1="Larry") In B2 enter: =(B1+(A2<"Larry"))*(A2<"Larry") Copy this down the column =MAX(B1:B7) returns the value 4 So experimenting with an array formula, this seems to work =MAX((B1:B6+(A2:A7<"Larry"))*(A2:A7<"Larry")) entered with CTRL+SHIFT+ENTER best wishes -- Bernard V Liengme www.stfx.ca/people/bliengme remove caps from email "Vasilis Tergen" wrote in message ... How would I find the maximum amount of intervals between "Larry" re- appearing in column A? I must use a formula- not a macro or a filter. The correct answer in this example= 4. That means that in column A, the maximum absence (or intervals) of "Larry" re- appearing was 4 times, which occured between rows 2 and 7. (Ex: As opposed to rows: 7 - 9, where he was only absent once) Rows Column A 1 John 2 Larry 3 John 4 John 5 John 6 Mary 7 Larry 8 Mary 9 Larry 10 John |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
COUNTIF in between rows
Here you go.....
Try something like this: With A2:A12000 contains names, with "larry" interspersed This ARRAY FORMULA returns the largest consecutive gap between "larry" cells B1: =MAX(SMALL(IF((A2:A12000="larry")*(A1:A11999<"lar ry")*(A3:A12001<"larry"),ROW(A2:A12000),10^99),RO W($A$2:INDEX(A:A,SUMPRODUCT(--(A2:A12000="larry")*(A1:A11999<"larry")*(A3:A1200 1<"larry")))))-SMALL(IF((A2:A12000="larry")*(A1:A11999<"larry")* (A3:A12001<"larry"),ROW(A2:A12000),10^99),ROW($A$ 1:INDEX(A:A,SUMPRODUCT(--(A2:A12000="larry")*(A1:A11999<"larry")*(A3:A1200 1<"larry"))-1)))-1) Note: For array formulas, hold down [Ctrl] and [Shift] when you press [Enter], instead of just pressing [Enter]. Yeah....I know....it's not very elegant, but it seems to get the job done. <g Does that help? *********** Regards, Ron XL2002, WinXP "Vasilis Tergen" wrote: How would I find the maximum amount of intervals between "Larry" re- appearing in column A? I must use a formula- not a macro or a filter. The correct answer in this example= 4. That means that in column A, the maximum absence (or intervals) of "Larry" re- appearing was 4 times, which occured between rows 2 and 7. (Ex: As opposed to rows: 7 - 9, where he was only absent once) Rows Column A 1 John 2 Larry 3 John 4 John 5 John 6 Mary 7 Larry 8 Mary 9 Larry 10 John |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
COUNTIF in between rows
Another formula solution - but not practical with the number of cells you're
trying to calculate (and also array entered): =MAX(IF(COUNTIF(A1:A12000,"larry")1,LARGE((A1:A12 000="larry")*ROW(A1:A12000),ROW(INDIRECT("1:"&COUN TIF(A1:A12000,"larry")-1)))-LARGE((A1:A12000="larry")*(ROW(A1:A12000)),ROW(IND IRECT("2:"&COUNTIF(A1:A12000,"larry"))))-1),IF(A1<"larry",MATCH("larry",A1:A12000,0)-1),IF(A12000<"larry",ROWS(A1:A12000)+ROW(A1)-1-MAX(IF(A1:A12000="larry",ROW(A1:A12000),"")))) Change the range reference as needed. It takes a little bit to calculate (but still under a minute on my machine). Without using any helper columns or VBA- you almost have to use an array formula, which will be quite a calculation load. If you have to do all 800 names, I'd look at using a custom function. "Vasilis Tergen" wrote: How would I find the maximum amount of intervals between "Larry" re- appearing in column A? I must use a formula- not a macro or a filter. The correct answer in this example= 4. That means that in column A, the maximum absence (or intervals) of "Larry" re- appearing was 4 times, which occured between rows 2 and 7. (Ex: As opposed to rows: 7 - 9, where he was only absent once) Rows Column A 1 John 2 Larry 3 John 4 John 5 John 6 Mary 7 Larry 8 Mary 9 Larry 10 John |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
COUNTIF in between rows
A UDF that appears to work okay:
syntax is: =maxinterval(A1:A12000,"larry") Function MaxInterval(rngData As Range, _ varCriteria As Variant) As Long Dim lngLast As Long Dim i As Long lngLast = 0 For i = 1 To rngData.Rows.Count If rngData(i, 1) = varCriteria Then MaxInterval = Application.Max(MaxInterval, i - lngLast - 1) lngLast = i ElseIf i = rngData.Rows.Count Then MaxInterval = Application.Max(MaxInterval, i - lngLast) End If Next i End Function "Vasilis Tergen" wrote: How would I find the maximum amount of intervals between "Larry" re- appearing in column A? I must use a formula- not a macro or a filter. The correct answer in this example= 4. That means that in column A, the maximum absence (or intervals) of "Larry" re- appearing was 4 times, which occured between rows 2 and 7. (Ex: As opposed to rows: 7 - 9, where he was only absent once) Rows Column A 1 John 2 Larry 3 John 4 John 5 John 6 Mary 7 Larry 8 Mary 9 Larry 10 John |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
COUNTIF in between rows
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 excel terms) I would more than likely be able to complete the needed formula. Thank you very much. "Ron Coderre" wrote: Here you go..... Try something like this: With A2:A12000 contains names, with "larry" interspersed This ARRAY FORMULA returns the largest consecutive gap between "larry" cells B1: =MAX(SMALL(IF((A2:A12000="larry")*(A1:A11999<"lar ry")*(A3:A12001<"larry"),ROW(A2:A12000),10^99),RO W($A$2:INDEX(A:A,SUMPRODUCT(--(A2:A12000="larry")*(A1:A11999<"larry")*(A3:A1200 1<"larry")))))-SMALL(IF((A2:A12000="larry")*(A1:A11999<"larry")* (A3:A12001<"larry"),ROW(A2:A12000),10^99),ROW($A$ 1:INDEX(A:A,SUMPRODUCT(--(A2:A12000="larry")*(A1:A11999<"larry")*(A3:A1200 1<"larry"))-1)))-1) Note: For array formulas, hold down [Ctrl] and [Shift] when you press [Enter], instead of just pressing [Enter]. Yeah....I know....it's not very elegant, but it seems to get the job done. <g Does that help? *********** Regards, Ron XL2002, WinXP "Vasilis Tergen" wrote: How would I find the maximum amount of intervals between "Larry" re- appearing in column A? I must use a formula- not a macro or a filter. The correct answer in this example= 4. That means that in column A, the maximum absence (or intervals) of "Larry" re- appearing was 4 times, which occured between rows 2 and 7. (Ex: As opposed to rows: 7 - 9, where he was only absent once) Rows Column A 1 John 2 Larry 3 John 4 John 5 John 6 Mary 7 Larry 8 Mary 9 Larry 10 John |
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
COUNTIF in between rows
Your issue has definitely crossed into User Defined Function territory. The
post by JMB is a good starting point. Let's see if he(she?) adjusts it to meet your new requirements. *********** Regards, Ron XL2002, WinXP "Vasilis Tergen" wrote: 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 excel terms) I would more than likely be able to complete the needed formula. Thank you very much. "Ron Coderre" wrote: Here you go..... Try something like this: With A2:A12000 contains names, with "larry" interspersed This ARRAY FORMULA returns the largest consecutive gap between "larry" cells B1: =MAX(SMALL(IF((A2:A12000="larry")*(A1:A11999<"lar ry")*(A3:A12001<"larry"),ROW(A2:A12000),10^99),RO W($A$2:INDEX(A:A,SUMPRODUCT(--(A2:A12000="larry")*(A1:A11999<"larry")*(A3:A1200 1<"larry")))))-SMALL(IF((A2:A12000="larry")*(A1:A11999<"larry")* (A3:A12001<"larry"),ROW(A2:A12000),10^99),ROW($A$ 1:INDEX(A:A,SUMPRODUCT(--(A2:A12000="larry")*(A1:A11999<"larry")*(A3:A1200 1<"larry"))-1)))-1) Note: For array formulas, hold down [Ctrl] and [Shift] when you press [Enter], instead of just pressing [Enter]. Yeah....I know....it's not very elegant, but it seems to get the job done. <g Does that help? *********** Regards, Ron XL2002, WinXP "Vasilis Tergen" wrote: How would I find the maximum amount of intervals between "Larry" re- appearing in column A? I must use a formula- not a macro or a filter. The correct answer in this example= 4. That means that in column A, the maximum absence (or intervals) of "Larry" re- appearing was 4 times, which occured between rows 2 and 7. (Ex: As opposed to rows: 7 - 9, where he was only absent once) Rows Column A 1 John 2 Larry 3 John 4 John 5 John 6 Mary 7 Larry 8 Mary 9 Larry 10 John |
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
COUNTIF in between rows
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 "Vasilis Tergen" wrote 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 excel terms) I would more than likely be able to complete the needed formula. Thank you very much. "Ron Coderre" wrote: Here you go..... Try something like this: With A2:A12000 contains names, with "larry" interspersed This ARRAY FORMULA returns the largest consecutive gap between "larry" cells B1: =MAX(SMALL(IF((A2:A12000="larry")*(A1:A11999<"lar ry")*(A3:A12001<"larry"),ROW(A2:A12000),10^99),RO W($A$2:INDEX(A:A,SUMPRODUCT(--(A2:A12000="larry")*(A1:A11999<"larry")*(A3:A1200 1<"larry")))))-SMALL(IF((A2:A12000="larry")*(A1:A11999<"larry")* (A3:A12001<"larry"),ROW(A2:A12000),10^99),ROW($A$ 1:INDEX(A:A,SUMPRODUCT(--(A2:A12000="larry")*(A1:A11999<"larry")*(A3:A1200 1<"larry"))-1)))-1) Note: For array formulas, hold down [Ctrl] and [Shift] when you press [Enter], instead of just pressing [Enter]. Yeah....I know....it's not very elegant, but it seems to get the job done. <g Does that help? *********** Regards, Ron XL2002, WinXP "Vasilis Tergen" wrote: How would I find the maximum amount of intervals between "Larry" re- appearing in column A? I must use a formula- not a macro or a filter. The correct answer in this example= 4. That means that in column A, the maximum absence (or intervals) of "Larry" re- appearing was 4 times, which occured between rows 2 and 7. (Ex: As opposed to rows: 7 - 9, where he was only absent once) Rows Column A 1 John 2 Larry 3 John 4 John 5 John 6 Mary 7 Larry 8 Mary 9 Larry 10 John |
#10
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
COUNTIF in between rows
Try array-entered (CSE):
=MAX(FREQUENCY(ROW(1:1201),IF((F1:F1200="Larry")+( H1:H1200="Larry"))* (S1:S1200="A"),ROW(1:1200))))-1 then eplace "A" by "B" or "C" for other results. Note: there is one extra row in the first argument of the frequency function Vasilis Tergen wrote: 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 excel terms) I would more than likely be able to complete the needed formula. Thank you very much. "Ron Coderre" wrote: Here you go..... Try something like this: With A2:A12000 contains names, with "larry" interspersed This ARRAY FORMULA returns the largest consecutive gap between "larry" cells B1: =MAX(SMALL(IF((A2:A12000="larry")*(A1:A11999<"lar ry")*(A3:A12001<"larry"),ROW(A2:A12000),10^99),RO W($A$2:INDEX(A:A,SUMPRODUCT(--(A2:A12000="larry")*(A1:A11999<"larry")*(A3:A1200 1<"larry")))))-SMALL(IF((A2:A12000="larry")*(A1:A11999<"larry")* (A3:A12001<"larry"),ROW(A2:A12000),10^99),ROW($A$ 1:INDEX(A:A,SUMPRODUCT(--(A2:A12000="larry")*(A1:A11999<"larry")*(A3:A1200 1<"larry"))-1)))-1) Note: For array formulas, hold down [Ctrl] and [Shift] when you press [Enter], instead of just pressing [Enter]. Yeah....I know....it's not very elegant, but it seems to get the job done. <g Does that help? *********** Regards, Ron XL2002, WinXP "Vasilis Tergen" wrote: How would I find the maximum amount of intervals between "Larry" re- appearing in column A? I must use a formula- not a macro or a filter. The correct answer in this example= 4. That means that in column A, the maximum absence (or intervals) of "Larry" re- appearing was 4 times, which occured between rows 2 and 7. (Ex: As opposed to rows: 7 - 9, where he was only absent once) Rows Column A 1 John 2 Larry 3 John 4 John 5 John 6 Mary 7 Larry 8 Mary 9 Larry 10 John |
#11
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
COUNTIF in between rows
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...) "Roger Govier" wrote: 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 "Vasilis Tergen" wrote 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 excel terms) I would more than likely be able to complete the needed formula. Thank you very much. "Ron Coderre" wrote: Here you go..... Try something like this: With A2:A12000 contains names, with "larry" interspersed This ARRAY FORMULA returns the largest consecutive gap between "larry" cells B1: =MAX(SMALL(IF((A2:A12000="larry")*(A1:A11999<"lar ry")*(A3:A12001<"larry"),ROW(A2:A12000),10^99),RO W($A$2:INDEX(A:A,SUMPRODUCT(--(A2:A12000="larry")*(A1:A11999<"larry")*(A3:A1200 1<"larry")))))-SMALL(IF((A2:A12000="larry")*(A1:A11999<"larry")* (A3:A12001<"larry"),ROW(A2:A12000),10^99),ROW($A$ 1:INDEX(A:A,SUMPRODUCT(--(A2:A12000="larry")*(A1:A11999<"larry")*(A3:A1200 1<"larry"))-1)))-1) Note: For array formulas, hold down [Ctrl] and [Shift] when you press [Enter], instead of just pressing [Enter]. Yeah....I know....it's not very elegant, but it seems to get the job done. <g Does that help? *********** Regards, Ron XL2002, WinXP "Vasilis Tergen" wrote: How would I find the maximum amount of intervals between "Larry" re- appearing in column A? I must use a formula- not a macro or a filter. The correct answer in this example= 4. That means that in column A, the maximum absence (or intervals) of "Larry" re- appearing was 4 times, which occured between rows 2 and 7. (Ex: As opposed to rows: 7 - 9, where he was only absent once) Rows Column A 1 John 2 Larry 3 John 4 John 5 John 6 Mary 7 Larry 8 Mary 9 Larry 10 John |
#12
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
COUNTIF in between rows
Sir,
I appreciate the reply. I am at the same time somewhat dissapointed however, as your estimation of the problem requiring a UDF, shatters any hopes I may have had, of solving the problem without one. I was previously pressed to find an answer using existing functions. Now I am pressed to wait upon others' replies or learn programming myself... "Ron Coderre" wrote: Your issue has definitely crossed into User Defined Function territory. The post by JMB is a good starting point. Let's see if he(she?) adjusts it to meet your new requirements. *********** Regards, Ron XL2002, WinXP "Vasilis Tergen" wrote: 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 excel terms) I would more than likely be able to complete the needed formula. Thank you very much. "Ron Coderre" wrote: Here you go..... Try something like this: With A2:A12000 contains names, with "larry" interspersed This ARRAY FORMULA returns the largest consecutive gap between "larry" cells B1: =MAX(SMALL(IF((A2:A12000="larry")*(A1:A11999<"lar ry")*(A3:A12001<"larry"),ROW(A2:A12000),10^99),RO W($A$2:INDEX(A:A,SUMPRODUCT(--(A2:A12000="larry")*(A1:A11999<"larry")*(A3:A1200 1<"larry")))))-SMALL(IF((A2:A12000="larry")*(A1:A11999<"larry")* (A3:A12001<"larry"),ROW(A2:A12000),10^99),ROW($A$ 1:INDEX(A:A,SUMPRODUCT(--(A2:A12000="larry")*(A1:A11999<"larry")*(A3:A1200 1<"larry"))-1)))-1) Note: For array formulas, hold down [Ctrl] and [Shift] when you press [Enter], instead of just pressing [Enter]. Yeah....I know....it's not very elegant, but it seems to get the job done. <g Does that help? *********** Regards, Ron XL2002, WinXP "Vasilis Tergen" wrote: How would I find the maximum amount of intervals between "Larry" re- appearing in column A? I must use a formula- not a macro or a filter. The correct answer in this example= 4. That means that in column A, the maximum absence (or intervals) of "Larry" re- appearing was 4 times, which occured between rows 2 and 7. (Ex: As opposed to rows: 7 - 9, where he was only absent once) Rows Column A 1 John 2 Larry 3 John 4 John 5 John 6 Mary 7 Larry 8 Mary 9 Larry 10 John |
#13
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
COUNTIF in between rows
Thank you for the reply
I tried the formula repeatedly and its' results are as follows: A) It creates no "freezing" problems whatsoever, due to calculation load, because of its' simplicity. B) It doesn't count the needed absences. It instead counts a volatile frequency between rows containing both "Larry" and "A". Ex: It will result in a count of 1158 absences (between rows containing both "Larry" and "A", searching through 12000 rows. (Your formula had to be slightly altered to produce this result) C) Again, to fully grasp the concept, imagine that you filter the 12000 rows for the name "Larry". Then, 51 rows would remain. Within those 51 rows, the maximum absence of both: a) "Larry" appearing under columns "F" or "H" & b) "A" appearing under column "S" (on the same row), would be easily determined as being= 15 times. Once again, thank you for the much needed assistance. "Lori" wrote: Try array-entered (CSE): =MAX(FREQUENCY(ROW(1:1201),IF((F1:F1200="Larry")+( H1:H1200="Larry"))* (S1:S1200="A"),ROW(1:1200))))-1 then eplace "A" by "B" or "C" for other results. Note: there is one extra row in the first argument of the frequency function Vasilis Tergen wrote: 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 excel terms) I would more than likely be able to complete the needed formula. Thank you very much. "Ron Coderre" wrote: Here you go..... Try something like this: With A2:A12000 contains names, with "larry" interspersed This ARRAY FORMULA returns the largest consecutive gap between "larry" cells B1: =MAX(SMALL(IF((A2:A12000="larry")*(A1:A11999<"lar ry")*(A3:A12001<"larry"),ROW(A2:A12000),10^99),RO W($A$2:INDEX(A:A,SUMPRODUCT(--(A2:A12000="larry")*(A1:A11999<"larry")*(A3:A1200 1<"larry")))))-SMALL(IF((A2:A12000="larry")*(A1:A11999<"larry")* (A3:A12001<"larry"),ROW(A2:A12000),10^99),ROW($A$ 1:INDEX(A:A,SUMPRODUCT(--(A2:A12000="larry")*(A1:A11999<"larry")*(A3:A1200 1<"larry"))-1)))-1) Note: For array formulas, hold down [Ctrl] and [Shift] when you press [Enter], instead of just pressing [Enter]. Yeah....I know....it's not very elegant, but it seems to get the job done. <g Does that help? *********** Regards, Ron XL2002, WinXP "Vasilis Tergen" wrote: How would I find the maximum amount of intervals between "Larry" re- appearing in column A? I must use a formula- not a macro or a filter. The correct answer in this example= 4. That means that in column A, the maximum absence (or intervals) of "Larry" re- appearing was 4 times, which occured between rows 2 and 7. (Ex: As opposed to rows: 7 - 9, where he was only absent once) Rows Column A 1 John 2 Larry 3 John 4 John 5 John 6 Mary 7 Larry 8 Mary 9 Larry 10 John |
#14
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
COUNTIF in between rows
Sir,
Your reply is greatly appreciated. I invoked the formula you provided repeatedly. Unfortunately, its' calculation result is not the desired one. A) In a blank worksheet, containing only column A, which in turn, contains "Larry" & other, various names, totalling 29 rows, it results in a "11971" count. B) In the target workbook, the results are of even greater disparity. C) I can't be entirely certain as of the error in calculation, as the formula in question repeats COUNTIF commands, using discretionary (& or volatile) references. P.S. I am sorry I couldn't reply sooner. However, I had (& have) to reply to all others which graciously offered a reply to this post, in the order that they did. (Including thoroughly testing their replies respectively) "JMB" wrote: Another formula solution - but not practical with the number of cells you're trying to calculate (and also array entered): =MAX(IF(COUNTIF(A1:A12000,"larry")1,LARGE((A1:A12 000="larry")*ROW(A1:A12000),ROW(INDIRECT("1:"&COUN TIF(A1:A12000,"larry")-1)))-LARGE((A1:A12000="larry")*(ROW(A1:A12000)),ROW(IND IRECT("2:"&COUNTIF(A1:A12000,"larry"))))-1),IF(A1<"larry",MATCH("larry",A1:A12000,0)-1),IF(A12000<"larry",ROWS(A1:A12000)+ROW(A1)-1-MAX(IF(A1:A12000="larry",ROW(A1:A12000),"")))) Change the range reference as needed. It takes a little bit to calculate (but still under a minute on my machine). Without using any helper columns or VBA- you almost have to use an array formula, which will be quite a calculation load. If you have to do all 800 names, I'd look at using a custom function. "Vasilis Tergen" wrote: How would I find the maximum amount of intervals between "Larry" re- appearing in column A? I must use a formula- not a macro or a filter. The correct answer in this example= 4. That means that in column A, the maximum absence (or intervals) of "Larry" re- appearing was 4 times, which occured between rows 2 and 7. (Ex: As opposed to rows: 7 - 9, where he was only absent once) Rows Column A 1 John 2 Larry 3 John 4 John 5 John 6 Mary 7 Larry 8 Mary 9 Larry 10 John |
#15
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
COUNTIF in between rows
Sir,
Admittedly, I initially hadn't expected to need a UDF. This, however, is the smartest approach simply because any alternative (traditional- funtion) method, would result in an augmented formula, which would in turn cause even the most powerful machines to halt. (When updating the 12000 rows) I effected your UDF. Unfortunately, there seems to be a syntax error. (Probably related to the first statement line) I regrettably must also ask you to consider altering the syntax altogether, as your (the previous) reply, reffered to my initial and incomplete question. The complete question can be found under my reply to Mr. Coderres' 2nd reply. Lastly, I must clarify that I am not knowledgeable enough to alter or correct your UDF myself. I cannot but be both overwhelmed and hopeful by and of a further, forward-pressing response. "JMB" wrote: A UDF that appears to work okay: syntax is: =maxinterval(A1:A12000,"larry") Function MaxInterval(rngData As Range, _ varCriteria As Variant) As Long Dim lngLast As Long Dim i As Long lngLast = 0 For i = 1 To rngData.Rows.Count If rngData(i, 1) = varCriteria Then MaxInterval = Application.Max(MaxInterval, i - lngLast - 1) lngLast = i ElseIf i = rngData.Rows.Count Then MaxInterval = Application.Max(MaxInterval, i - lngLast) End If Next i End Function "Vasilis Tergen" wrote: How would I find the maximum amount of intervals between "Larry" re- appearing in column A? I must use a formula- not a macro or a filter. The correct answer in this example= 4. That means that in column A, the maximum absence (or intervals) of "Larry" re- appearing was 4 times, which occured between rows 2 and 7. (Ex: As opposed to rows: 7 - 9, where he was only absent once) Rows Column A 1 John 2 Larry 3 John 4 John 5 John 6 Mary 7 Larry 8 Mary 9 Larry 10 John |
#16
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
COUNTIF in between rows
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 "Vasilis Tergen" wrote in 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...) "Roger Govier" wrote: 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 "Vasilis Tergen" wrote 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 excel terms) I would more than likely be able to complete the needed formula. Thank you very much. "Ron Coderre" wrote: Here you go..... Try something like this: With A2:A12000 contains names, with "larry" interspersed This ARRAY FORMULA returns the largest consecutive gap between "larry" cells B1: =MAX(SMALL(IF((A2:A12000="larry")*(A1:A11999<"lar ry")*(A3:A12001<"larry"),ROW(A2:A12000),10^99),RO W($A$2:INDEX(A:A,SUMPRODUCT(--(A2:A12000="larry")*(A1:A11999<"larry")*(A3:A1200 1<"larry")))))-SMALL(IF((A2:A12000="larry")*(A1:A11999<"larry")* (A3:A12001<"larry"),ROW(A2:A12000),10^99),ROW($A$ 1:INDEX(A:A,SUMPRODUCT(--(A2:A12000="larry")*(A1:A11999<"larry")*(A3:A1200 1<"larry"))-1)))-1) Note: For array formulas, hold down [Ctrl] and [Shift] when you press [Enter], instead of just pressing [Enter]. Yeah....I know....it's not very elegant, but it seems to get the job done. <g Does that help? *********** Regards, Ron XL2002, WinXP "Vasilis Tergen" wrote: How would I find the maximum amount of intervals between "Larry" re- appearing in column A? I must use a formula- not a macro or a filter. The correct answer in this example= 4. That means that in column A, the maximum absence (or intervals) of "Larry" re- appearing was 4 times, which occured between rows 2 and 7. (Ex: As opposed to rows: 7 - 9, where he was only absent once) Rows Column A 1 John 2 Larry 3 John 4 John 5 John 6 Mary 7 Larry 8 Mary 9 Larry 10 John |
#17
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
COUNTIF in between rows
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" wrote: 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 "Vasilis Tergen" wrote in 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...) "Roger Govier" wrote: 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 "Vasilis Tergen" wrote 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 excel terms) I would more than likely be able to complete the needed formula. Thank you very much. "Ron Coderre" wrote: Here you go..... Try something like this: With A2:A12000 contains names, with "larry" interspersed This ARRAY FORMULA returns the largest consecutive gap between "larry" cells B1: =MAX(SMALL(IF((A2:A12000="larry")*(A1:A11999<"lar ry")*(A3:A12001<"larry"),ROW(A2:A12000),10^99),RO W($A$2:INDEX(A:A,SUMPRODUCT(--(A2:A12000="larry")*(A1:A11999<"larry")*(A3:A1200 1<"larry")))))-SMALL(IF((A2:A12000="larry")*(A1:A11999<"larry")* (A3:A12001<"larry"),ROW(A2:A12000),10^99),ROW($A$ 1:INDEX(A:A,SUMPRODUCT(--(A2:A12000="larry")*(A1:A11999<"larry")*(A3:A1200 1<"larry"))-1)))-1) Note: For array formulas, hold down [Ctrl] and [Shift] when you press [Enter], instead of just pressing [Enter]. Yeah....I know....it's not very elegant, but it seems to get the job done. <g Does that help? *********** Regards, Ron XL2002, WinXP "Vasilis Tergen" wrote: How would I find the maximum amount of intervals between "Larry" re- appearing in column A? I must use a formula- not a macro or a filter. The correct answer in this example= 4. That means that in column A, the maximum absence (or intervals) of "Larry" re- appearing was 4 times, which occured between rows 2 and 7. (Ex: As opposed to rows: 7 - 9, where he was only absent once) Rows Column A 1 John 2 Larry 3 John 4 John 5 John 6 Mary 7 Larry 8 Mary 9 Larry 10 John |
#18
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
COUNTIF in between rows
To all parties having participated in this inquiry:
The formula I've been looking for may have not been conceived as an interpolation of traditional functions. However, a UDF, similar to the one kindly provided by JMB works. Insofar as applying this function to my target, statistical report however, a more interesting conclusion was drawn: Using arguably one of the most powerful pc's available to the public, 200 cells containing complex formulas such as Mr. Coderres' or JMBs', managed to halt excel repeatedly and ultimately, permanently. The report I've created, presents statistical & other information, regarding an average of 800, ever-changing names. The report draws information from a separate worksheet, which contains 12.000 rows and 22 columns of data. More rows are added daily, averaging 12.000 additional rows/ year. 12000 rows* 22 columns= 264.000 cells. The resulting report worksheet, contains 800 rows (one for each name) and 20 columns. Each of these cells, except for the names, contain mostly complex array formulas. Thus, resulting in a total of 800*22= 17.600 cells. I have found that if more than 10 rows of the report are filled with formulas, (200 cells), excel cannot function. Thus, regrettably, I now realize how the initial question was doomed from the start. Thank you very much for your cooperation. "Vasilis Tergen" wrote: Sir, Your reply is greatly appreciated. I invoked the formula you provided repeatedly. Unfortunately, its' calculation result is not the desired one. A) In a blank worksheet, containing only column A, which in turn, contains "Larry" & other, various names, totalling 29 rows, it results in a "11971" count. B) In the target workbook, the results are of even greater disparity. C) I can't be entirely certain as of the error in calculation, as the formula in question repeats COUNTIF commands, using discretionary (& or volatile) references. P.S. I am sorry I couldn't reply sooner. However, I had (& have) to reply to all others which graciously offered a reply to this post, in the order that they did. (Including thoroughly testing their replies respectively) "JMB" wrote: Another formula solution - but not practical with the number of cells you're trying to calculate (and also array entered): =MAX(IF(COUNTIF(A1:A12000,"larry")1,LARGE((A1:A12 000="larry")*ROW(A1:A12000),ROW(INDIRECT("1:"&COUN TIF(A1:A12000,"larry")-1)))-LARGE((A1:A12000="larry")*(ROW(A1:A12000)),ROW(IND IRECT("2:"&COUNTIF(A1:A12000,"larry"))))-1),IF(A1<"larry",MATCH("larry",A1:A12000,0)-1),IF(A12000<"larry",ROWS(A1:A12000)+ROW(A1)-1-MAX(IF(A1:A12000="larry",ROW(A1:A12000),"")))) Change the range reference as needed. It takes a little bit to calculate (but still under a minute on my machine). Without using any helper columns or VBA- you almost have to use an array formula, which will be quite a calculation load. If you have to do all 800 names, I'd look at using a custom function. "Vasilis Tergen" wrote: How would I find the maximum amount of intervals between "Larry" re- appearing in column A? I must use a formula- not a macro or a filter. The correct answer in this example= 4. That means that in column A, the maximum absence (or intervals) of "Larry" re- appearing was 4 times, which occured between rows 2 and 7. (Ex: As opposed to rows: 7 - 9, where he was only absent once) Rows Column A 1 John 2 Larry 3 John 4 John 5 John 6 Mary 7 Larry 8 Mary 9 Larry 10 John |
#19
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
COUNTIF in between rows
Got back pretty late from a retirement party for one of my co-workers.
I'll try to take a look at it again tomorrow (if the problem is still unresolved). "Vasilis Tergen" wrote: Sir, Admittedly, I initially hadn't expected to need a UDF. This, however, is the smartest approach simply because any alternative (traditional- funtion) method, would result in an augmented formula, which would in turn cause even the most powerful machines to halt. (When updating the 12000 rows) I effected your UDF. Unfortunately, there seems to be a syntax error. (Probably related to the first statement line) I regrettably must also ask you to consider altering the syntax altogether, as your (the previous) reply, reffered to my initial and incomplete question. The complete question can be found under my reply to Mr. Coderres' 2nd reply. Lastly, I must clarify that I am not knowledgeable enough to alter or correct your UDF myself. I cannot but be both overwhelmed and hopeful by and of a further, forward-pressing response. "JMB" wrote: A UDF that appears to work okay: syntax is: =maxinterval(A1:A12000,"larry") Function MaxInterval(rngData As Range, _ varCriteria As Variant) As Long Dim lngLast As Long Dim i As Long lngLast = 0 For i = 1 To rngData.Rows.Count If rngData(i, 1) = varCriteria Then MaxInterval = Application.Max(MaxInterval, i - lngLast - 1) lngLast = i ElseIf i = rngData.Rows.Count Then MaxInterval = Application.Max(MaxInterval, i - lngLast) End If Next i End Function "Vasilis Tergen" wrote: How would I find the maximum amount of intervals between "Larry" re- appearing in column A? I must use a formula- not a macro or a filter. The correct answer in this example= 4. That means that in column A, the maximum absence (or intervals) of "Larry" re- appearing was 4 times, which occured between rows 2 and 7. (Ex: As opposed to rows: 7 - 9, where he was only absent once) Rows Column A 1 John 2 Larry 3 John 4 John 5 John 6 Mary 7 Larry 8 Mary 9 Larry 10 John |
#20
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
COUNTIF in between rows
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" wrote in message ... 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" wrote: 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 "Vasilis Tergen" wrote in 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...) "Roger Govier" wrote: 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 "Vasilis Tergen" wrote 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 excel terms) I would more than likely be able to complete the needed formula. Thank you very much. "Ron Coderre" wrote: Here you go..... Try something like this: With A2:A12000 contains names, with "larry" interspersed This ARRAY FORMULA returns the largest consecutive gap between "larry" cells B1: =MAX(SMALL(IF((A2:A12000="larry")*(A1:A11999<"lar ry")*(A3:A12001<"larry"),ROW(A2:A12000),10^99),RO W($A$2:INDEX(A:A,SUMPRODUCT(--(A2:A12000="larry")*(A1:A11999<"larry")*(A3:A1200 1<"larry")))))-SMALL(IF((A2:A12000="larry")*(A1:A11999<"larry")* (A3:A12001<"larry"),ROW(A2:A12000),10^99),ROW($A$ 1:INDEX(A:A,SUMPRODUCT(--(A2:A12000="larry")*(A1:A11999<"larry")*(A3:A1200 1<"larry"))-1)))-1) Note: For array formulas, hold down [Ctrl] and [Shift] when you press [Enter], instead of just pressing [Enter]. Yeah....I know....it's not very elegant, but it seems to get the job done. <g Does that help? *********** Regards, Ron XL2002, WinXP "Vasilis Tergen" wrote: How would I find the maximum amount of intervals between "Larry" re- appearing in column A? I must use a formula- not a macro or a filter. The correct answer in this example= 4. That means that in column A, the maximum absence (or intervals) of "Larry" re- appearing was 4 times, which occured between rows 2 and 7. (Ex: As opposed to rows: 7 - 9, where he was only absent once) Rows Column A 1 John 2 Larry 3 John 4 John 5 John 6 Mary 7 Larry 8 Mary 9 Larry 10 John |
#21
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
COUNTIF in between rows
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) 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" wrote: 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" wrote in message ... 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" wrote: 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 "Vasilis Tergen" wrote in 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...) "Roger Govier" wrote: 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 "Vasilis Tergen" wrote 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Countif rows | Excel Discussion (Misc queries) | |||
Alternating BG colors between groups of rows | Excel Discussion (Misc queries) | |||
(Unsuccessfully!) Unhiding Rows in Excel 2003 | Excel Worksheet Functions | |||
countif for only visible rows when combined with autofilter - possible? | Excel Discussion (Misc queries) | |||
Hiding Rows if the linked rows are blank | Excel Discussion (Misc queries) |