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
|
|||
|
|||
Categorizing Data Question
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! |
#2
|
|||
|
|||
Categorizing Data Question
Put this in D1:
=IF(ISNA(MATCH(C1,B:B,0)),"",IF(INDEX(A:A,MATCH(C1 ,B:B, 0))="",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*pm, Demosthenes 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 * * *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! |
#3
|
|||
|
|||
Categorizing Data Question
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! |
#4
|
|||
|
|||
Categorizing Data Question
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! |
#5
|
|||
|
|||
Categorizing Data Question
You're welcome - thanks for feeding back.
Pete On Apr 22, 4:55*pm, Demosthenes 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 * * *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!- Hide quoted text - - Show quoted text - |
Thread Tools | |
Display Modes | |
|
|