"Name" and "Reports-to" Sequence Project

L

LTDLogan

New Member
Joined
Dec 1, 2018
Messages
5
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

[TABLE="width: 175, align: left"]
<tbody>[TR]
[TD="align: center"]NAME[/TD]
[TD="align: center"]REPORTS-TO[/TD]
[/TR]
[TR]
[TD="align: center"]A[/TD]
[TD="align: center"]NULL[/TD]
[/TR]
[TR]
[TD="align: center"]B[/TD]
[TD="align: center"]A[/TD]
[/TR]
[TR]
[TD="align: center"]C[/TD]
[TD="align: center"]A[/TD]
[/TR]
[TR]
[TD="align: center"]D[/TD]
[TD="align: center"]B[/TD]
[/TR]
[TR]
[TD="align: center"]E[/TD]
[TD="align: center"]B[/TD]
[/TR]
[TR]
[TD="align: center"]F[/TD]
[TD="align: center"]B[/TD]
[/TR]
[TR]
[TD="align: center"]G[/TD]
[TD="align: center"]C[/TD]
[/TR]
[TR]
[TD="align: center"]H[/TD]
[TD="align: center"]C[/TD]
[/TR]
[TR]
[TD="align: center"]I[/TD]
[TD="align: center"]C[/TD]
[/TR]
[TR]
[TD="align: center"]J[/TD]
[TD="align: center"]E[/TD]
[/TR]
[TR]
[TD="align: center"]K[/TD]
[TD="align: center"]E[/TD]
[/TR]
[TR]
[TD="align: center"]L[/TD]
[TD="align: center"]E[/TD]
[/TR]
[TR]
[TD="align: center"]M[/TD]
[TD="align: center"]I[/TD]
[/TR]
[TR]
[TD="align: center"]N[/TD]
[TD="align: center"]I[/TD]
[/TR]
[TR]
[TD="align: center"]O[/TD]
[TD="align: center"]I[/TD]
[/TR]
</tbody>[/TABLE]
The adjacent sheet with the re-sequenced data

[TABLE="width: 175, align: left"]
<tbody>[TR]
[TD="align: center"]NAME[/TD]
[TD="align: center"]REPORTS-TO[/TD]
[/TR]
[TR]
[TD="align: center"]A[/TD]
[TD="align: center"]NULL[/TD]
[/TR]
[TR]
[TD="align: center"]A[/TD]
[TD="align: center"]NULL[/TD]
[/TR]
[TR]
[TD="align: center"]B[/TD]
[TD="align: center"]A[/TD]
[/TR]
[TR]
[TD="align: center"]C[/TD]
[TD="align: center"]A[/TD]
[/TR]
[TR]
[TD="align: center"]B[/TD]
[TD="align: center"]A[/TD]
[/TR]
[TR]
[TD="align: center"]D[/TD]
[TD="align: center"]B[/TD]
[/TR]
[TR]
[TD="align: center"]E[/TD]
[TD="align: center"]B[/TD]
[/TR]
[TR]
[TD="align: center"]F[/TD]
[TD="align: center"]B[/TD]
[/TR]
[TR]
[TD="align: center"]E[/TD]
[TD="align: center"]B[/TD]
[/TR]
[TR]
[TD="align: center"]J[/TD]
[TD="align: center"]E[/TD]
[/TR]
[TR]
[TD="align: center"]K[/TD]
[TD="align: center"]E[/TD]
[/TR]
[TR]
[TD="align: center"]L[/TD]
[TD="align: center"]E[/TD]
[/TR]
[TR]
[TD="align: center"]C[/TD]
[TD="align: center"]A[/TD]
[/TR]
[TR]
[TD="align: center"]G[/TD]
[TD="align: center"]C[/TD]
[/TR]
[TR]
[TD="align: center"]H[/TD]
[TD="align: center"]C[/TD]
[/TR]
[TR]
[TD="align: center"]I[/TD]
[TD="align: center"]C[/TD]
[/TR]
[TR]
[TD="align: center"]I[/TD]
[TD="align: center"]C[/TD]
[/TR]
[TR]
[TD="align: center"]M[/TD]
[TD="align: center"]I[/TD]
[/TR]
[TR]
[TD="align: center"]N[/TD]
[TD="align: center"]I[/TD]
[/TR]
[TR]
[TD="align: center"]O[/TD]
[TD="align: center"]I[/TD]
[/TR]
</tbody>[/TABLE]
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
 
Last edited by a moderator:

Excel Facts

