Need formula help for a chart

[serge]

Excel Workbook
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q
2
3
4			6	34	11	3	8	36	19	10	5	2	25	37	48	3
5
6		1	1	1	1	1	1	1	1	1	1	1	1	1	1	1
7		2	2	2	2	2	2	2	2	2	2	2	2	2	2	2
8		3	3	3	3	3	3	3	3	3	3	4	3	3	3	3
9		4	4	4	4	5	4	4	4	4	4	5	5	4	4	5
10		5	5	5	5	6	6	5	5	5	5	6	6	6	5	6
11		6	6	6	6	7	7	7	6	6	7	8	7	7	7	8
12		7	8	7	7	8	8	8	8	7	8	9	9	8	8	9
13		9	10	9	8	9	10	9	9	9	10	11	10	10	9	10
14		13	14	11	10	11	12	11	10	10	11	12	12	11	11	12
15		15	16	15	16	17	18	13	12	13	14	15	13	13	12	13
16		24	25	17	18	19	20	19	14	15	16	17	16	14	14	15
17		25	26	26	27	28	29	21	22	23	24	25	18	17	15	16
18		26	27	27	28	29	30	30	31	32	33	34	35	19	18	19
19		31	32	28	29	30	31	31	32	33	34	35	36	36	20	21
20		33	34	33	34	35	36	32	33	34	35	36	37	37	37	38
21		36	37	38	39	40	41	42	43	44	45	46	47	48	38	39
22
Sheet

This table work column per column.
Each column is for a new day.

In Row 4 are the numbers that need to be found in the previous column.

example :
Number 6 in C4 is in cell B11 in the previous column. Then when I highlight B6:B21 and drag it over C6:C21 this number (6) BECOME THE NUMBER 1 in C6 and push down all the other numbers by 1 cell, and at the same time every number are added 1 when they pass over the next column (C).

So 1 become 2, 2 become 3 become 4, etc… since number 6 has been selected ( in C4 ), there would be no number 7 in column C ( since all numbers are suppose to added 1 in the next column over ), because now it become the number 1 in C6.

It look a little bit confusing but, it's very important to me.

To conclude, every single numbers need to be added 1 when they pass over the next column, and the number selected in row 4 become the number 1 in row 6.

Thank you for any help.

 

[serge]

Does anyone have an idea for this chart ?

 

[Gerald Higgins]

Let's just focus on columns C and D to begin with.

I think I understand the logic for generating the numbers in column C.
It looks like...

IF
the adjacent number in column B is LESS THAN OR EQUAL TO the number in C4,
THEN
copy it across
ELSE
copy it across and add 1

That is straightforward and we can write an Excel formula to do that.

But that logic does not seem to apply consistently to column D.
How do you get the 11 in Col D, for example ?

 

[serge]

Hi Gerald,

Firstable I want to apologized for not been able to respond right away, my work sometime is very busy.

I know what you mine when you say :
IF
the adjacent number in column B is LESS THAN OR EQUAL TO the number in C4,
THEN
copy it across
ELSE
copy it across and add 1

That is straightforward and we can write an Excel formula to do that.

But it's not the way it works, looking at the chart it kind of look like this.
So I made another chart to explain better :

Excel Workbook
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q
2
3
4			6	34	11	3	8	36	19	10	5	2	25	37	48	3
5
6		1	1	1	1	1	1	1	1	1	1	1	1	1	1	1
7		2	2	2	2	2	2	2	2	2	2	2	2	2	2	2
8		3	3	3	3	3	3	3	3	3	3	4	3	3	3	3
9		4	4	4	4	5	4	4	4	4	4	5	5	4	4	5
10		5	5	5	5	6	6	5	5	5	5	6	6	6	5	6
11		6	6	6	6	7	7	7	6	6	7	8	7	7	7	8
12		7	8	7	7	8	8	8	8	7	8	9	9	8	8	9
13		9	10	9	8	9	10	9	9	9	10	11	10	10	9	10
14		13	14	11	10	11	12	11	10	10	11	12	12	11	11	12
15		15	16	15	16	17	18	13	12	13	14	15	13	13	12	13
16		24	25	17	18	19	20	19	14	15	16	17	16	14	14	15
17		25	26	26	27	28	29	21	22	23	24	25	18	17	15	16
18		26	27	27	28	29	30	30	31	32	33	34	35	19	18	19
19		31	32	28	29	30	31	31	32	33	34	35	36	36	20	21
20		33	34	33	34	35	36	32	33	34	35	36	37	37	37	38
21		36	37	38	39	40	41	42	43	44	45	46	47	48	38	39
22
Sheet

