Processing
 

Excel: Matching Column B with Column A and placing Identifier in Corresponding Column C,D,E,etc

25/08/2016 17:30#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 top row. This shows that "JR" works for "BE" but that "BE" works for "JW" so "JR" is technically under "JW" too. What I am hoping to accomplish is to have a result similar to that shown below.

                Superv.    Employ.                Column A   Column B   Column C   Column D   Column E 

Names to Look for: BE JW AD

                  BE         JR          1         2          3                   BE         WP          1         2          3                   JW         BE          2         3                   AD         JW          3                   JW         JH          2         3                   AD         AD          3 

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

Names to Look for: BE AD JW

Employees WP JW BE JR BE WP JH JR WP JR

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. Thanks

25/08/2016 17:42Top#2

Joined at: 10 months ago

Post: 21

Thank: 0

Thanked: 0

A little change to the output would make this easy:

In C2 put:

=A2 

And copy down, this is the immediate supervisor.

Then in D2 we put:

=IFERROR(IF(C2=INDEX($A:$A,MATCH(C2,$B:$B,0)),"",INDEX($A:$A,MATCH(C2,$B:$B,0))),"") 

Copy over and down till only empty cells appear:

This will return the name and not a number.


To get a number we can add a simple table that would denote the supervisor number:

Then we change the formulas to these:

In C2:

=VLOOKUP(A2,I:J,2,FALSE) 

Copy down.

In D2:

=IFERROR(IF(C2=VLOOKUP(INDEX($A:$A,MATCH(INDEX($I:$I,MATCH(C2,$J:$J,0)),$B:$B,0)),$I:$J,2,FALSE),"",VLOOKUP(INDEX($A:$A,MATCH(INDEX($I:$I,MATCH(C2,$J:$J,0)),$B:$B,0)),$I:$J,2,FALSE)),"") 

copy down and over.

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

5 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