如何获取另一个数组中一个数组列号中的每个值? [英] 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')

解决方案

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?

这篇关于如何获取另一个数组中一个数组列号中的每个值?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！