PDA

View Full Version : Populate 2nd tab by taking data from 1st tab



pivotguy
08-03-2016, 10:52 AM
I have am employee date where employee and its reporting manager information are stored.
I would like to create a de-normalized excel file as shown below.

Please see the attached excel file.

Input File
==========
EmpID ---> Name --->supervisor id

1----------> ROB-------> 123
123--------> Craig-----> 456


Output file
===========
Emp ID----- Name---- supervisor name ---- supervisor id

1------- ROB-------------- CRAIG--------------- 123

SamT
08-03-2016, 02:50 PM
We need all the various "zone" names listed hierarchically as exampled



Nation Names

Area Names
Region Names
District Names
Territories


USA







EAST







New England







Boston







Herbert






Alfonso





PortsMouth







Adam






Baker






Charles





Penobscot







Delta





East Coast







New York
Etc





DC
Etc



South East







Atlantic







Miami
Etc





Charlotte
Etc




Gulf Coast







Dallas/FortWorth
Etc


Canada
etc







etc







etc
Etc






Etc


Etc






Etc






Etc







This has to be done and doing it in VBA from the example Input sheet you attached is a very difficult problem.

pivotguy
08-04-2016, 09:08 AM
Any updates from the other experts in the forum?

SamT
08-04-2016, 10:29 AM
Is this for a one time use Project, or will you be running the code many times?

I just can't help it, I have been running various code scenarios thru my mind to discover an efficient way to accomplish this with no prior knowledge.

SamT
08-04-2016, 10:48 AM
No matter how I look at it, the first algorithm is always to create a table similar to the one above, although with more detail. All code steps after that are trivial.

pivotguy
08-04-2016, 11:22 AM
This code will run many times for any employee file updates.


The logic should be something like

1.Update Input TAB: Input Tab contains column A-G. Create a column Employees Rank "Hierarchy Level" ( Column H )
(a) pulate Column H with Manager Hierarchy Level 1, District Manager 2, Regional Director 3,Area Director 4, VP 5

2. Output TAB

(a) Column A,B,C - Populate the value from Level 1 of input sheet
(b) Column D,E,F - Take Supervisor Emp ID of Hierarchy Level (COLUMN H) = 1 which is 5763. Search 5763 in Employee ID(Column C). Here you get D,E,F Values. Store Supervisor ID 5807 in a variable
(c) Column G,H,I - Search employee ID store in step (b) which is 5807 and search in in Employee ID(Column C). Here you will get values for G,H,I
and so.. on...

SamT
08-04-2016, 12:15 PM
Create a column Employees Rank "Hierarchy Level" ( Column H )
How easily you gloss over that and go straight to the trivial.

The trivial part is :
If Employee is found in Territory manager column of the hierarchy table then,
Offset(, -c), End(xlUp) level by level to the National VP level, recording data as you go. That will only take a few milliseconds per employee.

The hard part is the hierarchy table. Do you have a simple solution for that?

Once the hierarchy table is created, it becomes a semi permanent Reference table that is only updated when an employee is not found at the appropriate level. Hmmmm. This may take more than one reference table. Don't worry, all such tables can be hidden from casual view.

Coders Notes:
Create Hierarchy tree, with Sheets Input:


Create Table of all VP_NATION employees
Sort by Name
For each in table, find all Employees with SUPERVISOR EMPLOYEE NUMBER, inserting Rows as needed
sort by name
Shift to next lower Level Columns and Repeat steps 3,4.

Create individual hierarchy table

For each in Hierarchy tree ID columns, add to table, including hierarchy level
Sort by Employee ID

Create individual's supervisor table

With Hierarchy tree in reverse column order
Create table: Emp ID | Super ID
Sort by employee ID