Structuring data correctly to use in pivottable

muhleebbin

Board Regular
Joined
Sep 30, 2017
Messages
97
Office Version
  1. 365
  2. 2019
  3. 2016
  4. 2013
  5. 2010
Platform
  1. Windows
  2. MacOS
  3. Mobile
  4. Web
Hi,

Could someone please assist me in structuring the following data correctly so that it's usable in a pivot table?

I currently have a sheet where we input data and with a button it takes all relevant data and puts it into another sheet "Data".

NTE Tracking v3.xlsm
ABCDEFGHIJKLMNOPQ
1COR-PCO Number:
2
3Change Order:View Records
4
5RFI/Bulletin/ROM:1of3
6
7Status:
8
9Item #DescriptionMaterial Unit PriceShop LaborSite LaborTotal
10001Test 1----
11002Test 2----
12003Test 3----
13004Test 4----
14005Test 5----
15----
27Site, Extra 01 Subtotal:----
Input
Cell Formulas
RangeFormula
M5M5=COUNTA(Data!A:A)
F10:F15,F27F10=SUM(C10:E10)
C27:E27C27=SUM(C10:C26)
Cells with Data Validation
CellAllowCriteria
K5Whole numberbetween 1 and M5
D7:F7List=Lists!$Y$1:$Y$3


The Data sheet looks like this:

NTE Tracking v3.xlsm
ABCDEFGHIJKLMNOPQRSTUVWXYZAAABACADAEAFAGAHAIAJAKALAMANAOAPAQARASATAUAVAWAXAYAZBABBBCBDBE
1COR-PCO NumberChange OrderRFI/Bulletin/ROMStatusMU 10MU 11MU 12MU 13MU 14MU 15MU 16MU 17MU 18MU 19MU 20MU 21MU 22MU 23MU 24MU 25MU 26SL 10SL 11SL 12SL 13SL 14SL 15SL 16SL 17SL 18SL 19SL 20SL 21SL 22SL 23SL 24SL 25SL 26S 10S 11S 12S 13S 14S 15S 16S 17S 18S 19S 20S 21S 22S 23S 24S 25S 26
209/17/2020 08:08:43TestTest 1Test CO 1Test RFI 1Pending100040000000000000002005000000000000000030600000000000000
309/17/2020 08:09:35TestTest 2Test CO 2Test RFI 2Proposed100004000000000000000020005000000000000000003006000000000000000
409/17/2020 08:10:07TestTest 3Test CO 3Test RFI 3Approved100000400000000000000000200005000000000000000000300060000000000000000
Data


Is there a way to convert the data to look like this?

