I have no idea how to express this better than an example: Let's say I want to make a complete table of all the syllables in Mandarin Chinese. This is easy to conceptualize, every syllable has an Onset (a consonant or nothing) and then a Rhyme, which includes a vowel and a tone, plus an optional glide and final nasal consonant such as "n" or "ng". For a stripped down example, consonants "b" and "p" are in a table with rhymes "iān" and "iāo". what is required are the results are biān, piān, biāo, and piāo.
in actuality, there are 22 (give or take) initials and 180 (written, or 190 phonetic) rhymes.
if i type =CONCATENATE(B1,A2) and try to drag it across the row, what results is a row which concatenates the cell above it with the cell immediately to the left of it. if i ensure a cell like B3 says =...(B1,A3) and drag it down, it insists on a column which concatenates the cell 2 rows above it with the cell left of it. I have tried ArrayFormula, which doesn't do anything for the concatenate function like how I saw it work for mathematical functions (some might think me stupid for thinking this would work, but i don't have a technical understanding of these things); ranges like B1:1 and A2:A just concatenate those entire rangers together, creating entries of a truly ungodly length. A workaround, and an autofill suggestion, is to do something like =...(B1,"a") to make the row read ba, pa, etc., but this is also more work than I would assume is possible? I would like to be able to repeat similar processes in the future without manually inputting the smallest axis.
I would like for every cell to reference row 1 of its column and put it together with column A of its row. So, V19 should do (V1,A19) and F101 should do (F1,A101). I personally think this should be easy and I'm hoping it can be, but I acknowledge that my scenario is not the target demographic of daily spreadsheet activities.
Also, yes, other people have made tables of Mandarin syllables before. Conveniently ignore this fact so that I can hopefully gain a new capability; My goal is not simply "man I need a table of Pinyin, guess I have to make it myself", I really want to know how to do this.
Thank you all!