Need Formula Help

[CARBOB]

Need a formula for columns F:H using the values in columns C:E, that will return the number of rows that have occured since each value appeared.

example for 7 1 1
7 appears 2 rows down; the first 1 appears 3 rows down; the second 1 appears 4 rows down.

4 9 9
4 appears 2 rows down; the first 9 appears 1 row down; second 9 appears 3 rows down.

Excel Workbook
	B	C	D	E	F	G	H
1	03/02/11	7	1	1	2	3	4
2	03/01/11	4	9	9	2	1	3
3	02/28/11	9	7	0
4	02/27/11	3	1	4
5	02/26/11	9	5	1
6	02/25/11	5	4	2
7	02/24/11	6	8	1
Sheet1

Excel 2007

 

[Matty]

Maybe:

=IFERROR(SMALL(IF($C2:$E$7=C1,ROW(C2:C$7)-ROW(C2)+1),COUNTIF($C1:C1,C1)),0)

In F2, committed with CTRL+SHIFT+ENTER and copied down and across.

Matty

 

[CARBOB]

Thank you very much!!

 

[CARBOB]

Having a conflict between the formulas in F:H and K:M. The formula in F return a T, if the value in K is one of the last 5 unique values. Tried multiplying the values in K:m by 1, still get wrong result. The result in F should be a T. The last 5 unique values are 1,3,4,5,12.

Excel Workbook
	B	C	D	E	F	G	H	I	J	K	L	M
10	5	9	7	597	F	F	F	FFF	1	5	2	2
11	6	4	3	643	F	F	F	FFF	1	5	1	2
12	4	9	7	497	F	F	F	FFF	1	12	2	1
13	2	3	7	237	F	F	F	FFF	1	1	1	8
14	2	9	3	293	F	F	F	FFF	1	1	3	1
15	3	2	5	325	F	F	F	FFF	6	3	3	4
16	6	6	8	668	F	F	F	FTF	2	3	7	4
17	1	9	9	199	F	F	F	FFT	6	3	1	2
18	9	3	2	932	F	F	F	FTF	1	1	4	2
19	5	6	9	569	F	F	F	FTF	5	4	4	5
SKIPSUMS

Excel 2007

Cell Formulas
Range	Formula
E10	=B10&C10&D10
E11	=B11&C11&D11
E12	=B12&C12&D12
E13	=B13&C13&D13
E14	=B14&C14&D14
E15	=B15&C15&D15
E16	=B16&C16&D16
E17	=B17&C17&D17
E18	=B18&C18&D18
E19	=B19&C19&D19
I10	=F10&G10&H10
I11	=F11&G11&H11
I12	=F12&G12&H12
I13	=F13&G13&H13
I14	=F14&G14&H14
I15	=F15&G15&H15
I16	=F16&G16&H16
I17	=F17&G17&H17
I18	=F18&G18&H18
I19	=F19&G19&H19

#VALUE!

Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}.

 

[Matty]

Hi,

Could you elaborate more as to what you're trying to do?

It's not very clear from your most recent post.

Matty

 

[CARBOB]

Trying to use the formula in column F, using the values in column K to return a T, if the value in K10 is one of the last 5 unique digits in column K. The last 5 unique digits in column K are 1,3,4,5,12. Don't know if the formula in column F can be used with a 2-digit value.

 

[CARBOB]

Trying to use the formula in column F, using the values in column K to return a T, if the value in K10 is one of the last 5 unique digits in column K. The last 5 unique digits in column K are 1,3,4,5,12. Don't know if the formula in column F can be used with a 2-digit value.

Have no idea what I was doing wrong, but it works now, thanks for replying.