CODEWITHSUNDEEP
google-sheetsmatcharray-formulasappearanceexcel-indirect
Oleg Blonskiy
Oleg Blonskiy

Reputation: 21

How to get for each value in one array column number in another array?

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')

Upvotes: 1

Views: 203

Answers (2)

ridwanrwg
ridwanrwg

Reputation: 1

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

Upvotes: 0

JPV
JPV

Reputation: 27262

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:

Upvotes: 0

Related Questions