Categorizing Data Question - microsoft.public.excel.worksheet.functions

Hello, I have a list of data in C, and would like to create a column in D that organizes that data into a specific category. For example, given: Fruit apple apple orange orange banana lettuce kiwi banana tomato kiwi Veg lettuce potato potato orange banana kiwi tomato apple orange I want to create a new column in D that returns "fruit" or "veg" - whichever's appropriate for that row. The problem is that I have a lot of items and a lot of categories. I can't get an IF or LOOKUP function to work. I can do VLOOKUP, but would rather not reformat my data. Any thoughts? Thanks!

Put this in D1: =3DIF(ISNA(MATCH(C1,B:B,0)),"",IF(INDEX(A:A,MATCH(C1,B:B, 0))=3D"",LOOKUP("zzz",INDIRECT("A1:A"&MATCH(C1,B:B, 0))),INDEX(A:A,MATCH(C1,B:B,0)))) and copy down as far as you need. It will cope with column C cells being empty and with cells in C containing fruit or vegetables which are not in column B (eg Plum) - both return blanks. Hope this helps. Pete On Apr 21, 10:43=A0pm, Demosthenes <Demosthe...@discussions.microsoft.com> wrote: > Hello, > > I have a list of data in C, and would like to create a column in D that > organizes that data into a specific category. For example, given: > > Fruit =A0 =A0 =A0apple =A0 =A0 =A0 =A0 apple > =A0 =A0 =A0 =A0 =A0 =A0orange =A0 =A0 =A0 orange > =A0 =A0 =A0 =A0 =A0 =A0banana =A0 =A0 =A0 =A0 =A0 =A0lettuce > =A0 =A0 =A0 =A0 =A0 =A0kiwi =A0 =A0 =A0banana > =A0 =A0 =A0 =A0 =A0 =A0tomato =A0 =A0 =A0 =A0 =A0 =A0kiwi > Veg =A0 =A0lettuce =A0 =A0 =A0 =A0potato > =A0 =A0 =A0 =A0 =A0 =A0potato =A0 =A0 =A0 =A0 =A0 =A0 orange > =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0banana > =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0kiwi > =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0tomato > =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0apple > =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0orange > > I want to create a new column in D that returns "fruit" or "veg" - > whichever's appropriate for that row. The problem is that I have a lot of > items and a lot of categories. I can't get an IF or LOOKUP function to wo= rk. > I can do VLOOKUP, but would rather not reformat my data. > > Any thoughts? Thanks!

Think you could simplify the categorization task dramatically via pre-populating fully cols A and B (fill-it down), viz make it: Fruit apple Fruit orange Fruit banana Fruit kiwi Fruit tomato Veg lettuce Veg potato Then simply place in D2, copied down: =INDEX(A:A,MATCH(C2,B:B,0)) should accomplish the task of categorizing all the data in col C in seconds Above of any worth? hit YES below -- Max Singapore --- "Demosthenes" wrote: > I have a list of data in C, and would like to create a column in D that > organizes that data into a specific category. For example, given: > > Fruit apple apple > orange orange > banana lettuce > kiwi banana > tomato kiwi > Veg lettuce potato > potato orange > banana > kiwi > tomato > apple > orange > > I want to create a new column in D that returns "fruit" or "veg" - > whichever's appropriate for that row. The problem is that I have a lot of > items and a lot of categories. I can't get an IF or LOOKUP function to work. > I can do VLOOKUP, but would rather not reformat my data. > > Any thoughts? Thanks!

Pete, Thanks! That does what I wanted. Max, Thanks! That works, but I was hoping to not reformat it like that. "Max" wrote: > Think you could simplify the categorization task dramatically via > pre-populating fully cols A and B (fill-it down), viz make it: > > Fruit apple > Fruit orange > Fruit banana > Fruit kiwi > Fruit tomato > Veg lettuce > Veg potato > > Then simply place in D2, copied down: =INDEX(A:A,MATCH(C2,B:B,0)) > should accomplish the task of categorizing all the data in col C in seconds > Above of any worth? hit YES below > -- > Max > Singapore > --- > "Demosthenes" wrote: > > I have a list of data in C, and would like to create a column in D that > > organizes that data into a specific category. For example, given: > > > > Fruit apple apple > > orange orange > > banana lettuce > > kiwi banana > > tomato kiwi > > Veg lettuce potato > > potato orange > > banana > > kiwi > > tomato > > apple > > orange > > > > I want to create a new column in D that returns "fruit" or "veg" - > > whichever's appropriate for that row. The problem is that I have a lot of > > items and a lot of categories. I can't get an IF or LOOKUP function to work. > > I can do VLOOKUP, but would rather not reformat my data. > > > > Any thoughts? Thanks!

