| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
help!
I have 2 columns in sheet1, Division and Branch. Based on these two columns i
need to populate the state in a column "STATE". I have a table with branch n divisions by which state could be derived. Detailed layout is below: Sheet1 Branch Division STATE 041 0039 ? 041 0001 ? Mapping Table: Branch DIV State 041 0001 NY 041 0003 ATL 041 0008 FL 041 0010 CA 041 0015 NY 041 0021 NY 041 0023 NY 041 0024 NY 041 0026 MA 041 0027 PA 041 0030 MA 041 0031 MA 041 0038 MA 041 0039 ATL So, i need a formula n my sheet1 which has a condition if sheet1branch=41 and div=39 then ATL etc (Conditions vary as per mapping table). i cant do every value since there are a lot of records in the mapping table. Can anyone tell me how to do this? ANY HELP WLD BE APPRECIATED!...THANKS |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
help!
Your division #'s are not unique I take it? As in you can have a division
0001 in branch 41 and a division 0001 in branch 42 etc? "nick" wrote: I have 2 columns in sheet1, Division and Branch. Based on these two columns i need to populate the state in a column "STATE". I have a table with branch n divisions by which state could be derived. Detailed layout is below: Sheet1 Branch Division STATE 041 0039 ? 041 0001 ? Mapping Table: Branch DIV State 041 0001 NY 041 0003 ATL 041 0008 FL 041 0010 CA 041 0015 NY 041 0021 NY 041 0023 NY 041 0024 NY 041 0026 MA 041 0027 PA 041 0030 MA 041 0031 MA 041 0038 MA 041 0039 ATL So, i need a formula n my sheet1 which has a condition if sheet1branch=41 and div=39 then ATL etc (Conditions vary as per mapping table). i cant do every value since there are a lot of records in the mapping table. Can anyone tell me how to do this? ANY HELP WLD BE APPRECIATED!...THANKS |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
help!
Yes they are not unique...i have like 7-8 diff branches and one division
could belong to all 7 or just 1...forgot to mention that....sorry! "Tim M" wrote: Your division #'s are not unique I take it? As in you can have a division 0001 in branch 41 and a division 0001 in branch 42 etc? "nick" wrote: I have 2 columns in sheet1, Division and Branch. Based on these two columns i need to populate the state in a column "STATE". I have a table with branch n divisions by which state could be derived. Detailed layout is below: Sheet1 Branch Division STATE 041 0039 ? 041 0001 ? Mapping Table: Branch DIV State 041 0001 NY 041 0003 ATL 041 0008 FL 041 0010 CA 041 0015 NY 041 0021 NY 041 0023 NY 041 0024 NY 041 0026 MA 041 0027 PA 041 0030 MA 041 0031 MA 041 0038 MA 041 0039 ATL So, i need a formula n my sheet1 which has a condition if sheet1branch=41 and div=39 then ATL etc (Conditions vary as per mapping table). i cant do every value since there are a lot of records in the mapping table. Can anyone tell me how to do this? ANY HELP WLD BE APPRECIATED!...THANKS |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
help!
"nick" wrote: I have 2 columns in sheet1, Division and Branch. Based on these two columns i need to populate the state in a column "STATE". I have a table with branch n divisions by which state could be derived. Detailed layout is below: Sheet1 Branch Division STATE 041 0039 ? 041 0001 ? Mapping Table: Branch DIV State 041 0001 NY 041 0003 ATL 041 0008 FL 041 0010 CA 041 0015 NY 041 0021 NY 041 0023 NY 041 0024 NY 041 0026 MA 041 0027 PA 041 0030 MA 041 0031 MA 041 0038 MA 041 0039 ATL So, i need a formula n my sheet1 which has a condition if sheet1branch=41 and div=39 then ATL etc (Conditions vary as per mapping table). i cant do every value since there are a lot of records in the mapping table. Can anyone tell me how to do this? ANY HELP WLD BE APPRECIATED!...THANKS |
|
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
help!
Oh well, "sometimes the Dragon wins"......sorry about the blank post.
What I meant to say is, I would recommend CONCATENATION. For your Table, add a new column to it's left side with this formula in it and copied down =B2&"_"&C2 And name the entire table with the new column included "StateTable" Then, on Sheet1 in your STATES column, put this formula and copy down =VLOOKUP(A2&"_"&B2,StateTable,4,FALSE) hth Vaya con Dios, Chuck, CABGx3 "nick" wrote: I have 2 columns in sheet1, Division and Branch. Based on these two columns i need to populate the state in a column "STATE". I have a table with branch n divisions by which state could be derived. Detailed layout is below: Sheet1 Branch Division STATE 041 0039 ? 041 0001 ? Mapping Table: Branch DIV State 041 0001 NY 041 0003 ATL 041 0008 FL 041 0010 CA 041 0015 NY 041 0021 NY 041 0023 NY 041 0024 NY 041 0026 MA 041 0027 PA 041 0030 MA 041 0031 MA 041 0038 MA 041 0039 ATL So, i need a formula n my sheet1 which has a condition if sheet1branch=41 and div=39 then ATL etc (Conditions vary as per mapping table). i cant do every value since there are a lot of records in the mapping table. Can anyone tell me how to do this? ANY HELP WLD BE APPRECIATED!...THANKS |
|
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
help!
So Division 0001 in Branch 41 is NY
and you could have a Division 0001 in Branch 42 that is a different state other than NY? "nick" wrote: Yes they are not unique...i have like 7-8 diff branches and one division could belong to all 7 or just 1...forgot to mention that....sorry! "Tim M" wrote: Your division #'s are not unique I take it? As in you can have a division 0001 in branch 41 and a division 0001 in branch 42 etc? "nick" wrote: I have 2 columns in sheet1, Division and Branch. Based on these two columns i need to populate the state in a column "STATE". I have a table with branch n divisions by which state could be derived. Detailed layout is below: Sheet1 Branch Division STATE 041 0039 ? 041 0001 ? Mapping Table: Branch DIV State 041 0001 NY 041 0003 ATL 041 0008 FL 041 0010 CA 041 0015 NY 041 0021 NY 041 0023 NY 041 0024 NY 041 0026 MA 041 0027 PA 041 0030 MA 041 0031 MA 041 0038 MA 041 0039 ATL So, i need a formula n my sheet1 which has a condition if sheet1branch=41 and div=39 then ATL etc (Conditions vary as per mapping table). i cant do every value since there are a lot of records in the mapping table. Can anyone tell me how to do this? ANY HELP WLD BE APPRECIATED!...THANKS |
|
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
help!
damn...i missed so much information...sorry about that. Yes the state could
vary as well...for example if branch 41 and div 0039 is NY, branch 42 div 0039 could be NY branch 45 div 0001 could be NYn branch 42 div 0001 could be NY as well. "Tim M" wrote: So Division 0001 in Branch 41 is NY and you could have a Division 0001 in Branch 42 that is a different state other than NY? "nick" wrote: Yes they are not unique...i have like 7-8 diff branches and one division could belong to all 7 or just 1...forgot to mention that....sorry! "Tim M" wrote: Your division #'s are not unique I take it? As in you can have a division 0001 in branch 41 and a division 0001 in branch 42 etc? "nick" wrote: I have 2 columns in sheet1, Division and Branch. Based on these two columns i need to populate the state in a column "STATE". I have a table with branch n divisions by which state could be derived. Detailed layout is below: Sheet1 Branch Division STATE 041 0039 ? 041 0001 ? Mapping Table: Branch DIV State 041 0001 NY 041 0003 ATL 041 0008 FL 041 0010 CA 041 0015 NY 041 0021 NY 041 0023 NY 041 0024 NY 041 0026 MA 041 0027 PA 041 0030 MA 041 0031 MA 041 0038 MA 041 0039 ATL So, i need a formula n my sheet1 which has a condition if sheet1branch=41 and div=39 then ATL etc (Conditions vary as per mapping table). i cant do every value since there are a lot of records in the mapping table. Can anyone tell me how to do this? ANY HELP WLD BE APPRECIATED!...THANKS |
|
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
help!
=index(othersheet!$c$1:$c$100,
match(1,(a2=othersheet!$a$1:$a$100)*(b2=othersheet !$b$1:$b$100),0)) (all in one cell) This is an array formula. Hit ctrl-shift-enter instead of enter. If you do it correctly, excel will wrap curly brackets {} around your formula. (don't type them yourself.) Adjust the range to match--but you can't use the whole column. This returns the value in othersheet column C when column A and B (of othersheet) match A2 and B2 of the sheet with the formula. And you can add more conditions by just adding more stuff to that product portion of the formula: =index(othersheet!$d$1:$d$100, match(1,(a2=othersheet!$a$1:$a$100) *(b2=othersheet!$b$1:$b$100) *(c2=othersheet!$c$1:$c$100),0)) nick wrote: I have 2 columns in sheet1, Division and Branch. Based on these two columns i need to populate the state in a column "STATE". I have a table with branch n divisions by which state could be derived. Detailed layout is below: Sheet1 Branch Division STATE 041 0039 ? 041 0001 ? Mapping Table: Branch DIV State 041 0001 NY 041 0003 ATL 041 0008 FL 041 0010 CA 041 0015 NY 041 0021 NY 041 0023 NY 041 0024 NY 041 0026 MA 041 0027 PA 041 0030 MA 041 0031 MA 041 0038 MA 041 0039 ATL So, i need a formula n my sheet1 which has a condition if sheet1branch=41 and div=39 then ATL etc (Conditions vary as per mapping table). i cant do every value since there are a lot of records in the mapping table. Can anyone tell me how to do this? ANY HELP WLD BE APPRECIATED!...THANKS -- Dave Peterson |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Linear Mode
