TEXTJOIN, with a Value that is that of a cell which is at the intersection of a Column and Row

New Contributor

Hello,

In the attached spreadsheet, I created the formulas in cells B27, B28, B29 rather clunkily.  But they achieve what I am after:  A delimited (by comma) text string with value of a Row 3 cell, followed by its corresponding value of a Column C cell, followed by the value of the cell at the intersection of the Row and Column.

I am trying to figure out a Copy/Paste method of replicating these Formulas such that the Row and Column cells-- and thereby, their intersecting cell-- automatically advance one.  For example:  The next entry of this Formula, in Cell B30, should have the text sting 13,0,0.8; then continue to the right.  And after all of Row 4 has been completed, move on down to Row 5; example: the first entry for that would be [D3],[C5],[D5], or 0.9,-3.6,0.9.

This is for a Mesh, in CAD.  The data collected and provided is in the orientation depicted in my table.  But for entry into my CAD program, I need comma-delimited text-strings, in the order of X,Y,Z.

I know that might all sound confusing.  If so, I apologize; I tried hard to give it clarity.

Thanks for any help.  -Alvin

2 Replies

@Alvin_Gregorio 

Use this:

 

=CONCATENATE(TEXT(INDEX($D$3:$R$3,MOD(ROW(B27)-ROW($B$27),15)+1),"0.0"),",",TEXT(INDEX($C$4:$C$22,QUOTIENT(ROW(B27)-ROW($B$27),15)+1),"0.0"),",",TEXT(INDEX($D$4:$R$22,QUOTIENT(ROW(B27)-ROW($B$27),15)+1,MOD(ROW(B27)-ROW($B$27),15)+1),"0.0"))

 

See the attached version.

Thank You Hans. Well written.
Ultimately, it inspired me, and I went a much a simpler way.
Much appreciated, Alvin