Sorting Data

[FALLSILENT]

I'm trying to use this table to feed to a second sheet that will break down how many times customers had each unit, and how many times of what type. Everything I've done has resulted in counting every instance of a name. Sometimes a customer has a unit for a few days, sometimes just a day. I tried a pivot table, but if CUST 1 had the unit first, every customer after fell into a sub-group of that first.

UNIT	TYPE	10/1/17	10/1/17	10/2/17	10/2/17	10/3/17
12V1223	BOX	CUST 1	CUST 1	CUST 2	CUST 3	CUST 4
15V1245	BOX	CUST 1	CUST 1	CUST 2	CUST 3	CUST 4
16V1250	BOX	CUST 1	CUST 1	CUST 2	CUST 3	CUST 4
15F1244	FLATBED	CUST 1	CUST 1	CUST 2	CUST 4	CUST 4
7751408	FLATBED	CUST 1	CUST 1	CUST 2	CUST 3	CUST 4
7751458	REEFER	CUST 1	CUST 1	CUST 2	CUST 3	CUST 4
142525	SAD	CUST 1	CUST 1	CUST 2	CUST 3	CUST 4
7751404	SAD	CUST 1	CUST 1	CUST 2	CUST 3	CUST 4
133515	TAD	CUST 1	CUST 1	CUST 2	CUST 3	CUST 4
163536	TAD	CUST 1	CUST 1	CUST 2	CUST 3	CUST 4

<colgroup><col><col><col span="5"></colgroup><tbody>
</tbody>

 

[lrobbo314]

I pasted your table above into range A1:G11. This code assumes that there isn't anything below that data, so you will likely need to adjust the ranges and the array that holds the initial table data. Let me know if you have any questions about how to do that. But what this does is to change the way the data is presented so that you can make a pivot table that will give you the results you are looking for.

Sub Unwind()

Dim AR()
Dim Res(1 To 51, 1 To 4)
Dim Cnt As Long
Dim r As Range
Cnt = 2
AR = Range("A1:G11").Value
Res(1, 1) = "Customer"
Res(1, 2) = "Unit"
Res(1, 3) = "Type"
Res(1, 4) = "Date"
For i = 2 To UBound(AR)
    For j = 3 To UBound(AR, 2)
        Res(Cnt, 1) = AR(i, j)
        Res(Cnt, 2) = AR(i, 1)
        Res(Cnt, 3) = AR(i, 2)
        Res(Cnt, 4) = AR(1, j)
        Cnt = Cnt + 1
    Next j
Next i
Set r = Range("A15").Resize(UBound(Res), 4)
r.Value = Res()

End Sub

 

[FALLSILENT]

I have questions. I am having to learn to use excel on my own at work. Where do I input this code?

 

[lrobbo314]

When you have your workbook open, Hit Ctrl+F11. This will open the VBA editor. Then hit Ctrl+I+M, or go to Insert-->Module. This will bring up a new blank module. This is where you will paste the code. Then, you can either hit F5 to run the code, or run it from the workbook itself by hitting alt+F8 and selecting the module you want to run. I would suggest doing this on a test copy of your workbook because there is no Undo when running code like this.

 

[lrobbo314]

This is an optimized version of the code. With your original sheet activated, run the following code. It will add a sheet titled "Results" and it will dump your pivot table ready data into that sheet.

Sub Unwind()Dim ws As Worksheet
Dim AR()
Dim Res()
Dim Cnt As Long
Dim r As Range
Dim d As Range
Dim LR As Long


LR = Range("A" & Rows.Count).End(xlUp).Row
Cnt = 2
Set d = Range("C1:G" & LR)
ReDim Res(1 To d.Cells.Count, 1 To 4)
Set d = Range("A1").CurrentRegion
AR = d.Value


Res(1, 1) = "Customer"
Res(1, 2) = "Unit"
Res(1, 3) = "Type"
Res(1, 4) = "Date"
For i = 2 To UBound(AR)
    For j = 3 To UBound(AR, 2)
        Res(Cnt, 1) = AR(i, j)
        Res(Cnt, 2) = AR(i, 1)
        Res(Cnt, 3) = AR(i, 2)
        Res(Cnt, 4) = AR(1, j)
        Cnt = Cnt + 1
    Next j
Next i


Set ws = ActiveWorkbook.Sheets.Add(After:=Sheets(ActiveWorkbook.Sheets.Count))
ws.Name = "Results"


Set r = ws.Range("A1").Resize(UBound(Res), 4)
r.Value = Res()


End Sub

 

[FALLSILENT]

I can't get this to work, I'm sure it'll work, I just need to figure out what I'm doing wrong on my end. Ctrl-F11 just opens a Macro Sheet for me.

 

[Fluff]

That should be Alt F11 not Ctrl F11

 

[FALLSILENT]

That opens the window. I create the module, and hit F5 and get a blank Macros box. Am I missing a step?

 

[Fluff]

Did you paste the code lrobbo314 supplied into the module?

 

[FALLSILENT]

Yes, mind you I'm new at this level of excel.