An excel formula to find a row/column index in arr

问题:

I have an excel table array, with row and columns indexes. Inside the array, there are some empyu cells and some cells with unique values.

Now i want to get a simplified table, when in first column i have the list of the unique values from the table array and in second column, to get the exact cells of this value as a function of concatenation between row and column index. First this is the table example:

    a   b   c   d
31              RESET_N
30  A[9]        A[11]   
29      A[6]        A[7]
28  A[3]        A[4]    

And desired table:

A[9] a30
A[3] a28

etc

thanks

回答1:

=B2&A2 should be sufficient, but you would have to 'reverse pivot' first and maybe delete blanks en route. In Excel 2007:

Add label in A1.
Alt+D, P.

Drill down on 7 (the intersect of the Grand Totals).
For Value filter for (Blanks) only and delete selected rows. Take off filter and add formula in D2. Sort and delete surplus, if desired.