Need Help with Separating Data From Excel File

[cew75]

Hey guys, I'm not sure if Im posting in the right section, but I really hope you guys can help me with this!!! I work at a college & I have an excel file for my contracts that we do to pay the professors for the classes they teach in the summer. The file is a list of the contracts created & I import this file into my access database. A contract can have up to 10 courses on it. So the main identifier in the excel file is the WFID which is the contract ID & 1 row is created for that WFID. However, the CRN is the identifying factor for me, so I need an individual row for each CRN, rather than an individual row for a WFID # with multiple CRNs. I hope this makes sense...


Does anyone know how I could separate the data in excel or have access do it for me? Right now I have to do alot of sorting, copying, pasting & deleting in the excel file in order to get what I want, which takes quite a bit of time since this file typically has over 500 rows of data... So I very much appreciate any help you guys can give!!


Oh & how do I attach the excel file so you to get an idea of what I need?


Thanks again guys!!

Also posted here https://www.mrexcel.com/forum/microsoft-access/1080847-need-help-separating-data-excel-file.html

 

[Kamolga]

We can't attach file. You can share it using onedrive or dropbox links. An other easy way is to take a screen shot, upload it https://imgbb.com/ and paste the given url.

Indeed need to see data samples (one as input and the other as desired output) to determine best solution: pivot table, excel macro/vba or access query.

But if I understand you well, you don't add excel to a datamodel, nor create a link to access DB to excel document but you rather copy-paste excel table to Access table?

 

[SpillerBD]

....so I need an individual row for each CRN, rather than an individual row for a WFID # with multiple CRNs. I hope this makes sense...

It sounds like you don't have a Proper Data Set to begin with which would have a row/record per CRN.
If you have two Tables that have the CRN referencing the WFID and a seperate Table with the additional WFID details, we would join these through the DATA MODEL and generate a Pivot Table for all the reporting needs.

Mike hits a lot of these "basics" in Basic Business Analytics #12

 

[cew75]

Kamolga
I will see about getting a link for the file... Right now I don't link the excel table to the access, I just cut & paste into a table and then run an update query to insert the data into my main table. But I would definitely link the file if I could figure out how to get it to do what I need it to do... Maybe you could see what I need when I share links...

SpillerBD
I wish I had a choice on how I recieved the data. If I did, I would definitely have them give a separate line for the CRN rather than all the CRNS on 1 line for the WFID... But it is how the college gives the data... Ugggghhhh... I kind of understand what you said we could do, & I think I've tried to do something similar, but I couldn't figure out how it to reference all the CRNs because there are too many columns to pull them from...

I'm going now to work on getting yall a link for my files...

 

[cew75]

Ok.. Here is Dropbox Link

https://www.dropbox.com/sh/lh5uey3lneschti/AABHwJm52HS-0c_2z-oBuqlFa?dl=0


Original WWFID.xlsx is how i get the data originally
upWWFID.xlsx is how i have to change the data to paste into access
SSFA is thge access database

I paste the upWWFID data into the upWWFID table... then I run the following queries to update the data in *Classes with the data from the upWWFID table

Class7-upWWFID1
Class7-upWWFID2
Class7-upWWFID3
Class7-upWWFID4

I have to run that query 1-4 because I have multiple instructors teaching the class. the CRN is the primary in *Classes, which is the course number...

I know this is a mess, but maybe you guys can help me reformat my database... I am pretty good with access in the sense that I have learned on my own & have created some pretty complex queries, but I totally dont have a good grasp on formatting my tables & things like that. Basically, I've messed around enough to understand most of the help you can offer, but not good enough to have figured this out on my own...

I hope this is enough to help yall understand what I'm dealing with

 

[rlv01]

It seems like you want to solve this by running a query, but it can also be accomplished by a straightforward VBA exercise.

Sub ReformatData()
    Dim WB As Workbook
    Dim WS As Worksheet, DestWS As Worksheet
    Dim RangeOfCells As Range, Header As Range
    Dim DestR As Range
    Dim CrnData As Range
    Dim R As Range
    Dim I As Long
    Dim ColNo As Long

    Application.ScreenUpdating = False
    Set WB = ThisWorkbook
    Set WS = WB.Worksheets("xxx Original WWFID")      'original data
    Set DestWS = WB.Worksheets("Reformatted")         'sheet to put the reformatted data on

    DestWS.Cells.Clear
    WS.Range("A1").EntireRow.Copy DestWS.Range("A1")

    With WS
        Set Header = .Range(.Cells(1, 1), .Cells(1, .Columns.Count).End(xlToLeft))
    End With

    For Each R In Header
        If R.Value = "crn1" Then Find the             'crn column
        ColNo = R.Column
    End If
Next R

