Problem to separate in two different groups or columns with criteria

[wichiu1]

Hi there,

I'm a newbie. Tried to sort this matter out into two different groups (Big and Small) in two worksheets or columns.

1 Size Items
2 Big Beef Pig Chicken
3 Small Pig Chicken Beef
4 Small Chicken Beef Pig
5 Big Pig Beef Chicken

E.g. In A2 row, if items is "Big", I want them paste to E2-H2 column or New worksheet by "Big" items only automatically. Same as "Small" (I2 - L2).

So, the result would be

2 Big Beef Pig Chicken Small Pig Chicken Beef
3 Big Pig Beef Chicken Small Chicken Beef Pig

Many thanks for help!!
wichiu1

[ragulduy]

Put:
=IF(SMALL(IF($A$2:$A$5="Big",ROW(A$2:A$5)-1,100),ROW(A1))=100,"",INDEX(A$2:A$5,SMALL(IF($A$2:$A$5="Big",ROW(A$2:A$5)-1,100),ROW(A1))))
in E2 as an array formula (confirm with ctrl+shift+enter) and then copy across to H2 and likewise in I2 with:
=IF(SMALL(IF($A$2:$A$5="Small",ROW(A$2:A$5)-1,100),ROW(A1))=100,"",INDEX(A$2:A$5,SMALL(IF($A$2:$A$5="Small",ROW(A$2:A$5)-1,100),ROW(A1))))

[wichiu1]

According to the formula, it shown "Big" word on E2 and what about the rest of items?

[ragulduy]

Hi have you autofilled E2 across to H2?

[wichiu1]

Brilliant. I got! How about E3 or if I input more items into A6 and down, will it be possible to make them automatically appeared at E column cells?

[ragulduy]

Hi - if you autofill down to to row 3 it will pick up the next record.

If you have more items, then you need to change the $A$2:$A$5 type references to cover the size of your table, perhaps:
=IF(SMALL(IF($A$2:$A$500="Big",ROW(A$2:A$500)-1,100),ROW(A1))=100,"",INDEX(A$2:A$500,SMALL(IF($A$2:$A$500="Big",ROW(A$2:A$500)-1,100),ROW(A1))))

which would work for a table up to row 500.

[wichiu1]

I did. But start from the E3-H3 and down, all the cell shown #NUM!

[ragulduy]

Hi - I do not think you have followed correctly, please take a look at the attachment for an example.

[wichiu1]

Wow... A++++ It works prefect! Many thanks for your help