Moving below cell to next cell on the right

[JH78632]

Hi all,

Probably a bit of a longshot here, but I'm going to give it a go! I've tried myself and can't for the life of me figure it out.

I have been sent a sheet of data containing around 10,000+ cells going down, as seen in the below image, there is two of each in the 'date' tab however it has two different sets of data. I need these to go to the side (highlighted in yellow), however due to the large amount, this would take a very long time to do manually.

Is anyone able to advise on how I could do this in one bulk move? Appreciate all the help!

 

[steve the fish]

So you want whats in B3 then B5 then B7 etc?

=INDEX(B:B,1+2*ROWS($A$1:A1))

 

[JH78632]

So you want whats in B3 then B5 then B7 etc?

=INDEX(B:B,1+2*ROWS($A$1:A1))

Hey Steve,

Thanks for the help mate! That worked.

Just wondering if there was a way to do this if let's say there was more than 1 cell below? I anticipate that future sheets I'm sent may contain much more data under one column.

I.e;

44341 Test
44341 Testing
44341 Tester
44341 Test12345

 

[steve the fish]

Depends if its consistent. If its always the same number of rows apart then yes. If not then it depends if there is anything else to refer to that can identify the row required.

 

[JH78632]

Depends if its consistent. If its always the same number of rows apart then yes. If not then it depends if there is anything else to refer to that can identify the row required.

Would it be possible for something to identify duplicates in column A, and move the contents in column B side by side for all?

 

[Fluff]

Hi & welcome to MrExcel.
How about

+Fluff 1.xlsm
	A	B	C	D	E	F	G	H
1
2	1	A	B	C	D	E	F
3	1	B
4	1	C
5	1	D
6	1	E
7	1	F
8	2	G	H
9	2	H
10	3	I	J
11	3	J
12	4	K	L
13	4	L
14	5	M
15	6	N	O
16	6	O
17	7	P	Q	R	S
18	7	Q
19	7	R
20	7	S
21	8	T	U
22	8	U
23	9	V	W	X
24	9	W
25	9	X
26	10	Y	Z
27	10	Z
Master

Cell Formulas
Range	Formula
C2:H27	C2	=IF($A2=$A1,"",IFERROR(INDEX($B$2:$B$83,AGGREGATE(15,6,(ROW($A$2:$A$83)-ROW($A$2)+1)/($A$2:$A$83=$A2),COLUMNS($B2:C2))),""))

 

[steve the fish]

You could but it would be slow in your version of excel. This is how id go about it. First id create a unique dates row. For example in cell C2:

=MIN(A:A)+ROWS($A$1:A1)-1

In D2:

=INDEX($B$2:$B$20000,SMALL(IF($A$2:$A$20000=$C2,ROW($A$2:$A$20000)-ROW($A$2)+1),COLUMNS($A$1:A1)))

entered CTRL-SHIFT-ENTER

 

[steve the fish]

Oh hello. Was i just wasting my time?

 

[JH78632]

Hi & welcome to MrExcel.
How about

Hi Fluff,

Thanks for this - really appreciate it! Just wondering if it's possible to expand it after the column which has 'Z'? Just as my current sheet has 10,000+cells in total.

Oh hello. Was i just wasting my time?

Not at all - I will almost certainly use your formula also Thanks for your help!

Sorry if I'm asking silly questions guys! I'm not even at novice level when it comes to excel.

 

[Fluff]

Just change the range to cover however many rows you have.