software

I have data that I have been categorizing into multiple categories.
What I would like to do is put all the of category types in one cell
(separated by a comma) that can be assigned to that specific data. I
have the Index function working right now that matches the value I have
and gives me the data that corresponds to that. See example below.

=INDEX($D$1:$D$1000,SMALL(IF($A$1:$A$1000=K$1,ROW( $A$1:$A$1000)-ROW(A$1) 1),ROWS($1:3)))

Data Example:

A B
C, D, E, F (Spring, Summer, Fall, Winter)

Spring, Summer Beaches
Fall Leaves
Winter Snowboarding
Winter, Spring Cold Weather

The results would be as follows (where the index function above (k$1
would change to l$1 if I dragged it across - the values in those cells
would be spring, summer, fall, winter)

Spring Summer Fall
Winter
Beaches Beaches Leaves
Snowboarding
Cold Weater
Cold Weather

Like I said, I can get the function to work if there is only one value
in a cell in column A, but I would like to have multiple values listed
so I don't need to make a bigger sheet.

Please help if you can. Thanks.Hi!

Try changing this:

SMALL(IF($A$1:$A$1000=K$1

To:

SMALL(IF(ISNUMBER(SEARCH(K$1,$A$1:$A$100))

Biff

gt; wrote in message oups.com...
gt;I have data that I have been categorizing into multiple categories.
gt; What I would like to do is put all the of category types in one cell
gt; (separated by a comma) that can be assigned to that specific data. I
gt; have the Index function working right now that matches the value I have
gt; and gives me the data that corresponds to that. See example below.
gt;
gt; =INDEX($D$1:$D$1000,SMALL(IF($A$1:$A$1000=K$1,ROW( $A$1:$A$1000)-ROW(A$1) 1),ROWS($1:3)))
gt;
gt; Data Example:
gt;
gt; A B
gt; C, D, E, F (Spring, Summer, Fall, Winter)
gt;
gt; Spring, Summer Beaches
gt; Fall Leaves
gt; Winter Snowboarding
gt; Winter, Spring Cold Weather
gt;
gt; The results would be as follows (where the index function above (k$1
gt; would change to l$1 if I dragged it across - the values in those cells
gt; would be spring, summer, fall, winter)
gt;
gt; Spring Summer Fall
gt; Winter
gt; Beaches Beaches Leaves
gt; Snowboarding
gt; Cold Weater
gt; Cold Weather
gt;
gt; Like I said, I can get the function to work if there is only one value
gt; in a cell in column A, but I would like to have multiple values listed
gt; so I don't need to make a bigger sheet.
gt;
gt; Please help if you can. Thanks.
gt;
Typo in the range:

gt; SMALL(IF(ISNUMBER(SEARCH(K$1,$A$1:$A$100))

Should be:

SMALL(IF(ISNUMBER(SEARCH(K$1,$A$1:$A$1000))

Biff

quot;Biffquot; gt; wrote in message
...
gt; Hi!
gt;
gt; Try changing this:
gt;
gt; SMALL(IF($A$1:$A$1000=K$1
gt;
gt; To:
gt;
gt; SMALL(IF(ISNUMBER(SEARCH(K$1,$A$1:$A$100))
gt;
gt; Biff
gt;
gt; gt; wrote in message
gt; oups.com...
gt;gt;I have data that I have been categorizing into multiple categories.
gt;gt; What I would like to do is put all the of category types in one cell
gt;gt; (separated by a comma) that can be assigned to that specific data. I
gt;gt; have the Index function working right now that matches the value I have
gt;gt; and gives me the data that corresponds to that. See example below.
gt;gt;
gt;gt; =INDEX($D$1:$D$1000,SMALL(IF($A$1:$A$1000=K$1,ROW( $A$1:$A$1000)-ROW(A$1) 1),ROWS($1:3)))
gt;gt;
gt;gt; Data Example:
gt;gt;
gt;gt; A B
gt;gt; C, D, E, F (Spring, Summer, Fall, Winter)
gt;gt;
gt;gt; Spring, Summer Beaches
gt;gt; Fall Leaves
gt;gt; Winter Snowboarding
gt;gt; Winter, Spring Cold Weather
gt;gt;
gt;gt; The results would be as follows (where the index function above (k$1
gt;gt; would change to l$1 if I dragged it across - the values in those cells
gt;gt; would be spring, summer, fall, winter)
gt;gt;
gt;gt; Spring Summer Fall
gt;gt; Winter
gt;gt; Beaches Beaches Leaves
gt;gt; Snowboarding
gt;gt; Cold Weater
gt;gt; Cold Weather
gt;gt;
gt;gt; Like I said, I can get the function to work if there is only one value
gt;gt; in a cell in column A, but I would like to have multiple values listed
gt;gt; so I don't need to make a bigger sheet.
gt;gt;
gt;gt; Please help if you can. Thanks.
gt;gt;
gt;
gt;