Results 1 to 5 of 5

Thread: Match & Sort Same Vertical Data Onto One Row
Thanks Thanks: 0 Likes Likes: 0

  1. #1
    New Member
    Join Date
    Aug 2016
    Location
    DMV
    Posts
    39
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Match & Sort Same Vertical Data Onto One Row

    Good Morning Fellow Excel Experts,

    Per usual - a thanks to everyone who contributes to this forum! What would the macro be to sort on column A / row 1 (Last Name, First Name), data through [column B:column Q] then find that same name in multiple worksheets (tabs) and then place that same name from the different tabs onto row 1 of the original worksheet after column q / row 1. Then complete that step through the entire workbook.

    Any assistance would greatly be appreciated.

    -J

  2. #2
    New Member
    Join Date
    Aug 2016
    Location
    DMV
    Posts
    39
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Match & Sort Same Vertical Data Onto One Row

    Bump. I've labeled the data as current state and desired state. There are around 6k rows of data with the column a being the
    unique identifier (Last Name, First Name) each record may have more than one or just one, maximum of 5-6 rows per unique name.
    There is data in columns B through column P. If the name matches on column a, the next row would need to be moved up to the matching
    record row and pasted starting on column q, more than two records pasted on the same row, after the two original records.

    Current State
    Name date action data data numbers code org position codenum type type1 positin type from to comment
    Last Name, First Name xx/xx/xxxx words data data 1 897689890 words words x0x0 words words xx-01-21 blank blank words & numbers
    Last Name, First Name xx/xx/xxxx words data data 1 897689890 words words x0x1 words words xx-01-22 blank blank words & numbers
    Last Name1, First Name1 xx/xx/xxxx words data data 1 897689890 words words x0x2 words words xx-01-23 blank blank words & numbers
    Last Name1, First Name1 xx/xx/xxxx words data data 1 897689890 words words x0x3 words words xx-01-24 blank blank words & numbers
    Last Name1, First Name1 xx/xx/xxxx words data data 1 897689890 words words x0x4 words words xx-01-25 blank blank words & numbers
    Last Name2, First Name2 xx/xx/xxxx words data data 1 897689890 words words x0x5 words words xx-01-26 blank blank words & numbers
    Last Name2, First Name2 xx/xx/xxxx words data data 1 897689890 words words x0x6 words words xx-01-27 blank blank words & numbers
    Last Name3, First Name3 xx/xx/xxxx words data data 1 897689890 words words x0x7 words words xx-01-28 blank blank words & numbers
    Last Name4, First Name4 xx/xx/xxxx words data data 1 897689890 words words x0x8 words words xx-01-29 blank blank words & numbers
    Last Name4, First Name4 xx/xx/xxxx words data data 1 897689890 words words x0x9 words words xx-01-30 blank blank words & numbers
    Desired State
    Name date action data data numbers code org position codenum type type1 positin type from to comment Name date action data data numbers code org position codenum type type1 positin type from to comment Name date action data data numbers code org position codenum type type1 positin type from to comment
    Last Name, First Name xx/xx/xxxx words data data 1 897689890 words words x0x0 words words xx-01-21 blank blank words & numbers Last Name, First Name xx/xx/xxxx words data data 1 897689890 words words x0x1 words words xx-01-22 blank blank words & numbers
    Last Name1, First Name1 xx/xx/xxxx words data data 1 897689890 words words x0x2 words words xx-01-23 blank blank words & numbers Last Name1, First Name1 xx/xx/xxxx words data data 1 897689890 words words x0x3 words words xx-01-24 blank blank words & numbers Last Name1, First Name1 xx/xx/xxxx words data data 1 # words words x0x4 words words xx-01-25 blank blank words & numbers
    Last Name2, First Name2 xx/xx/xxxx words data data 1 897689890 words words x0x5 words words xx-01-26 blank blank words & numbers Last Name2, First Name2 xx/xx/xxxx words data data 1 897689890 words words x0x6 words words xx-01-27 blank blank words & numbers
    Last Name3, First Name3 xx/xx/xxxx words data data 1 897689890 words words x0x7 words words xx-01-28 blank blank words & numbers
    Last Name4, First Name4 xx/xx/xxxx words data data 1 897689890 words words x0x8 words words xx-01-29 blank blank words & numbers Last Name4, First Name4 xx/xx/xxxx words data data 1 897689890 words words x0x9 words words xx-01-30 blank blank words & numbers

  3. #3
    MrExcel MVP
    Join Date
    Jan 2008
    Posts
    14,837
    Post Thanks / Like
    Mentioned
    26 Post(s)
    Tagged
    12 Thread(s)

    Default Re: Match & Sort Same Vertical Data Onto One Row

    Based on post# 2 , try this:-
    Code:
    Sub MG08Aug22
    Dim Rng As Range, Dn As Range, n As Long, Q As Variant, nRng As Range
    Set Rng = Range(Range("A1"), Range("A" & Rows.Count).End(xlUp))
    With CreateObject("scripting.dictionary")
    .CompareMode = vbTextCompare
    For Each Dn In Rng
        If Not .Exists(Dn.Value) Then
            .Add Dn.Value, Array(Dn, 1)
        Else
        Q = .Item(Dn.Value)
            Q(1) = Q(1) + 16
            Rng(1).Resize(, 16).Copy Cells(1, Q(1))
            Dn.Resize(, 16).Copy Cells(Q(0).Row, Q(1))
            If nRng Is Nothing Then Set nRng = Dn Else Set nRng = Union(nRng, Dn)
        .Item(Dn.Value) = Q
        End If
    Next
    
    End With
    If Not nRng Is Nothing Then nRng.EntireRow.Delete
    End Sub
    Regards Mick

  4. #4
    New Member
    Join Date
    Aug 2016
    Location
    DMV
    Posts
    39
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Match & Sort Same Vertical Data Onto One Row

    Quote Originally Posted by MickG View Post
    Based on post# 2 , try this:-
    Code:
    Sub MG08Aug22
    Dim Rng As Range, Dn As Range, n As Long, Q As Variant, nRng As Range
    Set Rng = Range(Range("A1"), Range("A" & Rows.Count).End(xlUp))
    With CreateObject("scripting.dictionary")
    .CompareMode = vbTextCompare
    For Each Dn In Rng
        If Not .Exists(Dn.Value) Then
            .Add Dn.Value, Array(Dn, 1)
        Else
        Q = .Item(Dn.Value)
            Q(1) = Q(1) + 16
            Rng(1).Resize(, 16).Copy Cells(1, Q(1))
            Dn.Resize(, 16).Copy Cells(Q(0).Row, Q(1))
            If nRng Is Nothing Then Set nRng = Dn Else Set nRng = Union(nRng, Dn)
        .Item(Dn.Value) = Q
        End If
    Next
    
    End With
    If Not nRng Is Nothing Then nRng.EntireRow.Delete
    End Sub
    Regards Mick
    Thanks Mick! Worked exactly how I needed it. Appreciate your time.

  5. #5
    MrExcel MVP
    Join Date
    Jan 2008
    Posts
    14,837
    Post Thanks / Like
    Mentioned
    26 Post(s)
    Tagged
    12 Thread(s)

    Default Re: Match & Sort Same Vertical Data Onto One Row

    You're very welcome

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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