excel column data to individual record rows

hello,

I am trying to convert a large excel training spreadsheet into individual records to upload to an online training site.  The table example below is how it is set up with more columns out to column BN on my master.

Names are in Rows with title of each training as column headers out to column BN on the original.  (copy paste did not hold spacing but you get the idea, training 1-3 are column headers with the dates below them for the respective training type date completed.

LN      FN      EMPL      Training 1      Training 2      Training 3
Addy      John      123      5/22/2013      3/23/2012      12/21/2010
Bailey      Mark      456      need      4/19/2012      12/30/2010
Smith      Jerry      234      9/17/2013            1/1/2012
Wines      Charles      325      5/23/2013      3/21/2012      4/19/2011

Trying to convert the data to get one person per training record per row.

LN      FN      EMPL      Type      Date completed
Addy      John      123      Training 1      5/22/2013
Addy      John      123      Training 2      3/23/2012
Addy      John      123      Training 3      12/21/2010
Bailey      Mark      456      Training 1      need
Bailey      Mark      456      Training 2      4/19/2012
Bailey      Mark      456      Training 3      12/30/2010
Smith      Jerry      234      Training 1      9/17/2013
Smith      Jerry      234      Training 2      
Smith      Jerry      234      Training 3      1/1/2012
Wines      Charles      325      Training 1      5/23/2013
Wines      Charles      325      Training 2      3/21/2012
Wines      Charles      325      Training 3      4/19/2011

I've tried a pivot table but can not get all the training header columns to merge under one column.  thank you.
Training-record-sample.xls
johndeerejohnAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Rey Obrero (Capricorn1)Commented:
in access you can do this  with

Select LN,FN,EMPL, 'Training 1' As Type, [Training 1] As Completed From TableA  
union all
Select LN,FN,EMPL, 'Training 2' As Type, [Training 2] As Completed From TableA    
union all
Select LN,FN,EMPL, 'Training 3' As Type, [Training 3] As Completed From TableA
0
johndeerejohnAuthor Commented:
I imported the table into access but have limited experience with it.  If you can give me a few general instructions, I should be able to figure that statement above out.

where do i enter or select the above?  thank you. t
0
byundtMechanical EngineerCommented:
The following macro will normalize the data for you (arrange it into the requested columns), overwriting the original data. The macro assumes that you have header labels in row 1, and that the data starts in cell A2. Install the macro in a regular module sheet.
Sub Normalizer()
Dim rg As Range
Dim i As Long, ii As Long, j As Long, jCols As Long, k As Long, n As Long, nCols As Long, nTrainingCols As Long
Dim vHeaders As Variant, vResults As Variant, vInputs As Variant
nTrainingCols = 3       'Training columns must be on right side of table
Set rg = ActiveSheet.Range("A1").CurrentRegion
nCols = rg.Columns.Count
jCols = nCols - nTrainingCols
vHeaders = rg.Cells(1, jCols + 1).Resize(1, nTrainingCols).Value
rg.Cells(1, jCols + 1).Resize(1, nTrainingCols).ClearContents
rg.Cells(1, jCols + 1).Value = "Type"
rg.Cells(1, jCols + 2).Value = "Date completed"
n = rg.Rows.Count - 1   'Assumes data has header labels in first row
Set rg = rg.Offset(1, 0).Resize(n, nCols)
rg.Borders.LineStyle = xlNone
n = rg.Rows.Count
vInputs = rg.Value
ReDim vResults(1 To nTrainingCols * n, 1 To jCols + 2)
For i = 1 To n
    For j = 1 To nTrainingCols
        ii = (i - 1) * nTrainingCols + j
        For k = 1 To jCols
            vResults(ii, k) = vInputs(i, k)
        Next
        vResults(ii, jCols + 1) = vHeaders(1, j)
        vResults(ii, jCols + 2) = vInputs(i, jCols + j)
    Next
Next
rg.ClearContents
rg.Resize(ii, jCols + 2).Value = vResults
End Sub

Open in new window

Training-record-sampleQ28242359.xls
0
Newly released Acronis True Image 2019

In announcing the release of the 15th Anniversary Edition of Acronis True Image 2019, the company revealed that its artificial intelligence-based anti-ransomware technology – stopped more than 200,000 ransomware attacks on 150,000 customers last year.

Rey Obrero (Capricorn1)Commented:
<where do i enter or select the above?>

the one i posted is called a union query..

create a new query, switch to SQL view

copy and paste the following

Select LN,FN,EMPL, 'Training 1' As Type, [Training 1] As Completed From TableA  
union all
Select LN,FN,EMPL, 'Training 2' As Type, [Training 2] As Completed From TableA    
union all
Select LN,FN,EMPL, 'Training 3' As Type, [Training 3] As Completed From TableA
 

change the name of the table "TableA" to the name of your table

run the query.

.
0
johndeerejohnAuthor Commented:
thank you, not working well, sorry..  in the above, is it possible to list cell references instead of training 1, 2 ,3?

I'm much better versed in excel than Access.  

I've included a training sample 2.xlsx here.

Trying to get one name per row per class and date.
Each different training class date would have it's own row with one class title and its date completed.

LN, FN, MI, EMPL, Status, Active, Postion, Supv, DOB, 2013 Hearing, date
LN, FN, MI, EMPL, Status, Active, Postion, Supv, DOB, 2012 Hearing, date
LN, FN, MI, EMPL, Status, Active, Postion, Supv, DOB, 2011 Hearing, date

then it can be uploaded with each class and date as a separate record..
thank you very much.  John

I tried the macro above but it kept all classes in one row and duplicated the rows.   I like macros if it could just take one class and then break and put the next class on a different row.
training-sample-2.xlsx
0
byundtMechanical EngineerCommented:
When you tested the macro, did you change the number of training classes from 3 to 9? The macro isn't smart enough to be able to count the number of training classes--you need to make the change in the commented statement below:
nTrainingCols = 3     'Training columns must be on right side of table

Open in new window


I made the change to 9 training classes for you in the code below. I also turned screen updating off to prevent an annoying flicker while the macro runs with certain versions of Excel.
Sub Normalizer()
Dim rg As Range
Dim i As Long, ii As Long, j As Long, jCols As Long, k As Long, n As Long, nCols As Long, nTrainingCols As Long
Dim vHeaders As Variant, vResults As Variant, vInputs As Variant
Application.ScreenUpdating = False
nTrainingCols = 9       'Training columns must be on right side of table
Set rg = ActiveSheet.Range("A1").CurrentRegion
nCols = rg.Columns.Count
jCols = nCols - nTrainingCols
vHeaders = rg.Cells(1, jCols + 1).Resize(1, nTrainingCols).Value
rg.Cells(1, jCols + 3).Resize(1, nTrainingCols - 2).Borders.LineStyle = xlNone
rg.Cells(1, jCols + 1).Resize(1, nTrainingCols).ClearContents
rg.Cells(1, jCols + 1).Value = "Type"
rg.Cells(1, jCols + 2).Value = "Date completed"
n = rg.Rows.Count - 1   'Assumes data has header labels in first row
Set rg = rg.Offset(1, 0).Resize(n, nCols)
rg.Borders.LineStyle = xlNone
n = rg.Rows.Count
vInputs = rg.Value
ReDim vResults(1 To nTrainingCols * n, 1 To jCols + 2)
For i = 1 To n
    For j = 1 To nTrainingCols
        ii = (i - 1) * nTrainingCols + j
        For k = 1 To jCols
            vResults(ii, k) = vInputs(i, k)
        Next
        vResults(ii, jCols + 1) = vHeaders(1, j)
        vResults(ii, jCols + 2) = vInputs(i, jCols + j)
    Next
Next
rg.ClearContents
rg.Resize(ii, jCols + 2).Value = vResults
End Sub

Open in new window

training-sample-2-Q28242359.xlsm
0
johndeerejohnAuthor Commented:
I will experiment, as a macro, I cannot undo. Is there a way I can make the macro results copy to a new sheet/tab?  Thanks again. J
0
byundtMechanical EngineerCommented:
Macro now puts results on a newly added worksheet.
Sub Normalizer()
Dim rg As Range, rgDest As Range
Dim i As Long, ii As Long, j As Long, jCols As Long, k As Long, n As Long, nCols As Long, nTrainingCols As Long
Dim vHeaders As Variant, vResults As Variant, vInputs As Variant
Dim ws As Worksheet
Application.ScreenUpdating = False
nTrainingCols = 9       'Training columns must be on right side of table
Set rg = ActiveSheet.Range("A1").CurrentRegion
nCols = rg.Columns.Count
jCols = nCols - nTrainingCols
vHeaders = rg.Cells(1, jCols + 1).Resize(1, nTrainingCols).Value

Set ws = ActiveWorkbook.Worksheets.Add(After:=ActiveWorkbook.ActiveSheet)
Set rgDest = ws.Range(rg.Rows(1).Address)
rg.Cells(1, 1).Resize(1, jCols + 2).Copy rgDest
rgDest.Cells(1, jCols + 1).Value = "Type"
rgDest.Cells(1, jCols + 2).Value = "Date completed"
n = rg.Rows.Count - 1   'Assumes data has header labels in first row
Set rg = rg.Offset(1, 0).Resize(n, nCols)
n = rg.Rows.Count
vInputs = rg.Value
ReDim vResults(1 To nTrainingCols * n, 1 To jCols + 2)
For i = 1 To n
    For j = 1 To nTrainingCols
        ii = (i - 1) * nTrainingCols + j
        For k = 1 To jCols
            vResults(ii, k) = vInputs(i, k)
        Next
        vResults(ii, jCols + 1) = vHeaders(1, j)
        vResults(ii, jCols + 2) = vInputs(i, jCols + j)
    Next
Next
rgDest.Offset(1, 0).Resize(ii, jCols + 2).Value = vResults
rgDest.EntireColumn.AutoFit
End Sub

Open in new window

training-sample-2-Q28242359.xlsm
0
johndeerejohnAuthor Commented:
That works like a charm.   I actually have 57 training records.   a couple of questions.

If i add more demographic columns, does it see the overall range and back out the number of training columns based on what is entered in the formula?  I changed 9 to 57 and it works great.

- I think the last tweak is not to have it add a row or skip the row for the person if has no training for that subject.    It ends up with alot of blank training record rows.   I would have to sort them and delete all the blank training records.

that should do it.  Thank you for the new worksheet addition.   thank you so far.
excel for me works great for all involved.  J
0
byundtMechanical EngineerCommented:
The macro sees all the columns of data (assuming they are contiguous).

Since you have changed the number of training classes three times on me, I changed the macro so you select the training columns. They still need to be on the right, however.

I also added changes so it will not create a row for classes not yet taken.
Sub Normalizer()
Dim rg As Range, rgDest As Range
Dim i As Long, ii As Long, j As Long, jCols As Long, k As Long, n As Long, nCols As Long, nTrainingCols As Long
Dim vHeaders As Variant, vResults As Variant, vInputs As Variant
Dim ws As Worksheet
On Error Resume Next
Set rg = Application.InputBox("Please select the training columns", Type:=8)
On Error GoTo 0
If rg Is Nothing Then Exit Sub

Application.ScreenUpdating = False
nTrainingCols = rg.Columns.Count        'Training columns must be on right side of table
Set rg = ActiveSheet.Range("A1").CurrentRegion
nCols = rg.Columns.Count
jCols = nCols - nTrainingCols
vHeaders = rg.Cells(1, jCols + 1).Resize(1, nTrainingCols).Value

Set ws = ActiveWorkbook.Worksheets.Add(After:=ActiveWorkbook.ActiveSheet)
Set rgDest = ws.Range(rg.Rows(1).Address)
rg.Cells(1, 1).Resize(1, jCols + 2).Copy rgDest
rgDest.Cells(1, jCols + 1).Value = "Type"
rgDest.Cells(1, jCols + 2).Value = "Date completed"
n = rg.Rows.Count - 1   'Assumes data has header labels in first row
Set rg = rg.Offset(1, 0).Resize(n, nCols)
n = rg.Rows.Count
vInputs = rg.Value
ReDim vResults(1 To nTrainingCols * n, 1 To jCols + 2)
For i = 1 To n
    For j = 1 To nTrainingCols
        If vInputs(i, jCols + j) <> "" Then
            ii = ii + 1
            For k = 1 To jCols
                vResults(ii, k) = vInputs(i, k)
            Next
            vResults(ii, jCols + 1) = vHeaders(1, j)
            vResults(ii, jCols + 2) = vInputs(i, jCols + j)
        End If
    Next
Next
rgDest.Offset(1, 0).Resize(ii, jCols + 2).Value = vResults
rgDest.EntireColumn.AutoFit
End Sub

Open in new window

training-sample-2-Q28242359.xlsm
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
johndeerejohnAuthor Commented:
that works great.  I like the selection box for columns.  It threw about two or three blank not training rows or with nothing in the date completed box.   I cleaned up the header rows.  we have been using this training spreadsheet for years so maybe the data is a bit dirty?

what makes it skip that row?   a blank "" entry?   maybe a space or something held it up?

Overall, what I was looking at hours of typing that was solved by a macro.   I'll study the macro to learn more about it.  
thank you much.  j
0
johndeerejohnAuthor Commented:
this was a great work and use of this site.   excellent job by all.
0
byundtMechanical EngineerCommented:
If there is a space in the cell, it's not considered blank. The macro can test for this possibility by changing statement 30 (numbers appear in the code snippet in the Comment) to:
        If Trim(vInputs(i, jCols + j)) <> "" Then

Open in new window

0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.