|
| 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
April 9th, 2010, 05:27 PM
posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Help with function, not sure which one
I don't want to use a macro...is it possible to do the following with
functions? New York Joe Tim Kim Jane San Diego Tim Jane St Louis Joe Tim Kim If I enter New York I want excel to return Joe, Tim, Kim, Jane in a cell by looking at the table above which will be in the same worksheet If I enter St Louis I want it to Return Joe, Tim, Kim in a cell by looking at the above etc. Thanks so much I am having a hard time deciding where to start. 
|
|
#2
April 9th, 2010, 06:31 PM
posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Help with function, not sure which one
Assuming your data in A1:E3 and the person name in "one word"
Criteria in G1 In H1: =SUBSTITUTE(TRIM(INDEX(CONCATENATE(B1:B3," ",C1:C3," ",D1  3," ",E1:E3),MATCH(G1,A1:A3,)))," ",", ") "excelrookie" wrote: I don't want to use a macro...is it possible to do the following with functions? New York Joe Tim Kim Jane San Diego Tim Jane St Louis Joe Tim Kim If I enter New York I want excel to return Joe, Tim, Kim, Jane in a cell by looking at the table above which will be in the same worksheet If I enter St Louis I want it to Return Joe, Tim, Kim in a cell by looking at the above etc. Thanks so much I am having a hard time deciding where to start.
|
|
#3
April 9th, 2010, 07:10 PM
posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Help with function, not sure which one
Thank you. I have 90 rows and 12 columns so I will work on making the
formula longer, however when i tried the short version it only had 1 of the answers instead of two and it showed that one over and over? Any thoughts? Thanks "Teethless mama" wrote: Assuming your data in A1:E3 and the person name in "one word" Criteria in G1 In H1: =SUBSTITUTE(TRIM(INDEX(CONCATENATE(B1:B3," ",C1:C3," ",D1 3," ",E1:E3),MATCH(G1,A1:A3,)))," ",", ") "excelrookie" wrote: I don't want to use a macro...is it possible to do the following with functions? New York Joe Tim Kim Jane San Diego Tim Jane St Louis Joe Tim Kim If I enter New York I want excel to return Joe, Tim, Kim, Jane in a cell by looking at the table above which will be in the same worksheet If I enter St Louis I want it to Return Joe, Tim, Kim in a cell by looking at the above etc. Thanks so much I am having a hard time deciding where to start.
|
|
#4
April 10th, 2010, 03:25 AM
posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Help with function, not sure which one
Increase your column width
"excelrookie" wrote: Thank you. I have 90 rows and 12 columns so I will work on making the formula longer, however when i tried the short version it only had 1 of the answers instead of two and it showed that one over and over? Any thoughts? Thanks "Teethless mama" wrote: Assuming your data in A1:E3 and the person name in "one word" Criteria in G1 In H1: =SUBSTITUTE(TRIM(INDEX(CONCATENATE(B1:B3," ",C1:C3," ",D1 3," ",E1:E3),MATCH(G1,A1:A3,)))," ",", ") "excelrookie" wrote: I don't want to use a macro...is it possible to do the following with functions? New York Joe Tim Kim Jane San Diego Tim Jane St Louis Joe Tim Kim If I enter New York I want excel to return Joe, Tim, Kim, Jane in a cell by looking at the table above which will be in the same worksheet If I enter St Louis I want it to Return Joe, Tim, Kim in a cell by looking at the above etc. Thanks so much I am having a hard time deciding where to start.
|
|
#5
April 14th, 2010, 04:31 PM
posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Help with function, not sure which one
I increased the column width and that is how I saw that only 1 of the 2
correct answers was repeating over and over. Any other suggestions? You have gotten me the closest to the correct formula, it's just not working yet Thank you "Teethless mama" wrote: Increase your column width "excelrookie" wrote: Thank you. I have 90 rows and 12 columns so I will work on making the formula longer, however when i tried the short version it only had 1 of the answers instead of two and it showed that one over and over? Any thoughts? Thanks "Teethless mama" wrote: Assuming your data in A1:E3 and the person name in "one word" Criteria in G1 In H1: =SUBSTITUTE(TRIM(INDEX(CONCATENATE(B1:B3," ",C1:C3," ",D1 3," ",E1:E3),MATCH(G1,A1:A3,)))," ",", ") "excelrookie" wrote: I don't want to use a macro...is it possible to do the following with functions? New York Joe Tim Kim Jane San Diego Tim Jane St Louis Joe Tim Kim If I enter New York I want excel to return Joe, Tim, Kim, Jane in a cell by looking at the table above which will be in the same worksheet If I enter St Louis I want it to Return Joe, Tim, Kim in a cell by looking at the above etc. Thanks so much I am having a hard time deciding where to start.
|
| Thread Tools | |
| Display Modes | |
|
|
Linear Mode
