1

Hello,
I am tasked with adding employee to manager…these were two different excel sheets. I created a simple 1 sheet

Column 1 Column 2 Column 3 Column 4
NAME EMPLOYEE SUPERVISOR
ABLE, JOHN AARON, JASON TIME, JUSTIN
ABLE, JOHN ABLE, JOHN DAVIS, JASON
ABLE, JOHN ABBOTT, LESTER JUNIPER, STEPH
ARROW, TOM ACE, JACK CATCHER, LUTHER
ARROW, TOM ARROW, TOM PETERSON, JACK
BLANK, JASON BANKS, MISSY LIVELY, DON
BLANK, JASON BLANK, JASON CLEAR, THOMAS

so column
1 is duplicate names (different departments) same person
2. the employee roster(every employee)
3. the supervisor of the employee

what I need is to put the supervisor next to the name in column 1. like below
add the supervisor next to the name

EXAMPLE

Column 1 Column 2 Column 3 Column 4
NAME SUPERVISOR
ABLE, JOHN DAVIS, JASON
ABLE, JOHN DAVIS, JASON
ARROW, TOM PETERSON, JACK
ARROW, TOM PETERSON, JACK
BLANK, JASON CLEAR, THOMAS
BLANK, JASON CLEAR, THOMAS

if we can just pop these into 2 separate columns that would be ok with me

I have over 800 entries and most are just duplicate people but I need the employee to match the supervisor in all 800 entries and not all employees used. so I just need the supervisors for the ones in column 1.

I hope this makes sense.

p.s. I plan on deleting column 2 and 3 because I will not need the employee and supervisor anymore because I will have cell 1 and 2 and I will move them to a location in the spreadsheet that I need it to go.

thanks
Jimmy

2 Spice ups
2

This sounds like a simple VLOOKUP.

However, your example doesn’t make sense to me.

In the first “sheet”, you have three entries for the NAME with the value “ABLE, JOHN”. In your second “sheet”, there are only two. What logic was used to drop one of them? You haven’t explained this.

3 Spice ups
3

I’d use a VBA dictionary.


Sub getEmployeeSpuervisor()
  set obj = createObject("Scripting.Dictionary")
  'Get a unique list of employees and supervisors.
  for r = 2 to (whatever the last row of column 2 is)
    if not obj.exists(cells(r, 2).value) then obj.add Cells(r, 2).value, Cells(r, 3).value
  next
 
  'Now match teh supervisor to teh employee in column1
  for r = 2 to (whatever the last row of column 1 is)
    Cells(r, 2) = obj(cells(r,1))
  next r
End Sub

I didn’t test that, but it should loop through all the names in column 2 to get a unique list of employees (Key) and supervisors (Value). It then loops through the names in column 1 (key) to get the associated supervisor (Value).

Even with 900 names it should only take a couple of seconds to run.

4

This is the reason I refrained from replying, I couldn’t understand the reason for sharing different sheets or even understand what they reference.

Why does everyone need to be duplicated and show against a roster that’s not clear. (to you it may be).

If everything is like the second example, there are many ways to match a person in column 1 to their manager. VLOOKUP is one way.

Is excel even the right tool for your use case?

1 Spice up
5

I hope this makes sense

I have over 800 entries in column 1
I have all the employee’s and supervisors in column 2 and 3

so I want to look at the employee list with the supervisor

so in the end I have all of the 800 entries with supervisors

don’t hesitate to ask any questions.

if I can’t figure this out. I will have to manually match the 800 names to their supervisor…

6

Matching employees in column 2, to supervisors in column 3 is fairly straightforward, but what is column 1, this is the part throwing people off.

If you use AD, why are managers not populated in here?

7

800 names in column 1 are multiple of the same people
I need to put in their supervisor in next the the person’s name

so lets say I have 100 of the same name in column 1 but I need to put the supervisor of those names next to their names.

in column 1 I have 800 names but a lot of them are duplicate names. I need to get the duplicate names with the supervisor.

right now I would have to manually put in the supervisor next to each 800 names

sheet 1 has 800 names with duplicates names in it
sheet 2 has all the employee’s and their supervisor

I need 1 sheet have all 800 names with their supervisor

does that makes sense

here is what I have

