Processing
 

Excel: Matching 1500 names (Column A) with Supervisors (Column B) and placing Identifier in Corresponding Column C,D,E,etc

29/08/2016 12:37#1

Jrussell

Member

Joined at: 4 months ago

Post: 2

Thank: 0

Thanked: 0

I have a list of names with their direct supervisor that I am trying to expand upon by showing the employees the next few layers down that work for the list of names. Column C of the linked image brings back results of 1-7 by matching the list of names in A to those in the column of 7 names. This shows that "HB" works for "SW" but that "SW" works for "ZJ," so "HB" is technically under "ZJ" too. What I am hoping to accomplish is to have a result similar to that shown below (or something that will show the employees under each supervisor). As shown below, in the data there are many names not being searched for but that are needing to be mapped to those that are being searched for. At the current count there are 1500 employee names with 7 of them being the ones searched out of a list of 143 supervisors that repeat for the employees.

Names to Look for: "Fictional names used for scenario" Sam W. (SW) 1

Robert R. (RR) 2

Kegan G. (KG) 3

Isiah B. (IB) 4

Orville E. (OE) 5

Robert J. (RJ) 6

Zach J. (ZJ) 7

           Column A Column B    Column C    Column D    Column E            Superv.      Employ.               HB        PJ                                 7               SW        HB         1            7               BE        JR                      2               HB        IL                      1          7               IL        AP                                 1               BE        WP                      2               RR        BE         2                          KG        JW         3                JW        JH                      3               ZJ        SW         7  

These results would then be used to create lists of employees under a certain person.

Things I'm not sure affects this would be how the name is constructed in the workbook. Example Sam W. is listed as Wilson, Sam in the workbook.

Of course if there is another way to achieve the final result that would be easier then I wouldn't mind an altered format to what I currently have. If anyone has an idea how to achieve this please respond. If there are any questions about more specific things in the workbook that I could supply that could help resolve this quicker also let me know for any future inquiries I may have. Thank you.

29/08/2016 13:07Top#2

Eileen R

Member

Joined at: 4 months ago

Post: 1

Thank: 0

Thanked: 0

Well, if I understand what you're asking, you need to normalize your data. In this case, that means creating two tables linked by an employee code that you'll need to create.

Once you have these two tables, you can easily perform any query and summary report you need.

29/08/2016 14:47Top#3

pnuts

Member

Joined at: 10 months ago

Post: 231

Thank: 0

Thanked: 0

Giving a range that contains the 7 names and next to them 7 numbers the name AUarray then in C3 and copied down to suit:

=IFERROR(VLOOKUP(A3,LUarray,2,0),"")   

and in D2 and copied down to suit:

 =IFERROR(VLOOKUP(INDEX(A:A,MATCH(A3,B:B,0)),LUarray,2,0),"")  

I think something similar (but maybe a lot longer!) would work for ColumnE but I don't have time for that at present.

`

Similar articles

Excel More conditions in one formula?

4 months ago - Reply: 2 - Views: 171

Get Workbook reference from Open File dialog

4 months ago - Reply: 2 - Views: 128

Add Calculated Field To Pivot Table

4 months ago - Reply: 0 - Views: 5

Nested IF Functions in Excel not working

4 months ago - Reply: 3 - Views: 163

Formulating Date in Excel 2013

4 months ago - Reply: 2 - Views: 88

VB6 extracts in Excel 2013

5 months ago - Reply: 2 - Views: 153

Excel 2013 : Pull Matching Data from Column

5 months ago - Reply: 1 - Views: 19

excel search and copy in/from another file

5 months ago - Reply: 0 - Views: 1

Buggy PivotTable.ColumnRange

5 months ago - Reply: 0 - Views: 4

Excel static conditional formatting area

5 months ago - Reply: 0 - Views: 8

vé máy bay vé máy bay giá rẻ ve may bay ve may bay gia re vé máy bay vé máy bay giá rẻ ve may bay ve may bay gia re vé máy bay vé máy bay giá rẻ ve may bay ve may bay gia re vé máy bay vé máy bay giá rẻ ve may bay ve may bay gia re vé máy bay vé máy bay giá rẻ ve may bay ve may bay gia re vé máy bay vé máy bay giá rẻ ve may bay ve may bay gia re vé máy bay vé máy bay giá rẻ ve may bay ve may bay gia re