How do I cut every nth row and paste it on every nth row in Excel?

-1

Let's say I have a list of cities at rows 1, 4, 7... 3n - 2

And let's say I have a list of states at rows 3, 6, 9... 3n

The cities and states are in the same column

How do I cut the states so that at rows 1, 4, 7... 3n - 2 the information of City, State shows up?

For example,

Input:

Richmond
Virginia
New York City
New York

Output:

Richmond, Virginia
New York City, New York

Daniel Christl

Posted 2018-05-31T19:34:40.237

Reputation: 7

1Do you want to do this with VBA or formulas? Can you show us what you've tried so far? – cybernetic.nomad – 2018-05-31T19:50:47.183

I've never used excel before, so far I'm just cutting and pasting every third line – Daniel Christl – 2018-05-31T20:35:58.517

I looked at almost all excel formulas and it doesn't seem like you can manipulate data by cutting and pasting – Daniel Christl – 2018-05-31T20:51:09.103

@DanielChristl, if you are comfortable with VBA(Macro) then I can suggest one simple method to do what you are looking for. – Rajesh S – 2018-06-01T06:37:01.457

1Your description does not match your example, so you've received answers that don't match your description. Please make your question internally consistent. – fixer1234 – 2018-06-01T08:52:54.860

Answers

Formulas never copy/paste, but they can manipulate strings. In a different column, enter this formula:

=CONCATENATE(INDIRECT("A" & (ROW(C1)-1)*2+1),", ",INDIRECT("A" & (ROW(C1))*2+1))

Then populate it down the column.

Assumptions: Your data is in column A and the formula is in Column C

cybernetic.nomad

Posted 2018-05-31T19:34:40.237

Reputation: 4 469

Check the Screen Shot:

You can use the following Formula which generates effect like concatenation :

=OFFSET(L2,(ROW(L1)*1)-1,0)& ", "&OFFSET(L3,(ROW(L1)*1)-1,0)

N.B.

After you write the Formula in the desire cell, fill it downwards.
Adjust the cell address as per your need.

Rajesh S

Posted 2018-05-31T19:34:40.237

Reputation: 6 800