Need help with pasting rows with values in excel please!!

[Mr_RAI]

Hi All

I'm really stuck and need some help.
I have lots of data in a spreadsheet and I need to be able to copy the data into another programme (SPSS). However I have a problem.

At the moment, the report I have has the data in a rows however some of the cells in th row are not populated with any data.
I need to be able to copy the row, and paste only the cells with data in them into a new excel sheet.
I have tried filtering, but you can only filter by column and my data is laid out in rows i.e.

Gender
Male (cell A1) or Female (cell A2)

Age
16 - 24 (cell A3) or 25 - 34 (cel A4)


In the above, in my spreadsheet I have a system where the value 1 represents male and 2 represents female. For age, 16-24 is equal to 1 and 25-34 is equal to 2.

If Row 1 is a male of age 17, if I copy and paste the data into a sheet, it will copy as 1,0,1,0. I need it to copy as 1,1 skipping any blank cells. I have hundreds of rows and columns of data!

Please can anyone help? I'd really appreciate it

Thanks
Mr_Rai

 

[bryonwoods30]

copy the rows, paste special and transpose them into columns, do your filtering then copy and paste special again transpose back into rows

 

[Mr_RAI]

Thanks for the response!
How do I transpose into columns? It doesn't give you any such option in paste special?

 

[bryonwoods30]

highlight all the values in your row/rows, then control c to copy
go to a blank cell like on another worksheet/tab and right click, paste special, then down at the bottom right above the ok button is transpose

 

[Mr_RAI]

Perfect. And then how do I get rid of the blank cells? Which is the best filter option to use?

 

[bryonwoods30]

i usually choose filter, auto filter. then in the drop down arrow you can do one of two things. choose to show only blanks then delete them, or show non blanks and copy the data then go back paste special that data and transpose back into rows.

 

[Mr_RAI]

I'm really sorry, I apparently have no idea what I'm doing. I can only see option to make values greater or equal to certain value etc. I cant see away of selecting all non-blanks? How do I do that?
I can only see a filter option as opposed to auto filter..

 

[bryonwoods30]

if you have your column in column a and starting in a1, select cell a1 then go to data on the menu, filter, then auto filter. then in the drop down arrow that shows up in cell a1 down at the bottom choose non blanks. then you can just highlight those cells and copy them.

 

[Mr_RAI]

My problem is that the drop down arrow that shows up in cell a1 does not give the option to choose non blanks. I mhave Excel 2007 - in this version it gives option on that dropdown to sort smallest to largest, largest to smallest, sort by colour, and number filters (where the equals to, between values etc options are).
I cannot see anywhere to select non-blanks.

If this does not work...

Is there a way I can populate all blank cells with a 0 using a filter and then use one of the rules to filter?
Thanks for your help

 

[bryonwoods30]

you could highlight all the cells down to where your last value is, then find and replace. leave the find blank and replace with any value