Sorting and separate

[nalsur]

Sheet1

*	A	B	C	D	E	F	G	H
1	Name List	Shop	List	*	Except shop	Shop	Name List	Qty
2	Banana	Smith	Banana	*	John	Smith	Banana	3
3	Apple	John	Apple	*	Alex	Smith	Apple	1
4	Banana	Ellen	Berry	*	*	Ellen	Banana	2
5	Apple	Smith	Cerry	*	*	Ellen	Apple	1
6	Banana	Smith	*	*	*	Ellen	Berry	2
7	Apple	John	*	*	*	Koko	Cerry	1
8	Banana	Ellen	*	*	*	*	*	*
9	Banana	Smith	*	*	*	*	*	*
10	Berry	Alex	*	*	*	*	*	*
11	Apple	John	*	*	*	*	*	*
12	Berry	Alex	*	*	*	*	*	*
13	Apple	Ellen	*	*	*	*	*	*
14	Apple	Alex	*	*	*	*	*	*
15	Berry	Ellen	*	*	*	*	*	*
16	Berry	Ellen	*	*	*	*	*	*
17	Cerry	Koko	*	*	*	*	*	*

<tbody>
</tbody>

Column A B E is user input

Column C remove duplicated and list single Name List

{=IFERROR(INDEX($A:$A,SMALL(IF(FREQUENCY(IF($A$2:$A$114<>"",MATCH($A$2:$A$114,$A$2:$A$114,0)),ROW($A$2:$A$114)-ROW($A$2)+1)>=1,ROW($A$2:$A$114)),ROWS(B$2:B2))),"")}

How the code to list out for Column F G H

Thanks


Excel tables to the web >> Excel Jeanie HTML 4

 

[AlanY]

put this in I2, copy down and across

{=IFERROR(INDEX(F:F,SMALL(IF(FREQUENCY(IF(F$2:F$114<>"",MATCH(F$2:F$114,F$2:F$114,0)),ROW(F$2:F$114)-ROW(F$2)+1)>=1,ROW(F$2:F$114)),ROWS(I$2:I2))),"")}

 

[nalsur]

AlanY

assume column F G H empty ( user can't enter value in that) that column auto fill by coding
data for sort only column A B or C and E

Let say i want list Shop Name and Name List and put it value to column F and G, data for sorting from A and B
and remove if found shop name in column E , then fill in to column F and G

i hope more clear..

thanks

 

[nalsur]

any ideal to solve this..

thanks