NTE Tracking v3.xlsm
ABCDEFGHI
1DateUserCOR-PCO NumberChange OrderRFI/Bulletin/ROMStatusColumnRowAmount
209/17/2020 08:08:43TestTest 1Test CO 1Test RFI 1PendingMU1010
309/17/2020 08:08:43TestTest 1Test CO 1Test RFI 1PendingMU110
409/17/2020 08:08:43TestTest 1Test CO 1Test RFI 1PendingMU120
509/17/2020 08:08:43TestTest 1Test CO 1Test RFI 1PendingMU1340
609/17/2020 08:08:43TestTest 1Test CO 1Test RFI 1PendingMU140
709/17/2020 08:08:43TestTest 1Test CO 1Test RFI 1PendingMU150
809/17/2020 08:08:43TestTest 1Test CO 1Test RFI 1PendingMU160
909/17/2020 08:08:43TestTest 1Test CO 1Test RFI 1PendingMU170
1009/17/2020 08:08:43TestTest 1Test CO 1Test RFI 1PendingMU180
1109/17/2020 08:08:43TestTest 1Test CO 1Test RFI 1PendingMU190
1209/17/2020 08:08:43TestTest 1Test CO 1Test RFI 1PendingMU200
1309/17/2020 08:08:43TestTest 1Test CO 1Test RFI 1PendingMU210
1409/17/2020 08:08:43TestTest 1Test CO 1Test RFI 1PendingMU220
1509/17/2020 08:08:43TestTest 1Test CO 1Test RFI 1PendingMU230
1609/17/2020 08:08:43TestTest 1Test CO 1Test RFI 1PendingMU240
1709/17/2020 08:08:43TestTest 1Test CO 1Test RFI 1PendingMU250
1809/17/2020 08:08:43TestTest 1Test CO 1Test RFI 1PendingMU260
1909/17/2020 08:08:43TestTest 1Test CO 1Test RFI 1PendingSL100
2009/17/2020 08:08:43TestTest 1Test CO 1Test RFI 1PendingSL1120
2109/17/2020 08:08:43TestTest 1Test CO 1Test RFI 1PendingSL120
2209/17/2020 08:08:43TestTest 1Test CO 1Test RFI 1PendingSL1350
2309/17/2020 08:08:43TestTest 1Test CO 1Test RFI 1PendingSL140
2409/17/2020 08:08:43TestTest 1Test CO 1Test RFI 1PendingSL150
2509/17/2020 08:08:43TestTest 1Test CO 1Test RFI 1PendingSL160
2609/17/2020 08:08:43TestTest 1Test CO 1Test RFI 1PendingSL170
2709/17/2020 08:08:43TestTest 1Test CO 1Test RFI 1PendingSL180
2809/17/2020 08:08:43TestTest 1Test CO 1Test RFI 1PendingSL190
2909/17/2020 08:08:43TestTest 1Test CO 1Test RFI 1PendingSL200
3009/17/2020 08:08:43TestTest 1Test CO 1Test RFI 1PendingSL210
3109/17/2020 08:08:43TestTest 1Test CO 1Test RFI 1PendingSL220
3209/17/2020 08:08:43TestTest 1Test CO 1Test RFI 1PendingSL230
3309/17/2020 08:08:43TestTest 1Test CO 1Test RFI 1PendingSL240
3409/17/2020 08:08:43TestTest 1Test CO 1Test RFI 1PendingSL250
3509/17/2020 08:08:43TestTest 1Test CO 1Test RFI 1PendingSL260
3609/17/2020 08:08:43TestTest 1Test CO 1Test RFI 1PendingS100
3709/17/2020 08:08:43TestTest 1Test CO 1Test RFI 1PendingS110
3809/17/2020 08:08:43TestTest 1Test CO 1Test RFI 1PendingS1230
3909/17/2020 08:08:43TestTest 1Test CO 1Test RFI 1PendingS1360
4009/17/2020 08:08:43TestTest 1Test CO 1Test RFI 1PendingS140
4109/17/2020 08:08:43TestTest 1Test CO 1Test RFI 1PendingS150
4209/17/2020 08:08:43TestTest 1Test CO 1Test RFI 1PendingS160
4309/17/2020 08:08:43TestTest 1Test CO 1Test RFI 1PendingS170
4409/17/2020 08:08:43TestTest 1Test CO 1Test RFI 1PendingS180
4509/17/2020 08:08:43TestTest 1Test CO 1Test RFI 1PendingS190
4609/17/2020 08:08:43TestTest 1Test CO 1Test RFI 1PendingS200
4709/17/2020 08:08:43TestTest 1Test CO 1Test RFI 1PendingS210
4809/17/2020 08:08:43TestTest 1Test CO 1Test RFI 1PendingS220
4909/17/2020 08:08:43TestTest 1Test CO 1Test RFI 1PendingS230
5009/17/2020 08:08:43TestTest 1Test CO 1Test RFI 1PendingS240
5109/17/2020 08:08:43TestTest 1Test CO 1Test RFI 1PendingS250
5209/17/2020 08:08:43TestTest 1Test CO 1Test RFI 1PendingS260
5309/17/2020 08:09:35TestTest 2Test CO 2Test RFI 2ProposedMU10100
5409/17/2020 08:09:35TestTest 2Test CO 2Test RFI 2ProposedMU110
5509/17/2020 08:09:35TestTest 2Test CO 2Test RFI 2ProposedMU120
5609/17/2020 08:09:35TestTest 2Test CO 2Test RFI 2ProposedMU13400
5709/17/2020 08:09:35TestTest 2Test CO 2Test RFI 2ProposedMU140
5809/17/2020 08:09:35TestTest 2Test CO 2Test RFI 2ProposedMU150
5909/17/2020 08:09:35TestTest 2Test CO 2Test RFI 2ProposedMU160
6009/17/2020 08:09:35TestTest 2Test CO 2Test RFI 2ProposedMU170
6109/17/2020 08:09:35TestTest 2Test CO 2Test RFI 2ProposedMU180
6209/17/2020 08:09:35TestTest 2Test CO 2Test RFI 2ProposedMU190
6309/17/2020 08:09:35TestTest 2Test CO 2Test RFI 2ProposedMU200
6409/17/2020 08:09:35TestTest 2Test CO 2Test RFI 2ProposedMU210
6509/17/2020 08:09:35TestTest 2Test CO 2Test RFI 2ProposedMU220
6609/17/2020 08:09:35TestTest 2Test CO 2Test RFI 2ProposedMU230
6709/17/2020 08:09:35TestTest 2Test CO 2Test RFI 2ProposedMU240
6809/17/2020 08:09:35TestTest 2Test CO 2Test RFI 2ProposedMU250
6909/17/2020 08:09:35TestTest 2Test CO 2Test RFI 2ProposedMU260
7009/17/2020 08:09:35TestTest 2Test CO 2Test RFI 2ProposedSL100
7109/17/2020 08:09:35TestTest 2Test CO 2Test RFI 2ProposedSL11200
7209/17/2020 08:09:35TestTest 2Test CO 2Test RFI 2ProposedSL120
7309/17/2020 08:09:35TestTest 2Test CO 2Test RFI 2ProposedSL13500
7409/17/2020 08:09:35TestTest 2Test CO 2Test RFI 2ProposedSL140
7509/17/2020 08:09:35TestTest 2Test CO 2Test RFI 2ProposedSL150
7609/17/2020 08:09:35TestTest 2Test CO 2Test RFI 2ProposedSL160
7709/17/2020 08:09:35TestTest 2Test CO 2Test RFI 2ProposedSL170
7809/17/2020 08:09:35TestTest 2Test CO 2Test RFI 2ProposedSL180
7909/17/2020 08:09:35TestTest 2Test CO 2Test RFI 2ProposedSL190
8009/17/2020 08:09:35TestTest 2Test CO 2Test RFI 2ProposedSL200
8109/17/2020 08:09:35TestTest 2Test CO 2Test RFI 2ProposedSL210
8209/17/2020 08:09:35TestTest 2Test CO 2Test RFI 2ProposedSL220
8309/17/2020 08:09:35TestTest 2Test CO 2Test RFI 2ProposedSL230
8409/17/2020 08:09:35TestTest 2Test CO 2Test RFI 2ProposedSL240
8509/17/2020 08:09:35TestTest 2Test CO 2Test RFI 2ProposedSL250
8609/17/2020 08:09:35TestTest 2Test CO 2Test RFI 2ProposedSL260
8709/17/2020 08:09:35TestTest 2Test CO 2Test RFI 2ProposedS100
8809/17/2020 08:09:35TestTest 2Test CO 2Test RFI 2ProposedS110
8909/17/2020 08:09:35TestTest 2Test CO 2Test RFI 2ProposedS12300
9009/17/2020 08:09:35TestTest 2Test CO 2Test RFI 2ProposedS13600
9109/17/2020 08:09:35TestTest 2Test CO 2Test RFI 2ProposedS140
9209/17/2020 08:09:35TestTest 2Test CO 2Test RFI 2ProposedS150
9309/17/2020 08:09:35TestTest 2Test CO 2Test RFI 2ProposedS160
9409/17/2020 08:09:35TestTest 2Test CO 2Test RFI 2ProposedS170
9509/17/2020 08:09:35TestTest 2Test CO 2Test RFI 2ProposedS180
9609/17/2020 08:09:35TestTest 2Test CO 2Test RFI 2ProposedS190
9709/17/2020 08:09:35TestTest 2Test CO 2Test RFI 2ProposedS200
9809/17/2020 08:09:35TestTest 2Test CO 2Test RFI 2ProposedS210
9909/17/2020 08:09:35TestTest 2Test CO 2Test RFI 2ProposedS220
10009/17/2020 08:09:35TestTest 2Test CO 2Test RFI 2ProposedS230
10109/17/2020 08:09:35TestTest 2Test CO 2Test RFI 2ProposedS240
10209/17/2020 08:09:35TestTest 2Test CO 2Test RFI 2ProposedS250
10309/17/2020 08:09:35TestTest 2Test CO 2Test RFI 2ProposedS260
10409/17/2020 08:10:07TestTest 3Test CO 3Test RFI 3ApprovedMU101000
10509/17/2020 08:10:07TestTest 3Test CO 3Test RFI 3ApprovedMU110
10609/17/2020 08:10:07TestTest 3Test CO 3Test RFI 3ApprovedMU120
10709/17/2020 08:10:07TestTest 3Test CO 3Test RFI 3ApprovedMU134000
10809/17/2020 08:10:07TestTest 3Test CO 3Test RFI 3ApprovedMU140
10909/17/2020 08:10:07TestTest 3Test CO 3Test RFI 3ApprovedMU150
11009/17/2020 08:10:07TestTest 3Test CO 3Test RFI 3ApprovedMU160
11109/17/2020 08:10:07TestTest 3Test CO 3Test RFI 3ApprovedMU170
11209/17/2020 08:10:07TestTest 3Test CO 3Test RFI 3ApprovedMU180
11309/17/2020 08:10:07TestTest 3Test CO 3Test RFI 3ApprovedMU190
11409/17/2020 08:10:07TestTest 3Test CO 3Test RFI 3ApprovedMU200
11509/17/2020 08:10:07TestTest 3Test CO 3Test RFI 3ApprovedMU210
11609/17/2020 08:10:07TestTest 3Test CO 3Test RFI 3ApprovedMU220
11709/17/2020 08:10:07TestTest 3Test CO 3Test RFI 3ApprovedMU230
11809/17/2020 08:10:07TestTest 3Test CO 3Test RFI 3ApprovedMU240
11909/17/2020 08:10:07TestTest 3Test CO 3Test RFI 3ApprovedMU250
12009/17/2020 08:10:07TestTest 3Test CO 3Test RFI 3ApprovedMU260
12109/17/2020 08:10:07TestTest 3Test CO 3Test RFI 3ApprovedSL100
12209/17/2020 08:10:07TestTest 3Test CO 3Test RFI 3ApprovedSL112000
12309/17/2020 08:10:07TestTest 3Test CO 3Test RFI 3ApprovedSL120
12409/17/2020 08:10:07TestTest 3Test CO 3Test RFI 3ApprovedSL135000
12509/17/2020 08:10:07TestTest 3Test CO 3Test RFI 3ApprovedSL140
12609/17/2020 08:10:07TestTest 3Test CO 3Test RFI 3ApprovedSL150
12709/17/2020 08:10:07TestTest 3Test CO 3Test RFI 3ApprovedSL160
12809/17/2020 08:10:07TestTest 3Test CO 3Test RFI 3ApprovedSL170
12909/17/2020 08:10:07TestTest 3Test CO 3Test RFI 3ApprovedSL180
13009/17/2020 08:10:07TestTest 3Test CO 3Test RFI 3ApprovedSL190
13109/17/2020 08:10:07TestTest 3Test CO 3Test RFI 3ApprovedSL200
13209/17/2020 08:10:07TestTest 3Test CO 3Test RFI 3ApprovedSL210
13309/17/2020 08:10:07TestTest 3Test CO 3Test RFI 3ApprovedSL220
13409/17/2020 08:10:07TestTest 3Test CO 3Test RFI 3ApprovedSL230
13509/17/2020 08:10:07TestTest 3Test CO 3Test RFI 3ApprovedSL240
13609/17/2020 08:10:07TestTest 3Test CO 3Test RFI 3ApprovedSL250
13709/17/2020 08:10:07TestTest 3Test CO 3Test RFI 3ApprovedSL260
13809/17/2020 08:10:07TestTest 3Test CO 3Test RFI 3ApprovedS100
13909/17/2020 08:10:07TestTest 3Test CO 3Test RFI 3ApprovedS110
14009/17/2020 08:10:07TestTest 3Test CO 3Test RFI 3ApprovedS123000
14109/17/2020 08:10:07TestTest 3Test CO 3Test RFI 3ApprovedS136000
14209/17/2020 08:10:07TestTest 3Test CO 3Test RFI 3ApprovedS140
14309/17/2020 08:10:07TestTest 3Test CO 3Test RFI 3ApprovedS150
14409/17/2020 08:10:07TestTest 3Test CO 3Test RFI 3ApprovedS160
14509/17/2020 08:10:07TestTest 3Test CO 3Test RFI 3ApprovedS170
14609/17/2020 08:10:07TestTest 3Test CO 3Test RFI 3ApprovedS180
14709/17/2020 08:10:07TestTest 3Test CO 3Test RFI 3ApprovedS190
14809/17/2020 08:10:07TestTest 3Test CO 3Test RFI 3ApprovedS200
14909/17/2020 08:10:07TestTest 3Test CO 3Test RFI 3ApprovedS210
15009/17/2020 08:10:07TestTest 3Test CO 3Test RFI 3ApprovedS220
15109/17/2020 08:10:07TestTest 3Test CO 3Test RFI 3ApprovedS230
15209/17/2020 08:10:07TestTest 3Test CO 3Test RFI 3ApprovedS240
15309/17/2020 08:10:07TestTest 3Test CO 3Test RFI 3ApprovedS250
15409/17/2020 08:10:07TestTest 3Test CO 3Test RFI 3ApprovedS260
Data3


