Transposing data in a specific format.

J

JohnBecks

New Member
Joined
Aug 9, 2021
Messages
13
Office Version
  1. 2019
Platform
  1. Windows
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 AA-1A-2A-3A-4
Customer A01/08/202102/08/202103/08/202104/08/2021
Customer A100125150175
Customer BB-1B-2B-3B-4
Customer B04/08/202105/08/202106/08/202107/08/2021
Customer B200225225250
Customer BB-5---
Customer B08/08/2021---
Customer B275---
Customer CC-1C-2C-3-
Customer C06/08/202107/08/202108/08/2021-
Customer C300325350-
Customer DD-1---
Customer D09/08/2021---
Customer D375---

Appreciate all the help
 
Sort by date Sort by votes
Hi Peter,

Attached as requested.

Book1
ABCDEFGHIJKLMNOPQRST
1
2Customer CodeNameTermsLimitBalanceOver LimitDocument_DateValueDays Over529008491Jane Doe75150,000.00140,092.74 - 11/02/202111/02/202111/04/202111/04/2021
3529008491Jane Doe75150,000.00140,092.74 - 02/11/2021296.41+1529008492Jane Doe75150,000.00140,092.74 - 296.411,224.489.80928.55
4529008491Jane Doe75150,000.00140,092.74 - 02/11/20211,224.48+2529008493Jane Doe75150,000.00140,092.74 - +1+2-13-13
5529008491Jane Doe75150,000.00140,092.74 - 04/11/20219.80-13529008494Jane Doe75150,000.00140,092.74 - 11/04/202131/10/202111/04/202111/03/2021
6529008491Jane Doe75150,000.00140,092.74 - 04/11/2021928.55-13529008495Jane Doe75150,000.00140,092.74 - 19.44940.875.06455.84
7529008491Jane Doe75150,000.00140,092.74 - 04/11/202119.44-13529008496Jane Doe75150,000.00140,092.74 - -13+3-13-12
8529008491Jane Doe75150,000.00140,092.74 - 31/10/2021940.87+3529008497Jane Doe75150,000.00140,092.74 - 11/02/202111/02/2021
9529008491Jane Doe75150,000.00140,092.74 - 04/11/20215.06-13529008498Jane Doe75150,000.00140,092.74 - 28.129.57
10529008491Jane Doe75150,000.00140,092.74 - 03/11/2021455.84-12529008499Jane Doe75150,000.00140,092.74 - +4+5
11529008491Jane Doe75150,000.00140,092.74 - 02/11/202128.12+4
12529008491Jane Doe75150,000.00140,092.74 - 02/11/20219.57+5
Sheet1
 
Upvote 0

Excel Facts

Formula for Yesterday
Name Manager, New Name. Yesterday =TODAY()-1. OK. Then, use =YESTERDAY in any cell. Tomorrow could be =TODAY()+1.
JohnBecks said:
Attached as requested.
Click to expand...
There are some things that I don't understand about that
  1. The customer codes in column K differ from those in column A
  2. The dates also do not correspond. Column G are all dates in October-November, those in columns Q:T are all over the place.
 
Upvote 0
Hi Peter,

Apologies for that.

Attached is the fixed version.