You're welcome - thanks for feeding back. Pete On Apr 22, 4:55=A0pm, Demosthenes <Demosthe...@discussions.microsoft.com> wrote: > Pete, > > Thanks! That does what I wanted. > > Max, > > Thanks! That works, but I was hoping to not reformat it like that. > > > > "Max" wrote: > > Think you could simplify the categorization task dramatically via > > pre-populating fully cols A and B (fill-it down), viz make it: > > > Fruit =A0 =A0 =A0apple > > Fruit =A0 =A0 =A0orange > > Fruit =A0 =A0 =A0banana > > Fruit =A0 =A0 =A0kiwi > > Fruit =A0 =A0 =A0tomato > > Veg =A0 =A0 =A0 =A0lettuce > > Veg =A0 =A0 =A0 =A0potato > > > Then simply place in D2, copied down: =3DINDEX(A:A,MATCH(C2,B:B,0)) > > should accomplish the task of categorizing all the data in col C in sec= onds > > Above of any worth? hit YES below > > -- > > Max > > Singapore > > --- > > "Demosthenes" wrote: > > > I have a list of data in C, and would like to create a column in D th= at > > > organizes that data into a specific category. For example, given: > > > > Fruit =A0 =A0apple =A0 =A0 =A0 =A0 apple > > > =A0 =A0 =A0 =A0 =A0orange =A0 =A0 =A0 orange > > > =A0 =A0 =A0 =A0 =A0banana =A0 =A0 =A0 =A0 =A0 =A0lettuce > > > =A0 =A0 =A0 =A0 =A0kiwi =A0 =A0 =A0banana > > > =A0 =A0 =A0 =A0 =A0tomato =A0 =A0 =A0 =A0 =A0 =A0kiwi > > > Veg =A0 =A0lettuce =A0 =A0 =A0 =A0 =A0 =A0 =A0potato > > > =A0 =A0 =A0 =A0 =A0 =A0potato =A0 =A0 =A0 =A0 =A0 orange > > > =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0banana > > > =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0kiwi > > > =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0tomato > > > =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0apple > > > =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0orange > > > > I want to create a new column in D that returns "fruit" or "veg" - > > > whichever's appropriate for that row. The problem is that I have a lo= t of > > > items and a lot of categories. I can't get an IF or LOOKUP function t= o work. > > > I can do VLOOKUP, but would rather not reformat my data. > > > > Any thoughts? Thanks!- Hide quoted text - > > - Show quoted text -

Similiar Articles:

Extract unique data - microsoft.public.excel.misc
... Post Question Groups ... Categorizing Data Excel. Categorize Database Records Based on Category Table-Extract Unique ...

How do I set up icons in excel in one column using data from anoth ...
... Post Question Groups ... icons in data ... icon set with specified rule. First you need to categorize your column data ...

Relationship error in a query - microsoft.public.access ...
Is there a chance that this data has been imported ... and have one field in that table serve to categorize the ... have been my >> question anyway), there's some question ...

Free/Busy Background Color within Calendar - microsoft.public ...
... Post Question ... calendar I prefer to use the categorize colors -Hildegard EggHeadCafe - Software Developer Portal of Choice In-Memory Data ...

E_Fail Status with VB6 - microsoft.public.vb.general.discussion ...
Have a VB6 app that is being used to pull data from a ... Post Question Groups ... or it returned an error that VB or ADO couldn't categorize ...

IIf statement, wildcard to return all records - microsoft.public ...
It draws data from a dropdown box on an open form. ... Post Question Groups ... Hi, I want to categorize BMI =30 = "obese" I am using this ...

Report writer displaying zeros - microsoft.public.greatplains ...
... Post Question Groups ... to display something ... if the height of the Data ... Read how to categorize and display reports in different ...

How do I change my color coded labels? - microsoft.public.outlook ...
Click Categorize, then All Categories. Select the category ... Change All Data Labels in Excel 2007 Stacked Bar ... ... Smartstrip® Frequently Asked Questions Can my color bands ...

Creating a Distribution List in OUTLOOK 2007 - microsoft.public ...
... of all of the addresses in the view. http://www.outlook-tips.net/howto/copy_data ... Hi, Hopefully have a quick easy question... One of our users has created a distribution ...

How do I remove attachments from Outlook Calendar appointments ...
... Post Question Groups ... You can set View to Outlook data file to find the ... day-to-day activities, such as allowing you to categorize ...

Categorizing Data - Regents Exam Prep Center Topic Index
Practice with Categorizing Data: Teacher Resource: Activity to Show Sample Populations and Bias - Backpack Color - with Charts and Worksheet

Excel / Macro for categorizing data - AllExperts Questions & Answers
macros, poultry, spreadsheet: One way is to use a loop that would loop thru each cell in the range whose data needs to be categorized. As the loop stops on or looks ...

METHOD AND APPARATUS FOR COLLECTING AND CATEGORIZING DATA AT A ...
... METHOD AND APPARATUS FOR COLLECTING AND CATEGORIZING DATA AT A TERMINAL ... the employee may be prompted to ask a further question to more accurately categorize ...

Categorizing, Coding, and Manipulating Qualitative Data
Categorizing, Coding, and Manipulating Qualitative Data Using the WordPerfect® + Word Processor ... In the above example of a data bit, a question (Q:) began the ...

Analyzing Qualitative Data (G3658-12)
and analyze the data both by question and by case, individual or group. Step 3 Categorize information. Some people refer to categorizing information as coding the data or ...

7/29/2012 10:05:56 AM