Access - transpose some column data into row recor

Question

I would like to apply a similar function like this: "Transposing" some columns with ID fields into rows while copying the other data

but in these conditions:

Table:

A | B | 1 | 3 | 5 | 2|

C | D | 8 | 5 | 4 | 7|

into a table like this:

A | B | 1 |

A | B | 3 |

A | B | 5 |

A | B | 2 |

C | D | 8 |

C | D | 5 |

C | D | 4 |

C | D | 7 |

Is this possible by modifying the quoted function? Or a solution in Access is easier?

Answer 1

With data in columns A through F, pick any cell, say H3, and enter:

=INDEX(A:A,ROUNDUP(ROWS($1:1)/4,0))

and copy down. In I3 enter:

=INDEX(B:B,ROUNDUP(ROWS($1:1)/4,0))

and copy down. In J3 enter:

=INDEX($C$1:$F$100,ROUNDUP(ROWS($1:1)/4,0),IF(MOD(ROWS($1:1),4)=0,4,MOD(ROWS($1:1),4)))

and copy down:

Answer 2

Alternatively, consider an SQL union query which can be run in either Excel or Access, both using the Jet/ACE engine (Windows .dll files):

SELECT Col1, Col2, Col3
FROM TableOrSheet$Name
UNION ALL
SELECT Col1, Col2, Col4
FROM TableOrSheet$Name
UNION ALL
SELECT Col1, Col2, Col5
FROM TableOrSheet$Name
UNION ALL
SELECT Col1, Col2, Col6
FROM TableOrSheet$Name