+ Reply to Thread
Results 1 to 6 of 6

Match, align, find sum for columns

Hybrid View

  1. #1
    Registered User
    Join Date
    09-08-2013
    Location
    USA
    MS-Off Ver
    Excel 2010
    Posts
    3

    Match, align, find sum for columns

    Attached is a sample of the lists. The first sheet is what it currently looks like and the second sheet is what it should look like.

    - I need to match column A and D
    - In column D, there's an extra zero at the beginning of each number
    - If there's no match, insert a blank row and shift cells down to align columns
    - (Optional) If there's a match, in column C, find the sum of B and E. This is optional because I can use a formula for that.

    If you have suggestions for a formula to match/align columns, I'd appreciate that, too. None of my formulars work so I'm hoping macro would to the trick.

    Thank you in advance!
    Attached Files Attached Files

  2. #2
    Valued Forum Contributor
    Join Date
    06-17-2009
    Location
    Chennai,India
    MS-Off Ver
    Excel 2003,excel 2007
    Posts
    678

    Re: Match, align, find sum for columns

    file "madison 13090.xlsx" attched. see formula in G2 whiuch is copied down
    Attached Files Attached Files
    I am not an expert. better solutions may be available
    $$$$venkat1926$$$$@gmail.com

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

    Re: Match, align, find sum for columns

    VBA solution.
    Attached Files Attached Files

  4. #4
    Registered User
    Join Date
    09-08-2013
    Location
    USA
    MS-Off Ver
    Excel 2010
    Posts
    3

    Re: Match, align, find sum for columns

    I had this error when I ran the macro. Please help!
    Attached Images Attached Images

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

    Re: Match, align, find sum for columns

    Change to
    Sub test()
        Dim a, i As Long, w, x As Object
        a = Sheets("Currently look like this").Cells(1).CurrentRegion.Value
        With CreateObject("System.Collections.SortedList")
            For i = 2 To UBound(a, 1)
                a(i, 1) = Format$(a(i, 1), String(10, "0"))
                a(i, 4) = Format$(a(i, 4), String(10, "0"))
                If a(i, 1) <> "" Then
                    ReDim w(1 To UBound(a, 2))
                    w(1) = a(i, 1): w(2) = a(i, 2)
                    .Item(a(i, 1)) = w
                End If
            Next
            For i = 2 To UBound(a, 1)
                If a(i, 4) <> "" Then
                    If Not .contains(a(i, 4)) Then
                        ReDim w(1 To UBound(a, 2))
                        w(4) = a(i, 4): w(5) = a(i, 5)
                        .Item(a(i, 4)) = w
                    Else
                        w = .Item(a(i, 4))
                        w(4) = a(i, 4): w(5) = a(i, 5)
                        .Item(a(i, 4)) = w
                    End If
                End If
            Next
            Set x = .Clone
        End With
        With Sheets("sheet3").Cells(1).Resize(x.Count + 1, UBound(a, 2))
            .CurrentRegion.Clear
            With .Rows(1)
                .Value = a
                .Font.Bold = True
            End With
            Union(.Columns(1), .Columns(4)).NumberFormat = "@"
            Union(.Columns("b:c"), .Columns("e")).NumberFormat = _
            "_(* #,##0.00_);_(* (#,##0.00);_(* "" - ""??_);_(@_)"
            For i = 0 To x.Count - 1
                w = x.getbyindex(i)
                .Rows(i + 2).Value = x.getbyindex(i)
            Next
            .Columns(3).Offset(1).Formula = _
            "=if(and(rc[-1]<>0,rc[2]<>0),sum(rc[-1],rc[2]),"""")"
            .Columns.AutoFit
            .Parent.Select
        End With
    End Sub

  6. #6
    Registered User
    Join Date
    09-08-2013
    Location
    USA
    MS-Off Ver
    Excel 2010
    Posts
    3

    Re: Match, align, find sum for columns

    It worked like a dream. Thank you very much for your help!

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. Need to compare 2 columns and align them next to the match
    By naenightlucky213 in forum Excel Programming / VBA / Macros
    Replies: 10
    Last Post: 05-09-2013, 06:14 AM
  2. [SOLVED] Match and Align two columns of data?
    By Erik Thomas in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 11-08-2012, 07:24 PM
  3. match and align cells in two columns?
    By Timski in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 12-22-2010, 12:27 PM
  4. Replies: 2
    Last Post: 03-02-2009, 12:15 AM
  5. Match and Sort and Align two columns
    By szu604 in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 10-03-2008, 02:08 AM

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