Getting unique values in multiple columns using formulas

[ExelForum001]

I need to list only the unique values in a range found in multiple columns. Some cells are blank. I need to list all the unique values in numeric and/or alphanumeric order in another column within the same worksheet. I would prefer non-array formulas, if possible. Please see the attached file.

In the attached table, I have 5 columns and 5 rows of values in the range B2:F6. The unique values, in order, are 0, 1, 2, 3, 4, 5. This looks simple and can be done manually if the file is small but I have 150 columns and 150 rows, thus the need for automation.

I would appreciate your help. Thanks.

[oeldere]

It would be great if you add an excel file, without an password !!!

[cbatrody]

Hi,

The workbook you have uploaded is password protected and can't be opened.

[ExelForum001]

Hi oeldere and cbatrody,

My apologies. I was unaware that the file was password protected. That was my first file attachment ever on this forum and hence I will remember to check. The password has been removed and the file replaced. Thanks for the advice.

[oeldere]

I used a macro to get the result.

Column A needs to be filled to get all the values.

See the attached file, and push the button.