How do I transpose this Excel pivot worksheet data?

Question

I have a set of data in this format:

Column1 column2 column3 22/8  23/8  24/8 25/8 26/8
A       B         C      2       3    4    5   6
D       E         F      5       6    7    7   7

I need to transpose it to this format:

A   B   C  22/8    2
A   B   C  23/8    3
A   B   C  24/4    4
A   B   C  25/8    5
A   B   C  26/8    6
D   E   F  22/8    5

Is it possible in Excel/Access without copying and paste special to transpose format?

That worked! except that the original table where i took it from is gone and I need the original table to stay. — Skyblue, Jan 16 '18 at 07:42
It seems to me that you are looking for this solution: https://stackoverflow.com/questions/47283811/combining-different-fees-columns-to-create-pivottable-income-statement/47286415#47286415 — Ralph, Jan 16 '18 at 08:01
Possible duplicate of [Combining different fees columns to create PivotTable income statement?](https://stackoverflow.com/questions/47283811/combining-different-fees-columns-to-create-pivottable-income-statement) — Erik A, Jan 16 '18 at 20:05
@ErikvonAsmuth I tried that way and transposing using the query editor however it wasnt the way i wanted it to transpose as i only wanted the dates and values to transpose and the columns to be duplicating — Skyblue, Jan 17 '18 at 05:58

score 0 · Answer 1 · answered Jan 17 '18 at 06:52

While finding, I realised that this worked for me. I was able to transpose Initial Pic to After Tranposing in Access VBA coding.

1) I created a table(NewTypes) with the 2 fields: Column and Value

2) I used this set of codes:

Public Function TransposeType()
 Dim rs As DAO.Recordset, rs1 As DAO.Recordset
 Dim i As Integer, s, fldArr()

 Set rs = CurrentDb.OpenRecordset("Query1")  'change the name to the actual name of table
 Set rs1 = CurrentDb.OpenRecordset("NewTypes")  'new table created

 If rs.EOF Or rs.BOF Then
     MsgBox "no records"
     Exit Function
 End If
 rs.MoveFirst
     For i = 0 To rs.Fields.Count - 1
         ReDim Preserve fldArr(i)
         fldArr(i) = rs.Fields(i).Name
     Next
 Dim j
 Do Until rs.EOF
     For j = 1 To UBound(fldArr)
             With rs1
                 .AddNew
                 !Consign = rs("Consign") 'Field of old table=Field of new table
                                            'field that does not need to change any format
                 !Week = rs.Fields(fldArr(j))
                 .Update

             End With
     Next
     rs.MoveNext
 Loop
 End Function

How do I transpose this Excel pivot worksheet data?

1 Answers1