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!


0
reply7521 (17)
5/10/2004 12:19:05 PM
excel.newusers 15348 articles. 2 followers. Follow

3 Replies
595 Views

Similar Articles

[PageSpeed] 50

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" <reply@thisgroup.please> wrote in message
news:%23aBC8joNEHA.556@TK2MSFTNGP10.phx.gbl...
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!



0
ragdyer1 (4060)
5/10/2004 12:36:58 PM
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 edit>paste 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" <reply@thisgroup.please> wrote in message
news:%23aBC8joNEHA.556@TK2MSFTNGP10.phx.gbl...
> 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!
>
>


0
terre081 (3244)
5/10/2004 12:45:40 PM
"Peo Sjoblom" <terre08@mvps.org> wrote in message
news:uCof0yoNEHA.1312@TK2MSFTNGP12.phx.gbl...
> 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" <ragdyer@cutoutmsn.com> wrote in message
news:u9CDCuoNEHA.1616@TK2MSFTNGP12.phx.gbl...
> 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!)


0
reply7521 (17)
5/10/2004 1:26:26 PM
Reply:

Similar Artilces:

refer to second column of listbox
Hi, I have a multi selected listbox. How can I refer to the second column of the listbox? Me.lstContract.ItemData(varItem).column(1) ??? Dim varItem As Variant For Each varItem In Me.lstContract.ItemsSelected gstrReportFilter = "[Ck_ContractID]='" & Me.lstContract.ItemData(varItem) & "'" ''DoCmd.OpenReport "rptCoFundActivity_k" '', , , gstrReportFilter DoCmd.OpenReport "rptCostShareContribution_k", acViewPreview '', , gstrReportFilter Next varItem SF ...

Help creating two listings from one record
I have extracted "members" from my database. One record will contain LastName and FirstName, and may also contain SpouseName. Because I am creating membership cards in Publisher, I think I probably should actually create another query or ?? that contains one record with either [FirstName LastName] or [SpouseName LastName] Probablyl something like ... If SpouseName greater spaces List SpouseName LastName else next sentence. List FirstName LastName I don't have a clue how to do this in Access. Appreciate all help. Try this -- [FirstName] &a...

How to create treeview active control as another activex control's child?
I want to create an activex control who will wrap a treeview active control but has some its own logic. I don't know how to create it and how to set its position and size. say,if I want my active control's size is the same as the treeview active control. Any idea ? Thanks ...

return a column no
I have a cell containing text. I need a formula that takes the text and finds an exact match in a row and return the column no. Eg Find Text: Name3 Name1 Name2 Name3 Name4 Name5 I want to return the column number which in this case is row 3. I know there is a simple formula but I cant find it Thanks =match(a2,1:1,0) will return the number of the column of the first match (if there is one). Assumes that the Name3 is in A2 and the names are listed in A1:IV1 (row 1) Craig McLaughlin wrote: > > I have a cell containing text. I need a formula that takes the text...

re: best way to move columns between 2 excel docs
Ok, here is my little project: In execel.doc A I have some columns that I want to move to Excel.doc B, and the columns are always positioned the same way in A, they never change their position. How do you transfer them to B, to append a running process of pre-existing prefilled from previous days ? Any code samples ? Macros ? Please help!!! :- ) I am reading a book on application-level programming, VBA for Access, so I understand more and more everyday. How much work do I have here ? Thanks. ...

Can't insert column; keep receiving error message
I was updating a large spreadsheet and all of a sudden I was unable to insert columns. I kept getting an error message that said, "...unable to shift nonblank cells off worksheet." I tried everything from unhiding all columns and rows toreformatting all the comments to move and size with their relative cells. I also removed all the comments and I was still unable to insert a column. Does anyone have a solution Thank you >-----Original Message----- >I was updating a large spreadsheet and all of a sudden I was unable to insert columns. I kept getting an error message th...

Which one first?Install Windows Server 2003 SP1 or Exchange SP2 ? #2
Hi, We are running Exchange 2003 on Windows Server 2003. We planned to update the software in terms of patches, so which one should I do first, the OS patches (SP1 and onwards) or the Exchange patches ? By the way, any issue with Exchange SP2 ? Rgrds, Zul See http://go.microsoft.com/fwlink/?linkid=37488 and http://support.microsoft.com/?kbid=896742. I would recomment upgrading Exchange first. -- Regards, Roland "Zul J" wrote: > Hi, > > We are running Exchange 2003 on Windows Server 2003. We planned to update > the software in terms of patches, so which one...

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: M...