NAME EMPLOYEE SUPERVISOR
ABLE, JOHN AARON, JASON TIME, JUSTIN
ABLE, JOHN ABLE, JOHN DAVIS, JASON
ABLE, JOHN ABBOTT, LESTER JUNIPER, STEPH
ARROW, TOM ACE, JACK CATCHER, LUTHER
ARROW, TOM ARROW, TOM PETERSON, JACK
BLANK, JASON BANKS, MISSY LIVELY, DON
BLANK, JASON BLANK, JASON CLEAR, THOMAS

this list is just an example first cell is my 800 list | the middle cell is all the employee’s |the last is the supervisor of each employee.

8

Rod-IT
This is not using AD…it is an Excel report.

9

Is column 1 is for General list? Master list of the Employee? including Supervisors name?

is Column 2 is for supervisor list only?
do you have a unique identifier per employee? like employee #? or something?
do you know how to use Pivot table and jump to Vlookup?

10

I know it’s excel, I was asking why AD isn’t being used to add managers to their teams, if you use AD.

What you are asking for isn’t complicated, but you have a 3rd value, this isn’t clear.

Name is what?
Employee is different?

In your 3 table example above.

You want EMPLOYEE matching to SUPERVISOR?

What is NAME for, this is the field with duplicates, but I don’t understand why it exists in relation to EMPLOYEE.

11

Hello,
to add a bit more context

I know

Column 1 Column 2 Column 3 Column 4
CUSTOMER REPRESENTITIVE SUPERVISOR
1 ABLE, JOHN
2 ABLE, JOHN
3
4
5

What I have is the customer. example customers 1 and 2 are being taken care of by Able John

what I am missing is the Supervisor. I need to put the supervisor in column 3.

I had imported all the representitives and Supervisors. So what I need is every representive has the supervisor added. right now I don’t have the supervisor. I have 800 customers with 800 represtitives but it is missing the supervisor.

if can’t solve this. I will need to fill in the supervisor’s in for each line manually

again I have the list of customers that are with the Representative but the supervisor is not there, it is blank.

I hope that clears is up.

12

As others have mentioned, this should be a very simple vlookup to find the supervisor’s name from your employee list and enter it wherever you want it on your export.

13

I think you’re looking for something along these lines:

image
image555×266 15.4 KB
2 Spice ups
15

I think you’re correct.

It sounds like Column A is a data dump list of employee names (where the rows make sense to the OP, but don’t need to make sense to us offering help). Columns B&C of the OPS example are the reference table indicating which Supervisor is responsible for which employee.

17

Yes. I need to move the supervisors next to the names that repeat.

the other two columns are the employee list and the supervisor. That is the only way I could think to make this work is for the first column name matches the employee list (thus) giving me the supervisor name and then copy the supervisor name next to the repeating name.

18

YOU ARE CORRECT… that is exactly what I am looking for

19

Hey Tim,
I don’t know how to use a VBA in Excel. can you give me instructions on how to make it work? I am not Excel expert.

if you see the latest posts, It should help you see if this formula would work for me?

20

Hit Alt+F11 and paste this code in the editor for Sheet1 (or whatever your sheet is called).

Sub getEmployeeSupervisor()
    Set obj = CreateObject("Scripting.Dictionary")
    'Get a unique list of employees and supervisors.
    For r = 2 To 8
        If Not obj.exists(Cells(r, 2).Value) Then obj.Add Cells(r, 2).Value, Cells(r, 3).Value
    Next
    
    'Insert a column so you don't overwrite your existing data
'    Columns("B").Insert Shift:=xlToRight

    'Now match the supervisor to the employee in column1
    For r = 2 To 8
        Cells(r, 2) = obj(Cells(r, 1).Value)
    Next r
End Sub

This was your list I started with:

image
image348×192 4.29 KB

This is where I pasted the code after pressing Alt+F11:

image
image1176×337 28.3 KB

You can see the name of the workbook where I tested this, GlobalTagExport.csv, and the name of the sheet I used to hold the data, Sheet1. Ironically, Sheet1 is really Sheet2 to VBA. Don’t confuse Name with Label.

This is the result of running the cod (press F5 or click the Run button or Click Run in the menu)

image
image437×173 3.66 KB
21

A vlookup is simple enough here, if the Author wasn’t aware of it then a Macro will just be over complicating things that an individual will never re-use. Being aware of the functionality of a Vlookup will help again down the line with other tasks.

1 Spice up
22

Vlookup would probably also work here. You could paste something like this into column E to test:
=VLOOKUP(A2,C:D,2,FALSE)

image
image626×318 7.74 KB
1 Spice up