Identifying unique data from multiple columns/rows without Excel 2016 Get and Tranform

Hi

just wondering if anyone knows how to do this?

I have a sheet with multiple columns of data (and about 5000 rows), of suppliers. Many of these supplier names are repeated many times across about 50000 cells of data. I would like to generate a list of all the unique supplier names across all these columns/rows. I have tried using remove duplicates, but that only works for each column, and of course you can still have the same name repeated in multiple columns. I was just
wondering if there was a quick way of selecting a range and returning all the unique names in that range?

I am using Excel 2010, so don't have the Get and Transform options on the Data ribbon

Many thanks

There is also an excel 2010 power query addin that can work for you
https://www.microsoft.com/en-US/down....aspx?id=39379

Also for undoubling suppliernames without exact match there is a fuzzy lookup addin
https://www.microsoft.com/en-us/down....aspx?id=15011

Is the attached what you want to do.
it uses vba/macro and can be adjusted to work on any size sheet/array.
torachan.

Cheers thanks - just trying to edit the range now. Thanks for your help.