| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
IF Then using a column of text and assigning a number to text
I have a column containing text. In that column (200 rows worth) there are
40 different words. I need to know how to do the correct formula to find all the words that match and then place the number assigned to that word in the column and row next to that word. i.e. my column contains oranges, apples, etc orange=1, apples =2, etc. I need to have Excel go through the list and asssign 1 to oranges 2 to apples, etc. |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
IF Then using a column of text and assigning a number to text
=MATCH(A2,{"orange","apple","pear","banana"},0)
and copy down. -- __________________________________ HTH Bob "Dan" wrote in message ... I have a column containing text. In that column (200 rows worth) there are 40 different words. I need to know how to do the correct formula to find all the words that match and then place the number assigned to that word in the column and row next to that word. i.e. my column contains oranges, apples, etc orange=1, apples =2, etc. I need to have Excel go through the list and asssign 1 to oranges 2 to apples, etc. |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
IF Then using a column of text and assigning a number to text
okay well that was what I had but it still just assigns a number that is
totally random so you are saying =MATCH(A2,{"orange","apple","pear","banana"......} ,1,2,3,4.....) thats doesnt seem to work "Bob Phillips" wrote: =MATCH(A2,{"orange","apple","pear","banana"},0) and copy down. -- __________________________________ HTH Bob "Dan" wrote in message ... I have a column containing text. In that column (200 rows worth) there are 40 different words. I need to know how to do the correct formula to find all the words that match and then place the number assigned to that word in the column and row next to that word. i.e. my column contains oranges, apples, etc orange=1, apples =2, etc. I need to have Excel go through the list and asssign 1 to oranges 2 to apples, etc. |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
IF Then using a column of text and assigning a number to text
On a separate sheet enter a column of words.
In an adjacent column enter a column of corresponding numbers. On original sheet use VLOOKUP formula with the table range being sheet2 table. =VLOOKUP(A1,Sheet2!$A$1:$B$40,2,FALSE) Copy down 200 rows. Gord Dibben MS Excel MVP On Wed, 20 Aug 2008 12:25:02 -0700, Dan wrote: I have a column containing text. In that column (200 rows worth) there are 40 different words. I need to know how to do the correct formula to find all the words that match and then place the number assigned to that word in the column and row next to that word. i.e. my column contains oranges, apples, etc orange=1, apples =2, etc. I need to have Excel go through the list and asssign 1 to oranges 2 to apples, etc. |
|
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
IF Then using a column of text and assigning a number to text
You need the ,0 at the end as I showed.
-- __________________________________ HTH Bob "Dan" wrote in message ... okay well that was what I had but it still just assigns a number that is totally random so you are saying =MATCH(A2,{"orange","apple","pear","banana"......} ,1,2,3,4.....) thats doesnt seem to work "Bob Phillips" wrote: =MATCH(A2,{"orange","apple","pear","banana"},0) and copy down. -- __________________________________ HTH Bob "Dan" wrote in message ... I have a column containing text. In that column (200 rows worth) there are 40 different words. I need to know how to do the correct formula to find all the words that match and then place the number assigned to that word in the column and row next to that word. i.e. my column contains oranges, apples, etc orange=1, apples =2, etc. I need to have Excel go through the list and asssign 1 to oranges 2 to apples, etc. |
|
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
IF Then using a column of text and assigning a number to text
Thanks Gord
"Gord Dibben" wrote: On a separate sheet enter a column of words. In an adjacent column enter a column of corresponding numbers. On original sheet use VLOOKUP formula with the table range being sheet2 table. =VLOOKUP(A1,Sheet2!$A$1:$B$40,2,FALSE) Copy down 200 rows. Gord Dibben MS Excel MVP On Wed, 20 Aug 2008 12:25:02 -0700, Dan wrote: I have a column containing text. In that column (200 rows worth) there are 40 different words. I need to know how to do the correct formula to find all the words that match and then place the number assigned to that word in the column and row next to that word. i.e. my column contains oranges, apples, etc orange=1, apples =2, etc. I need to have Excel go through the list and asssign 1 to oranges 2 to apples, etc. |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Assigning a number value to text | Excel Worksheet Functions | |||
| Assigning numerical value to text | Excel Worksheet Functions | |||
| Counting a mixed text/number column based on text in another colum | Excel Discussion (Misc queries) | |||
| Assigning number values to text strings | Excel Discussion (Misc queries) | |||
| how do I count the number of times text in column A matches text i | Excel Worksheet Functions | |||
Linear Mode