As you could imagine, the pivot table using the first data is disturbingly disgusting whereas the second one would give me a nice clean pivot that looks very much like the original data. Is it even necessary to have the code that writes the first data as it is currently or could that be rewritten to just output it in this second more ideal format?

VBA Code:
Sub UpdateLogWorksheet()

    Dim historyWks As Worksheet
    Dim inputWks As Worksheet

    Dim nextRow As Long
    Dim oCol As Long

    Dim myRng As Range
    Dim myCopy As String
    Dim myCell As Range
    
    'cells to copy from Input sheet - some contain formulas
    myCopy = "D1,D3,D5,D7,C10,C11,C12,C13,C14,C15,C16,C17,C18,C19,C20,C21,C22,C23,C24,C25,C26,D10,D11,D12,D13,D14,D15,D16,D17,D18,D19,D20,D21,D22,D23,D24,D25,D26,E10,E11,E12,E13,E14,E15,E16,E17,E18,E19,E20,E21,E22,E23,E24,E25,E26"

    Set inputWks = Worksheets("Input")
    Set historyWks = Worksheets("Data")

    With historyWks
        nextRow = .Cells(.Rows.Count, "A").End(xlUp).Offset(1, 0).Row
    End With

    With inputWks
        Set myRng = .Range(myCopy)

        If Application.CountA(myRng) <> myRng.Cells.Count Then
            MsgBox "Please fill in all the cells!"
            Exit Sub
        End If
    End With

    With historyWks
        With .Cells(nextRow, "A")
            .Value = Now
            .NumberFormat = "mm/dd/yyyy hh:mm:ss"
        End With
        .Cells(nextRow, "B").Value = Application.UserName
        oCol = 3
        For Each myCell In myRng.Cells
            historyWks.Cells(nextRow, oCol).Value = myCell.Value
            oCol = oCol + 1
        Next myCell
    End With
    
    'clear input cells that contain constants
    With inputWks
      On Error Resume Next
         With .Range(myCopy).Cells.SpecialCells(xlCellTypeConstants)
              .ClearContents
              Application.GoTo .Cells(1) ', Scroll:=True
         End With
      On Error GoTo 0
    End With
    
    Range("D1:F1").ClearContents
    Range("D3:F3").ClearContents
    Range("D5:F5").ClearContents
    Range("D7:F7").ClearContents
    Range("C10") = "0"
    Range("C11") = "0"
    Range("C12") = "0"
    Range("C13") = "0"
    Range("C14") = "0"
    Range("C15") = "0"
    Range("C16") = "0"
    Range("C17") = "0"
    Range("C18") = "0"
    Range("C19") = "0"
    Range("C20") = "0"
    Range("C21") = "0"
    Range("C22") = "0"
    Range("C23") = "0"
    Range("C24") = "0"
    Range("C25") = "0"
    Range("C26") = "0"
    Range("D10") = "0"
    Range("D11") = "0"
    Range("D12") = "0"
    Range("D13") = "0"
    Range("D14") = "0"
    Range("D15") = "0"
    Range("D16") = "0"
    Range("D17") = "0"
    Range("D18") = "0"
    Range("D19") = "0"
    Range("D20") = "0"
    Range("D21") = "0"
    Range("D22") = "0"
    Range("D23") = "0"
    Range("D24") = "0"
    Range("D25") = "0"
    Range("D26") = "0"
    Range("E10") = "0"
    Range("E11") = "0"
    Range("E12") = "0"
    Range("E13") = "0"
    Range("E14") = "0"
    Range("E15") = "0"
    Range("E16") = "0"
    Range("E17") = "0"
    Range("E18") = "0"
    Range("E19") = "0"
    Range("E20") = "0"
    Range("E21") = "0"
    Range("E22") = "0"
    Range("E23") = "0"
    Range("E24") = "0"
    Range("E25") = "0"
    Range("E26") = "0"

