Oct 21 2021 12:48 PM
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
Oct 21 2021 01:15 PM
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.
Oct 21 2021 04:10 PM