Select Records and create a new column

[maxac8]

Hello,

Attached is a clip of a spreadsheet which will help illustrate where I am stumped.

Basically what I want to do is take any value that isnt "0" in column F and place it in another column in a nicely formatted table. So basically get rid of all the zeros...

Im sure this has been done and asked before but I have no idea what to search for or if there is some easy forumla in excel for situations like this.

Thanks for any tips,

Adam

P.S. I cant believe I never thought to find a forum for excel, this may become my new favorite site.

[zbor]

Something like this?

=IF(F1=0;"";F1)

(Change ; to , if needed)

[JBeaucaire]

Highlight column F
Press F5
Select Special
Select Constants
Select [ x ] Text only, uncheck the others
Press OK

CTRL-C to copy
Click on new column and press ENTER

========

For repetitive action, click on TOOLS > MACRO > Record a New Macro and record a macro of you doing all this, then you can do it all with the a click next time.

[maxac8]

Rereading my initial post I dont think I clearly stated my problem. Thank you bery much though JBeaucaire, that was what I initially asked.

My table has 30,000 records in it. Of those records, I have identified approxiametly 2000 records which aren't zero. I want to take those 2000 records and put them into a seperate column with no spaces or zeros in between them.

That way instead of having a table with 30,000 records, 2000 of which im interested in being dispersed randomly, i will have 1 nice neat column for which I can do further analysis on.

Any Ideas?

[JBeaucaire]

Click anwhere in the dataset and select DATA > AUTOFILTER. Small arrows will appear at the top of all the columns.

Click on the arrow for column F and select 0 from the top of the list. All values OTHER than zero will be hidden.

Copy and paste the visible data to another sheet or down below the original data, then go back and turn off the AUTOFILTER.

[carsto]

This will put all the non zeros into a table on sheet 2, leaving the zeros in Sheet 1

Please Login or Register  to view this content.

[JBeaucaire]

Today at 07:35 AM. Reason: MUCH EASIER SOLUTION JBEAUCAIRE!

Are you affirming my suggestion or chastising me? I can't figure out your intent.

Also, like my first suggestion, yours solves the original query by the OP, which he has since corrected.

[carsto]

to clarify

JBEAUCAIRE"s IS A MUCH EASIER SOLUTION than mine!

[maxac8]

Thank you both for your help, I actually tried both solutions...

The Autofilter worked great although I actually ended up using the "Sort Descending" command.

The code seemed to be okay but it kept coming up with an error in the "x1" part of it.

That may have been my fault however seeing as how I am not too familiar with VBA.

Many thanks to everyone.

Maxac8

[JBeaucaire]

Great! If that takes care of your need, be sure to EDIT your original post (Go Advanced) and mark the PREFIX box [SOLVED]