Copying data without blank cells, and only certain cells

I have 3 questions:

1).  I receive large data sets from scientists in excel format that'
been transferred from some instrument.  In column A are numbers usuall
starting from 200 to 780 counting up every one unit.  In the column 
is the data corresponding to the numbers in column A.  I'm trying t
figure out a formula that will copy the number 200 and every 5 unit
thereafter (200, 205, 210...etc) in one column plus the data tha
corresponds to that number in other column.  

2).  Also, in some data sets I receive, there are an even amount o
blank cells between each filled cell.  So A1 would be filled and fiv
cells later, A7 is filled and five cells later A13 if filled and etc.
How do I copy the numbers in column A but not the blank cells?

3).  Sometime I have a combination of problems 1 and 2. 


Thanks.
:confused

-----------------------------------------------
~~ Message posted from http://www.ExcelTip.com
~~View and post usenet messages directly from http://www.ExcelForum.com

0
11/21/2003 7:45:30 PM
excel.misc 78881 articles. 5 followers. Follow

2 Replies
447 Views

Similar Articles

[PageSpeed] 2

For item 2, just select all and use Data > Sort, this will put all th
blanks togethe

-----------------------------------------------
~~ Message posted from http://www.ExcelTip.com
~~View and post usenet messages directly from http://www.ExcelForum.com

0
11/21/2003 8:39:23 PM
Roshario,

2. To delete the empty rows:
- select Col A
- Edit>Goto>Special>Blank/Empty
- Edit>Delete>Entire Row


1. To copy every 5th row in Cols A and B to Cols C and D, assuming the =
table starts in A1 (otherwise adjust the formulas accordingly):

- in C1 enter =3DOFFSET($A$1,(ROW()-ROW($A$1))*5,0)
- in D1 enter =3DOFFSET($A$1,(ROW()-ROW($A$1))*5,1)
- fill down Cols C and D as far as needed
- select Cols C and D
- Edit>Copy
- Edit>Paste special>Values
- delete Cols A and B

HTH
Anders Silven

"Roshario" <Roshario.x9eta@excelforum-nospam.com> skrev i meddelandet =
news:Roshario.x9eta@excelforum-nospam.com...
>=20
> I have 3 questions:
>=20
> 1).  I receive large data sets from scientists in excel format that's
> been transferred from some instrument.  In column A are numbers =
usually
> starting from 200 to 780 counting up every one unit.  In the column B
> is the data corresponding to the numbers in column A.  I'm trying to
> figure out a formula that will copy the number 200 and every 5 units
> thereafter (200, 205, 210...etc) in one column plus the data that
> corresponds to that number in other column. =20
>=20
> 2).  Also, in some data sets I receive, there are an even amount of
> blank cells between each filled cell.  So A1 would be filled and five
> cells later, A7 is filled and five cells later A13 if filled and etc.=20
> How do I copy the numbers in column A but not the blank cells?
>=20
> 3).  Sometime I have a combination of problems 1 and 2.=20
>=20
>=20
> Thanks.
> :confused:
>=20
>=20
> ------------------------------------------------
> ~~ Message posted from http://www.ExcelTip.com/
> ~~View and post usenet messages directly from =
http://www.ExcelForum.com/
> 
0
11/21/2003 10:05:17 PM
Reply:

Similar Artilces:

When I am in one cell highlight another
Hello, When I am in one cell highlight another. Lets say that if my cursor is in A1 I want D1 highlighted or with another cursor on it. Then if I move to A2, D2 should be the one with another cursor or highlighted. Thank you for your help, Jose Juan Diaz hi, Jose Juan ! > When I am in one cell highlight another > ... if my cursor is in A1... D1 highlighted or with another cursor on it. > ... if I move to A2, D2 should be the one with another cursor or highlighted. 'put' a cursor on non-active-cell... [I don't think it's possible] :( to highlight 'D' wh...

Can't track certain Outlook Contacts
Hi, For one of my users (crm 3.0 - Outlook 2003), the 'Track in CRM' button does not appear for certain contacts. About 2/3s of the contacts are fine. I thought it might be contacts created in the Blackberry then sync'd to Outlook but that doesn't appear to be the case. I'm still working on finding a pattern, unfortunately this user is travelling a lot so it's hard for me to sit down with her and see what she's doing. I thought maybe someone may have had a similar experience. ...

Why does copy and paste between eMails not work?
Copy no longer copies to the clipboard. Is there a setting I have deleted somewhere? Using Windows 7 and Office 2007 proffessional. Does it work if you restart outlook? Do you have any addins installed that could be erasing the clipboard? I've seen this happen with addins and contacts, but not email. to test, open the new message form before copying so you just need to copy and switch to the other message. (http://www.slipstick.com/Contacts/clipboard.htm) -- Diane Poremsky [MVP - Outlook] Outlook Tips: http://www.outlook-tips.net/ Outlook & Exchange Solutions Cent...

Cells print so small I cannot read numbers. How do I fix?
I have been working with page break. Now I have the grid on 1 page..but it is far to small to read. now when I try to spread it back to 2 pages, it just takes the same tiny microscopic type and spreads it into 2 pages. I am stuck printing tiny type. How can I get the grid cells back to a size that is readable. It sound like you have selected Fit to 1 page in File > Page setup > Page > Scaling. Either select to fit it to 2 pages or select Adjust to 100% size -- HTH Sandy In Perth, the ancient capital of Scotland and the crowning place of kings sandymann2@mailinator.com Repl...

Process all cells in a (user) selection
I am using this code to give me cell by cell access to a code defined selection; Dim aCell As Range For Each aCell In Sheet4.Range("A4:A34") ' Do Stuff Here Next aCell which processes every cell in the A4:A34 Range How do I do the same thing for a selection drawn by the user before pressing my 'Process' button ? Thanks On 05 May 2010 11:10:21 GMT, Isis <isissoft@NOSPAMbtinternet.com> wrote: >I am using this code to give me cell by cell access to a code defined >selection; > >Dim aCell As Range >For Each aCell In S...

Copy field data to multiple places
Newbi here.... I have a access 07 file of about 1000 records (rows) and a field (column) I'll call the "project number". All the records do not have the project number inserted as of yet. Is there a simple means to insert a project number in say 50 records at a time, another project number in another 75 records etc. Copy/Paste will do it but may take months to enter. Any suggestions appreciated. TIA On Wed, 27 Feb 2008 15:31:05 -0500, "Meebers" <justme@idontkno.com> wrote: >Newbi here.... I have a access 07 file of about 1000 records (rows) and ...

Copying and Pasting Between Published Documents
Hi I have created a 132 Document in Publisher 2007 I needed to make a highly customised and intelligent Index so I created a second document and typed the index in that, having both documents open on extended Windows Pages I thought it would be easy to move the index from the second document into the first document containing the text It does not appear possbible to copy and paste table pages between the two publisher documents Is there any way I can accomplish this task without retyping the Index in the first document Roger Stenson Roger, 1. Create on your 132 page document a bla...

Pivot Tables & changing data
Hi - I have never used pivot tables in Excel before so hopefully what I am going to ask is possible and not too complicated for me.... :o) I am working with Excel 2003. I have a pivot table already set up and the information is pulling data from a row titled "sum of Subscriber". I added new data in a new column from the main spreadsheet and I would like to pull the data from there. Its titled "Adj Subscriber". Is it possible to switch it? If so, how? Thanks, Anna Marie Anna wrote: > Hi - I have never used pivot tables in Excel before so hopefully what I am &...

How Do I Copy My Outlook Files To My New Computer?
I exported my Outlook files from my old computer and burned them to a CD. During the export process, it asked me three times for a password. When I tried to import these files to my new computer I got "File access is denied. You do not have the permission required to access the file." Can anyone help? Apart from the fact that Import/Export options often lead to corruption - you should use a simple pst copy - The pst needs to be on the hd and the read only flag off, before In OL attempting to open it. NB not import <aprice6@hotmail.com> wrote in message news:1145746658.84...

Excel sheet truncated after copying to powerpoint slide
Hi, We are using Office 2000 with sp3. When we copy excel sheet to power point slide, some of the rows and colums are truncated. Also the font size is changed. We can see only some part of the sheet. Let me know if anybody faced this problem.. Thanks in advance Shekar. Debra Dalgleish posted this link that you may want to review: http://www.rdpslides.com/pptfaq/FAQ00068.htm Microsoft wrote: > > Hi, > > We are using Office 2000 with sp3. When we copy excel sheet to power point > slide, some of the rows and colums are truncated. Also the font size is > changed. We can s...

