VBA or formula to transpose/re-arranging data in columns

rozek

New Member
Joined
Aug 11, 2021
Messages
29
Office Version
  1. 365
Platform
  1. Windows
Hello. Appreciate any help on this transpose/re-arranging of columns. What I wanted to achieved is straight forward but seems difficult to do.

I wanted to re arrange this dataset below:
Book1
ABCD
1SALARY
2DATENAME1NAME2NAME3
31/1/2021101100
42/1/2021202200
53/1/2021303300
64/1/2021404400
75/1/2021505500
86/1/2021606600
97/1/2021707700
108/1/2021808800
119/1/2021909900
1210/1/2021100101000
Sheet1 (2)


To be in this arrangement
Book1
ABC
1NAMEDATESALARY
2NAME11/1/202110
3NAME12/1/202120
4NAME13/1/202130
5NAME14/1/202140
6NAME15/1/202150
7NAME16/1/202160
8NAME17/1/202170
9NAME18/1/202180
10NAME19/1/202190
11NAME110/1/2021100
12NAME21/1/20211
13NAME22/1/20212
14NAME23/1/20213
15NAME24/1/20214
16NAME25/1/20215
17NAME26/1/20216
18NAME27/1/20217
19NAME28/1/20218
20NAME29/1/20219
21NAME210/1/202110
22NAME31/1/2021100
23NAME32/1/2021200
24NAME33/1/2021300
25NAME34/1/2021400
26NAME35/1/2021500
27NAME36/1/2021600
28NAME37/1/2021700
29NAME38/1/2021800
30NAME39/1/2021900
31NAME310/1/20211000
Sheet1 (2)


Any help is much appreciated. Thank you.
 

Excel Facts

Can you sort left to right?
To sort left-to-right, use the Sort dialog box. Click Options. Choose "Sort left to right"

Hileolane

New Member
Joined
Aug 4, 2021
Messages
4
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
  2. MacOS
You can consider to use powerquery to complete it with a few clicks. It will be quite easy.
 

rozek

New Member
Joined
Aug 11, 2021
Messages
29
Office Version
  1. 365
Platform
  1. Windows
Thank you for the reply. I prefer if this could be done using VBA since need to combine with other automation.
 

Hileolane

New Member
Joined
Aug 4, 2021
Messages
4
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
  2. MacOS
As I don't know the target cells range to display the final data, I can't write VBA codes correspondingly but I did make it happen.
VBA Code:
Sub Rearrange()
Application.ScreenUpdating = False

Dim rng As Range

For Each rng In Selection
        Range("F10000").End(3).Offset(1).Value = Cells(3, rng.Column).Text
        Range("G10000").End(3).Offset(1).Value = Cells(rng.Row, 1).Text
        Range("H10000").End(3).Offset(1).Value = rng.Value
Next
Application.ScreenUpdating = True

End Sub

Step 1 Make sure the layout of the original data.
Step 2 Select the salary area, i.e. B4:D13 in my case
Step 3 Run the vba macro
 

Attachments

  • 屏幕截图 2021-08-24 174902.png
    屏幕截图 2021-08-24 174902.png
    62 KB · Views: 9

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
56,462
Office Version
  1. 365
Platform
  1. Windows
VBA or formula to transpose/re-arranging data in columns
Here is a formula approach. You only need to enter this formula in the top-left result cell and the results should automatically spill to the required rows and columns.

