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

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, ToolsMacrosMacroclick on movedataRun

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


--
Regards

Roger Govier


"PL" wrote in message
...
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

Another play using formulas, which can deliver both the required data
re-arrangements from 26 cols into one col, and then extract the list of
uniques into another col ..

Assuming source data in cols A to Z, from row2 down

Put in AA2:
=OFFSET(A$2,INT((ROWS($1:1)-1)/26),MOD(ROWS($1:1)-1,26))
Copy AA2 down as far as required, until zeros start to appear continuously
signalling exhaustion of data.

To auto-extract the list of uniques in col AA,
you can bolt-on this set-up ..

In AB2:
=IF(AA2=0,"",IF(COUNTIF(AA$2:AA2,AA2)1,"",ROW()))
Leave AB1 blank

In AC2:
=IF(ROWS($1:1)COUNT(AB:AB),"",INDEX(AA:AA,SMALL(A B:AB,ROWS($1:1))))
Select AB2:AC2, copy down to same extent as that done in col AA. Hide away
col AB. Col AC will return the list of uniques from col AA, all neatly
bunched at the top.

Alternatively, instead of the bolt-on, you could kill the formulas in col AA
with an "in-place" CopyPaste special as values, and then use advanced
filter Unique records only to drive out the list of uniques from col AA.
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"PL" wrote:
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