CODEWITHSUNDEEP
excelvbams-access
Arnold
Arnold

Reputation: 3

Access - transpose some column data into row records

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?

Upvotes: 0

Views: 3077

Answers (2)

Parfait
Parfait

Reputation: 107707

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

Upvotes: 0

Gary's Student
Gary's Student

Reputation: 96771

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:

enter image description here

Upvotes: 1

Related Questions