Interposing rows.

[serge]

Hi everyone,
I'm looking for a formula that will interpose 2 sets of row into 5 columns.
In A:E there is 5 cells : date, 3 digits and a $ amount and the same from G:K.
Those are lottery draws that happen twice per day, that's why each row has the same date, one is Mid day and the other is Evening.
What I'm looking for is to interlace those 2 draws per day into just 5 columns from M:Q like the example show.
Thank you.

 

[bebo021999]

Book1
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q
2	01-Jan	1	2	3	$400		01-Jan	19	20	21	$800		01-Jan	1	2	3	400
3	02-Jan	4	5	6	$450		02-Jan	22	23	24	$850		01-Jan	19	20	21	800
4	03-Jan	7	8	9	$500		03-Jan	25	26	27	$900		02-Jan	4	5	6	450
5	04-Jan	10	11	12	$550		04-Jan	28	29	30	$950		02-Jan	22	23	24	850
6	05-Jan	13	14	15	$600		05-Jan	31	32	33	$1,000		03-Jan	7	8	9	500
7	06-Jan	16	17	18	$650		06-Jan	34	35	36	$1,050		03-Jan	25	26	27	900
8													04-Jan	10	11	12	550
9													04-Jan	28	29	30	950
10													05-Jan	13	14	15	600
11													05-Jan	31	32	33	1000
12													06-Jan	16	17	18	650
13													06-Jan	34	35	36	1050
14
15
Sheet1

Cell Formulas
Range	Formula
M2:Q13	M2	=INDEX($A$2:$K$15,INT((ROWS($1:1)-1)/2)+1,IF(ISODD(ROWS($1:1)),0,6)+COLUMNS($A:A))

 

[serge]

Very nice, Than you bebo021999, your formula works like a charm for what I need, I really appreciate the help.

 

[serge]

Hello, I'm not sure if I need to open a new tread or keep going with this one since it's the same subject ?

bebo021999 solve my problem, Thank you again.
I found that that some other games have 3 draws per day so I would need the formula to be modified to add 1 more draw to my column !
To look like this example below :

This is the formula that I use :
=INDEX($A$3:$K$331,INT((ROWS($1:1)-1)/2)+1,IF(ISODD(ROWS($1:1)),0,6)+COLUMNS($A:A))

Thank you.

 

[bebo021999]

Could you post a mini-sheet like I did before?

XL2BB - Excel Range to BBCode

Excel 'mini-sheet' in messages - XL2BB Although experts prefer to read your description and question instead of working in your actual file to solve your problem, there are times that it is difficult to explain an issue without providing actual...

www.mrexcel.com

 

[StephenCrump]

Using the layout in Post#2, you can modify @bebo021999's formula to:

=INDEX($A$2:$Q$15,INT((ROWS($1:1)-1)/3)+1,COLUMNS($A:A)+MOD(ROWS($1:1)-1,3)*6)

Apologies for jumping in. I started at your other post: Overlapping rows.

 

[Peter_SSs]

I'm not sure if I need to open a new tread or keep going with this one

You should do one or the other but not both as per #12 of the Forum Rules
In this case I have locked the other one.

 

[serge]

Could you post a mini-sheet like I did before?

XL2BB - Excel Range to BBCode

Excel 'mini-sheet' in messages - XL2BB Although experts prefer to read your description and question instead of working in your actual file to solve your problem, there are times that it is difficult to explain an issue without providing actual...

www.mrexcel.com

Hi bebo021999,
I tried to installed it multiple time but it doesn't work maybe because I'm running on Excel 07, I don't now I would really like to have it it would make it easier for me sometime to explain my file but it can't be install on my excel !!!

 

[serge]

You should do one or the other but not both as per #12 of the Forum Rules
In this case I have locked the other one.

Sorry I apologize.

 

[serge]

Using the layout in Post#2, you can modify @bebo021999's formula to:

=INDEX($A$2:$Q$15,INT((ROWS($1:1)-1)/3)+1,COLUMNS($A:A)+MOD(ROWS($1:1)-1,3)*6)

Apologies for jumping in. I started at your other post: Overlapping rows.

Than you very much StephenCrump your formula work great I really appreciate it.