Stacking multiple 6x1 data sets underneath each other 500 times in alternating p

[hexcel]

Hi,


Right now, my data is formatted: All numbers, letters are one cell below. The blank is an empty cell.

QWERTY ___ UIOPAS

DFGHJKL ___ MNBVC

123456 ___ 7890@#


QWERTY, UIOPAS, DFGHJKL, and MNBVC are each their own separate data set. Of course, these repeat 500 times going down.


Is there a way to move these data sets around like so?

QWERTY

UIOPAS

DFGHJKL

MNBVC

123456

7890@#


So I need to mesh the "left" side of the stack in my original formatting with the "right side" in my original formatting. Kinda like when you hold hands and the fingers are between each other in an alternating manner. Don;t forget a letter /number is a cell.

 

[NARAYANK991]

Hi ,


I am not sure I have understood you , but assuming that your data is in columns A and C on Sheet1 , starting from A1 , then on Sheet2 , you can have the following formula in A1 :


=OFFSET(Sheet1!$A$1,INT((ROW()-ROW($A$1))/2),IF(MOD((ROW()-ROW($A$1)),2)=1,2,0))


Copy it down. It will skip the blank column B.


Narayan

 

[hexcel]

This is a great start for me. I guess that is my fault as I was a little unclear.

I have data from A1 to F1, H1 to M1, A2 to F2, and H2 to M2.

I wanted to move the data from H1 to M1 right below A1 to F1.

Then move the data from H2 to M2 right below the data that was from A2 to F2.


I used your method to maybe concatenate A1 to F1 and all the 4 sets then using your equations, then somehow unconcatenate-ing them. lol. I'm a lost cause. But at least a start right?

 

[NARAYANK991]

Hi ,


Let me understand this :


You have data in one sheet , in A1:F1 , G1 is blank , and then again H1:M1.


The next row again has data in A2:F2 , G2 is blank , and then you have data in H2:M2.


This continues for many rows.


You now wish to put this data only in the columns A through F.


Is this correct ?


Narayan

 

[hexcel]

Yes, alternatingly like before. A1:F1 in columns A through F in row 1. Then H1:M1 moved to columns A through F row 2. Then A2:F2 to columns A through F row 3. Then H2:M2 to columns A through F row 4.

 

[NARAYANK991]

Hi ,


Try this :


=OFFSET(Sheet1!$A$1,INT((ROW()-ROW($A$2))/2),(COLUMN()-COLUMN($A$1))+MOD(ROW(),2)*7)


This formula assumes the following :


1. Your input data is on Sheet1 ; it starts from cell A1 , and goes till column M , with column G being blank.


2. This formula is entered in cell A2 , on Sheet2. This is the reason for the usage of ROW($A$2) ; if you start on any other row , you will have to change this to reflect the new first row.


3. Because the first row is row 2 , which is even , the column offset should reset to 0 every even row ; this is the reason for the usage of MOD(ROW(),2)*7 ; if you start on an odd row , you will have to change this to MOD(ROW()+1,2)*7.


Narayan