SOLVED

Excel - Moving two columns of data into one column of data

Occasional Contributor

I need to move two columns of data into one column of data.  The second column needs each cell to go under the corresponding row cell next to it.  Is this possible?  See example below please. 

 

1  a

2  b

3  c

4  d

 

1

a

2

b

3

c

4

d

 

Thanks, Jhall212

 

9 Replies
best response confirmed by Jhall212 (Occasional Contributor)
Solution

@Jhall212 

Let's say the data are in A1:B4.

 

In D1, enter the formula

 

=INDEX($A:$B,QUOTIENT(ROW()-1,2)+1,MOD(ROW()-1,2)+1)

 

and fill down.

@Hans Vogelaar 

 

Thank you so much!  Do you know how to move items from one column into two columns?

 

Example:

 

1

A

 

2

B

 

3

C

 

4

D

 

TO:

1  A

2  B

3  C

4  D

 

Thanks!

Jhall212

 

@Jhall212 

Let's assume that your data are in column A, starting in A1.

 

Enter the following formula in C1:

=INDEX($A:$A,3*ROW()-2)

And in D1:

=INDEX($A:$A,3*ROW()-1)

 

Select C1 and D1, then fill or copy down.

It worked for cells A1 and A2 but then it skips cell A3 and goes to A4. It skips every third cell.

1A
B3
4D
5E

@Jhall212 

The way you posted the sample data makes it difficult to know what they really look like.

Could you attach a sample workbook?

17-329X
V33897360X
2017-1072X
V92921408X
2017-1233X
V55931648X
17-1362X
V11924954X

17-329X V33897360X
2017-1072X V92921408X
2017-1233X V55931648X
17-1362X V11924954X

@Jhall212 

I'm afraid that doesn't help.

Ok, thank you for helping. I am trying to get one column of information to split between two columns. The 1st and 3rd are the same types of numbers and the 2nd and 4th are the same types of numbers and so on. I have a large amount of data. I figured out how to get the first set of information to work but not the second.
In the example above I can get 17-329X, 2017-1072X, 2017-1233X and 17-1362X to go in column C but I get mixed information in column D when trying to get the Vxxx information. I used formula =INDEX($A:$A,3*ROW()-2) to work for column C but it doesn't work for column D. All information is coming from column A. I have tried different things to get a formula to work for column D but it only works if I manually change it. For example I enter =INDEX($A:$A,3*ROW()-1) and I get the information I want. I have to then change the 1 in each formula in the next cell down to the next number, 2,3,4 etc.
Not sure if this helps you help me or not. I appreciate the thought and help though.

Jhall212



I found a work around. I am using the filter feature instead. The numbers for each sequence I need have similar letters in them so I am using them to differentiate between my two columns. Thank you for the assist for the other formulas though, much appreciated!!