Data Organized

[Gusur]

Dear All,

I have list of names (column A) and every time I put a code (example:
2), instantly the function will create a list in another column.

For example
Data:
A B
NAME CODE
AA
BB 2
CC
DD 2
EE

Result:
BB
DD

And if I put another code (3 for example), the function will create
another list at next column.

Please help.

Best Regards,
Gusur

 

[Bob Phillips]

Try this array formula

=IF(ISERROR(SMALL(IF($B$1:$B$20=COLUMN(B1),ROW($A$1:$A$20)),ROW($A1))),"",
INDEX($A$1:$A$20,SMALL(IF($B$1:$B$20=COLUMN(B1),ROW($A$1:$A$20)),ROW($A1))))

copy down and across as required

 

[Gusur]

Dear Bob,

Maybe I'm not clearly to explain.

I have 200 employees name with department code in next column (total 5
departments). And I want make 5 groups/lists of employees name suitable
with their dept code.

I need this cause some of employee move to difference dept and I don't
want to make 5 new lists manually.

Many Thanks
Gusur

 

[Bob Phillips]

I think that is exactly what I gave you. It is not manual, it is dynamic
based upon the original data.

Have you tried it?

 

[Gusur]

Dear Bob,

I have tried it.

I have list of employees name at column A with header "NAME" and "DEPT.
CODE" in next column with value between 1 to 5. I put your formula in
cell D2, copy it down and the result is not I expected. I expected list
of employee name with dept. code = 1 at column D, dept. code = 2 at
column E and so on.

Did I miss something?

Best Regards,
Gusur

 

[Bob Phillips]

You originally said you wanted code 2, so I (blindly) coded it to start at
code 2. If you want to start at code 1, change COLUMN(B1) to COLUMN(A1) in
the formula.

What did you get when you tried it?

 

[Gusur]

Dear Bob,

Still not working.
But I got reference file from Pete_UK.
With minor modification, it's work perfectly.

Thanks to you both.

Best Regards,
Gusur