List with no BLANKS, no DUPLICATES, and is ALPHABETICAL

KDS14589

Board Regular
Joined
Jan 10, 2019
Messages
180
Office Version
  1. 2016
Platform
  1. Windows
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
    no blanks 7.22.20. .png
    76.1 KB · Views: 14

Excel Facts

Copy PDF to Excel
Select data in PDF. Paste to Microsoft Word. Copy from Word and paste to Excel.
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.
 
Upvote 0
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.
 
Upvote 0
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.
 
Upvote 0

Forum statistics

Threads
1,213,510
Messages
6,114,034
Members
448,543
Latest member
MartinLarkin

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

Which adblocker are you using?

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
Back
Top