cut and paste filtered cells (invisible cells only) from one column to another

[Ingrid Hens]

I have a table with a column in which positive and negative values are mixed (from an external source) and I want to split the column in 2 columns: one with positive values (money coming in) and one with negative values (money going out). I filter the column but I can't cut and paste the visible (filtered) values to another column...  I tried several times with the help function, but I didn't succeed at all... got error messages.

[#2 · Accepted solution]

Hi Ingrid,

For my knowledge simple copy/paste doesn'work with filtered/hided rows, actually you try to copy/paste non-adjustment cells. That could be few workarounds

1) Sort your column in ascending or descending order, copy first part (below zero) in one column and second one in another. If you need to return to initial order and had no another sorting field, create at very beginning helper column with ID (e.g. just fill it by sequential numbers), after finish copying sort back by this column.

Perhaps good enough for more or less static data.

2) You may split your column on two - select first column, in Ribbon menu under the Data click Text to Column item; on first screen of opened window select Delimited; on next one select Other and minus ("-") symbol, click Finish then.

Result will be like this

Negative numbers are converted to positive, but the are in separate column and you may interpret them as negative.

3) I'd prefer to use formula. If you have initial firs column, second will be for negative values and third for positive, add to second column cells (with proper reference on first column)

=IF([first column cell]<0,[first column cell],0)

and into the third column the formula with opposite condition.

After that apply to the cells in second and third columns custom format like this (use Ctrl+1 on selected cells)

#,##0.00;-#,##0.00;;@

Above will show cells with zeroes as empty ones:

If your initial data is relatively dynamic new/updated cells will go to new columns practically automatically, no need in repeating manipulations on entire data set as for first two options

[#3]

Thank you very much for this response!

[#4]

Try this.

I've sent a sample file.

If you have Amount in A2 Cell

In B2 Cell enter

=IF(A2>0,A2,"")

In C2 Cell enter

=IF(A2<0,A2,"")

Drag above formula upto column end.

[#5]

Hi Logaraj,

In your file please try to insert into E2

=C2+1

and in F2

=D2+1

after that copy cells down