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 :[TABLE="class: outer_border, width: 300, align: center"]
<tbody>[TR]
[TD]1
[/TD]
[TD][/TD]
[TD][/TD]
[TD]3
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]1
[/TD]
[TD][/TD]
[TD]7
[/TD]
[/TR]
[TR]
[TD]3
[/TD]
[TD]4
[/TD]
[TD][/TD]
[TD][/TD]
[TD]9
[/TD]
[/TR]
[TR]
[TD]5
[/TD]
[TD][/TD]
[TD]2
[/TD]
[TD]8
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]4
[/TD]
[TD][/TD]
[TD]3
[/TD]
[TD]6
[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
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