This chart add 1 ( 1 day ) to each number, it show how many days has pasted until that number has been selected.

So let take B6=1 ( in Blue ) this number when it pass over the next day become 2 in C7.

Explanation: ( 1+1=2 and been pushed down 1 cell because of the number 6 been selected and become the number 1 on top of the column ).

And then become 3 in D8, 4 in E9, etc... up to 15 in P16.
This number hasn't been selected in row 4, so it keep going.

And for the number 15 in B15 ( in Orange ), this number also keep going until it been selected in M4, then it become 1 and start again the same pattern.

I hope this would explain better ? I'm doing this chart by hand and it take me a lot of time to do it, so if one formula would do the trick, I would be very happy.

Thank you for the help.

 

[Gerald Higgins]

I'm sorry, I've read and re-read your post but I can't understand the logic behind this.

Can we just focus on the first three columns, B, C and D, and can you explain really clearly how the numbers in those three columns are calculated ?

 

[serge]

OK,

Column B:

is the starting column and imagine that the numbers in column B are " Numbers of Days ", the reason I add 1, is because each new column is a new day.

Column C:

From C6:C21 are the numbers from Column B (+1).
When in row 4, here ( D4 = 34 ).
This number (34) will be removed from column C and become ( as number 1 ) in D6, and will push, all the other numbers down by 1 cell, with plus 1 day.

 

[serge]

Here is a new table chart with only 3 columns for a better understanding.

Excel Workbook
	A	B	C	D	E
1
2
3
4			7	13
5
6		1	1	1
7		2	2	2
8		3	3	3
9		4	4	4
10		5	5	5
11		6	6	6
12		7	7	7
13		8	9	8
14		9	10	10
15		10	11	11
16		11	12	12
17		12	13	13
18		13	14	15
19		14	15	16
20		15	16	17
21		16	17	18
22
Sheet

I put arrows to explain better but don't show on the post sorry about that !!!

7 in C4 is the number that need to be found and removed from column B ( which is in B12 ), this number now will start at number 1 in C6.

And the same for the next column number 13 in D4 need to be found in column C ( which is in C17 ) and removed from it and then start at number 1 in D6.

That's it, it's much clearer now I hope.

Thank you in advance for anyone that would help.

( Does someone knows where to click in " Excel jeanie HTML " to show the arrows I put on the chart ? ) Thank you.

 

[serge]

Here I tried to explain in another way, let see if someone can help me ?

Excel Workbook
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O
1
2
3
4			7	13		Number	in	Become	in		Number	in	Become	in
5
6		1	1	1		1	B6	2	C7		1	C6	2	D7
7		2	2	2		2	B7	3	C8		2	C7	3	D8
8		3	3	3		3	B8	4	C9		3	C8	4	D9
9		4	4	4		4	B9	5	C10		4	C9	5	D10
10		5	5	5		5	B10	6	C11		5	C10	6	D11
11		6	6	6		6	B11	7	C12		6	C11	7	D12
12		7	7	7		7	B12	1	C6		7	C12	8	D13
13		8	9	8		8	B13	9	C13		9	C13	10	D14
14		9	10	10		9	B14	10	C14		10	C14	11	D15
15		10	11	11		10	B15	11	C15		11	C15	12	D16
16		11	12	12		11	B16	12	C16		12	C16	13	D17
17		12	13	13		12	B17	13	C17		13	C17	1	D1
18		13	14	15		13	B18	14	C18		14	C18	15	D18
19		14	15	16		14	B19	15	C19		15	C19	16	D19
20		15	16	17		15	B20	16	C20		16	C20	17	D20
21		16	17	18		16	B21	17	C21		17	C21	18	D21
22
9 (2)

Column F:I are is the explanation of column B,C and K:N of C,D.

Small mistake : 13 in C17 become 1 in D6

The number in row 4 ( here number 7 in C4 ) must be found in column B, then, all the numbers are rollover to column C,the number 7 become the number 1 in C6 and all the other numbers from B are all added 1 and put below the number 1, starting at C7.and then the same with number 13.Etc..

Basically, all the numbers from B are rollover to C with each one added 1 and the number in row 4 ( C4 ) is taking off B to become always a number 1 at the top of the next column.That it, and so on with the other number to come.

Hope someone can help, I know I'm pushing but I really need this formula.

Thank you all.

 

[Gerald Higgins]

I've been away on holiday - are you still looking for help on this one ?

 

[serge]

Hi Gerald,

Thanks for the reply, and yes I do but it seem to be hard, for the a understanding part so I let it go.

But yes I still want it if someone can figure it out.
Thank you.
Serge.