+ Reply to Thread
Results 1 to 8 of 8

copy code have different price and count

Hybrid View

jamalmail copy code have different... 11-13-2012, 02:52 PM
jindon Re: copy code have different... 11-13-2012, 04:04 PM
jamalmail Re: copy code have different... 11-13-2012, 04:54 PM
jindon Re: copy code have different... 11-13-2012, 05:54 PM
xladept Re: copy code have different... 11-14-2012, 01:56 AM
jamalmail Re: copy code have different... 11-14-2012, 10:04 AM
JBeaucaire Re: copy code have different... 11-14-2012, 10:46 AM
xladept Re: copy code have different... 11-14-2012, 01:40 PM
  1. #1
    Registered User
    Join Date
    03-14-2012
    Location
    Yemen
    MS-Off Ver
    Excel 2010
    Posts
    22

    copy code have different price and count

    help me to copy code have different price and count like example in attachment
    Attached Files Attached Files

  2. #2
    Forum Guru
    Join Date
    08-15-2004
    Location
    Tokyo, Japan
    MS-Off Ver
    2013 O.365
    Posts
    22,834

    Re: copy code have different price and count

    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
    Attached Files Attached Files

  3. #3
    Registered User
    Join Date
    03-14-2012
    Location
    Yemen
    MS-Off Ver
    Excel 2010
    Posts
    22

    Re: copy code have different price and count

    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

  4. #4
    Forum Guru
    Join Date
    08-15-2004
    Location
    Tokyo, Japan
    MS-Off Ver
    2013 O.365
    Posts
    22,834

    Re: copy code have different price and count

    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

  5. #5
    Forum Guru xladept's Avatar
    Join Date
    04-14-2012
    Location
    Pasadena, California
    MS-Off Ver
    Excel 2003,2010
    Posts
    12,378

    Re: copy code have different price and count

    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

  6. #6
    Registered User
    Join Date
    03-14-2012
    Location
    Yemen
    MS-Off Ver
    Excel 2010
    Posts
    22

    Re: copy code have different price and count

    THaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaank's alot will done

  7. #7
    Forum Expert JBeaucaire's Avatar
    Join Date
    03-21-2004
    Location
    Bakersfield, CA
    MS-Off Ver
    2010, 2016, Office 365
    Posts
    33,492

    Re: copy code have different price and count

    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 the icon 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!)

  8. #8
    Forum Guru xladept's Avatar
    Join Date
    04-14-2012
    Location
    Pasadena, California
    MS-Off Ver
    Excel 2003,2010
    Posts
    12,378

    Re: copy code have different price and count

    You're welcome!

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1