Search code examples
excelexcel-formula

In Excel, create table showing the row,column of the cells in another table

With an 8x8 table in Excel, I want to create another 8x8 table that takes the row and column of each of the first table's cells, formats those into a text string, and puts the string into the corresponding cell of the second table.

For example, my first table is in cells B3:I10. I select an 8x8 grid of empty cells for the second table. Into the first cell, I use the formula

=TEXTJOIN(",", TRUE, TEXT(ROW(B3:I10),"0"), TEXT(COLUMN(B3:I10),"0"))

I then type CTL-SHIFT-ENTER to create the array but this gives the table with each cell filled with "0,0,0,0,0,0,0,0,2,3,4,5,6,7,8,9". Obviously, not what I wanted. How can I get the row and column for each individual cell and put a formatted version of them into the second table? Do I need a macro?

Solution

  • If I understand correctly:

    =ROW(B3:I10)&","&COLUMN(B3:I10)

    enter image description here