List with no BLANKS, no DUPLICATES, and is ALPHABETICAL

KDS14589

Board Regular
With much internet searching I found and use with my requirements a formula for removing BLANKS and DUPLICATES
=IFERROR(LOOKUP(2, 1/((COUNTIF(\$BZ\$16:BZ16, \$BS\$17:\$BS\$27)=0)*(\$BS\$17:\$BS\$27<>"")),\$BS\$17:\$BS\$27),"")
and another formula to ALPHABETIZE this list
=IFERROR(INDEX(\$BZ\$17:\$BZ\$27, MATCH(SMALL(IF(COUNTIF(\$CG\$16:CG16, \$BZ\$17:\$BZ\$27)=0, COUNTIF(\$BZ\$17:\$BZ\$27, "<"&\$BZ\$17:\$BZ\$27), ""), 1), COUNTIF(\$BZ\$17:\$BZ\$27, "<"&\$BZ\$17:\$BZ\$27), 0)),"")
Is there a way to combine these to preform the same NO BLANKS, NO DUPLICTES, And ALPHABETIZE in one process?
I've include an image, the main field that holds the primary data is in cells BS17:BS27, the no blanks and no duplicates is cells BZ17:BZ27, the alphabetized list is in cells CG17:CG27 and is based on cells BZ17:BZ27.
Can I accomplish this all in one step

Attachments

• no blanks 7.22.20. .png
76.1 KB · Views: 13

Excel Facts

How to show all formulas in Excel?
Press Ctrl+` to show all formulas. Press it again to toggle back to numbers. The grave accent is often under the tilde on US keyboards.

Joe4

Is there some reason you cannot use Excel's built-in "Remove Duplidates" and "Sort" features?
You can make VBA code do this (if you record yourself doing this manually, you will get most of the code you need), so it can be a single step.

KDS14589

Board Regular
every time I make a change I don't want to do the 'Remove Duplicates' and 'Sort' over again. And this is just one section of many.

Joe4

every time I make a change I don't want to do the 'Remove Duplicates' and 'Sort' over again. And this is just one section of many.
You don't have to. VBA can run it automatically anytime a value in the range is manually updated.

KDS14589

Board Regular
I didn't know about it running automatically. THANKS

Replies
7
Views
138
Replies
6
Views
228
Replies
1
Views
113
Replies
3
Views
267
Replies
15
Views
531

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

1,163,899
Messages
5,834,299
Members
430,275
Latest member
Halis

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.

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

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