Sorting and separate

nalsur

New Member
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
Code:
``{=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

Excel Facts

Back into an answer in Excel
Use Data, What-If Analysis, Goal Seek to find the correct input cell value to reach a desired result

AlanY

Well-known Member
put this in I2, copy down and across

Code:
``{=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

New Member
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

New Member
any ideal to solve this..

thanks

Replies
7
Views
282
Replies
6
Views
212
Replies
3
Views
265
Replies
3
Views
256
Replies
2
Views
222

Threads
1,195,858
Messages
6,011,979
Members
441,661
Latest member
Pammie007

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back