Repeat in functions?
Hi. I've writte an function that looks for a word in column B and searches for that word in a column in a different worksheet, when found it returns a word found in column B in that row. For example: Worksheet "Schema": A B C D 1 a hi zoo now 2 b go now zoo 3 c lo zoo zoo worksheet "home": A B C D 1 a zoo 2 b zoo 3 c now Now the formula in worksheet "home" cell C1 should look for zoo (content in B1) in column C in worksheet "Schema" and return "hi lo". The formula in cell D1 should look for zoo (B1) in column D in "schema" and return "go lo": ie: worksheet "home": A B C D 1 a zoo hi lo go lo 2 b zoo hi lo go lo 3 c now go hi this is the current formula: Code: -------------------- =OM(ÄRSAKNAD(FÖRSKJUTNING(Schema!A1;PASSA($B20;Sch ema!D$1:Schema!D$400;0)-1;0));"-";FÖRSKJUTNING(Schema!$A$1;PASSA($B20;Schema!D$1:S chema!D$400;0)-1;0)) -------------------- or in english: Code: -------------------- =IF(ISMISSING(DISPLACEMENT(Schema!A1;WATCH($B20;Sc hema!D$1:Schema!D$400;0)-1;0));"-";DISPLACEMENT(Schema!$A$1;WATCH($B20;Schema!D$1:S chema!D$400;0)-1;0)) -------------------- (My translation) (Displacement moves relativeley, WATCH finds the content in one cell in another column) Now the problem, it only finds the first occasion and only returns one value, can it be repeated in some easy way to return every (up to 5) occasions in the column. -- Ruatha ------------------------------------------------------------------------ Ruatha's Profile: http://www.excelforum.com/member.php...o&userid=31083 View this thread: http://www.excelforum.com/showthread...hreadid=507578 |
Repeat in functions?
This will work up to 5 occurences. Use the array formula, entered with Ctrl-Shift-Enter, in C1, and copy to C1:D??: (change the 10's to a number higher than your row count on Schema) =TRIM(IF(COUNTIF(Schema!C$1:C$10,$B1)=5,INDEX(Sch ema!$B:$B,LARGE(((Schema!C$1:C$10=$B1)*ROW(Schema! C$1:C$10)),5)),"") & " " &IF(COUNTIF(Schema!C$1:C$10,$B1)=4,INDEX(Schema!$ B:$B,LARGE(((Schema!C$1:C$10=$B1)*ROW(Schema!C$1:C $10)),4)),"") & " " &IF(COUNTIF(Schema!C$1:C$10,$B1)=3,INDEX(Schema!$ B:$B,LARGE(((Schema!C$1:C$10=$B1)*ROW(Schema!C$1:C $10)),3)),"") & " " &IF(COUNTIF(Schema!C$1:C$10,$B1)=2,INDEX(Schema!$ B:$B,LARGE(((Schema!C$1:C$10=$B1)*ROW(Schema!C$1:C $10)),2)),"") & " " &IF(COUNTIF(Schema!C$1:C$10,$B1)=1,INDEX(Schema!$ B:$B,LARGE(((Schema!C$1:C$10=$B1)*ROW(Schema!C$1:C $10)),1)),"")) Note that this is the underlying "unit" formula: IF(COUNTIF(Schema!C$1:C$10,$B1)=5,INDEX(Schema!$B :$B,LARGE(((Schema!C$1:C$10=$B1)*ROW(Schema!C$1:C$ 10)),5) Where the 5 finds the 5th occurence... This will find the last five occurences. If you need to find the first five occurences, then your need to change the second 5 to the formula (with the progression) COUNTIF(Schema!C$1:C$10,$B1) COUNTIF(Schema!C$1:C$10,$B1) - 1 COUNTIF(Schema!C$1:C$10,$B1) - 2 HTH, Bernie MS Excel MVP "Ruatha" wrote in message ... Hi. I've writte an function that looks for a word in column B and searches for that word in a column in a different worksheet, when found it returns a word found in column B in that row. For example: Worksheet "Schema": A B C D 1 a hi zoo now 2 b go now zoo 3 c lo zoo zoo worksheet "home": A B C D 1 a zoo 2 b zoo 3 c now Now the formula in worksheet "home" cell C1 should look for zoo (content in B1) in column C in worksheet "Schema" and return "hi lo". The formula in cell D1 should look for zoo (B1) in column D in "schema" and return "go lo": ie: worksheet "home": A B C D 1 a zoo hi lo go lo 2 b zoo hi lo go lo 3 c now go hi this is the current formula: Code: -------------------- =OM(ÄRSAKNAD(FÖRSKJUTNING(Schema!A1;PASSA($B20;Sch ema!D$1:Schema!D$400;0)-1;0));"-";FÖRSKJUTNING(Schema!$A$1;PASSA($B20;Schema!D$1:S chema!D$400;0)-1;0)) -------------------- or in english: Code: -------------------- =IF(ISMISSING(DISPLACEMENT(Schema!A1;WATCH($B20;Sc hema!D$1:Schema!D$400;0)-1;0));"-";DISPLACEMENT(Schema!$A$1;WATCH($B20;Schema!D$1:S chema!D$400;0)-1;0)) -------------------- (My translation) (Displacement moves relativeley, WATCH finds the content in one cell in another column) Now the problem, it only finds the first occasion and only returns one value, can it be repeated in some easy way to return every (up to 5) occasions in the column. -- Ruatha ------------------------------------------------------------------------ Ruatha's Profile: http://www.excelforum.com/member.php...o&userid=31083 View this thread: http://www.excelforum.com/showthread...hreadid=507578 |
Repeat in functions?
Thankyou Very Much! -- Ruatha ------------------------------------------------------------------------ Ruatha's Profile: http://www.excelforum.com/member.php...o&userid=31083 View this thread: http://www.excelforum.com/showthread...hreadid=507578 |
Repeat in functions?
It works great. Here is your formula in Swedish: Code: -------------------- =RENSA(OM(ANTAL.OM(Schema!C$3:C$82;$B18)=5;INDEX( Schema!$A:$A;STÖRSTA(((Schema!C$3:C$82=$B18) *RAD(Schema!C$3:C$82));5));"")&" "&OM(ANTAL.OM(Schema!C$3:C$82;$B18)=4;INDEX(Schem a!$A:$A;STÖRSTA(((Schema!C$3:C$82=$B18)*RAD(Schema !C$3:C$82));4));"")&" "&OM(ANTAL.OM(Schema!C$3:C$82;$B18)=3;INDEX(Schem a!$A:$A;STÖRSTA(((Schema!C$3:C$82=$B18)*RAD(Schema !C$3:C$82));3));"")&" "&OM(ANTAL.OM(Schema!C$3:C$82;$B18)=2;INDEX(Schem a!$A:$A;STÖRSTA(((Schema!C$3:C$82=$B18)*RAD(Schema !C$3:C$82));2));"")&" "&OM(ANTAL.OM(Schema!C$3:C$82;$B18)=1;INDEX(Schem a!$A:$A;STÖRSTA(((Schema!C$3:C$82=$B18)*RAD(Schema !C$3:C$82));1));"")) -------------------- -- Ruatha ------------------------------------------------------------------------ Ruatha's Profile: http://www.excelforum.com/member.php...o&userid=31083 View this thread: http://www.excelforum.com/showthread...hreadid=507578 |
All times are GMT +1. The time now is 12:15 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com