Recording time between .....

[alkarkar]

This will be difficult for me to explain.
In cell A1 I get from a formula a name eg GEORGE .
In cell A2 I get from external source the current cpu time eg 10:12:34
Data updates every 0.5-1.5 sec (aprox)(not standard time)
At every update the conditions at cell A1 are calculated again and I get the names in that cell.
Eg.
NAME-------TIME
Bill--------10:12:34
Bill--------10:12:35
Bill--------10:12:37
Bill--------10:12:38
George---10:12:39
George---10:12:42
George---10:12:43
George---10:12:45
Tom-------10:12:48
Tom-------10:12:48
Tom-------10:12:49
George---10:12:50
George---10:12:52

In reality the name and the time are overwritten in cells A1,A2 and are not like a table as in the above example.

I want to record :
The first time a name is shown I need to get 0 value ( a new sequel of that name).
If the name is shown for the second time in a row I need the difference between these times.
If the name is shown for more than three times in a row I need the difference between the last appearance and the first.(the total time the name is shown)

The example:
NAME------TIME---------DIFF---------I NEED
Bill--------10:12:34-------0--------------0----------(first apear)
Bill--------10:12:35-------1--------------1
Bill--------10:12:37-------2--------------3
Bill--------10:12:38-------1--------------4---(10:12:38 minus 10:12:34)
George---10:12:39-------0--------------0-----------(first apear)
George---10:12:42-------3--------------3
George---10:12:43-------1--------------4
George---10:12:45-------2--------------6
Tom-------10:12:48-------0--------------0-----------(first apear)
Tom-------10:12:48-------0--------------0
Tom-------10:12:49-------1--------------1
George---10:12:50--------0--------------0-----------(first apear)
George---10:12:52--------2--------------2
I need the data from the last column lets say at Cell C1 (first the value 0,then 1,3,4,0,3,4,6,0,0,1,0,2). Every time a value is calculated at cell C1 I can record it using a macro (copy-paste it or is there is something better?).
It would be better if the diff between two times is given as a number and not time.
Example --- 2 and not 00:00:02.
The ideal would be not to use the time from cell A2 but every time this cell updates (from the external appl) to get the current cpu time with accuracy of milliseconds and get the time differences also with that accuracy ( this later ).
If I wasn’t clear please ask.
Thanks, Alexandros

 

[venkat1926]

I have slightly changed your data the firt row bill is the first appearance column C and D must be zero Now I opened new columns H and I and in these columns I have filled the formulas. I have introduced a header row.
see the fomula in H2 and I2. These formulas were copied down.
column I gives in seconds But remember the data should be sorted according to name and then time (column A and then column B) then only these formulas will give correct results.

check thorougly the results in columns H and I. after satisfying you can delete columns E,F and G

I noticed GEORGE IS REPEATED but are in different places. pl see my note in bold above

thes preadsheet with fomulas is given below.

Book1
	A	B	C	D	E	F	G	H	I	J	K
1	H1	H2	H3	H4	H5	H6		h41	INSECONDS
2	Bill	10:12:35	0	0	FIRSTAPPEAR			0		CHANGEDC1,D1,E1
3	Bill	10:12:37	2	3				2
4	Bill	10:12:38	1	4	(10:12:38	minus	10:12:34)	3	3
5	George	10:12:39	0	0	(first	apear)		0
6	George	10:12:42	3	3				3
7	George	10:12:43	1	4				4
8	George	10:12:45	2	6				6	6
9	Tom	10:12:48	0	0	(first	apear)		0
10	Tom	10:12:48	0	0				0
11	Tom	10:12:49	1	1				1	1
12	George	10:12:50	0	0	(first	apear)		0
13	George	10:12:52	2	2				2	2
Sheet3

 

[venkat1926]

I have slightly changed your data the firt row bill is the first appearance column C and D must be zero Now I opened new columns H and I and in these columns I have filled the formulas. I have introduced a header row.
see the fomula in H2 and I2. These formulas were copied down.
column I gives in seconds But remember the data should be sorted according to name and then time (column A and then column B) then only these formulas will give correct results.

check thorougly the results in columns H and I. after satisfying you can delete columns E,F and G

I noticed GEORGE IS REPEATED but are in different places. pl see my note in bold above. one way is to sort as I told earlier according to name and then time. But if you want to consder the appearance of george at row no. 12 (time 10 12 50) to be consdered as first apperance call him george1. that is what I have done.

thes preadsheet with fomulas is given below

Book1.xls
	A	B	C	D	E	F	G	H	I	J	K
1	H1	H2	H3	H4	H5	H6		h41	INSECONDS
2	Bill	10:12:35	0	0	FIRSTAPPEAR			0		CHANGEDC1,D1,E1
3	Bill	10:12:37	2	3				2
4	Bill	10:12:38	1	4	(10:12:38	minus	10:12:34)	3	3
5	George	10:12:39	0	0	(first	apear)		0
6	George	10:12:42	3	3				3
7	George	10:12:43	1	4				4
8	George	10:12:45	2	6				6	6
9	Tom	10:12:48	0	0	(first	apear)		0
10	Tom	10:12:48	0	0				0
11	Tom	10:12:49	1	1				1	1
12	George1	10:12:50	0	0	(first	apear)		0
13	George1	10:12:52	2	2				2	2	seenamechange
14	ifnameisnotchangedcolumnIwillgive13(i.e.101252-101239=13
Sheet3

 

[alkarkar]

Thanks for the response, it is not what i want because:
Data updates the values in cells A1,A2. No other cell is used (there is somewhere in my first post).
So available data is not in the form of a table. New data overwrites the past data in Cell A1,A2.