Employee Information

[humility36]

Good Afternoon Excel Team,

I've been given a task to clean up an excel spreadsheet and create a better way to view the data. I've tried to use VLOOKUP and HLOOKUP but it does not seem to be working right. Let me explain a little more

I have a spreadsheet that has employee information and areas that they are trained in. I need to be able to pull up their name and then display all the areas that they are trained in.

	ID	First Name	Last Name	Shift	Department	Shipping	Building	Receiving	PutAways	Forklift
1	A	B	C	D	E	F	G	H	I	J
2	23645	George	Lucas	Nights	QA			PO	X	X
3	55698	Sally	Mae	Days	Shipping	X			X	X
4	99687	John	Doe	Nights	Building		X	X		NC
5	89778	Steve	Smith	Days	Receiving			X	NT	X
6	LEGEND
	X = trained	NT = needs trained	PO = position offered	NC = Needs Certified

ABOVE is the format and layout of my table. My lookup value is their unique ID number and I can do a VLOOKUP on the ID to pull their first name, last name, shift, and department. The problem is that I need to pull the column name of the areas that they are trained in. I've been fighting with Index / Match, SEARCH, VLOOKUP, and HLOOKUP functions but I cannot get it right. Here's the logic:

1. Insert ID
2. Populate first name, last name, shift, department, and then list out areas they are trained in

FUTURE STATE:

I enter the ID number in A3 and the rest of the information populates. I'm having trouble figuring out how to search for non-empty cell and then displaying the column and then also displaying the contents of the Cell

	A	B	C	D	E
1						This is where I need help in this column. If the cell is not empty, the display the column name	If the cell is NOT empty, display the contents of the cell.
2	ID	FIRST	LAST	Shift	Department	Areas Trained	Icon
3	89778	Steve	Smith	Days	Receiving	Receiving	X
4						Forklift	X
5						Putaways	NT
6
7

If there is any way you guys can help me with this, I would be super grateful. With over 300 names, it's very hard manage and hunt and find it all.
Thanks everyone for your help!

- Humility36

 

[sandy666]

something like this?

or

with Power Query aka Get&Transform

// Table1
let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    UOC = Table.UnpivotOtherColumns(Source, {"ID", "First Name", "Last Name", "Shift", "Department"}, "Attribute", "Value")
in
    UOC

Pivot Table (blue) is created from Query Table (green)

 

[humility36]

@sandy666
Thank you for your prompt reply. Yes. The blue pivot table is what I'm looking for. Unfortunately, my information is not formatted like the green table you have displayed. Is there a way for me to convert, transpose my spreadsheet to to make it look like your green table? Currently, my information is formatted like my example with the column name and then marked with attributes.

I have over 300 employees that have different attributes and manually converting it seems overwhelming, but I fear I might have to do that so that I can get the pivot table you have created from the power query.

Let me know. I really appreciate your help.

 

[sandy666]

Below Pivot Table (blue) you can see M-code (Power Query aka Get&Transform) which create green table
so you can stay with green table or create Pivot Table from Query Table

again:

// Table1
let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    UOC = Table.UnpivotOtherColumns(Source, {"ID", "First Name", "Last Name", "Shift", "Department"}, "Attribute", "Value")
in
    UOC

select your source table
from Data tab select From Table (it will open Power Query Editor)
select columns with Ctrl key: ID, First Name, Last Name, Shift, Department
from menu find: Transform - Unpivot Columns - Unpivot Other Columns

 

[sandy666]

continue from above...
then Close&Load To...
choose Connection Only
Ok
then
Insert - Pivot Table

choose Query Table suitable to yours
and set layout of Pivot Table as you wish