row-column function???

**kuno0970** · 03-13-2005, 05:00 AM

Hi there,
I am sure this is a simple problem but I can't seem to be able to figure how it works ... so any help would be greatly appreciated. Here is the problem:

I have a dataset of 2000 in two columns (column 1 =word, column 2 =meaning). Like this

Column1 Column 2
Word 1 Meaning 1
Word 2 Meaning 2
Word 3 Meaning 3
Word 4 Meaning 4
etc ...

Now all I want to do is to reformat the dataset into the following format because I want to export the data to a flashcard program ...

Column 1
Word 1
Meaning1
Word 2
Meaning 2
Word 3
Meaning 3
Word 4
Meaning 4
etc.

In other words I am trying to "collapse" the two column dataset into a one-column dataset. Seems simple but everything I've tried doesn't seem to work....

Any ideas?

Thanks ... Tony

**olasa** · 03-13-2005, 08:30 AM

This is one option:
=OFFSET($A$2,INT((ROW(A2)-2)/2),MOD((ROW(A2)-2),2)<>0)
Put this formula in C2 and then copy it down.

... then Copy and Paste what you get as values (menu: Edit>Paste Special... Values) - to get rid of the formulas.

Ola Sandström

Note:
Offset is a way to point to a certain cell and get that cellvalue. It will shift x rows/cols down/right.
rows: INT((ROW(A2)-2)/2): if the row is 2 Int(0/2)=0
cols: MOD((ROW(A2)-2),2)<>0: mod give the fraction of an integer, in this case 0<>0--> false-->0

so in this case $A$2 (the starting point) will be picked in the offset.