displaying Values in an entire column in another worksheet

[wnstar21]

Is there a formula/function in excel that can take all the cells in a column, lets say Column A, and paste/display it in another file without the spaces between the cells with values and without duplicates?


Worksheet1
Column A

1 Name
2
3 Mike
4 Rob
5 Ryan
6
7 Mindy
8 Paul
9
10 Rob
11
12 Mindy
13 Chris


Worksheet2
Column A

1 Name
2 Mike
3 Rob
4 Ryan
5 Mindy
6 Paul
7 Chris
8
9
10
11
12
13

[oldchippy]

Hi,

Select column A, go to Edit > Go to > Click Special Button > Click Constants > OK, right click copy

Go to Sheet2 select A1, paste

[Richard Buttrey]

Hi,

Yes, this is standard Excel functionality.

Check out the Data, Filter, Advanced Filter, Copy to another location, and with the 'Unique' option selected.

HTH

[Richard Buttrey]

Thanks OC

Most interesting, that's definitely a new one for me - and elegant too.

[reggie1000]

heres a formula way,

try this,


in worksheet 2 cell A1

=IF(ISNUMBER(SMALL(IF(Sheet1!A$1:A$12<>"",ROW(Sheet1!A$1:A$12)),ROW())),INDEX(Sheet1!A$1:A$12,SMALL(IF(Sheet1!A$1:A$12<>"",ROW(Sheet1!A$1:A$12)),ROW()),1),"")

this is an array so, CTRL+SHIFT and ENTER to put the curly brackets around {}

cheers reg

[oldchippy]

Thanks OC

Most interesting, that's definitely a new one for me - and elegant too.

It only works on one column, it will not work on a range of columns

[NBVC]

It only works on one column, it will not work on a range of columns

And doesn't filter unique values.

[oldchippy]

NBVC........you are absolutely right there! I didn't see that one

[wnstar21]

Will that formula update itself? If I input values into column A in the original file, will the second worksheet with the formula automatically update when I open it to include newly added values?

[reggie1000]

hi wnstar21,

the formula will automatically update itself as long as the cell range is large enough, when i do these formulas i'll always end the cell range at A1000 for example, that way you wont have to keep changing the formula.

thanks reg

[wnstar21]

I copied the formula and changed the sheet reference to $A$7:$A$3000, and then did the Ctrl+Shift+Entr thing to get the brackets, but the cell returned a blank. There are values where I referenced, so why isn't it working?

[reggie1000]

hi wnstar21,

the formula starts reading the rows at cell A1, so the formula is reading the blank cell a1 to a6, if you place a "1" in cells a1 to a6 it will start at cell A7

if not let me know

thanks reg