Find the last row using 2 to 20 criteria
The worksheet looks like this:
Column A Column B Column C 1 3 21 5 8 26 16 21 37 1 19 49 2 37 41 3 21 51 The actual worksheet features 23 columns like the ones depicted above (A, B, C), and also features 205,000 rows of data. However, you may want to answer only in regards to the smaller table found above-and we'll take it from there. The question: How does one find the last row featuring say, the numbers 3 & 21? In the example above, the last row is row 6. Note the following: Whatever formula is to be used CANNOT use a single column as its reference. This because, any number can appear in any column. Ex: The number 3 may appear in either column A, B or C (and actually in another 21 columns of the actual worksheet). Also, instead of finding a row featuring only 2 numbers (Ex: numbers 3 & 12), there needs to be a way of also finding the LAST row featuring up to 12 chosen numbers. Ex: Find which is the last row which contains the following numbers: 1, 2, 3, 4, 5, 6, 7, 8, 10, 12, 14, 16. Lastly, we are using Office 2007 (using multiple cores) and a very powerful, quad-core cpu; this, in reference to the power demand of using such array formulas... Any assistance will be greatly appreciated. P.S. The closest we've come to the answer looks like this (Array formula): =MAX(IF((A1:A6=3)*(A1:C6=21),ROW(A1:C6),"")). This formula would work if the number 3 appeared only under column A... HOWEVER: This formula will not work because the reference to the first # (number 3) is made only in regards to column A. Instead, it should've refered to A1:C6-which is impossible in this formula. |
Find the last row using 2 to 20 criteria
Assuming that A1:W205000 contains the data, and Y1:AJ1 contains the 12
chosen numbers, try... =MAX(IF(MMULT(ISNUMBER(MATCH(A1:W205000,Y1:AJ1,0)) +0,TRANSPOSE(COLUMN(A1: W205000)^0))=COUNT(Y1:AJ1),ROW(A1:W5000))) or =MATCH(TRUE,MMULT(ISNUMBER(MATCH(A1:W205000,Y1:AJ1 ,0))+0,TRANSPOSE(COLUMN (A1:W205000)^0))=COUNT(Y1:AJ1),0)+ROW(A1)-1 Note that these formulas need to be confirmed with CONTROL+SHIFT+ENTER. -- Domenic Microsoft Excel MVP www.xl-central.com Your Quick Reference to Excel Solutions In article , Minister wrote: The worksheet looks like this: Column A Column B Column C 1 3 21 5 8 26 16 21 37 1 19 49 2 37 41 3 21 51 The actual worksheet features 23 columns like the ones depicted above (A, B, C), and also features 205,000 rows of data. However, you may want to answer only in regards to the smaller table found above-and we'll take it from there. The question: How does one find the last row featuring say, the numbers 3 & 21? In the example above, the last row is row 6. Note the following: Whatever formula is to be used CANNOT use a single column as its reference. This because, any number can appear in any column. Ex: The number 3 may appear in either column A, B or C (and actually in another 21 columns of the actual worksheet). Also, instead of finding a row featuring only 2 numbers (Ex: numbers 3 & 12), there needs to be a way of also finding the LAST row featuring up to 12 chosen numbers. Ex: Find which is the last row which contains the following numbers: 1, 2, 3, 4, 5, 6, 7, 8, 10, 12, 14, 16. Lastly, we are using Office 2007 (using multiple cores) and a very powerful, quad-core cpu; this, in reference to the power demand of using such array formulas... Any assistance will be greatly appreciated. P.S. The closest we've come to the answer looks like this (Array formula): =MAX(IF((A1:A6=3)*(A1:C6=21),ROW(A1:C6),"")). This formula would work if the number 3 appeared only under column A... HOWEVER: This formula will not work because the reference to the first # (number 3) is made only in regards to column A. Instead, it should've refered to A1:C6-which is impossible in this formula. |
Find the last row using 2 to 20 criteria
Try this (array formula)
=MAX(IF(ISERROR(FIND("-" & A2:C7 &"-","-3-21-49-22-")),0, ROW(A2:C7))) Use CTRL+SHIFT+Enter instead of Enter "Minister" a écrit dans le message de ... The worksheet looks like this: Column A Column B Column C 1 3 21 5 8 26 16 21 37 1 19 49 2 37 41 3 21 51 The actual worksheet features 23 columns like the ones depicted above (A, B, C), and also features 205,000 rows of data. However, you may want to answer only in regards to the smaller table found above-and we'll take it from there. The question: How does one find the last row featuring say, the numbers 3 & 21? In the example above, the last row is row 6. Note the following: Whatever formula is to be used CANNOT use a single column as its reference. This because, any number can appear in any column. Ex: The number 3 may appear in either column A, B or C (and actually in another 21 columns of the actual worksheet). Also, instead of finding a row featuring only 2 numbers (Ex: numbers 3 & 12), there needs to be a way of also finding the LAST row featuring up to 12 chosen numbers. Ex: Find which is the last row which contains the following numbers: 1, 2, 3, 4, 5, 6, 7, 8, 10, 12, 14, 16. Lastly, we are using Office 2007 (using multiple cores) and a very powerful, quad-core cpu; this, in reference to the power demand of using such array formulas... Any assistance will be greatly appreciated. P.S. The closest we've come to the answer looks like this (Array formula): =MAX(IF((A1:A6=3)*(A1:C6=21),ROW(A1:C6),"")). This formula would work if the number 3 appeared only under column A... HOWEVER: This formula will not work because the reference to the first # (number 3) is made only in regards to column A. Instead, it should've refered to A1:C6-which is impossible in this formula. |
Find the last row using 2 to 20 criteria
If the data you are looking for are
evenly allocated among your table then a public function might be faster. -------------------------------------------------- Public Function LastRow(xTab As Range, xCriteria As String) As Long 'xRange is the range of data ex: A2W25000 'xCriteria are the numbers to find in Xrange 'xCriteria is a string like "_1_2_3_4_5_6_7_8_10_12_14_16_" 'The function could be called by: '=LastRow(A2:W205000,"_1_2_3_4_5_6_7_8_10_12_14_16 _") Dim xI As Long, xJ As Long, xLastRow As Long, xLastCol As Long Dim xFirstRow As Long, xFirstColumn As Long Application.Volatile LastRow = 0 xFirstRow = xTab.Row xFirstColumn = xTab.Column xLastRow = xTab.Row + xTab.Rows.Count - 1 xLastCol = xTab.Column + xTab.Columns.Count - 1 For xI = xLastRow To xFirstRow Step -1 For xJ = xLastCol To xFirstColumn Step -1 If InStr(xCriteria, "_" & Cells(xI, xJ) & "_") 0 Then LastRow = xI Exit Function End If Next xJ Next xI End Function -------------------------------------------------- "Minister" a écrit dans le message de ... The worksheet looks like this: Column A Column B Column C 1 3 21 5 8 26 16 21 37 1 19 49 2 37 41 3 21 51 The actual worksheet features 23 columns like the ones depicted above (A, B, C), and also features 205,000 rows of data. However, you may want to answer only in regards to the smaller table found above-and we'll take it from there. The question: How does one find the last row featuring say, the numbers 3 & 21? In the example above, the last row is row 6. Note the following: Whatever formula is to be used CANNOT use a single column as its reference. This because, any number can appear in any column. Ex: The number 3 may appear in either column A, B or C (and actually in another 21 columns of the actual worksheet). Also, instead of finding a row featuring only 2 numbers (Ex: numbers 3 & 12), there needs to be a way of also finding the LAST row featuring up to 12 chosen numbers. Ex: Find which is the last row which contains the following numbers: 1, 2, 3, 4, 5, 6, 7, 8, 10, 12, 14, 16. Lastly, we are using Office 2007 (using multiple cores) and a very powerful, quad-core cpu; this, in reference to the power demand of using such array formulas... Any assistance will be greatly appreciated. P.S. The closest we've come to the answer looks like this (Array formula): =MAX(IF((A1:A6=3)*(A1:C6=21),ROW(A1:C6),"")). This formula would work if the number 3 appeared only under column A... HOWEVER: This formula will not work because the reference to the first # (number 3) is made only in regards to column A. Instead, it should've refered to A1:C6-which is impossible in this formula. |
Find the last row using 2 to 20 criteria
Sorry, I did not correctly read your message:
My formula return the last row if at least one number or more of the numbers to be found are in the row (and not all then numbers) "Charabeuh" a écrit dans le message de ... Try this (array formula) =MAX(IF(ISERROR(FIND("-" & A2:C7 &"-","-3-21-49-22-")),0, ROW(A2:C7))) Use CTRL+SHIFT+Enter instead of Enter "Minister" a écrit dans le message de ... The worksheet looks like this: Column A Column B Column C 1 3 21 5 8 26 16 21 37 1 19 49 2 37 41 3 21 51 The actual worksheet features 23 columns like the ones depicted above (A, B, C), and also features 205,000 rows of data. However, you may want to answer only in regards to the smaller table found above-and we'll take it from there. The question: How does one find the last row featuring say, the numbers 3 & 21? In the example above, the last row is row 6. Note the following: Whatever formula is to be used CANNOT use a single column as its reference. This because, any number can appear in any column. Ex: The number 3 may appear in either column A, B or C (and actually in another 21 columns of the actual worksheet). Also, instead of finding a row featuring only 2 numbers (Ex: numbers 3 & 12), there needs to be a way of also finding the LAST row featuring up to 12 chosen numbers. Ex: Find which is the last row which contains the following numbers: 1, 2, 3, 4, 5, 6, 7, 8, 10, 12, 14, 16. Lastly, we are using Office 2007 (using multiple cores) and a very powerful, quad-core cpu; this, in reference to the power demand of using such array formulas... Any assistance will be greatly appreciated. P.S. The closest we've come to the answer looks like this (Array formula): =MAX(IF((A1:A6=3)*(A1:C6=21),ROW(A1:C6),"")). This formula would work if the number 3 appeared only under column A... HOWEVER: This formula will not work because the reference to the first # (number 3) is made only in regards to column A. Instead, it should've refered to A1:C6-which is impossible in this formula. |
Find the last row using 2 to 20 criteria
Sorry, I did not correctly read your message:
My function return the last row if at least one number or more of the numbers to be found are in the row (and not all then numbers) "Charabeuh" a écrit dans le message de ... If the data you are looking for are evenly allocated among your table then a public function might be faster. -------------------------------------------------- Public Function LastRow(xTab As Range, xCriteria As String) As Long 'xRange is the range of data ex: A2W25000 'xCriteria are the numbers to find in Xrange 'xCriteria is a string like "_1_2_3_4_5_6_7_8_10_12_14_16_" 'The function could be called by: '=LastRow(A2:W205000,"_1_2_3_4_5_6_7_8_10_12_14_16 _") Dim xI As Long, xJ As Long, xLastRow As Long, xLastCol As Long Dim xFirstRow As Long, xFirstColumn As Long Application.Volatile LastRow = 0 xFirstRow = xTab.Row xFirstColumn = xTab.Column xLastRow = xTab.Row + xTab.Rows.Count - 1 xLastCol = xTab.Column + xTab.Columns.Count - 1 For xI = xLastRow To xFirstRow Step -1 For xJ = xLastCol To xFirstColumn Step -1 If InStr(xCriteria, "_" & Cells(xI, xJ) & "_") 0 Then LastRow = xI Exit Function End If Next xJ Next xI End Function -------------------------------------------------- "Minister" a écrit dans le message de ... The worksheet looks like this: Column A Column B Column C 1 3 21 5 8 26 16 21 37 1 19 49 2 37 41 3 21 51 The actual worksheet features 23 columns like the ones depicted above (A, B, C), and also features 205,000 rows of data. However, you may want to answer only in regards to the smaller table found above-and we'll take it from there. The question: How does one find the last row featuring say, the numbers 3 & 21? In the example above, the last row is row 6. Note the following: Whatever formula is to be used CANNOT use a single column as its reference. This because, any number can appear in any column. Ex: The number 3 may appear in either column A, B or C (and actually in another 21 columns of the actual worksheet). Also, instead of finding a row featuring only 2 numbers (Ex: numbers 3 & 12), there needs to be a way of also finding the LAST row featuring up to 12 chosen numbers. Ex: Find which is the last row which contains the following numbers: 1, 2, 3, 4, 5, 6, 7, 8, 10, 12, 14, 16. Lastly, we are using Office 2007 (using multiple cores) and a very powerful, quad-core cpu; this, in reference to the power demand of using such array formulas... Any assistance will be greatly appreciated. P.S. The closest we've come to the answer looks like this (Array formula): =MAX(IF((A1:A6=3)*(A1:C6=21),ROW(A1:C6),"")). This formula would work if the number 3 appeared only under column A... HOWEVER: This formula will not work because the reference to the first # (number 3) is made only in regards to column A. Instead, it should've refered to A1:C6-which is impossible in this formula. |
Find the last row using 2 to 20 criteria
Positively fabulous answer.
Your assistance is very, very much appreciated. I should send over a bottle of champagne... "Domenic" wrote: Assuming that A1:W205000 contains the data, and Y1:AJ1 contains the 12 chosen numbers, try... =MAX(IF(MMULT(ISNUMBER(MATCH(A1:W205000,Y1:AJ1,0)) +0,TRANSPOSE(COLUMN(A1: W205000)^0))=COUNT(Y1:AJ1),ROW(A1:W5000))) or =MATCH(TRUE,MMULT(ISNUMBER(MATCH(A1:W205000,Y1:AJ1 ,0))+0,TRANSPOSE(COLUMN (A1:W205000)^0))=COUNT(Y1:AJ1),0)+ROW(A1)-1 Note that these formulas need to be confirmed with CONTROL+SHIFT+ENTER. -- Domenic Microsoft Excel MVP www.xl-central.com Your Quick Reference to Excel Solutions In article , Minister wrote: The worksheet looks like this: Column A Column B Column C 1 3 21 5 8 26 16 21 37 1 19 49 2 37 41 3 21 51 The actual worksheet features 23 columns like the ones depicted above (A, B, C), and also features 205,000 rows of data. However, you may want to answer only in regards to the smaller table found above-and we'll take it from there. The question: How does one find the last row featuring say, the numbers 3 & 21? In the example above, the last row is row 6. Note the following: Whatever formula is to be used CANNOT use a single column as its reference. This because, any number can appear in any column. Ex: The number 3 may appear in either column A, B or C (and actually in another 21 columns of the actual worksheet). Also, instead of finding a row featuring only 2 numbers (Ex: numbers 3 & 12), there needs to be a way of also finding the LAST row featuring up to 12 chosen numbers. Ex: Find which is the last row which contains the following numbers: 1, 2, 3, 4, 5, 6, 7, 8, 10, 12, 14, 16. Lastly, we are using Office 2007 (using multiple cores) and a very powerful, quad-core cpu; this, in reference to the power demand of using such array formulas... Any assistance will be greatly appreciated. P.S. The closest we've come to the answer looks like this (Array formula): =MAX(IF((A1:A6=3)*(A1:C6=21),ROW(A1:C6),"")). This formula would work if the number 3 appeared only under column A... HOWEVER: This formula will not work because the reference to the first # (number 3) is made only in regards to column A. Instead, it should've refered to A1:C6-which is impossible in this formula. |
Find the last row using 2 to 20 criteria
Il y a un probleme: Je voudrais que les nombres (1,2,3,4,....) existe sur une autre tableaux comme ca: Y1=1, Z1=2, AA=3.... Alors, je peux pas rechercher les nombres avec une "UDF", parce que ces nombres doit pouvoir changer. Votre "UDF" c'est admirable neanmois. Merci beaucoup. "Charabeuh" wrote: Sorry, I did not correctly read your message: My function return the last row if at least one number or more of the numbers to be found are in the row (and not all then numbers) "Charabeuh" a écrit dans le message de ... If the data you are looking for are evenly allocated among your table then a public function might be faster. -------------------------------------------------- Public Function LastRow(xTab As Range, xCriteria As String) As Long 'xRange is the range of data ex: A2W25000 'xCriteria are the numbers to find in Xrange 'xCriteria is a string like "_1_2_3_4_5_6_7_8_10_12_14_16_" 'The function could be called by: '=LastRow(A2:W205000,"_1_2_3_4_5_6_7_8_10_12_14_16 _") Dim xI As Long, xJ As Long, xLastRow As Long, xLastCol As Long Dim xFirstRow As Long, xFirstColumn As Long Application.Volatile LastRow = 0 xFirstRow = xTab.Row xFirstColumn = xTab.Column xLastRow = xTab.Row + xTab.Rows.Count - 1 xLastCol = xTab.Column + xTab.Columns.Count - 1 For xI = xLastRow To xFirstRow Step -1 For xJ = xLastCol To xFirstColumn Step -1 If InStr(xCriteria, "_" & Cells(xI, xJ) & "_") 0 Then LastRow = xI Exit Function End If Next xJ Next xI End Function -------------------------------------------------- "Minister" a écrit dans le message de ... The worksheet looks like this: Column A Column B Column C 1 3 21 5 8 26 16 21 37 1 19 49 2 37 41 3 21 51 The actual worksheet features 23 columns like the ones depicted above (A, B, C), and also features 205,000 rows of data. However, you may want to answer only in regards to the smaller table found above-and we'll take it from there. The question: How does one find the last row featuring say, the numbers 3 & 21? In the example above, the last row is row 6. Note the following: Whatever formula is to be used CANNOT use a single column as its reference. This because, any number can appear in any column. Ex: The number 3 may appear in either column A, B or C (and actually in another 21 columns of the actual worksheet). Also, instead of finding a row featuring only 2 numbers (Ex: numbers 3 & 12), there needs to be a way of also finding the LAST row featuring up to 12 chosen numbers. Ex: Find which is the last row which contains the following numbers: 1, 2, 3, 4, 5, 6, 7, 8, 10, 12, 14, 16. Lastly, we are using Office 2007 (using multiple cores) and a very powerful, quad-core cpu; this, in reference to the power demand of using such array formulas... Any assistance will be greatly appreciated. P.S. The closest we've come to the answer looks like this (Array formula): =MAX(IF((A1:A6=3)*(A1:C6=21),ROW(A1:C6),"")). This formula would work if the number 3 appeared only under column A... HOWEVER: This formula will not work because the reference to the first # (number 3) is made only in regards to column A. Instead, it should've refered to A1:C6-which is impossible in this formula. |
Find the last row using 2 to 20 criteria
You're very welcome! I take it you caught the typo?
ROW(A1:W5000) should be ROW(A1:W205000) -- Domenic Microsoft Excel MVP www.xl-central.com Your Quick Reference to Excel Solutions In article , Minister wrote: Positively fabulous answer. Your assistance is very, very much appreciated. I should send over a bottle of champagne... "Domenic" wrote: Assuming that A1:W205000 contains the data, and Y1:AJ1 contains the 12 chosen numbers, try... =MAX(IF(MMULT(ISNUMBER(MATCH(A1:W205000,Y1:AJ1,0)) +0,TRANSPOSE(COLUMN(A1: W205000)^0))=COUNT(Y1:AJ1),ROW(A1:W5000))) or =MATCH(TRUE,MMULT(ISNUMBER(MATCH(A1:W205000,Y1:AJ1 ,0))+0,TRANSPOSE(COLUMN (A1:W205000)^0))=COUNT(Y1:AJ1),0)+ROW(A1)-1 Note that these formulas need to be confirmed with CONTROL+SHIFT+ENTER. -- Domenic Microsoft Excel MVP www.xl-central.com Your Quick Reference to Excel Solutions In article , Minister wrote: The worksheet looks like this: Column A Column B Column C 1 3 21 5 8 26 16 21 37 1 19 49 2 37 41 3 21 51 The actual worksheet features 23 columns like the ones depicted above (A, B, C), and also features 205,000 rows of data. However, you may want to answer only in regards to the smaller table found above-and we'll take it from there. The question: How does one find the last row featuring say, the numbers 3 & 21? In the example above, the last row is row 6. Note the following: Whatever formula is to be used CANNOT use a single column as its reference. This because, any number can appear in any column. Ex: The number 3 may appear in either column A, B or C (and actually in another 21 columns of the actual worksheet). Also, instead of finding a row featuring only 2 numbers (Ex: numbers 3 & 12), there needs to be a way of also finding the LAST row featuring up to 12 chosen numbers. Ex: Find which is the last row which contains the following numbers: 1, 2, 3, 4, 5, 6, 7, 8, 10, 12, 14, 16. Lastly, we are using Office 2007 (using multiple cores) and a very powerful, quad-core cpu; this, in reference to the power demand of using such array formulas... Any assistance will be greatly appreciated. P.S. The closest we've come to the answer looks like this (Array formula): =MAX(IF((A1:A6=3)*(A1:C6=21),ROW(A1:C6),"")). This formula would work if the number 3 appeared only under column A... HOWEVER: This formula will not work because the reference to the first # (number 3) is made only in regards to column A. Instead, it should've refered to A1:C6-which is impossible in this formula. |
All times are GMT +1. The time now is 04:49 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com