Help with rearranging text strings

[DanielBaker]

Hello,

How would I take a column of text (in this case, names of companies) transfer those names to another sheet, but remove rows that are blank? I need to do this automatically (or dynamically), because that column of names changes is populated by an interaction from a pivot table and slicers.

The column can be up to 3000 names long.

Any ideas what functions I might explore?

Thanks in advance,

Daniel

[JBeaucaire]

Perhaps highlight the entire column/range of cells to copy, press F5 (Go To), Special, Constants

[Special-K]

Could you maybe use the familiar

=IFERROR(INDEX($A$1:$A$3000,SMALL((IF($A$1:$A$3000<>"",ROW(INDIRECT("1:"&ROWS($A$1:$A$3000))))),ROW(A1)),1),"")
Array formula, use Ctrl-Shift-Enter

to remove blanks

[newdoverman]

Name the range of values that you want to copy to another location without spaces between the values. Use the name in the following formula and ARRAY enter (Ctrl + shift + enter). I used the word RANGE.

Please Login or Register  to view this content.

The same formula without using a Named Range is:

Please Login or Register  to view this content.

[XOR LX]

I would just point out that the use of an IFERROR construction in such cases can be quite inefficient, and especially so if the range in question is a particularly large one.

I think it's a touch unfortunate that almost all the sources around the various Excel sites seem to recommend such an approach, perhaps not realising just how costly it can be.

See here for a more detailed explanation and a simple, more efficient set-up:

http://superuser.com/questions/81272.../812848#812848

Regards

[newdoverman]

The IFERROR has to evaluate the whole formula contained within its () in order to determine error no error. Sometimes this is necessary but not for the case of running out of data like we have used above.
The formula that I gave re-written to compare the number of values in the source vs the number of values returned by the formula without a helper cell would be:

Please Login or Register  to view this content.

This stops when the count of data being returned is greater than the count of data in the source. This would again be more efficient if the count was constant in a referenced cell.

If the count (my formula using named ranges) is in F1 with the formula

Please Login or Register  to view this content.

the formula then becomes:

Please Login or Register  to view this content.

Thanks for the reminder.