Vertical to Horizontal to get 3 out of 6

[mrebholz72]

First, I want to apologize for not even knowing what to call this. It has made searching help files rather difficult. Hopefully somebody can point me in the right direction.

I have a list of training that our staff are either enrolled in, taking, or completed (generated from our PeopleSoft System). Our 2nd level of training requires completion of 3 out of 6 courses.

I have a data file that each row is a record for an individual and a specific course. So, I have multiple rows for each person.

I want to get data in a horizontal format, so that I can have a single row for each employee, course 1 status, course 2 status, course 3 status, etc.... (I think that will be the best way to be able to get if they have completed 3 out of 6 from the course list.)

A sample data source should be below.

Thanks for any help to point me in the right direction.

{Using Windows XP - Excel 2003 SP1}

Mike Rebholz

Training Example Sheet.xls
	A	B	C	D	E	F	G
1	ID	FirstName	LastName	Course	Enrollment	StartDate	EndDate
2	123456789	John	Doe	LEV1A	COMPLETE	8/12/1987	9/12/1989
3	123456789	John	Doe	LEV1B	COMPLETE	8/12/1987	9/12/1987
4	123456789	John	Doe	LEV1C	COMPLETE	8/12/1987	9/12/1987
5	123456789	John	Doe	LEV2A	COMPLETE	9/1/1990	10/1/1990
6	123456789	John	Doe	LEV2B	CURRENT	1/1/2005
7	123456789	John	Doe	LEV2D	COMPLETE	10/1/2004	11/1/2004
8	123456789	John	Doe	LEV3A	COMPLETE	6/5/2001	7/5/2001
9	123456789	John	Doe	LEV3B	ENROLLED	9/1/2005
10	213456789	Jim	Smith	COR1A	COMPLETE	8/12/1987	9/12/1987
11	213456789	Jim	Smith	COR1B	COMPLETE	8/12/1987	9/12/1987
12	213456789	Jim	Smith	COR1C	COMPLETE	8/12/1987	9/12/1987
13	312456789	Jane	White	COR1A	COMPLETE	2/1/1994	3/1/1994
14	312456789	Jane	White	COR1B	COMPLETE	2/1/1994	3/1/1994
15	312456789	Jane	White	COR1C	COMPLETE	2/1/1994	3/1/1994
Class Work

 

[Aladin Akyurek]

Depending on the questions/calculations you want to submit this data to, there is probably no need for re-formatting its layout.

 

[Andrew Poulsom]

If you add a column headed Name and concatenate First and Last:

=B2&" "&C2

you could create a Pivot Table with Name and Enrollment as Row fields and Count of Enrollment as the Data field.

 

[mrebholz72]

Basically, I'm wanting to generate a list of what is complete and what is not complete. Since the Level 1 requires all courses, that is easy. But since Level 2 requires 3 out of the 6 courses to be complete, I'm not certain how to build criteria to do that.

 

[Oaktree]

If I'm interpreting your question correctly, you want to normalize your data.

Try these steps:

1) Get a list of unique IDs (go to data --> filter --> advanced filter, select your range of IDs, and copy to a new place)
2) Do the same thing for courses.

Those steps explain A17:A20 and D17:H17 below.

Then, use the following formulas:
1) B18: =VLOOKUP($A18,$A$1:$G$15,MATCH(B$17,$A$1:$G$1,0),0)
2) C18 = B18 copied over
3) D18 =IF(ISERROR(INDEX($E$2:$E$15,MATCH($A18&"@"&D$17,$A$2:$A$15&"@"&$D$2:$D$15,0))),"N/A",INDEX($E$2:$E$15,MATCH($A18&"@"&D$17,$A$2:$A$15&"@"&$D$2:$D$15,0)))

D18 is an array formula, which must be confirmed with CTRL+SHIFT+ENTER

Copy D18 right for as many courses as you have.

Copy B18:H18 down through your IDs.

Book4
	A	B	C	D	E	F	G	H
1	ID	FirstName	LastName	Course	Enrollment	StartDate	EndDate
2	123456789	John	Doe	LEV1A	COMPLETE	32001	32763
3	123456789	John	Doe	LEV1B	COMPLETE	32001	32032
4	123456789	John	Doe	LEV1C	COMPLETE	32001	32032
5	123456789	John	Doe	LEV2A	COMPLETE	33117	33147
6	123456789	John	Doe	LEV2B	CURRENT	38353
7	123456789	John	Doe	LEV2D	COMPLETE	38261	38292
8	123456789	John	Doe	LEV3A	COMPLETE	37047	37077
9	123456789	John	Doe	LEV3B	ENROLLED	38596
10	213456789	Jim	Smith	COR1A	COMPLETE	32001	32032
11	213456789	Jim	Smith	COR1B	COMPLETE	32001	32032
12	213456789	Jim	Smith	COR1C	COMPLETE	32001	32032
13	312456789	Jane	White	COR1A	COMPLETE	34366	34394
14	312456789	Jane	White	COR1B	COMPLETE	34366	34394
15	312456789	Jane	White	COR1C	COMPLETE	34366	34394
16
17	ID	FirstName	LastName	LEV1A	LEV1B	LEV1C	LEV2A	LEV2B
18	123456789	John	Doe	COMPLETE	COMPLETE	COMPLETE	COMPLETE	CURRENT
19	213456789	Jim	Smith	N/A	N/A	N/A	N/A	N/A
20	312456789	Jane	White	N/A	N/A	N/A	N/A	N/A
Sheet1

 

[mrebholz72]

Oaktree,

Yes, that is what I'm looking for. I am headed into a meeting, so will try the formula you gave following later this afternoon.

Thank you.

 

[yankee428]

I would go for the simple method. Add an autofilter to your table (Data|Filter|Autofilter). Now, filter by whatever name you want to lookup, then by Enrollment=Complete

 

[Oaktree]

Yankee:

It is true that autofilter is "simple", but also more manually intensive. If there are a large number of people on the list, autofiltering for each person could take hours.

 

[mrebholz72]

Oaktree's solution worked perfectly. This provided exactly what I was looking for.

Thanks for the opportunity to learn something new.

Mike