'process the data
If ColNo > 0 Then
    For I = 0 To 27 Step 3
        With WS
            Set RangeOfCells = .Range(.Cells(2, ColNo + I), .Cells(.Rows.Count, ColNo + I).End(xlUp))
        End With

        For Each R In RangeOfCells
            With R
                Set CrnData = .Range("A1").Resize(, 3)
            End With

            With DestWS
                If CrnData.Range("A1").Value <> "na" Then
                    Set DestR = .Cells(.Rows.Count, "A").End(xlUp).Offset(1)
                    R.EntireRow.Copy DestR
                    CrnData.Copy DestR.Offset(, ColNo - 1)
                Else
                End If
            End With
        Next R
    Next I

    'delete the irrelevant columns
    Set RangeOfCells = DestWS.Columns(ColNo + 3).Resize(, 30)
    RangeOfCells.Delete
Else
    MsgBox "Key column not found"
End If
Application.ScreenUpdating = True
End Sub

 

[SpillerBD]

127 columns.
You will want to use PowerQuery (Get and Transform).
I can see a couple ways of going about.
1. You can import those columns that are not the CRN related values. Then load the CRN values using ten individual queries and then append those queries together.
2. Load the entire Table and use the Unpivot methods. Because you will need to combine and split columns at various points of that process, you will have many steps.

I believe your example upload file missed two records based on my initial work using PowerQuery and an Unpivot method. My results were for 47 CRNs(?)

Gil Raviv has a new book that goes into the Unpivot feature with several examples. I like buying thru Pearson or the Microsoft Press since I often get the digital versions with the printed books.

 

[cew75]

Rlv01 - I have worked in VBA before in this database, but Im afraid I need to ask how I should enter your code...

Can't wait to see what it does!!!

It seems like you want to solve this by running a query, but it can also be accomplished by a straightforward VBA exercise.

 

[cew75]

SpillerBD Thank you for those links to that book!! I just ordered it from Pearson... Got the printed book & the e-book for half of what the MS Press was listing it for & I even paid to have it over-nighted!!

127 columns.
You will want to use PowerQuery (Get and Transform).
I can see a couple ways of going about.
1. You can import those columns that are not the CRN related values. Then load the CRN values using ten individual queries and then append those queries together.
2. Load the entire Table and use the Unpivot methods. Because you will need to combine and split columns at various points of that process, you will have many steps.

I believe your example upload file missed two records based on my initial work using PowerQuery and an Unpivot method. My results were for 47 CRNs(?)

Gil Raviv has a new book that goes into the Unpivot feature with several examples. I like buying thru Pearson or the Microsoft Press since I often get the digital versions with the printed books.

 

[rlv01]

Rlv01 - I have worked in VBA before in this database, but Im afraid I need to ask how I should enter your code...

Paste it into a code module in your "Original WWFID.xlx" workbook and run it.

Sub ReformatData()
    Dim WB As Workbook
    Dim WS As Worksheet, DestWS As Worksheet
    Dim RangeOfCells As Range, Header As Range
    Dim DestR As Range
    Dim CrnData As Range
    Dim R As Range
    Dim I As Long
    Dim ColNo As Long

    Set WB = ThisWorkbook

    For Each WS In WB.Worksheets
        If WS.Name = "Reformatted" Then
            Application.DisplayAlerts = False
            WS.Delete
            Application.DisplayAlerts = False
        End If
    Next WS

    Set WS = WB.Worksheets("xxx Original WWFID")      'original data
    Set DestWS = WB.Worksheets.Add(After:=WB.Worksheets(1))
    DestWS.Name = "Reformatted"                       'sheet to put the reformatted data on
    DestWS.Activate
    WS.Range("A1").EntireRow.Copy DestWS.Range("A1")


    With WS
        Set Header = .Range(.Cells(1, 1), .Cells(1, .Columns.Count).End(xlToLeft))
    End With

    For Each R In Header
        If R.Value = "crn1" Then                      ' Find the crn column
            ColNo = R.Column
        End If
    Next R

    'process the data
    If ColNo > 0 Then
        Application.ScreenUpdating = False
        For I = 0 To 27 Step 3
            With WS
                Set RangeOfCells = .Range(.Cells(2, ColNo + I), .Cells(.Rows.Count, ColNo + I).End(xlUp))
            End With

            For Each R In RangeOfCells
                With R
                    Set CrnData = .Range("A1").Resize(, 3)
                End With

                With DestWS
                    If CrnData.Range("A1").Value <> "na" Then
                        Set DestR = .Cells(.Rows.Count, "A").End(xlUp).Offset(1)
                        R.EntireRow.Copy DestR
                        CrnData.Copy DestR.Offset(, ColNo - 1)
                    Else
                    End If
                End With
            Next R
        Next I

        'delete the irrelevant columns
        Set RangeOfCells = DestWS.Columns(ColNo + 3).Resize(, 28)
        RangeOfCells.Delete
        DestWS.Columns.AutoFit
        DestWS.Range("AI1").Select
        Application.ScreenUpdating = True
        MsgBox "Completed"
    Else
        MsgBox "Key column not found"
    End If
End Sub