21 08 24.xlsm
ABCDEFGH
1SALARY
2DATENAME1NAME2NAME3NAMEDATESALARY
31/01/2021101100NAME11/01/202110
42/01/2021202200NAME12/01/202120
53/01/2021303300NAME13/01/202130
64/01/2021404400NAME14/01/202140
75/01/2021505500NAME15/01/202150
86/01/2021606600NAME16/01/202160
97/01/2021707700NAME17/01/202170
108/01/2021808800NAME18/01/202180
119/01/2021909900NAME19/01/202190
1210/01/2021100101000NAME110/01/2021100
13NAME21/01/20211
14NAME22/01/20212
15NAME23/01/20213
16NAME24/01/20214
17NAME25/01/20215
18NAME26/01/20216
19NAME27/01/20217
20NAME28/01/20218
21NAME29/01/20219
22NAME210/01/202110
23NAME31/01/2021100
24NAME32/01/2021200
25NAME33/01/2021300
26NAME34/01/2021400
27NAME35/01/2021500
28NAME36/01/2021600
29NAME37/01/2021700
30NAME38/01/2021800
31NAME39/01/2021900
32NAME310/01/20211000
Rearrange
Cell Formulas
RangeFormula
F3:H32F3=LET(rws,ROWS(A3:A12),cols,COLUMNS(B2:D2),c_1,INDEX(B2:D2,INT(SEQUENCE(rws*cols,,rws)/rws)),c_2,INDEX(A3:A12,MOD(SEQUENCE(rws*cols,,0),rws)+1),CHOOSE({1,2,3},c_1,c_2,INDEX(B3:D12,MATCH(c_2,A3:A12,0),MATCH(c_1,B2:D2,0))))
Dynamic array formulas.
 

Hileolane

New Member
Joined
Aug 4, 2021
Messages
4
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
  2. MacOS
I use INDEX to get the answer for you too
ReArrange.xlsx
ABCDEFGHIJ
1SALARY
2先做这个 利用index是引用的属性
3DATENAME1NAME2NAME3NAMEDATESALARY
42021/8/263425NAME12021/8/263
52021/9/3907264NAME12021/9/390
62021/8/26433712NAME12021/8/2643
72021/8/15375119NAME12021/8/1537
82021/8/2275441NAME12021/8/227
92021/8/14797036NAME12021/8/1479
102021/8/2372631NAME12021/8/2372
112021/8/29468572NAME12021/8/2946
122021/8/28601514NAME12021/8/2860
132021/8/20332948NAME12021/8/2033
14NAME22021/8/2642
15NAME22021/9/372
16NAME22021/8/2637
17NAME22021/8/1551
18NAME22021/8/2254
19NAME22021/8/1470
20NAME22021/8/236
21NAME22021/8/2985
22NAME22021/8/2815
23NAME22021/8/2029
24NAME32021/8/265
25NAME32021/9/364
26NAME32021/8/2612
27NAME32021/8/1519
28NAME32021/8/2241
29NAME32021/8/1436
30NAME32021/8/2331
31NAME32021/8/2972
32NAME32021/8/2814
33NAME32021/8/2048
数据逆透视
Cell Formulas
RangeFormula
G4:G33G4=INDEX($3:$3,,COLUMN(INDEX(INDEX($B$4:$D$13,,ROUNDUP(ROWS($E$4:E4)/ROWS($B$4:$B$13),0)),IF(MOD(ROWS($E$4:E4),ROWS($B$4:$B$13))=0,ROWS($B$4:$B$13),MOD(ROWS($E$4:E4),ROWS($B$4:$B$13))))))
H4:H33H4=INDEX(A:A,ROW(INDEX(INDEX($B$4:$D$13,,ROUNDUP(ROWS($E$4:E4)/ROWS($B$4:$B$13),0)),IF(MOD(ROWS($E$4:E4),ROWS($B$4:$B$13))=0,ROWS($B$4:$B$13),MOD(ROWS($E$4:E4),ROWS($B$4:$B$13))))))
I4:I33I4=INDEX(INDEX($B$4:$D$13,,ROUNDUP(ROWS($E$4:E4)/ROWS($B$4:$B$13),0)),IF(MOD(ROWS($E$4:E4),ROWS($B$4:$B$13))=0,ROWS($B$4:$B$13),MOD(ROWS($E$4:E4),ROWS($B$4:$B$13))))
 

rozek

New Member
Joined
Aug 11, 2021
Messages
29
Office Version
  1. 365
Platform
  1. Windows