End Sub
 

Some videos you may like

Excel Facts

VLOOKUP to Left?
Use =VLOOKUP(A2,CHOOSE({1,2},$Z$1:$Z$99,$Y$1:$Y$99),2,False) to lookup Y values to left of Z values.

sandy666

Well-known Member
Joined
Oct 24, 2015
Messages
7,450
With Power Query aka Get&Transform
DateUserCOR-PCO NumberChange OrderRFI/Bulletin/ROMStatusMU 10MU 11MU 12MU 13MU 14MU 15MU 16MU 17MU 18MU 19MU 20MU 21MU 22MU 23MU 24MU 25MU 26SL 10SL 11SL 12SL 13SL 14SL 15SL 16SL 17SL 18SL 19SL 20SL 21SL 22SL 23SL 24SL 25SL 26S 10S 11S 12S 13S 14S 15S 16S 17S 18S 19S 20S 21S 22S 23S 24S 25S 26
44091.33939TestTest 1Test CO 1Test RFI 1Pending100040000000000000002005000000000000000030600000000000000
44091.33999TestTest 2Test CO 2Test RFI 2Proposed100004000000000000000020005000000000000000003006000000000000000
44091.34036TestTest 3Test CO 3Test RFI 3Approved100000400000000000000000200005000000000000000000300060000000000000000