Book3
ABCDEFGHIJKLMNOPQRST
2Customer CodeNameTermsLimitBalanceOver LimitDocument_DateValueDays Over529008491Jane Doe75150,000.00140,092.74-01/11/202102/11/202103/11/202104/11/2021
3529008491Jane Doe75150,000.00140,092.74-01/11/2021100.001529008491Jane Doe75150,000.00140,092.74-100.00200.00300.00400.00
4529008491Jane Doe75150,000.00140,092.74-02/11/2021200.002529008491Jane Doe75150,000.00140,092.74-12-13-13
5529008491Jane Doe75150,000.00140,092.74-03/11/2021300.00-13529008491Jane Doe75150,000.00140,092.74-05/11/202106/11/202107/11/202108/11/2021
6529008491Jane Doe75150,000.00140,092.74-04/11/2021400.00-13529008491Jane Doe75150,000.00140,092.74-500600700800
7529008491Jane Doe75150,000.00140,092.74-05/11/2021500.00-13529008491Jane Doe75150,000.00140,092.74--133-13-12
8529008491Jane Doe75150,000.00140,092.74-06/11/2021600.003529008491Jane Doe75150,000.00140,092.74-09/11/202110/11/2021
9529008491Jane Doe75150,000.00140,092.74-07/11/2021700.00-13529008491Jane Doe75150,000.00140,092.74-9001000
10529008491Jane Doe75150,000.00140,092.74-08/11/2021800.00-12529008491Jane Doe75150,000.00140,092.74-45
11529008491Jane Doe75150,000.00140,092.74-09/11/2021900.004
12529008491Jane Doe75150,000.00140,092.74-10/11/20211,000.005
Sheet1
 
Upvote 0
Sample data with only one Customer code, one Name, one Terms, one Limit, one Balance and one Over Limit may well lead to this being inadequate with a 'real' set of data, but give it a try.

VBA Code: 
Sub Rearrange_v3()
  Dim a As Variant, b As Variant
  Dim i As Long, c As Long, k As Long, x As Long, y As Long
  
  Const MaxCols As Long = 4
  
  a = Range("A2", Range("I" & Rows.Count).End(xlUp)).Value
  ReDim b(1 To Rows.Count, 1 To 6 + MaxCols)
  k = -2
  For i = 2 To UBound(a)
    If a(i, 1) <> a(i - 1, 1) Or c = MaxCols Then
      c = 0
      k = k + 3
      For x = 1 To 6
        For y = 0 To 2
          b(k + y, x) = a(i, x)
        Next y
      Next x
    End If
    b(k, 7 + c) = a(i, 7): b(k + 1, 7 + c) = a(i, 8): b(k + 2, 7 + c) = a(i, 9)
    c = c + 1
  Next i
  Range("K1").Resize(k + 2, UBound(b, 2)).Value = b
End Sub
 
Upvote 0
Hi Peter,

Yeah, you're right. The code did not work on a real set of data. I have added a couple more sample customers for the final sheet.

Book5
ABCDEFGHIJKLMNOPQRST
1CodeNameTermsLimitBalanceOver LimitInvoice DateValueDays Over
2529008491Jane75150,000.00100,000.00-01/01/2021100.001529008491Jane75150,000.00100,000.00-01/01/202102/01/202103/01/202104/01/2021
3529008491Jane75150,000.00100,000.00-02/01/2021150.002529008491Jane75150,000.00100,000.00-100150200250
4529008491Jane75150,000.00100,000.00-03/01/2021200.00-13529008491Jane75150,000.00100,000.00-12-13-13
5529008491Jane75150,000.00100,000.00-04/01/2021250.00-13529008491Jane75150,000.00100,000.00-05/01/202106/01/202107/01/202108/01/2021
6529008491Jane75150,000.00100,000.00-05/01/2021300.00-13529008491Jane75150,000.00100,000.00-300350400450
7529008491Jane75150,000.00100,000.00-06/01/2021350.003529008491Jane75150,000.00100,000.00--133-13-12
8529008491Jane75150,000.00100,000.00-07/01/2021400.00-13529008491Jane75150,000.00100,000.00-09/01/202110/01/2021
9529008491Jane75150,000.00100,000.00-08/01/2021450.00-12529008491Jane75150,000.00100,000.00-500550
10529008491Jane75150,000.00100,000.00-09/01/2021500.004529008491Jane75150,000.00100,000.00-45
11529008491Jane75150,000.00100,000.00-10/01/2021550.005
12529009000Mark50200,000.00250,000.0050,00011/01/20211,000.00-1529009000Mark50200,000.00250,000.0050,00011/01/202112/01/202113/01/2021
13529009000Mark50200,000.00250,000.0050,00012/01/20212,000.00-21,000.002,000.003,000.00
14529009000Mark50200,000.00250,000.0050,00013/01/20213,000.00-3-1-2-3
15529009100John3070,000.0010,000.00-14/01/20215,000.004
16529009100John3070,000.0010,000.00-15/01/20216,000.007529009100John3070,000.0010,000.00-14/01/202115/01/202116/01/202117/01/2021
17529009100John3070,000.0010,000.00-16/01/20217,000.0095,000.006,000.007,000.008,000.00
18529009100John3070,000.0010,000.00-17/01/20218,000.00-1479-1
19529009100John3070,000.0010,000.00-18/01/20219,000.00418/01/2021
209,000.00
214
Sheet4
 
