help me to copy code have different price and count like example in attachment
help me to copy code have different price and count like example in attachment
![]()
Option Explicit Sub test() Dim a, i As Long, txt As String, w, y, n As Long a = Sheets("data set (2)").Cells(1).CurrentRegion.Value With CreateObject("Scripting.Dictionary") .CompareMode = 1 For i = 1 To UBound(a, 1) txt = Join$(Array(a(i, 1), a(i, 2)), Chr(2)) If Not .exists(txt) Then .Item(txt) = VBA.Array(a(i, 1), a(i, 2), 1) Else w = .Item(txt) w(2) = w(2) + 1 .Item(txt) = w End If Next y = .items: n = .Count End With With Sheets("out put").Cells(1).Resize(n, 3) .CurrentRegion.ClearContents .Columns(1).NumberFormat = "@" .Value = Application.Transpose(Application.Transpose(y)) .Cells(1, 3).Value = "Count" .Columns.AutoFit End With End Sub
in your code also give me the code that don't have different price I need to give me the code have different price only
![]()
Option Explicit Sub test() Dim a, i As Long, w, n As Long, e, s a = Sheets("data set (2)").Cells(1).CurrentRegion.Value With CreateObject("Scripting.Dictionary") .CompareMode = 1 For i = 1 To UBound(a, 1) If Not .exists(a(i, 1)) Then Set .Item(a(i, 1)) = _ CreateObject("Scripting.Dictionary") End If If Not .Item(a(i, 1)).exists(a(i, 2)) Then .Item(a(i, 1))(a(i, 2)) = VBA.Array(a(i, 1), a(i, 2), 1) Else w = .Item(a(i, 1))(a(i, 2)) w(2) = w(2) + 1 .Item(a(i, 1))(a(i, 2)) = w End If Next ReDim Preserve a(1 To UBound(a, 1), 1 To 3) a(1, 3) = "Count": n = 1 For Each e In .keys If .Item(e).Count < 2 Then .Remove e Else For Each s In .Item(e).keys n = n + 1 For i = 0 To 2 a(n, i + 1) = .Item(e)(s)(i) Next Next End If Next End With With Sheets("out put").Cells(1).Resize(n, 3) .CurrentRegion.ClearContents .Columns(1).NumberFormat = "@" .Value = a .Columns.AutoFit End With End Sub
Here's a VBA routine that works if the out put is formatted as text:
![]()
Sub CodePrice(): Dim wd As Worksheet, wo As Worksheet, r As Long, o As Long Dim Code As String, Price As Single, Count As Long, i As Long, j As Long, k As Long Set wo = ActiveWorkbook.Sheets("out put"): o = 1 Set wd = ActiveWorkbook.Sheets("Data Set (2)") r = wd.Range("A" & Rows.Count).End(xlUp).row For i = 2 To r Code = wd.Range("A" & i): Price = wd.Range("B" & i): Count = 1 For j = 2 To r If j = i Then j = j + 1 For k = 2 To o If (wo.Range("A" & k) = Code And wo.Range("B" & k) = Price) Then GoTo GetNext: End If: Next k If wd.Range("A" & j) = Code And wd.Range("B" & j) = Price Then Count = Count + 1 Next j: o = o + 1 wo.Range("A" & o) = Code: wo.Range("B" & o) = Price: wo.Range("C" & o) = Count GetNext: Next i: End Sub
If I've helped you, please consider adding to my reputation - just click on the liitle star at the left.
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~(Pride has no aftertaste.)
You can't do one thing. XLAdept
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~aka Orrin
THaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaank's alot will done
If that takes care of your original query, please select Thread Tools from the menu above and mark the thread as solved. Thanks.
_________________
Microsoft MVP 2010 - Excel
Visit: Jerry Beaucaire's Excel Files & Macros
If you've been given good help, use theicon below to give reputation feedback, it is appreciated.
Always put your code between code tags. [CODE] your code here [/CODE]
?None of us is as good as all of us? - Ray Kroc
?Actually, I *am* a rocket scientist.? - JB (little ones count!)
You're welcome!
There are currently 1 users browsing this thread. (0 members and 1 guests)
Bookmarks