DateUserCOR-PCO NumberChange OrderRFI/Bulletin/ROMStatusColumnRowAmount
17/09/2020 08:08TestTest 1Test CO 1Test RFI 1PendingMU1010
17/09/2020 08:08TestTest 1Test CO 1Test RFI 1PendingMU110
17/09/2020 08:08TestTest 1Test CO 1Test RFI 1PendingMU120
17/09/2020 08:08TestTest 1Test CO 1Test RFI 1PendingMU1340
17/09/2020 08:08TestTest 1Test CO 1Test RFI 1PendingMU140
17/09/2020 08:08TestTest 1Test CO 1Test RFI 1PendingMU150
17/09/2020 08:08TestTest 1Test CO 1Test RFI 1PendingMU160
17/09/2020 08:08TestTest 1Test CO 1Test RFI 1PendingMU170
17/09/2020 08:08TestTest 1Test CO 1Test RFI 1PendingMU180
17/09/2020 08:08TestTest 1Test CO 1Test RFI 1PendingMU190
17/09/2020 08:08TestTest 1Test CO 1Test RFI 1PendingMU200
17/09/2020 08:08TestTest 1Test CO 1Test RFI 1PendingMU210
17/09/2020 08:08TestTest 1Test CO 1Test RFI 1PendingMU220
17/09/2020 08:08TestTest 1Test CO 1Test RFI 1PendingMU230
17/09/2020 08:08TestTest 1Test CO 1Test RFI 1PendingMU240
17/09/2020 08:08TestTest 1Test CO 1Test RFI 1PendingMU250
17/09/2020 08:08TestTest 1Test CO 1Test RFI 1PendingMU260
17/09/2020 08:08TestTest 1Test CO 1Test RFI 1PendingSL100
17/09/2020 08:08TestTest 1Test CO 1Test RFI 1PendingSL1120
17/09/2020 08:08TestTest 1Test CO 1Test RFI 1PendingSL120
17/09/2020 08:08TestTest 1Test CO 1Test RFI 1PendingSL1350
17/09/2020 08:08TestTest 1Test CO 1Test RFI 1PendingSL140
17/09/2020 08:08TestTest 1Test CO 1Test RFI 1PendingSL150
17/09/2020 08:08TestTest 1Test CO 1Test RFI 1PendingSL160
17/09/2020 08:08TestTest 1Test CO 1Test RFI 1PendingSL170
17/09/2020 08:08TestTest 1Test CO 1Test RFI 1PendingSL180
17/09/2020 08:08TestTest 1Test CO 1Test RFI 1PendingSL190
17/09/2020 08:08TestTest 1Test CO 1Test RFI 1PendingSL200
17/09/2020 08:08TestTest 1Test CO 1Test RFI 1PendingSL210
17/09/2020 08:08TestTest 1Test CO 1Test RFI 1PendingSL220
17/09/2020 08:08TestTest 1Test CO 1Test RFI 1PendingSL230
17/09/2020 08:08TestTest 1Test CO 1Test RFI 1PendingSL240
17/09/2020 08:08TestTest 1Test CO 1Test RFI 1PendingSL250
17/09/2020 08:08TestTest 1Test CO 1Test RFI 1PendingSL260
17/09/2020 08:08TestTest 1Test CO 1Test RFI 1PendingS100
17/09/2020 08:08TestTest 1Test CO 1Test RFI 1PendingS110
17/09/2020 08:08TestTest 1Test CO 1Test RFI 1PendingS1230
17/09/2020 08:08TestTest 1Test CO 1Test RFI 1PendingS1360
17/09/2020 08:08TestTest 1Test CO 1Test RFI 1PendingS140
17/09/2020 08:08TestTest 1Test CO 1Test RFI 1PendingS150
17/09/2020 08:08TestTest 1Test CO 1Test RFI 1PendingS160
17/09/2020 08:08TestTest 1Test CO 1Test RFI 1PendingS170
17/09/2020 08:08TestTest 1Test CO 1Test RFI 1PendingS180
17/09/2020 08:08TestTest 1Test CO 1Test RFI 1PendingS190
17/09/2020 08:08TestTest 1Test CO 1Test RFI 1PendingS200
17/09/2020 08:08TestTest 1Test CO 1Test RFI 1PendingS210
17/09/2020 08:08TestTest 1Test CO 1Test RFI 1PendingS220
17/09/2020 08:08TestTest 1Test CO 1Test RFI 1PendingS230
17/09/2020 08:08TestTest 1Test CO 1Test RFI 1PendingS240
17/09/2020 08:08TestTest 1Test CO 1Test RFI 1PendingS250
17/09/2020 08:08TestTest 1Test CO 1Test RFI 1PendingS260
17/09/2020 08:09TestTest 2Test CO 2Test RFI 2ProposedMU10100
17/09/2020 08:09TestTest 2Test CO 2Test RFI 2ProposedMU110
17/09/2020 08:09TestTest 2Test CO 2Test RFI 2ProposedMU120
17/09/2020 08:09TestTest 2Test CO 2Test RFI 2ProposedMU13400
17/09/2020 08:09TestTest 2Test CO 2Test RFI 2ProposedMU140
17/09/2020 08:09TestTest 2Test CO 2Test RFI 2ProposedMU150
17/09/2020 08:09TestTest 2Test CO 2Test RFI 2ProposedMU160
17/09/2020 08:09TestTest 2Test CO 2Test RFI 2ProposedMU170
17/09/2020 08:09TestTest 2Test CO 2Test RFI 2ProposedMU180
17/09/2020 08:09TestTest 2Test CO 2Test RFI 2ProposedMU190
17/09/2020 08:09TestTest 2Test CO 2Test RFI 2ProposedMU200
17/09/2020 08:09TestTest 2Test CO 2Test RFI 2ProposedMU210
17/09/2020 08:09TestTest 2Test CO 2Test RFI 2ProposedMU220
17/09/2020 08:09TestTest 2Test CO 2Test RFI 2ProposedMU230
17/09/2020 08:09TestTest 2Test CO 2Test RFI 2ProposedMU240
17/09/2020 08:09TestTest 2Test CO 2Test RFI 2ProposedMU250
17/09/2020 08:09TestTest 2Test CO 2Test RFI 2ProposedMU260
17/09/2020 08:09TestTest 2Test CO 2Test RFI 2ProposedSL100
17/09/2020 08:09TestTest 2Test CO 2Test RFI 2ProposedSL11200
17/09/2020 08:09TestTest 2Test CO 2Test RFI 2ProposedSL120
17/09/2020 08:09TestTest 2Test CO 2Test RFI 2ProposedSL13500
17/09/2020 08:09TestTest 2Test CO 2Test RFI 2ProposedSL140
17/09/2020 08:09TestTest 2Test CO 2Test RFI 2ProposedSL150
17/09/2020 08:09TestTest 2Test CO 2Test RFI 2ProposedSL160
17/09/2020 08:09TestTest 2Test CO 2Test RFI 2ProposedSL170
17/09/2020 08:09TestTest 2Test CO 2Test RFI 2ProposedSL180
17/09/2020 08:09TestTest 2Test CO 2Test RFI 2ProposedSL190
17/09/2020 08:09TestTest 2Test CO 2Test RFI 2ProposedSL200
17/09/2020 08:09TestTest 2Test CO 2Test RFI 2ProposedSL210
17/09/2020 08:09TestTest 2Test CO 2Test RFI 2ProposedSL220
17/09/2020 08:09TestTest 2Test CO 2Test RFI 2ProposedSL230
17/09/2020 08:09TestTest 2Test CO 2Test RFI 2ProposedSL240
17/09/2020 08:09TestTest 2Test CO 2Test RFI 2ProposedSL250
17/09/2020 08:09TestTest 2Test CO 2Test RFI 2ProposedSL260
17/09/2020 08:09TestTest 2Test CO 2Test RFI 2ProposedS100
17/09/2020 08:09TestTest 2Test CO 2Test RFI 2ProposedS110
17/09/2020 08:09TestTest 2Test CO 2Test RFI 2ProposedS12300
17/09/2020 08:09TestTest 2Test CO 2Test RFI 2ProposedS13600
17/09/2020 08:09TestTest 2Test CO 2Test RFI 2ProposedS140
17/09/2020 08:09TestTest 2Test CO 2Test RFI 2ProposedS150
17/09/2020 08:09TestTest 2Test CO 2Test RFI 2ProposedS160
17/09/2020 08:09TestTest 2Test CO 2Test RFI 2ProposedS170
17/09/2020 08:09TestTest 2Test CO 2Test RFI 2ProposedS180
17/09/2020 08:09TestTest 2Test CO 2Test RFI 2ProposedS190
17/09/2020 08:09TestTest 2Test CO 2Test RFI 2ProposedS200
17/09/2020 08:09TestTest 2Test CO 2Test RFI 2ProposedS210
17/09/2020 08:09TestTest 2Test CO 2Test RFI 2ProposedS220
17/09/2020 08:09TestTest 2Test CO 2Test RFI 2ProposedS230
17/09/2020 08:09TestTest 2Test CO 2Test RFI 2ProposedS240
17/09/2020 08:09TestTest 2Test CO 2Test RFI 2ProposedS250
17/09/2020 08:09TestTest 2Test CO 2Test RFI 2ProposedS260
17/09/2020 08:10TestTest 3Test CO 3Test RFI 3ApprovedMU101000
17/09/2020 08:10TestTest 3Test CO 3Test RFI 3ApprovedMU110
17/09/2020 08:10TestTest 3Test CO 3Test RFI 3ApprovedMU120
17/09/2020 08:10TestTest 3Test CO 3Test RFI 3ApprovedMU134000
17/09/2020 08:10TestTest 3Test CO 3Test RFI 3ApprovedMU140
17/09/2020 08:10TestTest 3Test CO 3Test RFI 3ApprovedMU150
17/09/2020 08:10TestTest 3Test CO 3Test RFI 3ApprovedMU160
17/09/2020 08:10TestTest 3Test CO 3Test RFI 3ApprovedMU170
17/09/2020 08:10TestTest 3Test CO 3Test RFI 3ApprovedMU180
17/09/2020 08:10TestTest 3Test CO 3Test RFI 3ApprovedMU190
17/09/2020 08:10TestTest 3Test CO 3Test RFI 3ApprovedMU200
17/09/2020 08:10TestTest 3Test CO 3Test RFI 3ApprovedMU210
17/09/2020 08:10TestTest 3Test CO 3Test RFI 3ApprovedMU220
17/09/2020 08:10TestTest 3Test CO 3Test RFI 3ApprovedMU230
17/09/2020 08:10TestTest 3Test CO 3Test RFI 3ApprovedMU240
17/09/2020 08:10TestTest 3Test CO 3Test RFI 3ApprovedMU250
17/09/2020 08:10TestTest 3Test CO 3Test RFI 3ApprovedMU260
17/09/2020 08:10TestTest 3Test CO 3Test RFI 3ApprovedSL100
17/09/2020 08:10TestTest 3Test CO 3Test RFI 3ApprovedSL112000
17/09/2020 08:10TestTest 3Test CO 3Test RFI 3ApprovedSL120
17/09/2020 08:10TestTest 3Test CO 3Test RFI 3ApprovedSL135000
17/09/2020 08:10TestTest 3Test CO 3Test RFI 3ApprovedSL140
17/09/2020 08:10TestTest 3Test CO 3Test RFI 3ApprovedSL150
17/09/2020 08:10TestTest 3Test CO 3Test RFI 3ApprovedSL160
17/09/2020 08:10TestTest 3Test CO 3Test RFI 3ApprovedSL170
17/09/2020 08:10TestTest 3Test CO 3Test RFI 3ApprovedSL180
17/09/2020 08:10TestTest 3Test CO 3Test RFI 3ApprovedSL190
17/09/2020 08:10TestTest 3Test CO 3Test RFI 3ApprovedSL200
17/09/2020 08:10TestTest 3Test CO 3Test RFI 3ApprovedSL210
17/09/2020 08:10TestTest 3Test CO 3Test RFI 3ApprovedSL220
17/09/2020 08:10TestTest 3Test CO 3Test RFI 3ApprovedSL230
17/09/2020 08:10TestTest 3Test CO 3Test RFI 3ApprovedSL240
17/09/2020 08:10TestTest 3Test CO 3Test RFI 3ApprovedSL250
17/09/2020 08:10TestTest 3Test CO 3Test RFI 3ApprovedSL260
17/09/2020 08:10TestTest 3Test CO 3Test RFI 3ApprovedS100
17/09/2020 08:10TestTest 3Test CO 3Test RFI 3ApprovedS110
17/09/2020 08:10TestTest 3Test CO 3Test RFI 3ApprovedS123000
17/09/2020 08:10TestTest 3Test CO 3Test RFI 3ApprovedS136000
17/09/2020 08:10TestTest 3Test CO 3Test RFI 3ApprovedS140
17/09/2020 08:10TestTest 3Test CO 3Test RFI 3ApprovedS150
17/09/2020 08:10TestTest 3Test CO 3Test RFI 3ApprovedS160
17/09/2020 08:10TestTest 3Test CO 3Test RFI 3ApprovedS170
17/09/2020 08:10TestTest 3Test CO 3Test RFI 3ApprovedS180
17/09/2020 08:10TestTest 3Test CO 3Test RFI 3ApprovedS190
17/09/2020 08:10TestTest 3Test CO 3Test RFI 3ApprovedS200
17/09/2020 08:10TestTest 3Test CO 3Test RFI 3ApprovedS210
17/09/2020 08:10TestTest 3Test CO 3Test RFI 3ApprovedS220
17/09/2020 08:10TestTest 3Test CO 3Test RFI 3ApprovedS230
17/09/2020 08:10TestTest 3Test CO 3Test RFI 3ApprovedS240
17/09/2020 08:10TestTest 3Test CO 3Test RFI 3ApprovedS250
17/09/2020 08:10TestTest 3Test CO 3Test RFI 3ApprovedS260

