Completely Lost

[LambVBA]

Hi guys,

I'm completely fogged here and don't know where to begin. I have a table that I need to know a subtotal based on each order. Hopefully I can describe this correctly. Of the table below what I need as an output is the Order Number and how many distinct load numbers there are to each. So a result may be some sort of code which adds a column F with the distinct Order Number and column G with the total distinct amount of Load Numbers.
[TABLE="width: 525"]
<TBODY>[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][TABLE="class: grid, width: 400"]
<TBODY>[TR]
[TD]Load Number</SPAN>
[/TD]
[TD]Host External ID</SPAN>
[/TD]
[TD]Order Number</SPAN>
[/TD]
[TD]Carrier Move ID</SPAN>
[/TD]
[TD][TABLE="width: 525"]
<TBODY>[TR]
[TD]Schedule Batch Number</SPAN>
[/TD]
[/TR]
[TR]
[TD][/TD]
[/TR]
</TBODY>[/TABLE]
[/TD]
[/TR]
[TR]
[TD]00082126381</SPAN>
[/TD]
[TD]12345
[/TD]
[TD]XA82949</SPAN>
[/TD]
[TD]00863</SPAN>
[/TD]
[TD]2
[/TD]
[/TR]
[TR]
[TD]00082139824</SPAN>
[/TD]
[TD]12345</SPAN>
[/TD]
[TD]XA82949</SPAN>
[/TD]
[TD]00863</SPAN>
[/TD]
[TD][TABLE="width: 525"]
<TBODY>[TR]
[TD]</SPAN>
[/TD]
[/TR]
[TR]
[TD]2
[/TD]
[/TR]
</TBODY>[/TABLE]
[/TD]
[/TR]
[TR]
[TD]00010663629</SPAN></SPAN></SPAN>
[/TD]
[TD]12345</SPAN>
[/TD]
[TD]XA82949</SPAN>
[/TD]
[TD]00863</SPAN>
[/TD]
[TD][TABLE="width: 525"]
<TBODY>[TR]
[TD]</SPAN>
[/TD]
[/TR]
[TR]
[TD]2
[/TD]
[/TR]
</TBODY>[/TABLE]
[/TD]
[/TR]
[TR]
[TD]00010553630</SPAN>
[/TD]
[TD]12345</SPAN>
[/TD]
[TD]TG12302</SPAN>
[/TD]
[TD]00863</SPAN>
[/TD]
[TD][TABLE="width: 525"]
<TBODY>[TR]
[TD][/TD]
[/TR]
[TR]
[TD]2
[/TD]
[/TR]
</TBODY>[/TABLE]
[/TD]
[/TR]
[TR]
[TD]00020663631</SPAN>
[/TD]
[TD]12345</SPAN>
[/TD]
[TD]WY12305</SPAN>
[/TD]
[TD]00863</SPAN>
[/TD]
[TD]2
[/TD]
[/TR]
[TR]
[TD]00010663432</SPAN>
[/TD]
[TD]12345</SPAN>
[/TD]
[TD]WY12305</SPAN>
[/TD]
[TD]00863</SPAN>
[/TD]
[TD][TABLE="width: 525"]
<TBODY>[TR]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]2
[/TD]
[/TR]
</TBODY>[/TABLE]
[/TD]
[/TR]
</TBODY>[/TABLE]
[/TD]
[/TR]
</TBODY>[/TABLE]

 

[hiker95]

LambVBA,

You did not show us what your results should look like.

If I understand you correctly.

Sample raw data in the active worksheet:

Excel 2007
	A	B	C	D	E	F	G
1	Load Number	Host Exteral ID	Order Number	Carrier Move ID	Schedule Batch Number	distinct Order Number	total Load Numbers
2	82126381	12345	XA82949	863	2
3	82139824	12345	XA82949	863	2
4	10663629	12345	XA82949	863	2
5	10553630	12345	TG12302	863	2
6	20663631	12345	WY12305	863	2
7	10663432	12345	WY12305	863	2
8
Sheet1

After the macro:

Excel 2007
	A	B	C	D	E	F	G
1	Load Number	Host Exteral ID	Order Number	Carrier Move ID	Schedule Batch Number	distinct Order Number	total Load Numbers
2	82126381	12345	XA82949	863	2	XA82949	3
3	82139824	12345	XA82949	863	2	TG12302	1
4	10663629	12345	XA82949	863	2	WY12305	2
5	10553630	12345	TG12302	863	2
6	20663631	12345	WY12305	863	2
7	10663432	12345	WY12305	863	2
8
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/28/2014, ME814635
Dim c As Range, rng As Range, n As Long, ki
With ActiveSheet
  Set rng = .Range("C2:C" & .Range("C" & Rows.Count).End(xlUp).Row)
  With CreateObject("Scripting.Dictionary")
    .CompareMode = vbTextCompare
    For Each c In rng
      If Not .Exists(c.Value) Then
        .Add c.Value, 1
      Else
        .Item(c.Value) = .Item(c.Value) + 1
      End If
    Next
    n = .Count
    ki = Application.Transpose(Array(.Keys, .Items))
  End With
  .Range("F2").Resize(n, 2) = ki
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.

 

[LambVBA]

hiker95,

Thank you so much. That is perfect!

 

[hiker95]

LambVBA,

Thanks for the feedback.

You are very welcome. Glad I could help.

And, come back anytime.