Macro to rearrange data order from one sheet to another

crispangilinan

New Member
Joined
Sep 16, 2021
Messages
4
Office Version
  1. 365
  2. 2019
  3. 2016
Hi Excel Gurus. I need your expert guidance on how to write a simple macro that will help to reorganize my raw data from Sheet 1 into Sheet 2. Basically, each data entry on Sheet 1 will be broken down into 8 data points once transferred to Sheet 2.

From this:
Sheet 1 Data
Full TimeFull TimeFull TimeFull TimePart TimePart TimePart TimePart Time
LocationEmp IDManagerEmailNameQ1Q2Q3Q4Q5Q6Q7Q8
Location 1
100001​
Manager 1email 1Employee 100010001
Location 2
100002​
Manager 2email 2Employee 200100010
Location 3
100003​
Manager 3email 3Employee 301000100
Location 4
100004​
Manager 4email 4Employee 410111011
Location 5
100005​
Manager 5email 5Employee 500000000
Location 1
100006​
Manager 6email 6Employee 600000000
Location 2
100007​
Manager 7email 7Employee 701010101
Location 3
100008​
Manager 8email 8Employee 810001000
Location 4
100009​
Manager 9email 9Employee 900000000
Location 5
100010​
Manager 10email 10Employee 1001100110
Location 1
100011​
Manager 1email 11Employee 1100000000
Location 2
100012​
Manager 2email 12Employee 1200000000
Location 3
100013​
Manager 3email 13Employee 1300010001
Location 4
100014​
Manager 4email 14Employee 1400100010
Location 5
100015​
Manager 5email 15Employee 1501000100
Location 1
100016​
Manager 6email 16Employee 1610001000
Location 2
100017​
Manager 7email 17Employee 1700100010
Location 3
100018​
Manager 8email 18Employee 1800010001
Location 4
100019​
Manager 9email 19Employee 1900000000
Location 5
100020​
Manager 10email 20Employee 2001100110
Location 1
100021​
Manager 1email 21Employee 2100000000
Location 2
100022​
Manager 2email 22Employee 2210101010
Location 3
100023​
Manager 3email 23Employee 2300010001
Location 4
100024​
Manager 4email 24Employee 2400000000
Location 5
100025​
Manager 5email 25Employee 2501000100
Location 1
100026​
Manager 6email 26Employee 2600000000
Location 2
100027​
Manager 7email 27Employee 2710001000
Location 3
100028​
Manager 8email 28Employee 2800000000
Location 4
100029​
Manager 9email 29Employee 2901110111
Location 5
100030​
Manager 10email 30Employee 3000000000

To This:
Sheet 2 Data
Emp IDNameEmailManagerLocationStatusQuestionValue
100001​
Employee 1email 1Manager 1Location 1Full TimeQ10
100001​
Employee 1email 1Manager 1Location 1Full TimeQ20
100001​
Employee 1email 1Manager 1Location 1Full TimeQ30
100001​
Employee 1email 1Manager 1Location 1Full TimeQ41
100001​
Employee 1email 1Manager 1Location 1Part TimeQ50
100001​
Employee 1email 1Manager 1Location 1Part TimeQ60
100001​
Employee 1email 1Manager 1Location 1Part TimeQ70
100001​
Employee 1email 1Manager 1Location 1Part TimeQ81
100002​
Employee 2email 2Manager 2Location 2Full TimeQ10
100002​
Employee 2email 2Manager 2Location 2Full TimeQ20
100002​
Employee 2email 2Manager 2Location 2Full TimeQ31
100002​
Employee 2email 2Manager 2Location 2Full TimeQ40
100002​
Employee 2email 2Manager 2Location 2Part TimeQ50
100002​
Employee 2email 2Manager 2Location 2Part TimeQ60
100002​
Employee 2email 2Manager 2Location 2Part TimeQ71
100002​
Employee 2email 2Manager 2Location 2Part TimeQ80
100003​
Employee 3email 3Manager 3Location 3Full TimeQ10
100003​
Employee 3email 3Manager 3Location 3Full TimeQ21
100003​
Employee 3email 3Manager 3Location 3Full TimeQ30
100003​
Employee 3email 3Manager 3Location 3Full TimeQ40
100003​
Employee 3email 3Manager 3Location 3Part TimeQ50
100003​
Employee 3email 3Manager 3Location 3Part TimeQ61
100003​
Employee 3email 3Manager 3Location 3Part TimeQ70
100003​
Employee 3email 3Manager 3Location 3Part TimeQ80

As you can observe, a single line data on Sheet 1 will be broken into 8 data points once transferred to sheet 2. Also, the order of the rows are changed/reorganized. Would appreciate if the macro can be adjusted to cover more data points on Sheet 1 (probable 5000?). Thanks in advance!!
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
66,062
Office Version
  1. 365
Platform
  1. Windows
Glad we could help & thanks for the feedback.
 

Excel Facts

Why does 9 mean SUM in SUBTOTAL?
It is because Sum is the 9th alphabetically in Average, Count, CountA, Max, Min, Product, StDev.S, StDev.P, Sum, VAR.S, VAR.P.

Forum statistics

Threads
1,148,362
Messages
5,746,259
Members
424,001
Latest member
Sophiemakescakes

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