Find Delay…

[motilulla]

Using Excel 2010
Hello,

Here is example of 30 rows from C6:P35 every week will be added 1 row below 35….

I want VBA or formula 3 row below of the last data row fid delay of 1, X and 2 signs…in this example T1 has delay for 1=0, for X=1 and for 2=2…. example T2 has delay for 1=6, for X=0 and for 2=1….and so on for T3 to T14.

Note:--1st formula can set find delay max 24 draw back from 0, 2nd always 3 row below of the last data row.

Find Delay.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X	Y	Z
4
5			T1	T2	T3	T4	T5	T6	T7	T8	T9	T10	T11	T12	T13	T14
6		30	X	X	2	2	2	X	1	1	2	X	X	2	2	1
7		29	X	2	2	1	1	X	1	1	1	1	2	X	1	1
8		28	X	1	2	2	2	2	1	1	2	1	2	1	1	X
9		27	1	1	1	1	1	X	X	1	X	2	X	2	2	1
10		26	1	1	2	1	1	1	1	2	1	1	1	2	1	1
11		25	1	X	1	X	1	2	1	1	X	2	1	2	2	1
12		24	1	1	2	1	1	1	1	1	2	1	1	1	X	1
13		23	1	X	1	1	1	2	1	X	1	1	2	2	1	1
14		22	X	1	X	1	X	X	2	1	1	2	1	2	1	1			Here is example of 30 rows from C6:P35 every week will be added 1 row below 35…. I want VBA or formula 3 row below of the last data row fid delay of 1, X and 2 signs…in this example T1 has delay for 1=0, for X=1 and for 2=2…. example T2 has delay for 1=6, for X=0 and for 2=1….and so on for T3 to T14. Note:--1st formula can set find delay max 24 draw back from 0, 2nd always 3 row below of the last data row.
15		21	1	X	X	2	1	X	2	X	X	1	2	2	2	1
16		20	X	X	2	1	X	2	1	1	1	X	2	2	1	2
17		19	2	2	1	X	2	2	2	1	1	X	1	2	1	1
18		18	2	X	X	1	X	1	X	2	X	1	2	1	2	1
19		17	X	2	X	1	1	X	1	1	X	1	1	2	X	2
20		15	1	X	X	2	2	1	X	1	X	2	2	2	1	1
21		14	X	X	2	2	2	X	1	1	2	X	X	2	2	1
22		13	X	2	2	1	1	X	1	1	1	1	2	X	1	1
23		12	X	1	2	2	2	2	1	1	2	1	2	1	1	X
24		11	1	1	1	1	1	X	X	1	X	2	X	2	2	1
25		10	1	1	2	1	1	1	1	2	1	1	1	2	1	1
26		9	1	X	1	X	1	2	1	1	X	2	1	2	2	1
27		8	1	1	2	1	1	1	1	1	2	1	1	1	X	1
28		7	1	X	1	1	1	2	1	X	1	1	2	2	1	1
29		6	X	1	X	1	X	X	2	1	1	2	1	2	1	1
30		5	1	X	X	2	1	X	2	X	X	1	2	2	2	1
31		4	X	X	2	1	X	2	1	1	1	X	2	2	1	2
32		3	2	2	1	X	2	2	2	1	1	X	1	2	1	1
33		2	2	X	X	1	X	1	X	2	X	1	2	1	2	1
34		1	X	2	X	1	1	X	1	1	X	1	1	2	X	2
35		0	1	X	X	2	2	1	X	1	X	2	2	2	1	1
36
37
38			T1	T2	T3	T4	T5	T6	T7	T8	T9	T10	T11	T12	T13	T14
39	Delay	1	0	6	3	1	1	0	1	0	3	1	1	2	0	0
40	Delay	X	1	0	0	3	2	1	0	5	0	3	11	13	1	12
41	Delay	2	2	1	4	0	0	3	3	2	8	0	0	0	2	1
Delay-1X2

Regards,
Moti

 

[Anthony47]

In C39

=MAX(IF(C$1:C$38<>"", ROW(C$1:C$38),""))-MAX(IF(C$1:C$38=$B39, ROW(C$1:C$38),""))

Need to be confirmed using the combination Contr-Shift-Enter
Then copy to the bottom and to the right

Try...

 

[motilulla]

In C39

=MAX(IF(C$1:C$38<>"", ROW(C$1:C$38),""))-MAX(IF(C$1:C$38=$B39, ROW(C$1:C$38),""))

Need to be confirmed using the combination Contr-Shift-Enter
Then copy to the bottom and to the right

