Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Oooh, I guarantee you are going to have fun with this one.
Let us say I have 5 columns:
A / B / C / D / E 010101 / 01 / -V / 010101-01-V / 5 010101 / 02 / -V / 010101-02-V / 7 010101 / 07 / -CH / 010101-07-CH / 6.3 010101 / 03 / -V / 010101-03-V / 2.1 010101 / 12 / -CH / 010101-01-CH / 9.1 010101 / 04 / -V / 010101-04-V / 1215 010101 / 69 / -CH / 010101-69-CH / 32 And so on, for many many rows. Now, let us say the first 4 (A to D) columns are text, and column 4 (D) is a string of columns 1, 2 & 3. As you can see, the codes ending in CH are not consecutive numerically, nor next to each other. My problem is this, I can not know what is the next -CH value after the first without copying down a formula for at least two columns. I mean, the first one I can find with no problem using VLOOKUP("-CH",C1:D7,2,false). Now, I would need to, based on the first -CH value (or any other means) determine what the second is, and then the third based on the second and so on, to use a VLOOKUP formula to get at the values on column 5 (E). Now the problem is compounded by the fact that I can not use filters or any other fancy methods ;) because some of the ppl that are going to be using the workbook hardly know how to open the file. Sorry for the lengthy explanation, but I believe it was necessary in order to understand the problem. Thanks in advance for any help you can give me, and if you can't, well, you have helped me plenty in the past, so thanks anyway :D. Regards, Joe |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Oooh, I guarantee you are going to have fun with this one.
Hi!
Not sure which column of values you want to return, column D or E? Or both? Anyhow, this will return the values from column E for each corresponfing instance of "-CH". Entered as an array using the key combination of CTRL,SHIFT,ENTER: (pay attention to this statement! It means something!!) =IF(ROWS($1:1)<=COUNTIF(C$1:C$7,"-CH"),INDEX(E$1:E$7,SMALL(IF(C$1:C$7="-CH",ROW(C$1:C$7)-ROW(C$1)+1),ROWS($1:1))),"") Copy down until you get blanks. Biff "Clueless" wrote in message ... Let us say I have 5 columns: A / B / C / D / E 010101 / 01 / -V / 010101-01-V / 5 010101 / 02 / -V / 010101-02-V / 7 010101 / 07 / -CH / 010101-07-CH / 6.3 010101 / 03 / -V / 010101-03-V / 2.1 010101 / 12 / -CH / 010101-01-CH / 9.1 010101 / 04 / -V / 010101-04-V / 1215 010101 / 69 / -CH / 010101-69-CH / 32 And so on, for many many rows. Now, let us say the first 4 (A to D) columns are text, and column 4 (D) is a string of columns 1, 2 & 3. As you can see, the codes ending in CH are not consecutive numerically, nor next to each other. My problem is this, I can not know what is the next -CH value after the first without copying down a formula for at least two columns. I mean, the first one I can find with no problem using VLOOKUP("-CH",C1:D7,2,false). Now, I would need to, based on the first -CH value (or any other means) determine what the second is, and then the third based on the second and so on, to use a VLOOKUP formula to get at the values on column 5 (E). Now the problem is compounded by the fact that I can not use filters or any other fancy methods ;) because some of the ppl that are going to be using the workbook hardly know how to open the file. Sorry for the lengthy explanation, but I believe it was necessary in order to understand the problem. Thanks in advance for any help you can give me, and if you can't, well, you have helped me plenty in the past, so thanks anyway :D. Regards, Joe |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Oooh, I guarantee you are going to have fun with this one.
Try this *array* formula:
=IF(COUNTIF(C$1:C$7,"-CH")=ROWS($1:1),INDEX(E$1:E$7,SMALL(IF(C$1:C$7= "-CH", ROW($1:$7)),ROW(1:1))),"") -- Array formulas must be entered with CSE, <Ctrl <Shift <Enter, instead of the regular <Enter, which will *automatically* enclose the formula in curly brackets, which *cannot* be done manually. You *must also* use CSE when revising the formula. After the CSE entry, copy down as far as you anticipate there will be returns. You could reference a cell that contains your lookup value ("-CH", "-V", ....etc.) so that you could change the value without having to change the formula, say F1: =IF(COUNTIF(C$1:C$7,$F$1)=ROWS($1:1),INDEX(E$1:E$ 7,SMALL(IF(C$1:C$7=$F$1,RO W($1:$7)),ROW(1:1))),"") -- HTH, RD --------------------------------------------------------------------------- Please keep all correspondence within the NewsGroup, so all may benefit ! --------------------------------------------------------------------------- "Clueless" wrote in message ... Let us say I have 5 columns: A / B / C / D / E 010101 / 01 / -V / 010101-01-V / 5 010101 / 02 / -V / 010101-02-V / 7 010101 / 07 / -CH / 010101-07-CH / 6.3 010101 / 03 / -V / 010101-03-V / 2.1 010101 / 12 / -CH / 010101-01-CH / 9.1 010101 / 04 / -V / 010101-04-V / 1215 010101 / 69 / -CH / 010101-69-CH / 32 And so on, for many many rows. Now, let us say the first 4 (A to D) columns are text, and column 4 (D) is a string of columns 1, 2 & 3. As you can see, the codes ending in CH are not consecutive numerically, nor next to each other. My problem is this, I can not know what is the next -CH value after the first without copying down a formula for at least two columns. I mean, the first one I can find with no problem using VLOOKUP("-CH",C1:D7,2,false). Now, I would need to, based on the first -CH value (or any other means) determine what the second is, and then the third based on the second and so on, to use a VLOOKUP formula to get at the values on column 5 (E). Now the problem is compounded by the fact that I can not use filters or any other fancy methods ;) because some of the ppl that are going to be using the workbook hardly know how to open the file. Sorry for the lengthy explanation, but I believe it was necessary in order to understand the problem. Thanks in advance for any help you can give me, and if you can't, well, you have helped me plenty in the past, so thanks anyway :D. Regards, Joe |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Oooh, I guarantee you are going to have fun with this one.
Guys,
Thanks for the prompt response, but that is just the thing, I did not want to use drop down formulas because it would take a lot of space. Perhaps further explanation is necessary. The last column (E) is a numerical value, but is not the last column in my spreadsheet. As a matter of fact there are about 20 more columns with numerical data, each of course with different information. In case there is a -CH value, that means it's a check, and that check could be for one or several of the parameters in columns E to XX, now the data in the main spreadsheet is arranged horizontally, but in order to present it to my client, the parameters have to be presented vertically. The value I need to find is the 010101-XX-CH right after the first 010101-XX-CH so I can use VLOOKUP to get the numeric data out of the main spreadsheet. If it can't be done I may need to change the format of column B to numbers, but that creates a whole different set of unforseen problems. "Ragdyer" wrote: Try this *array* formula: =IF(COUNTIF(C$1:C$7,"-CH")=ROWS($1:1),INDEX(E$1:E$7,SMALL(IF(C$1:C$7= "-CH", ROW($1:$7)),ROW(1:1))),"") -- Array formulas must be entered with CSE, <Ctrl <Shift <Enter, instead of the regular <Enter, which will *automatically* enclose the formula in curly brackets, which *cannot* be done manually. You *must also* use CSE when revising the formula. After the CSE entry, copy down as far as you anticipate there will be returns. You could reference a cell that contains your lookup value ("-CH", "-V", ....etc.) so that you could change the value without having to change the formula, say F1: =IF(COUNTIF(C$1:C$7,$F$1)=ROWS($1:1),INDEX(E$1:E$ 7,SMALL(IF(C$1:C$7=$F$1,RO W($1:$7)),ROW(1:1))),"") -- HTH, RD --------------------------------------------------------------------------- Please keep all correspondence within the NewsGroup, so all may benefit ! --------------------------------------------------------------------------- "Clueless" wrote in message ... Let us say I have 5 columns: A / B / C / D / E 010101 / 01 / -V / 010101-01-V / 5 010101 / 02 / -V / 010101-02-V / 7 010101 / 07 / -CH / 010101-07-CH / 6.3 010101 / 03 / -V / 010101-03-V / 2.1 010101 / 12 / -CH / 010101-01-CH / 9.1 010101 / 04 / -V / 010101-04-V / 1215 010101 / 69 / -CH / 010101-69-CH / 32 And so on, for many many rows. Now, let us say the first 4 (A to D) columns are text, and column 4 (D) is a string of columns 1, 2 & 3. As you can see, the codes ending in CH are not consecutive numerically, nor next to each other. My problem is this, I can not know what is the next -CH value after the first without copying down a formula for at least two columns. I mean, the first one I can find with no problem using VLOOKUP("-CH",C1:D7,2,false). Now, I would need to, based on the first -CH value (or any other means) determine what the second is, and then the third based on the second and so on, to use a VLOOKUP formula to get at the values on column 5 (E). Now the problem is compounded by the fact that I can not use filters or any other fancy methods ;) because some of the ppl that are going to be using the workbook hardly know how to open the file. Sorry for the lengthy explanation, but I believe it was necessary in order to understand the problem. Thanks in advance for any help you can give me, and if you can't, well, you have helped me plenty in the past, so thanks anyway :D. Regards, Joe |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Oooh, I guarantee you are going to have fun with this one.
If I understand what you're saying, you're looking to return a value from
*various* columns, dependent on the row matching a criteria. BUT ... what determines *which* row, since you have multiple rows matching the criteria? -- Regards, RD --------------------------------------------------------------------------- Please keep all correspondence within the NewsGroup, so all may benefit ! --------------------------------------------------------------------------- "Clueless" wrote in message ... Guys, Thanks for the prompt response, but that is just the thing, I did not want to use drop down formulas because it would take a lot of space. Perhaps further explanation is necessary. The last column (E) is a numerical value, but is not the last column in my spreadsheet. As a matter of fact there are about 20 more columns with numerical data, each of course with different information. In case there is a -CH value, that means it's a check, and that check could be for one or several of the parameters in columns E to XX, now the data in the main spreadsheet is arranged horizontally, but in order to present it to my client, the parameters have to be presented vertically. The value I need to find is the 010101-XX-CH right after the first 010101-XX-CH so I can use VLOOKUP to get the numeric data out of the main spreadsheet. If it can't be done I may need to change the format of column B to numbers, but that creates a whole different set of unforseen problems. "Ragdyer" wrote: Try this *array* formula: =IF(COUNTIF(C$1:C$7,"-CH")=ROWS($1:1),INDEX(E$1:E$7,SMALL(IF(C$1:C$7= "-CH", ROW($1:$7)),ROW(1:1))),"") -- Array formulas must be entered with CSE, <Ctrl <Shift <Enter, instead of the regular <Enter, which will *automatically* enclose the formula in curly brackets, which *cannot* be done manually. You *must also* use CSE when revising the formula. After the CSE entry, copy down as far as you anticipate there will be returns. You could reference a cell that contains your lookup value ("-CH", "-V", ....etc.) so that you could change the value without having to change the formula, say F1: =IF(COUNTIF(C$1:C$7,$F$1)=ROWS($1:1),INDEX(E$1:E$ 7,SMALL(IF(C$1:C$7=$F$1,RO W($1:$7)),ROW(1:1))),"") -- HTH, RD -------------------------------------------------------------------------- - Please keep all correspondence within the NewsGroup, so all may benefit ! -------------------------------------------------------------------------- - "Clueless" wrote in message ... Let us say I have 5 columns: A / B / C / D / E 010101 / 01 / -V / 010101-01-V / 5 010101 / 02 / -V / 010101-02-V / 7 010101 / 07 / -CH / 010101-07-CH / 6.3 010101 / 03 / -V / 010101-03-V / 2.1 010101 / 12 / -CH / 010101-01-CH / 9.1 010101 / 04 / -V / 010101-04-V / 1215 010101 / 69 / -CH / 010101-69-CH / 32 And so on, for many many rows. Now, let us say the first 4 (A to D) columns are text, and column 4 (D) is a string of columns 1, 2 & 3. As you can see, the codes ending in CH are not consecutive numerically, nor next to each other. My problem is this, I can not know what is the next -CH value after the first without copying down a formula for at least two columns. I mean, the first one I can find with no problem using VLOOKUP("-CH",C1:D7,2,false). Now, I would need to, based on the first -CH value (or any other means) determine what the second is, and then the third based on the second and so on, to use a VLOOKUP formula to get at the values on column 5 (E). Now the problem is compounded by the fact that I can not use filters or any other fancy methods ;) because some of the ppl that are going to be using the workbook hardly know how to open the file. Sorry for the lengthy explanation, but I believe it was necessary in order to understand the problem. Thanks in advance for any help you can give me, and if you can't, well, you have helped me plenty in the past, so thanks anyway :D. Regards, Joe |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Oooh, I guarantee you are going to have fun with this one.
I would, with a vlookup function, but in order to use vlookup, I have to know
what it is I am looking for. But never mind with an answer, apparently someone else had the same problem and came up with a solution: creating a function all of their own, which works for me like a charm. If anyone is interested, here is the link: http://www.ozgrid.com/Excel/find-nth.htm I would like to thank you all for helping me. This really is a wonderful resource and has gotten me out of a tight spot more times than I can count. Best regards, Joe "Ragdyer" wrote: If I understand what you're saying, you're looking to return a value from *various* columns, dependent on the row matching a criteria. BUT ... what determines *which* row, since you have multiple rows matching the criteria? -- Regards, RD |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Oooh, I guarantee you are going to have fun with this one.
As a topic of discussion:
The formulas that Biff and I suggested will return *all* matches to the search criteria, since you didn't stipulate in your post that you would be interested in a *specific* occurrence. However, the formulas we posted can very easily return a *specific* instance of the criteria if desired, and can accomplish this *without adding* additional columns containing additional formulas. Since your data is populating out to, say Column Z, you could enter your criteria in AA1 and the instance (occurrence) of the criteria to return in AA2, and the Column to return of *that* occurrence in AA3, and try this *array* formula: =VLOOKUP(INDEX(E1:E7,SMALL(IF(C1:C7=AA1,ROW(1:7)), AA2)),E1:Z7,AA3,0) Array formulas must be entered with CSE, <Ctrl <Shift <Enter, instead of the regular <Enter, which will *automatically* enclose the formula in curly brackets, which *cannot* be done manually. You *must also* use CSE when revising the formula. Now, since it appears that you're looking for a single formula to return a single value, there's no error checking included, and no absolutes for copying down. However, this works with your *present* data set, with *no* extra columns or formulas ... BUT ... it *is* an array formula, so you know whether or not this will satisfy your needs, as compared to the scenario displayed in the link you posted. -- Regards, RD --------------------------------------------------------------------------- Please keep all correspondence within the NewsGroup, so all may benefit ! --------------------------------------------------------------------------- "Clueless" wrote in message ... I would, with a vlookup function, but in order to use vlookup, I have to know what it is I am looking for. But never mind with an answer, apparently someone else had the same problem and came up with a solution: creating a function all of their own, which works for me like a charm. If anyone is interested, here is the link: http://www.ozgrid.com/Excel/find-nth.htm I would like to thank you all for helping me. This really is a wonderful resource and has gotten me out of a tight spot more times than I can count. Best regards, Joe "Ragdyer" wrote: If I understand what you're saying, you're looking to return a value from *various* columns, dependent on the row matching a criteria. BUT ... what determines *which* row, since you have multiple rows matching the criteria? -- Regards, RD |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|