How implement counting some rows

[Greg Simoni]

I need to count the number of rows a value skips until the same value appears again in another row down.

For example, on Sheet1 the value 15 appears in F4 and then appears again in C13. There are 8 rows the value 15 missed, so this is what I want count and for each of the values in J1:AS1.
The consecutive counting should be like illustrated on Sheet2. The current row where the value appear I want use a "-" (dash) and an "x" in those rows that has not been hit yet a value for the first time.

I already filled Sheet2 with the results from values 1 to 7 for better understanding the project.

Any help is appreciated.

on attached file:
Sheet1 - Partial data structure
Sheet2 - Project

 

[NARAYANK991]

Hi ,

See if this is correct.

Narayan

 

[bosco_yip]

Try, formulas were put in Sheet3......

1] In "Sheet3" D2, formula copy across to AM2 :

=IF(COUNTIF(Sheet1!$C2:$G2,D$1),0,"x")

2] In "Sheet3" D3, formula copy across to AM3 and all copy down :

=IF(COUNTIF(Sheet1!$C3:$G3,D$1),0,IF(D2="x","x",D2+1))

3] Format all cells in >> "Accounting" >>
Decimal Places : 0
Symbol : None

Regards
Bosco

 

[Greg Simoni]

Thank you guys, I can get the results I need with any of the two formulas. I'm glad to be helped here. Nice community!