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
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?
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.
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...
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
Powered by vBulletin® Version 4.2.5 Copyright © 2024 vBulletin Solutions Inc. All rights reserved.