think cell program
is anyone familiar with a program called think cell? Any thoughts? (powerpoint v 2003). Is this an add-in? thanks Sara It's very good. You can "try for free" from their website: http://www.think-cell.com/ Recent interview with one of the founders on Indezine: http://blog.indezine.com/2009/12/think-cell-conversation-with-markus.html -- Echo [MS PPT MVP] http://www.echosvoice.com What's new in PPT 2010? http://www.echosvoice.com/2010.htm Fixing PowerPoint Annoyances http://tinyurl.com/36grcd PowerPoint 2007 Complete Makeover Kit http://tinyurl.com/3...

how to edit my x-axis data on a line graph
Version: 2008 Operating System: Mac OS X 10.5 (Leopard) Processor: Intel my x axis data on my graph is just showing numbers 1 2 3 4 5 6 7 8 9 10 11.... and it is suppose to show the years. I can't figure out how to change these values ...

Data from Access query to Excel
To pull data from an Access 2003 database, I have created the queries in Access, then import into Excel. The problem is that all the numbers that are pulled into Excel are text and need to convert them into numbers to run formulas on. I have converted a few sheets by hand, but, some have will over 50,000 rows. Is there a function to select all number colums (the colums are the same through out the sheets) and convert? Thanks There are instructions here for converting text to numbers: http://www.contextures.com/xlDataEntry03.html You can select all the columns, and only the num...

Using a VBscript to copy/delete/move Outlook messages
I need to write a VBscript to: - open my Outlook (2000 or XP) mailbox - read the list of the folders in either the Exchange Mailbox and in local ..pst files - open each folder and subfolders - read information about messages (i.e. sender, recipient, creation date, and so on) - move, copy or delete messages based on the above information Can anybody please provide a pointer to a sample I can start from? Regards Marius ...

How to delete duplicate data
Hi, I am using excel to consolidate monthly room booking data. I have a date column and time column. May I know how to to delete those rows which contains duplicate data with same date stated in the date columnand and same time range in the time column? Thank you. Data>filter>advanced filter, unique records only and copy to another location -- Regards, Peo Sjoblom Excel 95 - Excel 2007 Northwest Excel Solutions www.nwexcelsolutions.com (Remove ^^ from email) "PL" <PL@discussions.microsoft.com> wrote in message news:F45DAE2B-252D-4E17-81D8-0F4C060BCB21@m...

Newbie Cell Reference Question...
Hello, I apologize if this question has been answered before, but I'm no quite sure what to search for as I'm not very good with excel. I'm making a spreadsheet where one sheet references another. I'll tr to explain this as best as possible.... I'm creating a spreadsheet for a fantasy basketball league. I have "Data" sheet that contains data for all players. I have another sheet "Teams" that has all the players on each team. Column B contains th players name, and column C contains a number that corresponds to th row this player is on in the dat...

Compressed Outlined Data Copy
I need to copy only the data that is compressed in Grouped rows and not all the rows when they are expanded. When I copy and paste the compressed data, I get all the data that is within the group, and not just the compressed data. For example: I need just the following (which is compressed): Ship $ Line $5,278.00 CYP Total $133,122.00 TI Total $34,660,135.00 Vic Total $34,798,535.00 Grand Total when I copy it (D2:E17, but only showing 5 lines because of the compression), it pastes 16 lines: Ship $ Line $123.00 CYP $44.00 CYP $4,444.00 CYP $667.00 CYP $5,278.00 CYP Total $123,124.00 TI $...

inverting data #2
Please help, I'm stuck! I conducted a survey where respondants each gave answers to 30 questions using a Likert scale i.e. they answered either 1,2,3,4 or 5 to each question. The data is in the following form: Question # q1 q2 q3 q4 q5 1 2 4 2 1 2 2 4 1 2 1 4 3 4 4 2 1 4 4 2 3 2 2 5 5 2 3 4 5 3 My problem is that half of the questions were asked in a negative direction to avoid possible response bias. For the answers to these questions, I need to reverse the answers. If the repondant answered 1, I want to record 5, if 2 then 4, if 3 then 3, if 4 then 2, if 5 then 1. Is there an...