Power Query:
let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    UOC = Table.UnpivotOtherColumns(Source, {"Date", "User", "COR-PCO Number", "Change Order", "RFI/Bulletin/ROM", "Status"}, "Attribute", "Value"),
    SC = Table.SplitColumn(UOC, "Attribute", Splitter.SplitTextByDelimiter(" ", QuoteStyle.Csv), {"Attribute.1", "Attribute.2"}),
    Ren = Table.RenameColumns(SC,{{"Attribute.1", "Column"}, {"Attribute.2", "Row"}, {"Value", "Amount"}}),
    Type = Table.TransformColumnTypes(Ren,{{"Date", type datetime}, {"User", type text}, {"COR-PCO Number", type text}, {"Change Order", type text}, {"RFI/Bulletin/ROM", type text}, {"Status", type text}, {"Column", type text}, {"Row", Int64.Type}, {"Amount", Int64.Type}})
in
    Type
 

muhleebbin

Board Regular
Joined
Sep 30, 2017
Messages
97
Office Version
  1. 365
  2. 2019
  3. 2016
  4. 2013
  5. 2010
Platform
  1. Windows
  2. MacOS
  3. Mobile
  4. Web
Thank you very much Sandy! That worked very nicely....i'll be honest, I had no idea how to use Power Query let alone input that code so I had to teach myself a few things :p
 

