General discussion

Selecting cells in Excel

I have a column of data. I want to select every sixth cell in that column, starting with the third cell, and copy those values into a different column? Can I do that, and If so, how do I do that?

Thanks in advance for the help.

mstrvlr

Discussion is locked

Follow
Reply to: Selecting cells in Excel
PLEASE NOTE: Do not post advertisements, offensive materials, profanity, or personal attacks. Please remember to be considerate of other members. If you are new to the CNET Forums, please read our CNET Forums FAQ. All submitted content is subject to our Terms of Use.
Reporting: Selecting cells in Excel
This post has been flagged and will be reviewed by our staff. Thank you for helping us maintain CNET's great community.
Sorry, there was a problem flagging this post. Please try again now or at a later time.
If you believe this post is offensive or violates the CNET Forums' Usage policies, you can report it below (this will not automatically remove the post). Once reported, our moderators will be notified and the post will be reviewed.
Comments
- Collapse -
Easiest way

The easiest way is to point the destination cell to the 6th cells you want copied, eg:

Column B has 27 cells filled in from B1 to B27

Column C has;

C1=B3
C2=B9
C3=B15
C4=B21
C5=B27

and so on.

Is that what you mean?

Mark

- Collapse -
Mark, a further question

Mark,

That's exactly what I mean. Can I make it automatic, in the sense that I don't have to type in individual entries? My data column have about 5000 elements. I'm a real novice in Excel.

Thanks for your suggestion

- Collapse -
You would have to write a macro to do it.

It wouldn't be difficult for a programmer, but I've never done exactly this kind of macro putting formulas in a cell.

Kees

- Collapse -
I agree

I don't see any easy way to do this.

swisse, a contributor here would love this sort of problem and is a wiz at macros.

But I haven't seen swisse around for a while.

If no-one else here can help you, you may find Microsoft's Community Newsgroups very useful. They have Excel threads there and many experts who might help.

http://www.microsoft.com/office/community/en-us/default.mspx?dg=microsoft.public.access.modulesdaovba&lang=en&cr=US

You may have to register to use them, and, the site may not like non-IE browsers, Happy

(My apologies if CNET would rather I did not suggest a non-CNET forum).

Mark

- Collapse -
A possible solution

If I could help.

Assuming your data starting in A1

I would add a column with your desired lines like this:
C1 = 3
C2 = C1 + 6
C3 = C2 + 6
just recopy

Then I would use OFFSET like this:
D1 = OFFSET($A$1,C1-1,0)
D2 = OFFSET($A$1,C2-1,0)
just recopy

- Collapse -
(NT) (NT) That obviously is the solution.
- Collapse -
Yeah

I forgot about OFFSET!

I always had difficulty with Offset and Match functions. Time for more reading again I suppose.........

Mark

- Collapse -
(NT) (NT) I didn't even know OFFSET. Nice function.
- Collapse -
Thanks

A very nice solution indeed--I appreciate it. I guess you can do a very similiar thing with an IF statement.

CNET Forums

Forum Info