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

 

[motilulla]

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"

Wow Anthony47, perfect this is amazing it worked like a piece of cake.

Thank you for taking a time and solving it.

Kind Regards,
Moti

 

[Anthony47]

Thank you for the feedback