Excel help required

My apologies for posting such basic Qs. I can assure you it is a last resort, and unfortunately search engines dont allow the explanation of an issue to find the answer so well…

2 basic issues I am sure. I am on a different PC than usual and have a couple of problems with excel 2000.

  1. When filtering values, I want to cut the results of the filter but selecting the range, and paste them into another column. Problem is, it is taking ALL the cells between the first and last filtered value, not just the visible ones.

  2. I have support for east asian languages installed, and fonts too, but when I paste some chinese into excel is is displayed as ???

Appreciate any help :help:

Dunno of any other solution than deleting the unwanted columns after pasting, or before selecting the data you make them visible and only select the columns you want to copy by pressing CTRL + the header (A, B, C … ) of the columns you want to copy.

Office has AFAIK it’s own language tools, try to find them on the install CD.

regarding 1, here is some more background of what I am trying to do.

I have got a sheet with 3500 lines, 1 column, about 300 cells have contents that are longer than x.

I filter the sheet to show me only those cells with contents longer than x.

What I want to do is MOVE the filtered values from column 1 to 3, without touching those cells inbetween the filtered values. It seems if I select the whole filtered column or drag select the values, the clipboard picks up the non filtered cells too.

I am sure I have done this for years without encountering this problem…I dunno.

Regarding Item 1: I just tried on a spreadsheet I have and it works fine just using ctrl-C and ctrl-V. I am using Excel 2002, but I’m sure it worked the same way on 2000.

[quote=“Truant”]regarding 1, here is some more background of what I am trying to do.

I have got a sheet with 3500 lines, 1 column, about 300 cells have contents that are longer than x.

I filter the sheet to show me only those cells with contents longer than x.

What I want to do is MOVE the filtered values from column 1 to 3, without touching those cells inbetween the filtered values. It seems if I select the whole filtered column or drag select the values, the clipboard picks up the non filtered cells too.[/quote]
Any particular reason to move them, rather than just copy? That would seem easier to me.
E.g. in Cell C1 insert =IF(LEN(A1)>x,A1,""), in Cell C2 insert =IF(LEN(A2)>x,A2,"") etc.

Actually that’s what you are doing, isn’t it? :s

I am not sure whether I understand exactly what you want to do, but I tried the following and it worked in Excel 2000 running in XP

One column, several lines as below

ee
rr
tzu
gg
hhh
ff
sss
gg
cccccc
sss
eeeeee

Custome filter for “does not equal” “??”

The filtered list only contains

tzu
hhh
sss
cccccc
sss
eeeeee

I select these cells and use the little plus sign at the edge of the selection (I hope you understand what I mean) and drag it to column 3

result, both column 2 and 3 look like this:

tzu

hhh

sss

cccccc
sss
eeeeee

Delete everything in column 2.

I’ve just done it with Excel 2000 with no problem. What I did:

Select the column
Turn on Auto Filter
Apply a filter e.g. greater than x
Select the filtered cells (this is important. If you just copy without doing this then you effectively still have the whole column selected)
Copy
Turn off the filter
Paste in different column

[quote=“Rascal”][quote=“Truant”]regarding 1, here is some more background of what I am trying to do.

I have got a sheet with 3500 lines, 1 column, about 300 cells have contents that are longer than x.

I filter the sheet to show me only those cells with contents longer than x.

What I want to do is MOVE the filtered values from column 1 to 3, without touching those cells inbetween the filtered values. It seems if I select the whole filtered column or drag select the values, the clipboard picks up the non filtered cells too.[/quote]
Any particular reason to move them, rather than just copy? That would seem easier to me.
E.g. in Cell C1 insert =IF(LEN(A1)>x,A1,""), in Cell C2 insert =IF(LEN(A2)>x,A2,"") etc.

Actually that’s what you are doing, isn’t it? :s[/quote]
thanks. Using a function like that works well to copy the data as I need. I need to have 2 separate columns one with data <= len (x), one with data > len (x). I can use a similar function to give me the rest in another column with = my 2 columns. This is going into a database, so I was just using xls to massage the data.
I was trying to use the fliter, cut, paste routine to do it like I have done in the past (an confirmed by others who did just that) but for some reason this PC won’t let me do that.

Got it done one way or another, thanks for everyone’s help.

PS. Still haven’t got that language sorted out. I tried the Office utility which installed it ok, but they still come up as ???.
EDIT: got that sorted too. Turns out I wasn’t importing the data as unicode…doh.

Thanks everyone. Have a great New Years!!

Use the “Select visible cells” icon. This will select only the visible cells, and not the ones the filter has excluded.

Find this button by menu Tools>Customise, click the “Commands” tab, select “Edit” and scroll down to the buttom. Drag the Select Visible Cells button onto the menu/icon bar then close the Customise dialogue box.

To use: Slect the area containing the cells you need. Of course that will include the ones hidden by the filter. Click the SVC button and only the ones visible to you will be selected, ie, not those the filter has hidden. Then paste them wherever you want.

HTH

Believe it or not I tried that and it didn’t work. I am starting to believe this PC is dodgy.

Got it all worked out now tho, cheers.

How? Let us know please.

How? Let us know please.[/quote]
I opted for the formula idea you suggested, over a couple of columns. Worked well, and a much better idea than just cut & paste :blush:

:notworthy: