Subject: Automate the reorganization of two columns based on the unique identifier "Name", and a corresponding "Reports-to" column.
Version: Office 365
OS: Windows 10
Urgency: Low
Examples Included
So I have a project that I am trying to work on in my free time (but could use some help with) to automate the reorganization of two columns based on their unique identifier ("Name") and a "Reports-to" column. The reports-to column consists of repeating values from the "Name" column and a single blank cell which will denote the first value in the sequence. As a sub-sequence of relationships meets its end, the next sequence will start with a second occurrence of the most superior value of that sub sequence. I will put an example in below. Basically, this will sort data by the supervisor and subordinate relationships in a vertical pattern. I imagine the solution to this being most feasible in VBA, however if anyone has any ideas utilizing embedded formulas or even a Query, I would love to hear your ideas. In fact, if you could get this into an Excel Query I would be ecstatic.
Some further explanation of what I imagine this to look like:
If a name has a subordinate, then it MUST have a second occurrence in the sequence, even the first value without a reports-to value must have a second occurrence as I show in my examples below. If a value does not have a subordinate it will only appear once.
The new sequence would preferably be on a separate sheet so that the data can be maintained in an alphabetical sort and the second sheet would be in the proper sequence
The Source Columns
<tbody>
</tbody>The adjacent sheet with the re-sequenced data
<tbody>
</tbody>Thank you so much. I truly appreciate all of you who help answer these questions every day. You're the best. This may be my first post, but I owe this community more than I can describe. Ya'll helped teach me Excel from the ground up and launched me into a career path in less than 10 months.
Best,
Logan
Version: Office 365
OS: Windows 10
Urgency: Low
Examples Included
So I have a project that I am trying to work on in my free time (but could use some help with) to automate the reorganization of two columns based on their unique identifier ("Name") and a "Reports-to" column. The reports-to column consists of repeating values from the "Name" column and a single blank cell which will denote the first value in the sequence. As a sub-sequence of relationships meets its end, the next sequence will start with a second occurrence of the most superior value of that sub sequence. I will put an example in below. Basically, this will sort data by the supervisor and subordinate relationships in a vertical pattern. I imagine the solution to this being most feasible in VBA, however if anyone has any ideas utilizing embedded formulas or even a Query, I would love to hear your ideas. In fact, if you could get this into an Excel Query I would be ecstatic.
Some further explanation of what I imagine this to look like:
If a name has a subordinate, then it MUST have a second occurrence in the sequence, even the first value without a reports-to value must have a second occurrence as I show in my examples below. If a value does not have a subordinate it will only appear once.
The new sequence would preferably be on a separate sheet so that the data can be maintained in an alphabetical sort and the second sheet would be in the proper sequence
The Source Columns
NAME | REPORTS-TO |
A | NULL |
B | A |
C | A |
D | B |
E | B |
F | B |
G | C |
H | C |
I | C |
J | E |
K | E |
L | E |
M | I |
N | I |
O | I |
<tbody>
</tbody>
NAME | REPORTS-TO |
A | NULL |
A | NULL |
B | A |
C | A |
B | A |
D | B |
E | B |
F | B |
E | B |
J | E |
K | E |
L | E |
C | A |
G | C |
H | C |
I | C |
I | C |
M | I |
N | I |
O | I |
<tbody>
</tbody>
Best,
Logan
Last edited by a moderator: