Flawed design?? Generating two column display from xlsx sheet

[a61morris]

I'm not even sure HOW dumb a question this is, so I apologize in advance if this would be better on the "basics" board.

I used my limited knowledge of text functions to quickly conjugate a list of verbs. My goal is generate two columns:
Column A -- a concatenated text string telling the name of the verb, plus the pronoun and tense.
Column B -- the correct conjugation for the verb/pronoun/tense combination
The idea is to quickly generate vocabulary lists which can be exported to flashcard sites (like Quizlet) for my students to study.


The problem is that my data is in rows, and short of copying each group of six values for Col A and their values for Col B, and then pasting special with transpose, I don't know how to achieve this. I don't know if I fundamentally messed up by not figuring out a way to have the conjugations develop in columns instead of rows as my initial design.

I would appreciate any suggestions.
Thanks!cerVerbs--Quizlet.xlsx

[Olly]

Okay - in Sheet 2, range A1, enter:

=OFFSET(Sheet1!$A$1,INT((ROW()-1)/12)+2,0)&": "&OFFSET(Sheet1!$C$1,0,MOD(ROW()-1,12))

And in B1, enter:

=OFFSET(Sheet1!$C$1,INT((ROW()-1)/12)+2,MOD(ROW()-1,12))

Then copy those formulae down as far as you want, and it will generate your 2 column conjugation table.

[Olly]

Ooops - just spotted your requirement for the concatenation of verb name with pronoun and tense - formula adjusted accordingly!

[a61morris]

Thanks OllyXLS!

I use Excel all the time, but as you can see, I don't really push the limits of what it can do!
Thanks for your solution, it works great. Now my next job is to look at the function you provided and see if I can figure out how it works and employ it on my own.

I really appreciate your help.

[Olly]

Glad it helped - thanks for the feedback