Column Headers #7
How do I print Column Headers on every page? File | Page Setup | Sheet Put the row you want to Repeat in the spot provided for Rows to Repeat at top. tj "Cscritch" wrote: > How do I print Column Headers on every page? ...

Comparing two Columns #3
I have to columns full of data one column is "inventory" and the secon column is "charged items" inventory is what should be on the shel according to the computer, and charged items are the books that ar checked out. So the inventory - charged items would more less give m what "should" be on the shelf according to the computer. So I want column C to list me everything that is in Column A that i not in Column B. Column A is Inventory Column B is charged items (goes up to 6000). After I typed it out it seems very simply I just haven't figured ou how to...

Renaming a column label
How do I rename the column header labels from "A", "B", etc. to something else? Made a valiant effort. Can't figure it out. Mike -- DO NOT reply to the "from" address in this post. Instead, construct a reply address from this template: v6nos at yahoo dot com. Short answer: you don't - that capability (other than using numbers - check the Tools/Options/General R1C1 reference style checkbox) doesn't exist in XL. Longer answer: If you hide the row and column headers (Tools/Options/View) you can format Row 1 for column headers. You can even use t...

Set One Field based On Another Field
I think I have a pretty simple request. I am looking for some script to put on an on exit field. Here is what I need it to do: When a value is put in say $550, if this is greater than a specified limit in the code I need it to set Field2 to Yes. So if field1 > 500 set field2 = yes Real world example: I have a dollar amount of credit to give to a customer, I want to auto populate the approval required field with a yes. Your help is appreciated. Brent, Try: IIf([Field1]>500,[Field2]="Yes") However if the field is a Bit data type then IIf([Field1]&...

How may I add more than 256 columns to an Excel spreadsheet?
I am trying to create a horizontal 12 month calendar in an Excel spreadsheet. I need 370 columns. Is there anyway to accomplish this within Excel? SPO, excel only has 256 columns -- Paul B Always backup your data before trying something new Please post any response to the newsgroups so others can benefit from it Feedback on answers is always appreciated! Using Excel 2002 & 2003 "SPO" <SPO@discussions.microsoft.com> wrote in message news:75457654-C31C-4787-9790-D07E220849BA@microsoft.com... > I am trying to create a horizontal 12 month calendar in an Excel spreads...

Incorporating detail of one sheet into another.
There are 2 excel worksheet. I would like to incorporate detail of sheet 2 into sheet1. e.g Sheet2 Contain the following details A B C D E F G Agent1 Agent2 Agent3 Agent4 Agent5 1 Apple Cycle 2 Mango Car 3 Banana Bus 4 Coconut Motor and so on in coloumn c , d , e,f and g. Now what I would like to do is If in sheet 1 If I select Agent 1 all the detail of sheet2 which has column as agent1 should appear exactly as it is in sheet2. And the same thing for Column B, c ...

Multiple rendering after one action
Hi There, Is it possible to render many reports with one link / action? I'm looking for any programmatically approach, which allows me to automatically renrder 25 reports to PDF/XLS Thanks in advance. michlimes Hi, You could use RS as a web service to trigger whatever you want. Seee http://msdn.microsoft.com/en-us/library/ms152787.aspx for details. -- Patrice "michlimes" <michlimes@discussions.microsoft.com> a �crit dans le message de groupe de discussion : E5B78CCB-7636-4FF8-80A3-7064C09DD9DE@microsoft.com... > Hi There, > > Is it po...

Matching Columns in work sheets and copying both rows to new
I am trying to match up two different spread sheets based on one column but to copy both rows to a new sheet. eg. First sheet has the following headings: "Owner Group " "Owner CC " "Type " "Product Categorization Tier 2 " "Product Categorization Tier 3 " "Device Name " "Status " "Model Number " "Mfg. Name " "CI ID " "Old Asset ID " "Serial Number " "Region " "Country " "Site " "Building " "Floor " "...

How do I combine 2 text columns in Microsoft Excel?
I have two columns of descriptive text, the second column is the end of the first column's sentence, however I can not find a way in the Help options to combine the text values to create one complete sentence in one column. Does anyone know of a way to do this? Try: =A5&" "&B5 if you want spaces between the columns or look at the CONCATENATE function example =CONCATENATE(A5,B5) Domaniman wrote: > I have two columns of descriptive text, the second column is the end of the > first column's sentence, however I can not find a way in the Help options to ...

sum values from the same item in a single column?
Hi, I have a query to get a table like this: Item Num Values A 1 10 A 2 22 A 3 78 B 1 32 B 2 40 B 3 87 C 1 34 C 2 76 C 3 98 actually each "Item" has more than a thousand of "Num". how to sum all "Item" (A+B+C) at each "Num"? Like: Num Sum 1 76 2 138 3 263 Thanks! pemt pemt, Have a look at Crosstab queries under Help, that should give you what you...

Extracting Data in Cells in order -- (or) eliminating empty cell space in a column
Hi I have this problem that I bet is easy to solve, but i am lost. I am an expert at the slow way to do things, but maybe there is a better way. The only way I can describe the problem is by means of an example. Lets say I have a column of numbers: >_A_|_B_| etc >> 1_1_|___| 2_3_|___| 3_2_|___| 5_5_|___| 5_3_|___| 6_4_|___| 7_7_|___| 8_3_|___| 9_1_|___| and then i write a little function in the adjoing cell, B1: =if(a1=3,a2,"") From there I fill down column B to B9. OK, pretty simple so far, right? What I am looking for is instances where I find a '3' in co...

unique values of a column
hi how can i get the unique values of a column in an array? thanks in advance I think Advanced Filter will do what you want, there is an excellent tutorial here from Debra Dalgleish http://www.contextures.com/xladvfilter01.html Regards <anonymous@discussions.microsoft.com> wrote in message news:073d01c49649$6e4e49e0$a401280a@phx.gbl... > > hi > > how can i get the unique values of a column in an array? > thanks in advance > > Hi, Additionally check out Chip Pearson's website at: http://www.cpearson.com/excel/duplicat.htm#ExtractingUnique >-----Orig...

"Group"ing adjacent columns independently
I was hoping that someone might know how to do the following - or whether it is possible at all ... I am using Excel's "Group and Outline" capability and I would like to independently group adjacent columns (or rows). For example, I'd like to be able to create a group of columns A and B and a separate group of columns C and D - with each group being independently selectable via the "+" and "-" buttons. As far as I can tell, if you try to group adjacent columns, the two groups get merged into a single group. Also, if I play games by trying to insert an ...

Only one user at a time can access OWA
Using Exchange 5.5 SP3 on W2K. Only one user can log on using OWA at any one time. As soon as the first user logs out the next can then log on, but in a organisation of 1300 - bit of a problem. Previously we did have things working fine with Exchange 5.5 on WinNT but a hacking attack put paid to that system! Have run through MS trouble shooting but found no mention of this problem anywhere else. Any suggestions welcomed Regards Mike Cameron cameronm@queensbury.beds.sch.uk (Mike Cameron) wrote: >Using Exchange 5.5 SP3 on W2K. Only one user can log on using OWA at >any one time. As...

creating a table with multiple columns
for a High School science project my daughter created a survey with 39 possible responses (3 groups of 13 responses in each group) the people taking the survey are supposed to pick the top 4 responses in each group (numbered 1-4, 1 being the highest). Other information is Age, grade and gender of the person taking the survey. What would be the best way to create the table and eventually charts to diplay the results of the survey? I was thinking of a flat table with a number in the cells correspondig to their answers but I have no idea how to create the charts or compare the answers w...

Prevent data entry if another field contains data
I need code to prevent data entry into Form Field B if Form Field A has data in it? Access version is 97. Thanks Ron Ron, I would put this code in the Forms Current event, so that as soon as you move to a record, it will do this. I would also put it in the AfterUpdate event of the control that is bound to [Field A] on your form. This assumes that [Field B] is a textbox with the name txt_FieldB me.txt_FieldB.Locked = (LEN(me.txt_FieldA & "") > 0) The code on the right of the equal sign will evaluate to be either True or False, depending on whether there is a value ...

Print 12/1/1 in cell (Excel changes it to date or another #
While trying to formulate a magazine order list, Excel changes the number of magazines needed (12/1/1) to a number other than that typed into the cell. What I need is the number 12 (for English), 1 (for MP3) and 1 (for Finnish) in this particular cell. How do I get it to print the actual numbers? try putting a ' infront of the numbers "Say Goodnight, Gracie" wrote: > While trying to formulate a magazine order list, Excel changes the number of > magazines needed (12/1/1) to a number other than that typed into the cell. > What I need is the number 12 (for Engli...