SFO without joined to MSCRM domain (continue) for Mr.John
Dear Mr.John, Please tell me, Why SFO can not use RPC over Http? because I must to explain with my customer. Do you have any idea for using SFO with MSCRM behind firewall and client are using SFO had multi domain in their company? rpc over http is used to coomunicate with exchange server. -- John O'Donnell Microsoft CRM MVP http://www.mscrmfaq.us "phong" <phong@discussions.microsoft.com> wrote in message news:8FB1B72F-6021-4D50-B71D-F92433CD44B8@microsoft.com... > Dear Mr.John, > Please tell me, Why SFO can not use RPC over Http? because I must to &...

Cell background shading in Excel
Why is it that the background shading colors available under the Paintbucket Icon chjange from file to file? Also, how can I add some of the colors to the paintbucket that are availabe under the format/cells/pattern pulldown? Colors are a Workbook-level property, so you can change the color palette for any workbook. Choose Tools/Options/Colors... and modify the colors to your hearts' content. You can also import another workbook's color palette from that dialog. Note however, that there are only 56 bins in the color palette, so you can only use 56 colors at a time. The paint b...

Sum every other cell?
I own a used-book shop. I have an Excel SS to track how many books pe day I sell in each of 28 categories and the $$$ I take in for eac category. So there's two columns for each day: #books & $$$. therefore want to add every odd-numbered cell in a row to get the tota number of books for a category in a given period of days and ever even-numbered cell in the same row for the the total $$$. Is there simple formula or function for this -- bookmanj ----------------------------------------------------------------------- bookmanjb's Profile: http://www.excelforum.com/member.php?acti...

Retreiving data
We move mailbox on a new server but somethimes we receive the following message: "outlook is retrieving data from the OLD exchange server" Can anyone help me -- Dambo On Thu, 1 Feb 2007 01:21:01 -0800, Damb0 <Damb0@discussions.microsoft.com> wrote: >We move mailbox on a new server but somethimes we receive the following >message: "outlook is retrieving data from the OLD exchange server" > >Can anyone help me What steps did you do to decommission the old server? If you give us an idea of the version of the old and new ones we can post the right help ...

Help Required with Macro to Manipulate Data
I have a spreadsheet(s) with the following data on it (this is imported from a text file) A B C D 1 Branch Date Total 2 4501 030204 29 3 4 4501 030204 14 5 6 4502 030204 331 7 8 4502 030204 52 9 10 4503 030204 54 11 12 4503 030204 85 Hi Alay and what do you want to achieve :-) -- Regards Frank Kabel Frankfurt, Germany "Alan T >" <<Alan.T.12jthe@excelforum-nospam.com> schrieb im Newsbeitrag news:Alan.T.12jthe@excelforum-nospam.com... > I have a spreadsheet(s) with the following data on it (this is imported ...

how to make a view (without a title bar) draggable?
Hi, I want to create a listbox (using CWnd::CreateEx()) as a child view of the main view. But the lustbox must not have a menu bar and a title bar. How can I make this listbox child view draggable (but not resizable)? Please help. Thanks. Tony Handle OnMouseMove() at the Listbox level and send a WM_NCLBUTTONDOWN message with wParam = HTCAPTION to the listbox itself. Something like void CYourListBox::OnMouseMove(UINT nFlags, CPoint point) { SendMessage( WM_NCLBUTTONDOWN, HTCAPTION, 0 ); // CListBox::OnMouseMove(nFlags, point); } -- Cheers Check Abdoul [VC++ MVP] --...

MERGE CELLS
I have Name, PO Box, street address, city, state, zip across a row in 6 seperate cells/columns. I want to have this format in 1 cell: Name PO Box Street address City, State Zip STEVE wrote: > I have Name, PO Box, street address, city, state, zip across a row in 6 > seperate cells/columns. > > I want to have this format in 1 cell: > > Name > PO Box > Street address > City, State Zip > You do realize that this will screw up your ability to sort the data. A better solution would be to describe what you want to do with the data. You may not need it in the ...