Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Running out of Functions
Hi I want to do make a long function with lots of IF's but have used the max
amount of functions. Can someone help? Perhaps another method or even how to translate my code into a macro ( I dont know much about macros or even how to use them) =IF(ISERROR(MATCH(A2,G:G,0)),IF(ISERROR(MATCH(A2,H :H,0)),"NotFound",$H$7),$G$7)..................... .............etc |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Running out of Functions
Are you trying to match the value in A2 to a range...
The below array formula will return a value greater than 0 for a match.. =MIN(IF(G1:J1000="Green",ROW(G1:J1000))) If this post helps click Yes --------------- Jacob Skaria "Dingerz" wrote: Hi I want to do make a long function with lots of IF's but have used the max amount of functions. Can someone help? Perhaps another method or even how to translate my code into a macro ( I dont know much about macros or even how to use them) =IF(ISERROR(MATCH(A2,G:G,0)),IF(ISERROR(MATCH(A2,H :H,0)),"NotFound",$H$7),$G$7)..................... .............etc |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Running out of Functions
Jacob,
That did not help Thanks anyway. I have got a few more columns in my formula. =IF(ISERROR(MATCH(A25,G:G,0)),IF(ISERROR(MATCH(A25 ,H:H,0)),IF(ISERROR(MATCH(A25,I:I,0)),IF(ISERROR(M ATCH(A25,J:J,0)),IF(ISERROR(MATCH(A25,K:K,0)),IF(I SERROR(MATCH(A25,L:L,0)),"NotFound",6),5),4),3),2) ,1) But swtill i am limited. basically it looks like this 1 2 3 4 5 A dhhs fgkgj hhhh kkkk hghhg B ggjnk jghn rfh hhh ccfg etyiikn This continues for alot of colums and rows. Now I have another bunch of data which iam trying to see if is in the data above. If so what column is it in. IE. Cell G9 (Another set of data) contains hhhh. I want it to tell me that there is a duplicate item in column 3. At the moment as you can see from my formula im checking row by row but iam limited by the number of rows i can check Regards, Garrath "Jacob Skaria" wrote: Are you trying to match the value in A2 to a range... The below array formula will return a value greater than 0 for a match.. =MIN(IF(G1:J1000="Green",ROW(G1:J1000))) If this post helps click Yes --------------- Jacob Skaria "Dingerz" wrote: Hi I want to do make a long function with lots of IF's but have used the max amount of functions. Can someone help? Perhaps another method or even how to translate my code into a macro ( I dont know much about macros or even how to use them) =IF(ISERROR(MATCH(A2,G:G,0)),IF(ISERROR(MATCH(A2,H :H,0)),"NotFound",$H$7),$G$7)..................... .............etc |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Running out of Functions
Try the below in a new workbook and once done you should be able to work out
a formula... Open a new workbook. Enter a text; say "Dingerz" into any cell in the range of A2:Z10000 Enter the below formula in A1. Please note that this is an array formula. Within the cell in edit mode (F2) paste this formula and press Ctrl+Shift+Enter to apply this formula. If successful in 'Formula Bar' you can notice the curly braces at both ends like "{=<formula}" =MIN(IF(A2:Z10000="Dingerz",COLUMN(A2:Z10000))) This should return the column number..... If this post helps click Yes --------------- Jacob Skaria "Dingerz" wrote: Jacob, That did not help Thanks anyway. I have got a few more columns in my formula. =IF(ISERROR(MATCH(A25,G:G,0)),IF(ISERROR(MATCH(A25 ,H:H,0)),IF(ISERROR(MATCH(A25,I:I,0)),IF(ISERROR(M ATCH(A25,J:J,0)),IF(ISERROR(MATCH(A25,K:K,0)),IF(I SERROR(MATCH(A25,L:L,0)),"NotFound",6),5),4),3),2) ,1) But swtill i am limited. basically it looks like this 1 2 3 4 5 A dhhs fgkgj hhhh kkkk hghhg B ggjnk jghn rfh hhh ccfg etyiikn This continues for alot of colums and rows. Now I have another bunch of data which iam trying to see if is in the data above. If so what column is it in. IE. Cell G9 (Another set of data) contains hhhh. I want it to tell me that there is a duplicate item in column 3. At the moment as you can see from my formula im checking row by row but iam limited by the number of rows i can check Regards, Garrath "Jacob Skaria" wrote: Are you trying to match the value in A2 to a range... The below array formula will return a value greater than 0 for a match.. =MIN(IF(G1:J1000="Green",ROW(G1:J1000))) If this post helps click Yes --------------- Jacob Skaria "Dingerz" wrote: Hi I want to do make a long function with lots of IF's but have used the max amount of functions. Can someone help? Perhaps another method or even how to translate my code into a macro ( I dont know much about macros or even how to use them) =IF(ISERROR(MATCH(A2,G:G,0)),IF(ISERROR(MATCH(A2,H :H,0)),"NotFound",$H$7),$G$7)..................... .............etc |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Running out of Functions
On Thu, 21 May 2009 23:56:01 -0700, Dingerz
wrote: I have got a few more columns in my formula. =IF(ISERROR(MATCH(A25,G:G,0)),IF(ISERROR(MATCH(A2 5,H:H,0)),IF(ISERROR(MATCH(A25,I:I,0)),IF(ISERROR( MATCH(A25,J:J,0)),IF(ISERROR(MATCH(A25,K:K,0)),IF( ISERROR(MATCH(A25,L:L,0)),"NotFound",6),5),4),3),2 ),1) But swtill i am limited. basically it looks like this 1 2 3 4 5 A dhhs fgkgj hhhh kkkk hghhg B ggjnk jghn rfh hhh ccfg etyiikn This continues for alot of colums and rows. Now I have another bunch of data which iam trying to see if is in the data above. If so what column is it in. IE. Cell G9 (Another set of data) contains hhhh. I want it to tell me that there is a duplicate item in column 3. At the moment as you can see from my formula im checking row by row but iam limited by the number of rows i can check To return the Column number of data (in A25) in your data table (Name the range: Tbl ), try this **array-entered** formula: =MATCH(A25,OFFSET(Tbl,MATCH(1,(OFFSET(Tbl,0,0,,1)= A25)+ (OFFSET(Tbl,0,1,,1)=A25)+(OFFSET(Tbl,0,2,,1)=A25)+ (OFFSET(Tbl,0,3,,1)=A25)+(OFFSET(Tbl,0,4,,1)=A25)+ (OFFSET(Tbl,0,5,,1)=A25),0)-1,0,1),0) This formula must be **array-entered**: ---------------------------------------- To **array-enter** a formula, after entering the formula into the cell or formula bar, hold down <ctrl<shift while hitting <enter. If you did this correctly, Excel will place braces {...} around the formula. Note that with versions of Excel prior to 2007, you cannot reference an entire column in an array formula. Also note that I used a NAME'd range for your data table. This will make future maintenance of this formula much simpler than hard-coding the ranges. Tbl could even be defined as a dynamic range, so as to avoid "oversizing" the range to search. This will return #N/A if the data in A25 is not found in Tbl. So you could also do something like: Pre 2007: (**array-entered**) =IF(ISNA(MATCH(A25,OFFSET(Tbl,MATCH(1,(OFFSET(Tbl, 0,0,,1)=A25)+ (OFFSET(Tbl,0,1,,1)=A25)+(OFFSET(Tbl,0,2,,1)=A25)+ (OFFSET(Tbl,0,3,,1)=A25)+(OFFSET(Tbl,0,4,,1)=A25)+ (OFFSET(Tbl,0,5,,1)=A25),0)-1,0,1),0)),"Not Found", MATCH(A25,OFFSET(Tbl,MATCH(1,(OFFSET(Tbl,0,0,,1)=A 25)+ (OFFSET(Tbl,0,1,,1)=A25)+(OFFSET(Tbl,0,2,,1)=A25)+ (OFFSET(Tbl,0,3,,1)=A25)+(OFFSET(Tbl,0,4,,1)=A25)+ (OFFSET(Tbl,0,5,,1)=A25),0)-1,0,1),0)) Or, if you have 2007+: =IFERROR(MATCH(A25,OFFSET(Tbl,MATCH(1,(OFFSET(Tbl, 0,0,,1)=A25)+ (OFFSET(Tbl,0,1,,1)=A25)+(OFFSET(Tbl,0,2,,1)=A25)+ (OFFSET(Tbl,0,3,,1)=A25)+(OFFSET(Tbl,0,4,,1)=A25)+ (OFFSET(Tbl,0,5,,1)=A25),0)-1,0,1),0),"Not Found") --ron |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
Running out of Functions
On Fri, 22 May 2009 07:29:25 -0400, Ron Rosenfeld
wrote: On Thu, 21 May 2009 23:56:01 -0700, Dingerz wrote: I have got a few more columns in my formula. =IF(ISERROR(MATCH(A25,G:G,0)),IF(ISERROR(MATCH(A 25,H:H,0)),IF(ISERROR(MATCH(A25,I:I,0)),IF(ISERROR (MATCH(A25,J:J,0)),IF(ISERROR(MATCH(A25,K:K,0)),IF (ISERROR(MATCH(A25,L:L,0)),"NotFound",6),5),4),3), 2),1) But swtill i am limited. basically it looks like this 1 2 3 4 5 A dhhs fgkgj hhhh kkkk hghhg B ggjnk jghn rfh hhh ccfg etyiikn This continues for alot of colums and rows. Now I have another bunch of data which iam trying to see if is in the data above. If so what column is it in. IE. Cell G9 (Another set of data) contains hhhh. I want it to tell me that there is a duplicate item in column 3. At the moment as you can see from my formula im checking row by row but iam limited by the number of rows i can check To return the Column number of data (in A25) in your data table (Name the range: Tbl ), try this **array-entered** formula: =MATCH(A25,OFFSET(Tbl,MATCH(1,(OFFSET(Tbl,0,0,,1) =A25)+ (OFFSET(Tbl,0,1,,1)=A25)+(OFFSET(Tbl,0,2,,1)=A25) + (OFFSET(Tbl,0,3,,1)=A25)+(OFFSET(Tbl,0,4,,1)=A25) + (OFFSET(Tbl,0,5,,1)=A25),0)-1,0,1),0) This formula must be **array-entered**: ---------------------------------------- To **array-enter** a formula, after entering the formula into the cell or formula bar, hold down <ctrl<shift while hitting <enter. If you did this correctly, Excel will place braces {...} around the formula. Note that with versions of Excel prior to 2007, you cannot reference an entire column in an array formula. Also note that I used a NAME'd range for your data table. This will make future maintenance of this formula much simpler than hard-coding the ranges. Tbl could even be defined as a dynamic range, so as to avoid "oversizing" the range to search. This will return #N/A if the data in A25 is not found in Tbl. So you could also do something like: Pre 2007: (**array-entered**) =IF(ISNA(MATCH(A25,OFFSET(Tbl,MATCH(1,(OFFSET(Tbl ,0,0,,1)=A25)+ (OFFSET(Tbl,0,1,,1)=A25)+(OFFSET(Tbl,0,2,,1)=A25) + (OFFSET(Tbl,0,3,,1)=A25)+(OFFSET(Tbl,0,4,,1)=A25) + (OFFSET(Tbl,0,5,,1)=A25),0)-1,0,1),0)),"Not Found", MATCH(A25,OFFSET(Tbl,MATCH(1,(OFFSET(Tbl,0,0,,1)= A25)+ (OFFSET(Tbl,0,1,,1)=A25)+(OFFSET(Tbl,0,2,,1)=A25) + (OFFSET(Tbl,0,3,,1)=A25)+(OFFSET(Tbl,0,4,,1)=A25) + (OFFSET(Tbl,0,5,,1)=A25),0)-1,0,1),0)) Or, if you have 2007+: =IFERROR(MATCH(A25,OFFSET(Tbl,MATCH(1,(OFFSET(Tbl ,0,0,,1)=A25)+ (OFFSET(Tbl,0,1,,1)=A25)+(OFFSET(Tbl,0,2,,1)=A25) + (OFFSET(Tbl,0,3,,1)=A25)+(OFFSET(Tbl,0,4,,1)=A25) + (OFFSET(Tbl,0,5,,1)=A25),0)-1,0,1),0),"Not Found") --ron Forget this. Jacob's response is much simpler. It could also be rewritten as the **array-formula**: =MIN(IF(A25=Tbl,COLUMN(Tbl))) However, the number returned will be the absolute column number, and the column number within Tbl. --ron |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How to convert cell formula functions to code functions | Excel Discussion (Misc queries) | |||
formula/functions for average and if functions | Excel Worksheet Functions | |||
running VBA Functions in spreadsheets | New Users to Excel | |||
efficiency: database functions vs. math functions vs. array formula | Excel Discussion (Misc queries) | |||
Looking for a site with functions that substitute the ATP functions | Excel Worksheet Functions |