Try...

Hello Anthony47, yes it worked fine. I changed the range…

This
=MAX(IF(C$1:C$38<>"", ROW(C$1:C$38),""))-MAX(IF(C$1:C$38=$B39, ROW(C$1:C$38),""))

To this…
=MAX(IF(C$6:C$35<>"", ROW(C$6:C$35),""))-MAX(IF(C$6:C$35=$B39, ROW(C$6:C$35),""))

Just to get result as I wanted…

Is there any way this formula can be converting in VBA and change the range as new row is added below. For example at present range is set 6:35 when row 36 entered it change 6:36 and give the result 3 row below…and after row 37 entered it change 6:37 and give the result always 3 row below the data range….

Thank you for your help.

Kind regards,
Moti

 

[Anthony47]

I used C$1:C$38 just to allow you to "Insert" new lines as necessary: since they would be added before line 38 the formula will resize automatically and become C$1:C$39, C$1:C$40, etc

 

[motilulla]

I used C$1:C$38 just to allow you to "Insert" new lines as necessary: since they would be added before line 38 the formula will resize automatically and become C$1:C$39, C$1:C$40, etc

Yes I noticed that but it gives me +3 result where is 0 it give 3, and where is 6 it gives 9 that is why I changed.

I guess only the solution could be VBA when I add new row below 35 VBA delete the these 3 result row and find last row and adjust the formula 3 row below adding till last row data may be..

 

[Anthony47]

Are lines 36 to 38 empty? (no formulas, no spaces, no hidden characters)
This is what I get:

Cartel2
	A	B	C	D	E	F	G	H
29		6	X	1	X	1	X	X
30		5	1	X	X	2	1	X
31		4	X	X	2	1	X	2
32		3	2	2	1	X	2	2
33		2	2	X	X	1	X	1
34		1	X	2	X	1	1	X
35		0	1	X	X	2	2	1
36
37
38
39	Delay	1	0	6	3	1	1	0
40		x	1	0	0	3	2	1
41		2	2	1	4	0	0	3
42
43
44
45
Foglio2

Cell Formulas
Range	Formula
C39:H41	C39	=MAX(IF(C$1:C$38<>"", ROW(C$1:C$38),""))-MAX(IF(C$1:C$38=$B39, ROW(C$1:C$38),""))

 

[motilulla]

Are lines 36 to 38 empty? (no formulas, no spaces, no hidden characters)
This is what I get:

Cartel2
	A	B	C	D	E	F	G	H
29		6	X	1	X	1	X	X
30		5	1	X	X	2	1	X
31		4	X	X	2	1	X	2
32		3	2	2	1	X	2	2
33		2	2	X	X	1	X	1
34		1	X	2	X	1	1	X
35		0	1	X	X	2	2	1
36
37
38
39	Delay	1	0	6	3	1	1	0
40		x	1	0	0	3	2	1
41		2	2	1	4	0	0	3
42
43
44
45
Foglio2

Cell Formulas
Range	Formula
C39:H41	C39	=MAX(IF(C$1:C$38<>"", ROW(C$1:C$38),""))-MAX(IF(C$1:C$38=$B39, ROW(C$1:C$38),""))

Ok I understand because you cleared row 38 headers in this case yes your formula is perfect. I must have noticed sorry for it.

Now what if I add every week 1 row below the row 35 100 weeks 100 rows how could be managed this situation? As every time I want result 3 rows below the data.

 

[Anthony47]

If line 38 is to contain the headers then use C$1:C$37, and add as many lines you need before line 37

 

[motilulla]

If line 38 is to contain the headers then use C$1:C$37, and add as many lines you need before line 37

Yes now understood changing so result is correct. In this case when I want to add data below row 35 I need to insert line before adding data to extend the data range is it right?

This affect my next columns data are longer it lives a space between in that. Which, I did not mention it is my fault. I have to think how can I manage?

 

[Anthony47]

Using C$1:C$37 you may fit new data in rows and the formula will still return the correct answer; but if you need to add more that 2 lines of new data then you should work by first inserting the new line and then writing the new data.
If you want to make space only in columns A:P (without changing the content of subsequent columns) then you have to use the command "Insert Cells": select Ax:Px (where x is the line before the headers (before the results) and the Menu /Home /Insert (group Cells), Cells
You can obtain the result with the following macro:

Sub AddLine()
    Range("A1").End(xlDown).Offset(-2, 0).Range("A1:P1").Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
End Sub

This assumes column A is empty until the cell containing "Delay"