sandy666

Well-known Member
Joined
Oct 24, 2015
Messages
7,450
You are welcome
You will see this is a VERY useful tool for small and big things
 

muhleebbin

Board Regular
Joined
Sep 30, 2017
Messages
97
Office Version
  1. 365
  2. 2019
  3. 2016
  4. 2013
  5. 2010
Platform
  1. Windows
  2. MacOS
  3. Mobile
  4. Web

ADVERTISEMENT

I can see that! It looks like you use it quite extensively, could you recommend a book or site where a novice could possibly learn more?

Thanks again and have a great weekend!
 

muhleebbin

Board Regular
Joined
Sep 30, 2017
Messages
97
Office Version
  1. 365
  2. 2019
  3. 2016
  4. 2013
  5. 2010
Platform
  1. Windows
  2. MacOS
  3. Mobile
  4. Web

ADVERTISEMENT

Thank you!
 

sandy666

Well-known Member
Joined
Oct 24, 2015
Messages
7,450
you are welcome and thanks for the feedback
it's nice to see if someone appreciate your work so hit (y) Like in the posts which have helped you
Have a nice day
 

muhleebbin

Board Regular
Joined
Sep 30, 2017
Messages
97
Office Version
  1. 365
  2. 2019
  3. 2016
  4. 2013
  5. 2010
Platform
  1. Windows
  2. MacOS
  3. Mobile
  4. Web
Ahhh didn't even notice that! :geek:

I often times wrote out my appreciation but will definitely hit the (y) in the future
 

Watch MrExcel Video

Forum statistics

Threads
1,119,116
Messages
5,576,194
Members
412,705
Latest member
Collie
Top