Remove duplicate values in rows and cells

[aequitas1903]

Hello everyone,

Is it possible to gather a list of unique items from a selected area like 50rows x 50columns ?
As far as I know, removing duplicates from excel includes columns only. But I do have a data which is 50x50 cells and have to see all unique items at once. Otherwise I have to do it for each column seperately, combine them and remove duplicates for last time. (which I already did but I wonder if it was possible)

Example :

1			3
		1		7
3	4			9
5		2	8
4		3	6

<tbody>
</tbody>

The outcome should be in a column like
1
3
5.... (the sorting is not important )

It could be a vba code or a formula. But a formula will be better for understanding the process.

Your help is always appreciated.
Regards

 

[patel45]

Sub duplicateRemove()
lr = ActiveSheet.UsedRange.Rows.Count
lc = ActiveSheet.UsedRange.Columns.Count
Cells(1, lc).Select
drow = lr + 1
For c = 2 To lc
  Range(Cells(1, c), Cells(lr, c)).Cut Cells(drow, 1)
  drow = drow + lr
  Application.CutCopyMode = False
Next
ActiveSheet.UsedRange.RemoveDuplicates Columns:=1, Header:=xlNo
End Sub

 

[aequitas1903]

Hi patel45,

Thanks for the reply. It works perfectly. Thanks again.

Have a nice day