Upvote 0
Once again I do not understand. Why does Jane get all her data repeated in all these green rows below her first row but Mark and John do not get their data repeated in the blue and yellow rows?
Which is correct?

Other than that, didn't my code produce pretty much the correct results apart from a blank row between each section?

JohnBecks.xlsm
KLMNOPQRST
1
2529008491Jane75150,000.00100,000.00-1/01/20212/01/20213/01/20214/01/2021
3529008491Jane75150,000.00100,000.00-100150200250
4529008491Jane75150,000.00100,000.00-12-13-13
5529008491Jane75150,000.00100,000.00-5/01/20216/01/20217/01/20218/01/2021
6529008491Jane75150,000.00100,000.00-300350400450
7529008491Jane75150,000.00100,000.00--133-13-12
8529008491Jane75150,000.00100,000.00-9/01/202110/01/2021
9529008491Jane75150,000.00100,000.00-500550
10529008491Jane75150,000.00100,000.00-45
11
12529009000Mark50200,000.00250,000.005000011/01/202112/01/202113/01/2021
13100020003000
14-1-2-3
15
16529009100John3070,000.0010,000.00-14/01/202115/01/202116/01/202117/01/2021
175000600070008000
18479-1
1918/01/2021
209000
214
Sheet4
 
Upvote 0
The data should be repeated. Apologies for missing that.

Visits.ods
ABCDEFGHIJKLMNOPQRST
1CodeNameTermsLimitBalanceOver LimitInvoice DateValueDays Over
2529008491Jane75150,000.00100,000.00-01/01/20211001529008491Jane75150,000.00100,000.00-01/01/202102/01/202103/01/202104/01/2021
3529008491Jane75150,000.00100,000.00-02/01/20211502529008491Jane75150,000.00100,000.00-100150200250
4529008491Jane75150,000.00100,000.00-03/01/2021200-13529008491Jane75150,000.00100,000.00-12-13-13
5529008491Jane75150,000.00100,000.00-04/01/2021250-13529008491Jane75150,000.00100,000.00-05/01/202106/01/202107/01/202108/01/2021
6529008491Jane75150,000.00100,000.00-05/01/2021300-13529008491Jane75150,000.00100,000.00-300350400450
7529008491Jane75150,000.00100,000.00-06/01/20213503529008491Jane75150,000.00100,000.00--133-13-12
8529008491Jane75150,000.00100,000.00-07/01/2021400-13529008491Jane75150,000.00100,000.00-09/01/202110/01/2021
9529008491Jane75150,000.00100,000.00-08/01/2021450-12529008491Jane75150,000.00100,000.00-500550
10529008491Jane75150,000.00100,000.00-09/01/20215004529008491Jane75150,000.00100,000.00-45
11529008491Jane75150,000.00100,000.00-10/01/20215505
12529009000Mark50200,000.00250,000.0050,00011/01/20211,000.00-1529009000Mark50200,000.00250,000.0050,00011/01/202112/01/202113/01/2021
13529009000Mark50200,000.00250,000.0050,00012/01/20212,000.00-2529009000Mark50200,000.00250,000.0050,0001,000.002,000.003,000.00
14529009000Mark50200,000.00250,000.0050,00013/01/20213,000.00-3529009000Mark50200,000.00250,000.0050,000-1-2-3
15529009100John3070,000.0010,000.00-14/01/20215,000.004
16529009100John3070,000.0010,000.00-15/01/20216,000.007529009100John3070,000.0010,000.00-14/01/202115/01/202116/01/202117/01/2021
17529009100John3070,000.0010,000.00-16/01/20217,000.009529009100John3070,000.0010,000.00-5,000.006,000.007,000.008,000.00
18529009100John3070,000.0010,000.00-17/01/20218,000.00-1529009100John3070,000.0010,000.00-479-1
19529009100John3070,000.0010,000.00-18/01/20219,000.004529009100John3070,000.0010,000.00-18/01/2021
20529009100John3070,000.0010,000.00-9,000.00
21529009100John3070,000.0010,000.00-4
Sheet7
 
