Count and Subtotal ColA based on ColB Then Total

[LambVBA]

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]Load
[/TD]
[TD]Order
[/TD]
[/TR]
[TR]
[TD]12345
[/TD]
[TD]ABC123
[/TD]
[/TR]
[TR]
[TD]23456
[/TD]
[TD]ABC000
[/TD]
[/TR]
[TR]
[TD]34567
[/TD]
[TD]ABC000
[/TD]
[/TR]
[TR]
[TD]45678
[/TD]
[TD]XYZ123
[/TD]
[/TR]
</tbody>[/TABLE]

Hi everyone. I'm looking for an easy way to create a new couple of columns with the Distinct order # and next to it the total number of Loads. See example above and then the expected output below. Anyone know??

Load Order Distinct Order Number of Loads
12345 ABC123 ABC123 1
23456 ABC000 ABC000 2
34567 ABC000 XYZ123 1
45678 XYZ123

 

[hiker95]

LambVBA,

1. What version of Excel and Windows are you using?

2. Are you using a PC or a Mac?


Sample raw data:

Excel 2007
	A	B	C	D
1	Load	Order
2	12345	ABC123
3	23456	ABC000
4	34567	ABC000
5	45678	XYZ123
6
Sheet1

After the macro:

Excel 2007
	A	B	C	D
1	Load	Order	Distinct Order	Number of Loads
2	12345	ABC123	ABC123	1
3	23456	ABC000	ABC000	2
4	34567	ABC000	XYZ123	1
5	45678	XYZ123
6
Sheet1

Please TEST this FIRST in a COPY of your workbook (always make a backup copy before trying new code, you never know what you might lose).

1. Copy the below code
2. Open your NEW workbook
3. Press the keys ALT + F11 to open the Visual Basic Editor
4. Press the keys ALT + I to activate the Insert menu
5. Press M to insert a Standard Module
6. Where the cursor is flashing, paste the code
7. Press the keys ALT + Q to exit the Editor, and return to Excel
8. To run the macro from Excel press ALT + F8 to display the Run Macro Dialog. Double Click the macro's name to Run it.

Sub GetUniquesCount()
' hiker95, 10/30/2014, ME815188
Dim c As Range
With CreateObject("Scripting.Dictionary")
  .CompareMode = vbTextCompare
  For Each c In Range("B2", Range("B" & Rows.Count).End(xlUp))
    If c <> "" Then
      If Not .Exists(c.Value) Then
        .Add c.Value, 1
      Else
        .Item(c.Value) = .Item(c.Value) + 1
      End If
    End If
  Next
  Range("C1").Resize(, 2).Value = Array("Distinct Order", "Number of Loads")
  Range("C2").Resize(.Count, 2) = Application.Transpose(Array(.keys, .Items))
  Columns("C:D").AutoFit
End With
End Sub

Before you use the macro with Excel 2007 or newer, save your workbook, Save As, a macro enabled workbook with the file extension .xlsm

Then run the GetUniquesCount macro.

 

[Peter_SSs]

I'm looking for an easy way ...

Have a look at Excel's in-built Pivot table feature, on the Insert ribbon tab.
It is designed exactly for that sort of requirement.

Excel Workbook
	A	B	C	D	E	F
1	Load	Order		Count of Load
2	12345	ABC123		Order	Total
3	23456	ABC000		ABC000	2
4	34567	ABC000		ABC123	1
5	45678	XYZ123		XYZ123	1
6				Grand Total	4
PT