Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Can I get Excel to determine the line curve formula without graph. | Excel Discussion (Misc queries) | |||
User-defined functions created in Excel 2000 fail in Excel 2003 | Excel Discussion (Misc queries) | |||
Database functions should use criteria in formula, as 1-2-3 does | Excel Worksheet Functions | |||
Title Cut Off if Rows to Repeat & Columns to Repeat are Both Selec | Excel Discussion (Misc queries) | |||
Visible rows and functions that work | Excel Worksheet Functions |