How can you turn a range sideways?
Copy the range. Select a blank cell. Right-click, Paste Special, then choose Transpose.
Sort by date Sort by votes
Try this for results in "D & E"
Code: 
[COLOR="Navy"]Sub[/COLOR] MG02Dec20
[COLOR="Navy"]Dim[/COLOR] Rng [COLOR="Navy"]As[/COLOR] Range, Dn [COLOR="Navy"]As[/COLOR] Range, n [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long[/COLOR]
[COLOR="Navy"]Set[/COLOR] Rng = Range(Range("B2"), Range("B" & Rows.Count).End(xlUp))
[COLOR="Navy"]With[/COLOR] CreateObject("scripting.dictionary")
.CompareMode = vbTextCompare

[COLOR="Navy"]For[/COLOR] [COLOR="Navy"]Each[/COLOR] Dn [COLOR="Navy"]In[/COLOR] Rng
[COLOR="Navy"]If[/COLOR] Not .exists(Dn.Value) [COLOR="Navy"]Then[/COLOR] .Item(Dn.Value) = Empty
[COLOR="Navy"]Next[/COLOR]

[COLOR="Navy"]For[/COLOR] [COLOR="Navy"]Each[/COLOR] Dn [COLOR="Navy"]In[/COLOR] Rng.Offset(, -1)
    [COLOR="Navy"]If[/COLOR] .exists(Dn.Value) [COLOR="Navy"]Then[/COLOR]
        .Item(Dn.Value) = Dn.Offset(, 1).Value
    [COLOR="Navy"]End[/COLOR] If
[COLOR="Navy"]Next[/COLOR] Dn


[COLOR="Navy"]Dim[/COLOR] K [COLOR="Navy"]As[/COLOR] Variant, c [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long[/COLOR]
Range("D1:E1").Value = Array("NAME", "REPORTS-TO")
c = 1
 
 [COLOR="Navy"]For[/COLOR] [COLOR="Navy"]Each[/COLOR] K [COLOR="Navy"]In[/COLOR] .keys
        c = c + 1
        Cells(c, "D").Resize(, 2).Value = Array(K, .Item(K))
        Cells(c, "D").Resize(, 2).Font.Bold = True
        [COLOR="Navy"]For[/COLOR] [COLOR="Navy"]Each[/COLOR] Dn [COLOR="Navy"]In[/COLOR] Rng
            [COLOR="Navy"]If[/COLOR] .Item(K) = Empty [COLOR="Navy"]Then[/COLOR]
                 [COLOR="Navy"]If[/COLOR] Dn = K [COLOR="Navy"]Then[/COLOR] Cells(c, "D").Resize(, 2).Value = Dn.Offset(, -1).Resize(, 2).Value
            [COLOR="Navy"]Else[/COLOR]
                [COLOR="Navy"]If[/COLOR] Dn.Value = .Item(K) And Dn.Offset(, -1).Value = K [COLOR="Navy"]Then[/COLOR]
                    [COLOR="Navy"]For[/COLOR] n = 1 To Rng.Count
                        [COLOR="Navy"]If[/COLOR] Dn.Offset(n).Value = K [COLOR="Navy"]Then[/COLOR]
                            c = c + 1
                            Cells(c, "D") = Dn.Offset(n, -1).Value
                            Cells(c, "E") = Dn.Offset(n).Value
                        [COLOR="Navy"]End[/COLOR] If
                     [COLOR="Navy"]Next[/COLOR] n
                [COLOR="Navy"]End[/COLOR] If
            [COLOR="Navy"]End[/COLOR] If
        [COLOR="Navy"]Next[/COLOR] Dn
[COLOR="Navy"]Next[/COLOR] K
[COLOR="Navy"]End[/COLOR] With

[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]Sub[/COLOR]
Regards Mick
 
Upvote 0
Thank you, Mick!
Your code did it perfectly in reference to the data that I provided. Unfortunately, there are some small glitches.
When I scramble up the list, it gets all out of wack. The ABC's in the Name column are placeholders and their reports to will not be in any ordered fashion either.

PHP: 
Sub Calldown_Creator_v1()
'Use this macro to create a calldown based on 2 columns of data.
'The first column must contain the staff names.
'The second column must contain the staff member that they report to
'The  must be the only staff member without a reports-to value
'
'
'
Sheets("Staff List").Select
        Dim sourceCol As Integer, rowCount As Integer, currentRow As Integer, i As Integer, j As Integer, k As Integer
       
    Dim currentRowValue As String
 
    sourceCol = 2   'column B has a value of 2
    rowCount = Cells(Rows.Count, sourceCol).End(xlUp).Row
    j = 2
   
 
'for every row, find the first blank cell and select it
    For currentRow = 1 To rowCount
        currentRowValue = Cells(currentRow, sourceCol).Value
        If IsEmpty(currentRowValue) Or currentRowValue = "" Then
            Cells(currentRow, sourceCol).Select
        End If
        Next
            Range(ActiveCell, ActiveCell.End(xlToLeft)).Copy
Sheets("Calldown").Activate
            Range(Cells(2, 1), Cells(2, 2)).PasteSpecial xlPasteValues
'
'--- is now at the top of the calldown
For i = 2 To rowCount
Sheets("Staff List").Select
            If (Cells(i, 2).Value = ThisWorkbook.Sheets("Calldown").Cells(j, 1)) Then
            j = j + 1
            Range(Cells(i, 1), Cells(i, 2)).Copy
            Sheets("Calldown").ActivateS
            Range(Cells(j, 1), Cells(j, 2)).PasteSpecial xlPasteValues
            Else
        End If
        Next
                       
End Sub
So this was my first attempt at the macro. Maybe It can give you a better idea of what I was looking for.
 
Upvote 0
I thought that might be the case !!!
The only way I might be able to give a more reliable code is for you to post an more comprehensive example of your data and the expected results that cover the short comings of my original code.
 
Upvote 0
You must log in or register to reply here.

Similar threads

S
  • Question Question
Creating a random sequence of items from a weighted list with no duplicates
Replies
6
Views
217
shg
shg
N
  • Question Question
VBA to add title from formula to a save as name
Replies
0
Views
96
NKUNZI
N
GMC The Macro Man
  • Question Question
SEQUENCE formula
Replies
0
Views
122
GMC The Macro Man
GMC The Macro Man
D
  • Question Question
Generating Reports in Word Using Data in Excel
Replies
1
Views
328
Dan_W
D
T
  • Question Question
Formula to sum multiple sequences/ranges of values within an array?
Replies
2
Views
250
Fluff
Fluff

Forum statistics

Threads
1,223,888
Messages
6,175,213
Members
452,618
Latest member
Tam84

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