Rearrange data from another excel spreadsheet

[jimgima]

I'm a training manager for the Air Force and I'm trying to make my job easier. So, here's the quick of it.

I receive a weekly product that lists everyone's training in my squadron. Here is the format (condensed)

Name____Emp#__Class____Due Date
Jim Jan___123____M16____12 dec 08
Jim Jan___123____SABC___20 mar 07
Jim Jan___123____LOAC___10 dec 06
Jim Jan___123____Forklift__20 dec 08
Jim Jan___123____AF Train_12 dec 08
Bob Man__543____M16_____9 mar 07
Bob Man__543____LOAC____10 nov 06
Bob Man__543____SABC____23 dec 07

And so on... it's a few thousand lines of data.

I want to have the data rearranged to look like this:

Name____Emp#____SABC_____LOAC_____Forklift______M16
Jim Jan___123_____20 mar 07__10 dec 06__20 dec 08___12 dec 06
Bob Man__543_____23 dec 07__10 Nov 06_____________9 Mar 07


This way people can look at their name and see if they have any training due. I'm not sure how I would even begin to do this. Please take it easy on me... I'm not an idiot, but I'm not a brain either.

Thanks,
Jim

 

[agihcam]

Hi -

you can do that easily using a Pivot Table from Data > Pivot Table and PivotChart Report. just follow the wizard.
post back if did not make it.

Book1
	A	B	C	D	E	F	G
3	Sum of Due Date		Class
4	Name	Emp#	AF Train	Forklift	LOAC	M16	SABC
5	Bob Man	543			11/10/06	03/09/07	12/23/07
6	Jim Jan	123	12/12/08	12/20/08	12/10/06	12/12/08	03/20/07
Sheet4

 

[stanleydgromjr]

What you are looking for is possible.

What is the sheetname where this data is?
Name____Emp#__Class____Due Date
Jim Jan___123____M16____12 dec 08
Jim Jan___123____SABC___20 mar 07

What is the row, and columns, for the headings?
Name____Emp#__Class____Due Date

What are all the possible 'Class' examples?

And, what column would you like them in?
A________B_______C_________D________E___________F______?
Name____Emp#____SABC_____LOAC_____Forklift______M16


Have a great day,
Stan

 

[jimgima]

In reply to stanleydgromjr

Let me explain a little more. Here is the whole heading of the sheet where the data is:

A: Shop B: Name C: Emp# D: CCode E: Course Title F: Comp G: Due Date H: Event ID

These are on an excel spreadsheet (AAA.xls) which is created weekly by another system.

You asked, "What are all the possible 'Class' examples?"
Well, that's a tricky question. Let's put it this way... I'd like to create my headings once. My headings on my excel product (Self Managed Unit Training.xls) will be:

A: Shop B: Name C: Emp# D: Explosive Safety E:LOAC F:Use Of Force G:Anti-Terror

And so on... There is no need to list them all. If you get me headed in the right direction I'll be able to run with it.

I must add this. The classes are easier to identify by their course code (cc) located in column D. Example...
explosive safety is cc 12062.
LOAC is cc 18024.
Use of Force is cc 11002
Anti-Terror is 18036

I have a friend in my office who is well versed in Visual Basic and will be able to help me a bit. So, like I said, if you give us an example using this information I provided we should be able to figure out how to fit the rest of the data in.

BTW... there are some courses in the AAA.xls that I don't care about so I won't need to import them. I figure if we just ignore that data it would be fine.

Thanks so much for your help. Your country thanks you!

Jim

 

[agihcam]

Hi -
have you tried using pivot table? if you want a macro then record it whike doing the pivot.

 

[stanleydgromjr]

Jim,

Your request keeps getting more complicated. And, I do not think a pivot table would work for you.

You would have to create an Excel Template file, for example, called 'Self Managed Unit Training.xlt', with two sheets, one called 'RawData', and one called 'Report'.

A macro could be written to do the following:

You would have to manually copy the data from 'AAA.xls' into the 'RawData' sheet. Or, you could write code to automatically pull the information in from 'AAA.xls'.

The data in the 'RawData' sheet woudl have to be sorted by 'Name', and then by 'Due Date'.

While the 'Name' field is the same in 'RawData', copy all the fields into the 'Report' sheet (one row). Check each row in 'RawData' and use code for a 'Select Case Statement' to convert the 'CCode' into a column number of where to paste the 'Due Date'.


Have a great day,
Stan

 

[jindon]

Hi
Using the sample as explained.
Activate the sheet in qusetion and run the code
diplay the results in Sheet2 (you need to prepare before you run)
adjust "h_r" to the actual row reference for the header
Let's see if it works

Sub test()
Dim dic As Object, a, e, i As Long, b(), h_r As Long, z As String, MyCc
Set dic = CreateObject("Scripting.Dictionary")
dic.CompareMode=vbTextCompare
h_r = 1 '<- adjust the number to actual header row
With ActiveSheet
   a = .Range(.Cells(h_r,"d"),.Cells(Rows.Count,"d").End(xlUp)).Value
   For Each e In a
      If Not IsEmpty(e) And Not dic.Exists(e) Then dic.add e, Nothing
   Next
   ReDim b(1 To Rows.Count, 1 To dic.count + 3)
   myCc = dic.keys : dic.removeall
   For i = 4 To UBound(b,2) : b(1,i) = myCc(i-4) : Next
   a = .Range("a" & h_r & , .Range("a" & Rows.Count).End(xlUp)).Resize(,7).Value
End With
n = 1
For i = 2 To UBound(a,1)
   z = a(i,1) & ";" & a(i,2) & ";" & a(i,3)
   If Not dic.exists(z) Then
      n = n + 1
      dic.add z, n
   Else
      dic(z) = dic(z) + 1
   End If
   For ii = 3 To UBound(b,2)
      If a(i,4) = b(1,ii) Then b(dic(z),4) = a(i,4) : Exit For
   Next
   b(dic(z),ii)  = a(i,7)
   For ii = 1 To 3 : b(dic(z),ii)=a(i,ii) : Next
Next
Set dic =Nothing
Sheets("Sheet2").Range("a1").Resize(n,UBound(b,2) = b
End Sub

edited : 12:52

 

[jimgima]

thanks for your help

We've managed to get what we needed. thanks for the help.