A function for calculate the cells takes to appear each element

[caldera55]

Hello,
I want to obtain from some elements the number of cells it takes to appear:
We have for example A,B,C, and D,
and they appear in the next order:

1 A
2 C
3 D
4 A
5 D
6 B
7 C
8 A
9 A

What I want to know is how much last in appear each element.

1 A 1
2 C 2
3 D 3
4 A 3
5 D 2
6 B 6
7 C 5
8 A 4
9 A 1

For example, the first “A” last one in appear, but the next element “C” last two in appear. In the forth line again cames the “A”, then are three cells. The “C” was in the cell2, and cames again in the seventh cell, then it takes five cells. In the cells eight and nine are two “A”, then in the cell nine takes one cell in appear again.


Thank you very much,
regards

[davesexcel]

Could you attach an example workbook showing the original data and what your desired result should be?

[shg]

Maybe like this:

Please Login or Register  to view this content.

The row above the data is necessary for the formulas.

The formula in B2 and copied down is =IF(COUNTIF(A$1:A1, A2) = 0, ROW() - 1, ROW() - MATCH(2, 1 / (A$1:A1=A2) ) ) - ROW($A$2) + 2


This is an array formula, meaning it MUST be confirmed with Ctrl+Shift+Enter, not just Enter. (With the cursor in the formula bar, hold down Ctrl and Shift, then press Enter.) You'll know you did it correctly if curly braces appear around the formula in the Formula Bar; you cannot type in the braces directly.

[Domenic]

Assuming that A1:A9 contains the data, try...

B1, confirmed with CONTROL+SHIFT+ENTER, and copied down:

=IF(COUNTIF($A$1:A1,A1)>=2,SUM(LARGE(IF($A$1:A1=A1,ROW($A$1:A1)),{1,2})*{1,-1}),ROWS(B$1:B1))

Hope this helps!

[mikerickson]

Assuming that your data is in column A, put 1 in B1 and put this array formula in B2 and drag down .

=ROW()-MAX(--($B$1:B1=B2)*ROW($B$1:B1))

Array formulas are confirmed with Ctrl-Shift-Enter (Cmd+Return for Mac)

[caldera55]

Thank you very much, altough I can't make it works. I'm trying doing what you told me, but I can't make works an array formula.
I put a new thread trying to understand what is the problem:

http://www.excelforum.com/showthread.php?t=644566

Regards

[Domenic]

Have a look at the attached sample. I assumed that A2:A10 contains the data,
I entered the following formula in B2, I confirmed the formula with CONTROL+SHIFT+ENTER, not just ENTER, and then I copied/dragged the formula down the column...

=IF(COUNTIF($A$2:A2,A2)>=2,ROW()-LARGE(IF($A$2:A2=A2,ROW($A$2:A2)),2),ROWS(B$2:B2))

Hope this helps!

[caldera55]

Thank you Domenic,
With the sample you give me It works.
There are two errors I was doing:
1) in my program the separate of the formulas is an ";" instead of a ",". That was one of the reasons that gives error for the formulas.
2) The translation. If I don't translate to spanish, or I translate it bad, it doesn't work. In your formula, Domenic:

=IF(COUNTIF($A$2:A2,A2)>=2,ROW()-LARGE(IF($A$2:A2=A2,ROW($A$2:A2)),2),ROWS(B$2:B2))

I did translate for this:

=SI(CONTAR.SI($A$2:A2;A2)>=2;FILA()-LARGO(SI($A$2:A2=A2;FILA($A$2:A2));2);FILAS(B$2:B2))

but when I open the worksheet you send, it appears like this:

=SI(CONTAR.SI($A$2:A2;A2)>=2;FILA()-K.ESIMO.MAYOR(SI($A$2:A2=A2;FILA($A$2:A2));2);FILAS(B$2:B2))

I thought that "Large" has to be translate as "Largo", but, I don't know why, it has to be translate as "K.ESIMO.MAYOR"...

Thank you very much

[NBVC]

For your information, LARGO is for LEN in Excel english (ie. Length).

according to this site: http://cherbe.free.fr/traduc_fonctions_xl97.html

[Domenic]

Thank you Domenic,

You're welcome!

I thought that "Large" has to be translate as "Largo", but, I don't know why, it has to be translate as "K.ESIMO.MAYOR"...

I'm not familiar with non-English versions of Excel, but it looks like the correct translation is K.ESIMO.MAYOR, at least as far as Excel is concerned.

[caldera55]

Yes, finally it is K.ESIMO.MAYOR, Dominic.

Very useful for me that link of translate functions, NBVC.

Thank you very much to both.