Macro To Populate a Matrix from Employee List

[jjb373]

Hello All - In Excel I have a list of employees (attached fig.1). This list can have more employees added to it over time. In Another tab in this spreadsheet, a matrix (attached fig.2) to show where those employees fall regarding certain criteria.

I want to be able to run a macro and have this matrix populate the employee names depending on where they fall within the defined criteria.

I have created a number of macros using IF statements so I do have experience with some VBA. But I am thinking this one may need a loop to look at each row in the list of employees until you come to a row that is blank, etc. Also - if it is possible, the ability to run this macro and update the matrix with any people who were recently added to the employee list (refresh the matrix with new employees).

If some other technique other than a macro would be better, I am open to ideas. THANKS!


Fig. 1
Name Position Yrs Exp. Next Step
Jim Analyst 5 Lateral
Susan Manager 10 Promote
Paul Manager 15 Promote
Jon Analyst 2 Evaluate
Kim Operator 1 Evaluate




Fig. 2
POSITIONS
N Executive Manager Analyst Operator
E Promote Susan
X Paul
T
Lateral Jim

S
T Remain
E
P Evaluate Jon Kim
S




-- JJB373 --

 

[SkipVought]

As you can see, your fig 2 is almost useless. Check out TGML by doing a FIND on this page and use the TT TGML Tag.

However, in terms of what your fig 2 appears to be, it is almost as useless as the display.
What you could use is a simple PivotTable like this...
[tt]
Count of Name Position
Next Step Name Analyst Manager Operator Grand Total
Evaluate Jon 1 1
Kim 1 1
Lateral Jim 1 1
Promote Paul 1 1
Susan 1 1
Grand Total 2 2 1 5
[/tt]


Skip,
^{Don't let the Diatribe...
talk you to death!}
_{Just traded in my old subtlety...
for a NUANCE!}

 

[jjb373]

Skip - Thank you for taking the time to reply. I didn't realize I could use TGML Tags....

I completely agree that the matrix that is being desired is pointless. I tried to explain that to them but that is what they want. I see no value added use for it.

With that being said, do you (or anyone else on the forum) have any suggestions about how I can accomplish this? Basically I want what you created as a PivotTable view but without the employee names as an extra "grouping level" in the row section. Also, the employee names will be represented where the counts are in the PivotTable.

Any type of input or futher discussion would be much appreciated.

Thanks!

-- JJB373 --

 

[SkipVought]

1. Name your column data ranges, using Insert > Name > Create: Create names in top row

2. This assumes that your source data on a separate sheet, lets call it Sheet1. On a new sheet in row 1...
[tt]
Position Name Analyst Manager Operator
[/tt]
3. Using MS Query via Data > Import Exsternal Data ... faq68-5829 get [Next Step], [Name] from [Sheet1$], UNselecting Data formatting and layout Include field names in Data Range Properites, when you File > Return data to Misrosoft Excel. The result will look like this...
[tt]
Position Name Analyst Manager Operator
Lateral Jim
Promote Susan
Promote Paul
Evaluate Jon
Evaluate Kim
[/tt]
You can eventually HIDE the Name column.

4. The formula
[tt]
C2: =IF(INDEX(Position,MATCH($B2,Name,0),1)=C$1,$B2,"")
[/tt]
copy c2 thru the columns to the right and the rows below. The result...
[tt]
Position Name Analyst Manager Operator
Lateral Jim Jim
Promote Susan Susan
Promote Paul Paul
Evaluate Jon Jon
Evaluate Kim Kim
[/tt]




Skip,
^{Don't let the Diatribe...
talk you to death!}
_{Just traded in my old subtlety...
for a NUANCE!}