Transposing data in a specific format.

[JohnBecks]

Hi,

I have an excel which has an unlimited number of data that needs to be transposed into a specific format. The number of columns is fixed to four but the number of rows is constantly increasing. I would appreciate the help.

Example : ( Data )

Customer Invoice Date Amount
Customer A A-1 01/08/2021 100
Customer A A-2 02/08/2021 125
Customer A A-3 03/08/2021 150
Customer A A-4 04/08/2021 175
Customer B B-1 04/08/2021 200
Customer B B-2 05/08/2021 225
Customer B B-3 06/08/2021 225
Customer B B-4 07/08/2021 250
Customer B B-5 08/08/2021 275
Customer C C-1 06/08/2021 300
Customer C C-2 07/08/2021 325
Customer C C-3 08/08/2021 350
Customer D D-1 09/08/2021 375

Into something like this - So for every

Customer A	A-1	A-2	A-3	A-4
Customer A	01/08/2021	02/08/2021	03/08/2021	04/08/2021
Customer A	100	125	150	175
Customer B	B-1	B-2	B-3	B-4
Customer B	04/08/2021	05/08/2021	06/08/2021	07/08/2021
Customer B	200	225	225	250
Customer B	B-5	-	-	-
Customer B	08/08/2021	-	-	-
Customer B	275	-	-	-
Customer C	C-1	C-2	C-3	-
Customer C	06/08/2021	07/08/2021	08/08/2021	-
Customer C	300	325	350	-
Customer D	D-1	-	-	-
Customer D	09/08/2021	-	-	-
Customer D	375	-	-	-

Appreciate all the help

 

[JohnBecks]

The method I chose (because it is very fast*) transfers values only, not formatting, as you have noticed. So the target cells would be formatted as 'General' unless you had formatted them differently.

To get the formatting like the original cells, the code would need to either ..

1. Copy and paste the original data bit-by-bit (relatively slow*), or
2. Enter the data as values per the code above and individually format the various sections (faster but fiddly*). I mentioned fiddly since columns Q:T would not have the same formatting in every row.

* I have referenced speed of the code a number of times. This would only be relevant if your actual data might be very big, say thousands or tens of thousands of rows.

So how big is your actual data?
Do you see a reason to choose either method 1. or 2. over the other & why?

Hi Peter,


The sheet usually contains thousands of lines, and the data must be presented in a particular format - "according to company standards." So, in that case, method one should be adequate, even if slower.


However, if it is too complicated, kindly ignore it as you have significantly assisted so much already.

 

[Peter_SSs]

OK, see how this goes.

Sub Rearrange_v5()
  Dim c As Long, r As Long, nr As Long
  
  Const MaxCols As Long = 4
  
  Application.ScreenUpdating = False
  nr = -2
  For r = 2 To Range("A" & Rows.Count).End(xlUp).Row
    If Cells(r, 1).Value = Cells(r - 1, 1).Value Then
      c = c + 1
      If c = MaxCols Then
        c = 0
        nr = nr + 3
      End If
    Else
      nr = nr + 4
      c = 0
    End If
    If c = 0 Then Cells(r, 1).Resize(, 6).Copy Destination:=Cells(nr, 11).Resize(3)
    Cells(r, 7).Resize(, 3).Copy
    Cells(nr, 17 + c).PasteSpecial Paste:=xlPasteAll, Transpose:=True
  Next r
  Application.CutCopyMode = False
  Application.ScreenUpdating = True
End Sub