Upvote 0
So it seems the existing code does everything correctly except for the blank rows between the change of codes & headings are now in row 1 instead of row 2.
Try this slight variation.

VBA Code: 
Sub Rearrange_v4()
  Dim a As Variant, b As Variant
  Dim i As Long, c As Long, k As Long, x As Long, y As Long
  
  Const MaxCols As Long = 4
  
  a = Range("A1", Range("I" & Rows.Count).End(xlUp)).Value
  ReDim b(1 To Rows.Count, 1 To 6 + MaxCols)
  k = -2
  For i = 2 To UBound(a)
    If a(i, 1) <> a(i - 1, 1) Or c = MaxCols Then
      c = 0
      k = k + IIf(a(i, 1) <> a(i - 1, 1), 4, 3)
      For x = 1 To 6
        For y = 0 To 2
          b(k + y, x) = a(i, x)
        Next y
      Next x
    End If
    b(k, 7 + c) = a(i, 7): b(k + 1, 7 + c) = a(i, 8): b(k + 2, 7 + c) = a(i, 9)
    c = c + 1
  Next i
  Range("K1").Resize(k + 2, UBound(b, 2)).Value = b
End Sub
 
Upvote 0
Thanks Peter,

Work perfectly!

One thing I noticed is that the format changes after I run the Macro. Is there a way to fix that?

I have highlighted the changes below.

Centrally Controlled Credit Customers as of 05-01-2021.xls
ABCDEFGHIJKLMNOPQRST
1 CodeNameTermsLimitBalanceOver LimitDocumentDateDays
2529008491Jane Doe75150,000.00200,000.0050,000.00INSOP/0105702002/11/21+11529008491Jane Doe7515000020000050000INSOP/01057020INSOP/01057024INSOP/01060048INSOP/01060052
3529008491Jane Doe75150,000.00200,000.0050,000.00INSOP/0105702402/11/21-11529008491Jane Doe751500002000005000002/11/202102/11/202104/11/202104/11/2021
4529008491Jane Doe75150,000.00200,000.0050,000.00INSOP/0106004804/11/21+13529008491Jane Doe751500002000005000011-1113-13
5529008491Jane Doe75150,000.00200,000.0050,000.00INSOP/0106005204/11/21-13
Invoices
Cell Formulas
RangeFormula
F2:F5F2=E2-D2
 
Upvote 0
JohnBecks said:
One thing I noticed is that the format changes after I run the Macro. Is there a way to fix that?
Click to expand...
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?
 
Upvote 0
You must log in or register to reply here.

Similar threads

M
  • Solved
Formula Help.
Replies
3
Views
98
Fluff
Fluff
C
  • Solved
Extract Dates from 2 columns
Replies
2
Views
330
Peter_SSs
Peter_SSs
M
  • Solved
Having issues with a IF(MAX(INDEX formula returning Yes or No
Replies
3
Views
98
RoryA
RoryA
T
  • Solved
Date of Max value
Replies
5
Views
197
Fluff
Fluff
H
  • Solved
Transpose data.
Replies
4
Views
124
harzer
H

Forum statistics

Threads
1,215,335
Messages
6,124,327
Members
449,155
Latest member
ravioli44

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top