Results 1 to 5 of 5

Thread: Move data from rows into columns for every unique value

  1. #1
    Senior Member
    Join Date
    Mar 2013
    Posts
    146
    Rep Power
    0

    Move data from rows into columns for every unique value

    Hi,
    I have a 2 Table
    No. (1) contains a repetition of names and numbers
    The second table is the result of the first table
    Attached Files Attached Files

  2. #2
    Member
    Join Date
    Jun 2013
    Posts
    93
    Rep Power
    12
    put name in column A and num in column B, then run this macro
    Code:
    Sub a()
    LR = Cells(Rows.Count, "A").End(xlUp).Row
    r = 4
    c = 2
    Do While Cells(r, 1) <> ""
      If Cells(r, 1) = Cells(r - 1, 1) Then
        c = c + 1
        Cells(r - 1, c) = Cells(r, 2)
        Rows(r).Delete
      Else
        c = 2
        r = r + 1
      End If
    Loop
    End Sub

  3. #3
    Senior Member
    Join Date
    Mar 2013
    Posts
    146
    Rep Power
    0
    THANK YOU
    I hope that by the formula

  4. #4
    Administrator Admin's Avatar
    Join Date
    Mar 2011
    Posts
    1,123
    Rep Power
    10
    Hi

    All are ARRAY formulas.

    In A21:

    =SUM(IF(FREQUENCY(MATCH(B3:B19,B3:B19,0),ROW(B3:B1 9)-ROW(B3)+1),1))

    A23 and copied down:

    =IF(ROWS($A$23:A23)<=$A$21,INDEX($B$3:$B$19,MATCH( 0,COUNTIF($A$22:A22,$B$3:$B$19),0)),"")

    B23 and copied across:

    =IF(COLUMNS($B23:B23)<=COUNTIF($B$3:$B$19,$A23),IN DEX($A$3:$A$19,SMALL(IF($B$3:$B$19=$A23,ROW($B$3:$ B$19)-ROW($B$3)+1),COLUMNS($B23:B23))),"")

    Please find attached.
    Attached Files Attached Files
    Cheers !

    Excel Range to BBCode Table
    Use Social Networking Tools If You Like the Answers !

    Message to Cross Posters

    @ Home - Office 2010/2013/2016 on Win 10 (64 bit); @ Work - Office 2016 on Win 10 (64 bit)

  5. #5
    Senior Member
    Join Date
    Mar 2013
    Posts
    146
    Rep Power
    0
    Thank you. Now I will study your solution and learn.

    Thank you.

Similar Threads

  1. Replies: 4
    Last Post: 05-01-2013, 09:49 PM
  2. Macro Copy Columns and Paste in rows.
    By TommyKris in forum Excel Help
    Replies: 3
    Last Post: 03-06-2013, 02:36 PM
  3. Move or Copy Duplicate Rows to Difference Sheet
    By Vgabond in forum Excel Help
    Replies: 3
    Last Post: 12-08-2012, 12:33 PM
  4. Replies: 2
    Last Post: 06-14-2012, 04:10 AM
  5. Hide and Unhide Rows and Columns
    By Admin in forum Download Center
    Replies: 0
    Last Post: 05-11-2011, 12:00 AM

Posting Permissions

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