1

Please look the example sheet. The 'sheet1' is a gradually filled table. In column "A" some kind of ID for each entity. These identifiers are sometimes repeated. In the result I just want to know the appearance number of each ID.

To solve this I created 'sheet2'. in 'sheet2' column 'A' list of unique values from 'sheet1'. Then with huge formula in cell 'sheet2!B2' I get all row numbers for each unique value from 'sheet1' separated by columns. And finally I want to get in 'sheet1' column numbers for each rownumber from 'sheet2'.

I want to use 'match' comand, but it perfectly works without 'arrayformula' like you can see in 'sheet1' column E.

=IF(ISBLANK(A2);;MATCH(B2;INDIRECT("'sheet2'!B"&C2&":Z"&C2)))

And i don't understand whats going wrong in 'sheet1' column D, when I try to add 'arayformula'

=ARRAYFORMULA(IF(ISBLANK(A2:A);;MATCH(B2:B;INDIRECT("'sheet2'!B"&C2:C&":Z"&C2:C))))

I read many posts about these commands doesn't work together like I want. there must be some another way. But my imagination is over. Please help.

Expected result:

value 1 |   1   |(first appearance of 'value 1')

value 2 |   1   |(first appearance of 'value 2')

value 1 |   2   |(second appearance of 'value 1')

value 3 |   1   |(first appearance of 'value 3')

value 4 |   1   |(first appearance of 'value 4')

value 5 |   1   |(first appearance of 'value 5')

value 3 |   2   |(second appearance of 'value 3')

value 3 |   3   |(third appearance of 'value 3')

value 4 |   2   |(second appearance of 'value 4')

value 5 |   2   |(second appearance of 'value 5')
Oleg Blonskiy
  • 21
  • 3

2 Answers2

0

In G2 I entered this formula

=ArrayFormula(iferror(SORT(ROW(A2:A);SORT(ROW(A2:A);A2:A;1);1)-MATCH(A2:A;SORT(A2:A);0)-ROW()+2))

See if that works for you?

Explanation:

JPV
  • 26,499
  • 4
  • 33
  • 48
  • 2
    It looks so simple. I am ashamed, i don't understand how does this formula work)) i'll go to figure it out. maybe you can say few words about logic?) – Oleg Blonskiy May 02 '19 at 14:48
  • Thanks a lot. I look at this sheet every day but I absolutely can not understand what is happening in step 2. Can you explain, please? – Oleg Blonskiy May 11 '19 at 13:34
  • Hi @OlegBlonskiy, Sorry for the time it took to reply, but I was away. Basically, think of step 2 as a lookup of row numbers. I added some more clarification to the spreadsheet. Hope this helps. – JPV May 14 '19 at 07:22
0

Try using this:

=ArrayFormula(if(Z2:Z="";"";COUNTIFS(Z2:Z;Z2:Z;ROW(Z2:Z);"<="&ROW(Z2:Z))))

The result >

Ref

1 A 2 A 1 B 1 C 3 A

ridwanrwg
  • 1