Thank you Peter. Awesome formula. Just curious if its difficult to write this in VBA? Any help is appreciated for benefit of others as well.
 

rozek

New Member
Joined
Aug 11, 2021
Messages
29
Office Version
  1. 365
Platform
  1. Windows
I use INDEX to get the answer for you too
ReArrange.xlsx
ABCDEFGHIJ
1SALARY
2先做这个 利用index是引用的属性
3DATENAME1NAME2NAME3NAMEDATESALARY
42021/8/263425NAME12021/8/263
52021/9/3907264NAME12021/9/390
62021/8/26433712NAME12021/8/2643
72021/8/15375119NAME12021/8/1537
82021/8/2275441NAME12021/8/227
92021/8/14797036NAME12021/8/1479
102021/8/2372631NAME12021/8/2372
112021/8/29468572NAME12021/8/2946
122021/8/28601514NAME12021/8/2860
132021/8/20332948NAME12021/8/2033
14NAME22021/8/2642
15NAME22021/9/372
16NAME22021/8/2637
17NAME22021/8/1551
18NAME22021/8/2254
19NAME22021/8/1470
20NAME22021/8/236
21NAME22021/8/2985
22NAME22021/8/2815
23NAME22021/8/2029
24NAME32021/8/265
25NAME32021/9/364
26NAME32021/8/2612
27NAME32021/8/1519
28NAME32021/8/2241
29NAME32021/8/1436
30NAME32021/8/2331
31NAME32021/8/2972
32NAME32021/8/2814
33NAME32021/8/2048
数据逆透视
Cell Formulas
RangeFormula
G4:G33G4=INDEX($3:$3,,COLUMN(INDEX(INDEX($B$4:$D$13,,ROUNDUP(ROWS($E$4:E4)/ROWS($B$4:$B$13),0)),IF(MOD(ROWS($E$4:E4),ROWS($B$4:$B$13))=0,ROWS($B$4:$B$13),MOD(ROWS($E$4:E4),ROWS($B$4:$B$13))))))
H4:H33H4=INDEX(A:A,ROW(INDEX(INDEX($B$4:$D$13,,ROUNDUP(ROWS($E$4:E4)/ROWS($B$4:$B$13),0)),IF(MOD(ROWS($E$4:E4),ROWS($B$4:$B$13))=0,ROWS($B$4:$B$13),MOD(ROWS($E$4:E4),ROWS($B$4:$B$13))))))
I4:I33I4=INDEX(INDEX($B$4:$D$13,,ROUNDUP(ROWS($E$4:E4)/ROWS($B$4:$B$13),0)),IF(MOD(ROWS($E$4:E4),ROWS($B$4:$B$13))=0,ROWS($B$4:$B$13),MOD(ROWS($E$4:E4),ROWS($B$4:$B$13))))
Thank you

Hileolane

 

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
56,462
Office Version
  1. 365
Platform
  1. Windows
Just curious if its difficult to write this in VBA?
Should I assume
  1. That there could be more or less than 10 rows of actual data?
  2. That there could be more or less than 3 names in row 2?
  3. That the 'DATE' heading will be in cell A2 of the active sheet?
 

rozek

New Member
Joined
Aug 11, 2021
Messages
29
Office Version
  1. 365
Platform
  1. Windows
Should I assume
  1. That there could be more or less than 10 rows of actual data?
  2. That there could be more or less than 3 names in row 2?
  3. That the 'DATE' heading will be in cell A2 of the active sheet?
  1. That there could be more or less than 10 rows of actual data? Since this is for number of days in a month. (can be 28/29, 30 or 31 days) equivalent to this much rows.
  2. That there could be more or less than 3 names in row 2? Yes, it could be more but for example purposes, 3 names should be ok.
  3. That the 'DATE' heading will be in cell A2 of the active sheet? Heading name is not compulsory but good to have at that cell. The arrangement of data is important.
 

Forum statistics

Threads
1,175,479
Messages
5,897,673
Members
434,668
Latest member
alteriusquiuus

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
Top