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

Mr_RAI

New Member
Joined
Mar 3, 2011
Messages
6
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
 
Last edited:

Excel Facts

Select all contiguous cells
Pressing Ctrl+* (asterisk) will select the "current region" - all contiguous cells in all directions.
copy the rows, paste special and transpose them into columns, do your filtering then copy and paste special again transpose back into rows
 
Upvote 0
Thanks for the response!
How do I transpose into columns? It doesn't give you any such option in paste special?
 
Upvote 0
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
 
Upvote 0
Perfect. And then how do I get rid of the blank cells? Which is the best filter option to use?
 
Upvote 0
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.
 
Upvote 0
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..
 
Upvote 0
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.
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0

Forum statistics

Threads
1,213,552
Messages
6,114,278
Members
448,559
Latest member
MrPJ_Harper

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top