Is it a way to get a unique list from multiple columns?

[PL]

I need to create a unique list of the staff name which key in multiple
columns (A2:Z26) where it also contains of empty data within the range.

I know we can used filter if the data is within a column, or is it any other
way for me to combine all the data from column A to Z into column AA?

Please help. Thanks.
PL

 

[Roger Govier]

Hi

The following short piece of code will do that for you.

Sub movedata()
Dim i As Long, j As Long, k As Long
k = 2
For i = 2 To 26
For j = 1 To 26
If Cells(i, j) <> "" Then
Cells(k, 27) = Cells(i, j).Value
k = k + 1
End If
Next j
Next i
End Sub

You can copy the code and paste it into your Visual Basic Editor (VBE)
in a Standard Module located in your file.

To do this,

Alt + F11 (open VBE)
Ctrl + R (open Project Explorer)
Select the file name on the left
Insert > Module
Paste code in Module

To run the macro, Tools>Macros>Macro>click on movedata>Run

For more help on inserting code into workbooks, David McRitchie has lots
of useful help on his site at
http://www.mvps.org/dmcritchie/excel/install.htm
http://www.mvps.org/dmcritchie/excel/getstarted.htm