Remove Blanks & Duplicates using only formulas

[johnhe]

Hi There

I have been struggling over this for days now and have scanned the forum for a resolution and I just can't find one! I have the attached spreadsheet which is a Matrix for preferred people to work with.

Basically, on the tab MgdServ1 I am trying to get the list in cells G28:G66 to be filtered to show no duplicates and no blank cells but using ONLY FORMULAS. I can't use filters as I need it to update without interference.

I have already solved half the problem by removing the duplicates by using the formula =IF(COUNTIF(G$28:G28,G28)=1,G28,""). All I need to do now is to take the list in J28:J66 appear in column K without any blanks.

I think there must be a really simple resolution but as I say I can't find it anywhere.

Any help is massively appreciated.

[Glenn Kennedy]

Hi there. I had to make use of a Named Range to make this more manageable. To see it hit CTRL F3.

Try this. These are Array Formulas. They're a little different from ordinary formulas in that they MUST be confirmed by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER.

You will know the array is active when you see curly braces { } appear around the outside of your formula. If you do not CTRL+SHIFT+ENTER you will get an error message or an incorrect answer. Press F2 on that cell and try again.

Don't type the curly braces yourself - it won't work...

[johnhe]

Haha, AMAZING!! Thank you so much Glenn!!!!

[Glenn Kennedy]

Thanks for the Reputation that you have added. It's much appreciated.