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
|
|||
|
|||
Relating one column to another
Stupid newbie question coming up:
Let's say that in column A I have a series (not sequential) of numbers. In column B I have a word that goes with that number. In column C, I have numbers, which represent the same words as the number in column a represents the word in column B. In other words, I have: Number Word Type: 1: Banana 2 2: Fruit 3: Sausage 4 4: Meat I want to end up with Number Word Type: 1: Banana Fruit 2: Fruit 3: Sausage Meat 4: Meat Search and replace is out of the question, as there are 361 "things" under about 140 "types". The output is the only way I could get it from a database which is now no longer there. Is there any way of doing this in Excel? Thanks! |
#2
|
|||
|
|||
Relating one column to another
With data starting in A2 to C100,
Add a "helper" column D. In D2, enter this formula: =IF(C2"",INDEX($B$2:$B$100,MATCH(C2,$A$2:$A$100, 0)),"") And copy down as needed. -- HTH, RD ============================================== Please keep all correspondence within the Group, so all may benefit! ============================================== "Alan Parker" wrote in message ... Stupid newbie question coming up: Let's say that in column A I have a series (not sequential) of numbers. In column B I have a word that goes with that number. In column C, I have numbers, which represent the same words as the number in column a represents the word in column B. In other words, I have: Number Word Type: 1: Banana 2 2: Fruit 3: Sausage 4 4: Meat I want to end up with Number Word Type: 1: Banana Fruit 2: Fruit 3: Sausage Meat 4: Meat Search and replace is out of the question, as there are 361 "things" under about 140 "types". The output is the only way I could get it from a database which is now no longer there. Is there any way of doing this in Excel? Thanks! |
#3
|
|||
|
|||
Relating one column to another
Add a help column for simplicity assume you use column D
assume all data starts in row 2 so in D2 put this formula =IF(C2="","",VLOOKUP(C2,$A$2:$B$500,2,0)) copy down the formula alongside C, when done copy the new column, do editpaste special as values in place, delete column C I noticed that the numbers in A have a colon after them, is that the way it is? If so use =IF(C2="","",VLOOKUP(C2&"*",$A$2:$B$500,2,0)) -- Regards, Peo Sjoblom "Alan Parker" wrote in message ... Stupid newbie question coming up: Let's say that in column A I have a series (not sequential) of numbers. In column B I have a word that goes with that number. In column C, I have numbers, which represent the same words as the number in column a represents the word in column B. In other words, I have: Number Word Type: 1: Banana 2 2: Fruit 3: Sausage 4 4: Meat I want to end up with Number Word Type: 1: Banana Fruit 2: Fruit 3: Sausage Meat 4: Meat Search and replace is out of the question, as there are 361 "things" under about 140 "types". The output is the only way I could get it from a database which is now no longer there. Is there any way of doing this in Excel? Thanks! |
#4
|
|||
|
|||
Relating one column to another
"Peo Sjoblom" wrote in message
... Add a help column for simplicity assume you use column D assume all data starts in row 2 so in D2 put this formula =IF(C2="","",VLOOKUP(C2,$A$2:$B$500,2,0)) "RagDyeR" wrote in message ... With data starting in A2 to C100, Add a "helper" column D. In D2, enter this formula: =IF(C2"",INDEX($B$2:$B$100,MATCH(C2,$A$2:$A$100, 0)),"") And copy down as needed. Many thanks to both of you - it worked fine! How do you work these things out?? It would have taken me days! I've been googling for this all morning, but it's very hard to find the right keywords (got plenty of Excel tips sites in the bookmarks now, though!) |
Thread Tools | |
Display Modes | |
|
|