List with no BLANKS, no DUPLICATES, and is ALPHABETICAL

KDS14589

Board Regular
Joined
Jan 10, 2019
Messages
54
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: 11

Some videos you may like

Excel Facts

Formula for Yesterday
Name Manager, New Name. Yesterday =TODAY()-1. OK. Then, use =YESTERDAY in any cell. Tomorrow could be =TODAY()+1.

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
55,882
Office Version
  1. 365
Platform
  1. Windows
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
Joined
Jan 10, 2019
Messages
54
Office Version
  1. 2016
Platform
  1. Windows
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

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
55,882
Office Version
  1. 365
Platform
  1. Windows
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
Joined
Jan 10, 2019
Messages
54
Office Version
  1. 2016
Platform
  1. Windows
I didn't know about it running automatically. THANKS
 

Watch MrExcel Video

Forum statistics

Threads
1,122,193
Messages
5,594,774
Members
413,934
Latest member
austinb

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
Top