crispangilinan
New Member
- Joined
- Sep 16, 2021
- Messages
- 9
- Office Version
- 365
- 2019
- 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
To This:
Sheet 2 Data
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!!
From this:
Sheet 1 Data
Full Time | Full Time | Full Time | Full Time | Part Time | Part Time | Part Time | Part Time | |||||
Location | Emp ID | Manager | Name | Q1 | Q2 | Q3 | Q4 | Q5 | Q6 | Q7 | Q8 | |
Location 1 | 100001 | Manager 1 | email 1 | Employee 1 | 0 | 0 | 0 | 1 | 0 | 0 | 0 | 1 |
Location 2 | 100002 | Manager 2 | email 2 | Employee 2 | 0 | 0 | 1 | 0 | 0 | 0 | 1 | 0 |
Location 3 | 100003 | Manager 3 | email 3 | Employee 3 | 0 | 1 | 0 | 0 | 0 | 1 | 0 | 0 |
Location 4 | 100004 | Manager 4 | email 4 | Employee 4 | 1 | 0 | 1 | 1 | 1 | 0 | 1 | 1 |
Location 5 | 100005 | Manager 5 | email 5 | Employee 5 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 |
Location 1 | 100006 | Manager 6 | email 6 | Employee 6 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 |
Location 2 | 100007 | Manager 7 | email 7 | Employee 7 | 0 | 1 | 0 | 1 | 0 | 1 | 0 | 1 |
Location 3 | 100008 | Manager 8 | email 8 | Employee 8 | 1 | 0 | 0 | 0 | 1 | 0 | 0 | 0 |
Location 4 | 100009 | Manager 9 | email 9 | Employee 9 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 |
Location 5 | 100010 | Manager 10 | email 10 | Employee 10 | 0 | 1 | 1 | 0 | 0 | 1 | 1 | 0 |
Location 1 | 100011 | Manager 1 | email 11 | Employee 11 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 |
Location 2 | 100012 | Manager 2 | email 12 | Employee 12 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 |
Location 3 | 100013 | Manager 3 | email 13 | Employee 13 | 0 | 0 | 0 | 1 | 0 | 0 | 0 | 1 |
Location 4 | 100014 | Manager 4 | email 14 | Employee 14 | 0 | 0 | 1 | 0 | 0 | 0 | 1 | 0 |
Location 5 | 100015 | Manager 5 | email 15 | Employee 15 | 0 | 1 | 0 | 0 | 0 | 1 | 0 | 0 |
Location 1 | 100016 | Manager 6 | email 16 | Employee 16 | 1 | 0 | 0 | 0 | 1 | 0 | 0 | 0 |
Location 2 | 100017 | Manager 7 | email 17 | Employee 17 | 0 | 0 | 1 | 0 | 0 | 0 | 1 | 0 |
Location 3 | 100018 | Manager 8 | email 18 | Employee 18 | 0 | 0 | 0 | 1 | 0 | 0 | 0 | 1 |
Location 4 | 100019 | Manager 9 | email 19 | Employee 19 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 |
Location 5 | 100020 | Manager 10 | email 20 | Employee 20 | 0 | 1 | 1 | 0 | 0 | 1 | 1 | 0 |
Location 1 | 100021 | Manager 1 | email 21 | Employee 21 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 |
Location 2 | 100022 | Manager 2 | email 22 | Employee 22 | 1 | 0 | 1 | 0 | 1 | 0 | 1 | 0 |
Location 3 | 100023 | Manager 3 | email 23 | Employee 23 | 0 | 0 | 0 | 1 | 0 | 0 | 0 | 1 |
Location 4 | 100024 | Manager 4 | email 24 | Employee 24 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 |
Location 5 | 100025 | Manager 5 | email 25 | Employee 25 | 0 | 1 | 0 | 0 | 0 | 1 | 0 | 0 |
Location 1 | 100026 | Manager 6 | email 26 | Employee 26 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 |
Location 2 | 100027 | Manager 7 | email 27 | Employee 27 | 1 | 0 | 0 | 0 | 1 | 0 | 0 | 0 |
Location 3 | 100028 | Manager 8 | email 28 | Employee 28 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 |
Location 4 | 100029 | Manager 9 | email 29 | Employee 29 | 0 | 1 | 1 | 1 | 0 | 1 | 1 | 1 |
Location 5 | 100030 | Manager 10 | email 30 | Employee 30 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 |
To This:
Sheet 2 Data
Emp ID | Name | Manager | Location | Status | Question | Value | |
100001 | Employee 1 | email 1 | Manager 1 | Location 1 | Full Time | Q1 | 0 |
100001 | Employee 1 | email 1 | Manager 1 | Location 1 | Full Time | Q2 | 0 |
100001 | Employee 1 | email 1 | Manager 1 | Location 1 | Full Time | Q3 | 0 |
100001 | Employee 1 | email 1 | Manager 1 | Location 1 | Full Time | Q4 | 1 |
100001 | Employee 1 | email 1 | Manager 1 | Location 1 | Part Time | Q5 | 0 |
100001 | Employee 1 | email 1 | Manager 1 | Location 1 | Part Time | Q6 | 0 |
100001 | Employee 1 | email 1 | Manager 1 | Location 1 | Part Time | Q7 | 0 |
100001 | Employee 1 | email 1 | Manager 1 | Location 1 | Part Time | Q8 | 1 |
100002 | Employee 2 | email 2 | Manager 2 | Location 2 | Full Time | Q1 | 0 |
100002 | Employee 2 | email 2 | Manager 2 | Location 2 | Full Time | Q2 | 0 |
100002 | Employee 2 | email 2 | Manager 2 | Location 2 | Full Time | Q3 | 1 |
100002 | Employee 2 | email 2 | Manager 2 | Location 2 | Full Time | Q4 | 0 |
100002 | Employee 2 | email 2 | Manager 2 | Location 2 | Part Time | Q5 | 0 |
100002 | Employee 2 | email 2 | Manager 2 | Location 2 | Part Time | Q6 | 0 |
100002 | Employee 2 | email 2 | Manager 2 | Location 2 | Part Time | Q7 | 1 |
100002 | Employee 2 | email 2 | Manager 2 | Location 2 | Part Time | Q8 | 0 |
100003 | Employee 3 | email 3 | Manager 3 | Location 3 | Full Time | Q1 | 0 |
100003 | Employee 3 | email 3 | Manager 3 | Location 3 | Full Time | Q2 | 1 |
100003 | Employee 3 | email 3 | Manager 3 | Location 3 | Full Time | Q3 | 0 |
100003 | Employee 3 | email 3 | Manager 3 | Location 3 | Full Time | Q4 | 0 |
100003 | Employee 3 | email 3 | Manager 3 | Location 3 | Part Time | Q5 | 0 |
100003 | Employee 3 | email 3 | Manager 3 | Location 3 | Part Time | Q6 | 1 |
100003 | Employee 3 | email 3 | Manager 3 | Location 3 | Part Time | Q7 | 0 |
100003 | Employee 3 | email 3 | Manager 3 | Location 3 | Part Time | Q8 | 0 |
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!!