If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
|
#1
|
|||
|
|||
MATCH Multiple Criteria & Return Previous / Penultimate Match
Hi All,
I am using a dynamic range called "Data", spanning many rows and columns. The range holds TEXT data and starts at row number 12, column "H". The oldest data is in row 12, the start / top of my range; the most recent data is at the end / bottom of my range. The value to be returned is numeric and held in a single column, dynamic range called "ID", adjacent to dynamic range "Data". The return value will be returned down a single column. I would like the Formula to be flexible using Input cells to hold the varying criteria. The MATCH sequence will be: 1) Match specific Text 2) Match varying sequential numbers of EMPTY TEXT rows (could be anything from 0 (zero) EMPTY TEXT rows to 100+ EMPTY TEXT rows in sequential row order) . NB: When the match of EMPTY TEXT is 0: there should be two sequential row matches of the same TEXT (as found in number 1 above). 3) Return previous / penultimate MATCH of the above (1 & 2). I've tried a few variations but still NO eureka! =IF(AW7="","",INDEX(MATCH(2,(INDEX(1/((OFFSET('Site Lond'!Appraisal,0,ROWS($1: 1)-1,,1)=TEXT(AW7,0)))*(OFFSET('Site Lond'!Appraisal,1,ROWS($1:1)-1,,1)=ROWS (AZ7)&""),0,1)))+OFFSET('Site Lond'!ID,,,,1),0,1))-1 =IF(AW7="","",INDEX(MATCH(1,(OFFSET('Site Lond'!Appraisal,0,ROWS($1:1)-1,,1) =TEXT(AW7,0))*(OFFSET('Site Lond'!Appraisal,1,ROWS($1:1)-1,,1)=ROWS(AZ7)&"")) +OFFSET('Site Lond'!ID,,,,1),0,1))-1 Thanks Sam -- Message posted via http://www.officekb.com |
#2
|
|||
|
|||
MATCH Multiple Criteria & Return Previous / Penultimate Match
Hi All,
Just to clarify. The Criteria to Match: The MATCH sequence will be: 1) Match specific Text In Column1 - Single occurrence of Text value zero "0". (changing criteria) 2) Match varying sequential numbers of EMPTY TEXT rows (could be anything from 0 (zero) EMPTY TEXT rows to 100+ EMPTY TEXT rows in sequential row order) NB: When the match of EMPTY TEXT is 0: there should be two sequential row matches of the same TEXT (as found in number 1 above). In Column1 - Match one EmptyText row after / below single occurrence of Text value zero "0". (changing criteria). 3) Return previous / penultimate MATCH of the above (1 & 2). Return previous / penultimate match of single instance of Text value zero "0" with one EmptyText row below it. Expected Result ID 1304. In the non-working formula below: Cell AW7 holds the Text value of interest. Cell AZ7 holds the number of EmptyText rows to match. The Formula needs to filldown a single column. =IF(AW7="","",INDEX(MATCH(2,(INDEX(1/((OFFSET('Site Lond'!Appraisal,0,ROWS($1: 1)-1,,1)=TEXT(AW7,0)))*(OFFSET('Site Lond'!Appraisal,1,ROWS($1:1)-1,,1)=ROWS (AZ7)&""),0,1)))+OFFSET('Site Lond'!ID,,,,1),0,1))-1 Sample Data Layout: ID, Col1, Col2, Col3, 1300, EmptyText,1, EmptyText, 1301, 00, EmptyText,EmptyText, 1302, 0, 1, EmptyText, 1303, 0, 1, EmptyText, 1304, EmptyText,1, 2, 1305, 00, EmptyText,2, 1306, 0, EmptyText,EmptyText, 1307, 0, EmptyText,EmptyText, 1308, 0, 1, EmptyText, 1309, EmptyText,1, 22, NB: Commas separate the columns. 1) Match specific Text for Column2 - Single occurrence of Text value "1" 2) Match varying sequential numbers of EMPTY TEXT rows - Match zero EmptyText row after / below single occurrence of Text value one "1". this means two sequential instances of text value "1". 3) Return previous / penultimate MATCH of the above (1 & 2). - Return previous / penultimate match of single instance of Text value one "1" with zero EmptyText row below it. Expected Result ID 1304. Cheers Sam Sam wrote: Hi All, I am using a dynamic range called "Data", spanning many rows and columns. The range holds TEXT data and starts at row number 12, column "H". The oldest data is in row 12, the start / top of my range; the most recent data is at the end / bottom of my range. The value to be returned is numeric and held in a single column, dynamic range called "ID", adjacent to dynamic range "Data". The return value will be returned down a single column. I would like the Formula to be flexible using Input cells to hold the varying criteria. The MATCH sequence will be: 1) Match specific Text 2) Match varying sequential numbers of EMPTY TEXT rows (could be anything from 0 (zero) EMPTY TEXT rows to 100+ EMPTY TEXT rows in sequential row order) . NB: When the match of EMPTY TEXT is 0: there should be two sequential row matches of the same TEXT (as found in number 1 above). 3) Return previous / penultimate MATCH of the above (1 & 2). I've tried a few variations but still NO eureka! =IF(AW7="","",INDEX(MATCH(2,(INDEX(1/((OFFSET('Site Lond'!Appraisal,0,ROWS($1: 1)-1,,1)=TEXT(AW7,0)))*(OFFSET('Site Lond'!Appraisal,1,ROWS($1:1)-1,,1)=ROWS (AZ7)&""),0,1)))+OFFSET('Site Lond'!ID,,,,1),0,1))-1 =IF(AW7="","",INDEX(MATCH(1,(OFFSET('Site Lond'!Appraisal,0,ROWS($1:1)-1,,1) =TEXT(AW7,0))*(OFFSET('Site Lond'!Appraisal,1,ROWS($1:1)-1,,1)=ROWS(AZ7)&"")) +OFFSET('Site Lond'!ID,,,,1),0,1))-1 Thanks Sam -- Message posted via OfficeKB.com http://www.officekb.com/Uwe/Forums.a...tions/200709/1 |
#3
|
|||
|
|||
MATCH Multiple Criteria & Return Previous / Penultimate Match
Try...
=IF(AW7"",INDEX(ID,MATCH(2,1/((OFFSET(INDEX(Data,0,2),,,ROWS(Data)-1)=T EXT(AW7,0))*(OFFSET(INDEX(Data,0,2),1,,ROWS(Data)-1)="")))),"") ....confirmed with CONTROL+SHIFT+ENTER. Hope this helps! In article 78e69a99cec71@uwe, "Sam via OfficeKB.com" u4102@uwe wrote: Sample Data Layout: ID, Col1, Col2, Col3, 1300, EmptyText,1, EmptyText, 1301, 00, EmptyText,EmptyText, 1302, 0, 1, EmptyText, 1303, 0, 1, EmptyText, 1304, EmptyText,1, 2, 1305, 00, EmptyText,2, 1306, 0, EmptyText,EmptyText, 1307, 0, EmptyText,EmptyText, 1308, 0, 1, EmptyText, 1309, EmptyText,1, 22, NB: Commas separate the columns. 1) Match specific Text for Column2 - Single occurrence of Text value "1" 2) Match varying sequential numbers of EMPTY TEXT rows - Match zero EmptyText row after / below single occurrence of Text value one "1". this means two sequential instances of text value "1". 3) Return previous / penultimate MATCH of the above (1 & 2). - Return previous / penultimate match of single instance of Text value one "1" with zero EmptyText row below it. Expected Result ID 1304. Cheers Sam |
#4
|
|||
|
|||
MATCH Multiple Criteria & Return Previous / Penultimate Match
Hi Domenic,
Thank you very much for reply and assistance. The first cell returned the LAST ID occurrence of the matched text pattern. I require the previous / penultimate occurrence of the matched pattern. The following cells returned error value #N/A. The Formula: 1) Where you've hard coded the INDEX column with 2, I've changed that to ROWS ($1:1) , so that I can access the correct columns when the Formula is filled down. 2) I cannot see where in the Formula the varied number of EmptyText rows is matched. This varies for each cell row and also needs to be filled down. In my non-working version of the Formula, I was using cell AZ7 to hold the number of emptyText rows I need to match after the Text value. 3) The Text value and the number of EmptyText rows to match are both variable criteria. 4) Amending the Formula as in 1 above, all cells return the LAST matched occurrence, but I require the previous / penultimate occurrence of the matched pattern (Text Value = cell AW7 and varied EmptyText rows = cell AZ7). These criteria will fill down with each row. Further assistance appreciated. Cheers, Sam Domenic wrote: Try... =IF(AW7"",INDEX(ID,MATCH(2,1/((OFFSET(INDEX(Data,0,2),,,ROWS(Data)-1)=T EXT(AW7,0))*(OFFSET(INDEX(Data,0,2),1,,ROWS(Dat a)-1)="")))),"") ...confirmed with CONTROL+SHIFT+ENTER. Hope this helps! -- Message posted via OfficeKB.com http://www.officekb.com/Uwe/Forums.a...tions/200709/1 |
#5
|
|||
|
|||
MATCH Multiple Criteria & Return Previous / Penultimate Match
Assuming that the formula will be entered in BA7 and copied down, try
the following... 1) Select cell BA7 2) Define the following... Insert Name Define Name: Range Refers to: =INDEX(Data,0,ROWS($BA$7:$BA7)) Click Add Name: Arrray1 Refers to: =TRANSPOSE(ROW(INDIRECT("1:"&$AZ7))) Click Add Name: Array2 Refers to: =MMULT(Array1^0,ISNUMBER(MATCH(TRANSPOSE(IF(Range= TEXT($AX7,0),ROW(Range) +Array1,"")),IF(Range="",ROW(Range)),0))+0) Click Ok 3) Enter the following formula in BA7, and copy down: =INDEX(ID,LARGE(IF(Array2=$AZ7,TRANSPOSE(ROW(ID)-MIN(ROW(ID))+1)),2)) ....confirmed with CONTROL+SHIFT+ENTER **Note that if cell AX7 is formatted as 'Text', TEXT($AX7,0) can be replaced with $AX7. Hope this helps! In article 78e8c043e0a20@uwe, "Sam via OfficeKB.com" u4102@uwe wrote: Hi Domenic, Thank you very much for reply and assistance. The first cell returned the LAST ID occurrence of the matched text pattern. I require the previous / penultimate occurrence of the matched pattern. The following cells returned error value #N/A. The Formula: 1) Where you've hard coded the INDEX column with 2, I've changed that to ROWS ($1:1) , so that I can access the correct columns when the Formula is filled down. 2) I cannot see where in the Formula the varied number of EmptyText rows is matched. This varies for each cell row and also needs to be filled down. In my non-working version of the Formula, I was using cell AZ7 to hold the number of emptyText rows I need to match after the Text value. 3) The Text value and the number of EmptyText rows to match are both variable criteria. 4) Amending the Formula as in 1 above, all cells return the LAST matched occurrence, but I require the previous / penultimate occurrence of the matched pattern (Text Value = cell AW7 and varied EmptyText rows = cell AZ7). These criteria will fill down with each row. Further assistance appreciated. Cheers, Sam Domenic wrote: Try... =IF(AW7"",INDEX(ID,MATCH(2,1/((OFFSET(INDEX(Data,0,2),,,ROWS(Data)-1)=T EXT(AW7,0))*(OFFSET(INDEX(Data,0,2),1,,ROWS(Dat a)-1)="")))),"") ...confirmed with CONTROL+SHIFT+ENTER. Hope this helps! |
#6
|
|||
|
|||
MATCH Multiple Criteria & Return Previous / Penultimate Match
Hi Domenic,
Thank you for your time and assistance. 1) The Formula returns incorrect results with some #REF! errors. I think the #REF! errors may be from =TRANSPOSE(ROW(INDIRECT("1:"&$AZ7))). Not sure? 2) Also, I think the Formula is trying to return the penultimate ID that matches the specific TEXT criteria above the EmptyText rows. I require the penultimate ID that matches first, Text criteria and then the specific number of variable EmptyText rows below the Text criteria. The penultimate ID returned should match with the last EmptyText row criteria. Further assistance most appreciated. Cheers, Sam Domenic wrote: Assuming that the formula will be entered in BA7 and copied down, try the following... 1) Select cell BA7 2) Define the following... Insert Name Define Name: Range Refers to: =INDEX(Data,0,ROWS($BA$7:$BA7)) Click Add Name: Arrray1 Refers to: =TRANSPOSE(ROW(INDIRECT("1:"&$AZ7))) Click Add Name: Array2 Refers to: =MMULT(Array1^0,ISNUMBER(MATCH(TRANSPOSE(IF(Range =TEXT($AX7,0),ROW(Range) +Array1,"")),IF(Range="",ROW(Range)),0))+0) Click Ok 3) Enter the following formula in BA7, and copy down: =INDEX(ID,LARGE(IF(Array2=$AZ7,TRANSPOSE(ROW(ID )-MIN(ROW(ID))+1)),2)) ...confirmed with CONTROL+SHIFT+ENTER **Note that if cell AX7 is formatted as 'Text', TEXT($AX7,0) can be replaced with $AX7. Hope this helps! -- Message posted via OfficeKB.com http://www.officekb.com/Uwe/Forums.a...tions/200709/1 |
Thread Tools | |
Display Modes | |
|
|