1. Computer problem? Tech Support Guy is completely free -- paid for by advertisers and donations. Click here to join today! If you're new to Tech Support Guy, we highly recommend that you visit our Guide for New Members.

Solved: Excel Sort and Merge Data from two Columns into a row

Discussion in 'Business Applications' started by mjb696, Apr 6, 2011.

Thread Status:
Not open for further replies.
Advertisement
  1. mjb696

    mjb696 Thread Starter

    Joined:
    Apr 6, 2011
    Messages:
    4
    Hi, This is a fab site, just spent a good few hours reading through without realising the time!
    ANyway, I am stuck on an Excel problem.

    I have two columns, of which there are lots of duplicates in column A, and various values in column B:

    Like this:

    A. B.

    Book Title 1. Song One
    Book Title 1. Song Two
    Book Title 1. Song THree
    Book Title 2. Song One
    Book TItle 2. Song Two
    Book Title 2. Song Three
    Book TItle 2. Song Four

    and so on, for 339334 rows.

    What I would like to do is have all of the relevant info from column B in just one cell, in one row, rather than a separate row for each song title.

    So what I ideally need is:

    A. B.
    Book Title One. Song One, Song Two, Song Three
    Book Title Two. Song One, Song Two, Song Three, Song Four

    and so on. I have no idea where to begin with this. I have read several similar things that might help, but the problem is that each 'Book Title' has a different number of songs in it.

    Can any one advise a complete beginner on how I might accomplish this?

    Many thanks for any guidance!
     
  2. bomb #21

    bomb #21

    Joined:
    Jul 1, 2005
    Messages:
    8,546
    Welcome to the board, "complete beginner". :)

    Not sure this is "on" with just formulas. The following assumes it's already sorted by Book Title.

    1. Unique Book Titles in column D

    In D1:

    =A1

    In D2:

    =INDEX(A:A,MATCH(D1,A:A,1)+1)

    Then copy D2 down until it starts to error.

    2. Song Titles in E:? per Book Title

    In E1:

    =IF(COLUMN()-4>COUNTIF($A:$A,$D1),#N/A,INDEX($B:$B,MATCH($D1,$A:$A,0)+COLUMN()-5))

    Copy/paste E1 to E1:I1 ; copy/paste E1:I1 to E1:I2.

    The snag, as you'll see, is how many Song Titles per Book Title? Over to you. :)
     
  3. bomb #21

    bomb #21

    Joined:
    Jul 1, 2005
    Messages:
    8,546
    OK, I missed "in just one cell". :D

    You'll need code for that. But try the formula part first, it's all good practice. :)


    Edit: note to self
    LastRow = Range("D" & Rows.Count).End(xlUp).Row
    For Each Cell In Range("D1:D" & LastRow)
    x = WorksheetFunction.CountIf(Columns(1), Cell)
    For i = 1 To x
    sstr = sstr & Cell.Offset(, i) & ", "
    Next i
    sstr = Left(sstr, Len(sstr) - 2)
    MsgBox sstr,,Cell
    sstr = ""
    Next Cell
     
  4. bomb #21

    bomb #21

    Joined:
    Jul 1, 2005
    Messages:
    8,546
    Looks like the approach suggested first may have put you off a bit. Let's try something else.

    1. Enter this formula in D1:

    =IF(COUNTIF(A1:A$1,A1)=1,A1,#N/A)

    , then copy it down to D339334.

    2. Select all of column D and copy > Edit > Paste Special > Values > OK.

    3. With all of column D still selected, press F5 > click Special > select Constants -- Errors > click OK.

    4. Edit > Delete > Shift cells up > OK.

    That's "Unique Book Titles in column D". The next bit requires code (AKA "a macro").
     
  5. mjb696

    mjb696 Thread Starter

    Joined:
    Apr 6, 2011
    Messages:
    4
    Hi,
    THanks for your replies - I have been trying the first reply out and experimenting with it and learning lots! I will try the second suggestion too, as its all useful stuff.

    I've got the first reply working, but it seems to ignore a large amount of the cells in my first column - it seems to start at entries beginning with C, and ignores anything beginning with a number. Is there a workaround for this? If so, then this is perfect for what I need!

    Thanks again!
     
  6. bomb #21

    bomb #21

    Joined:
    Jul 1, 2005
    Messages:
    8,546
    The code, then.

    Your "Unique Book Titles in column D" layout should be as per the attached.

    To run the code, press CTRL+Shift+M.


    Sub the_code()
    Columns(5).Delete
    Application.ScreenUpdating = False
    LastA = Range("A" & Rows.Count).End(xlUp).Row
    For Each Cell In Range("A1:A" & LastA)
    res = Application.Match(Cell, Columns(4), 0)
    Cells(res, 5) = Cells(res, 5) & Cell.Offset(, 1) & ", "
    Next Cell

    LastE = Range("E" & Rows.Count).End(xlUp).Row
    Range("F1:F" & LastE).FormulaR1C1 = "=LEFT(RC[-1],LEN(RC[-1])-2)"
    Columns(6).Value = Columns(6).Value
    Columns(5).Delete
    Application.ScreenUpdating = True
    End Sub
     

    Attached Files:

  7. mjb696

    mjb696 Thread Starter

    Joined:
    Apr 6, 2011
    Messages:
    4
    I've been trying to do as you have suggested - the main problem I have is excel crashing when trying to copy the formula down the whole of column d, even in stages! I will persevere though and just do it in batches.

    Upon trying out a small smaple though, I get an error message with the macro:
    Run-time error '13'1: Type mismatch.

    When I click debug it highlights : Cells(res, 5) = Cells(res, 5) & Cell.Offset(, 1) & ", "

    I have no understanding of macros and would not even know where to begin - can you help me out on this?

    Thanks!
     
  8. bomb #21

    bomb #21

    Joined:
    Jul 1, 2005
    Messages:
    8,546
    I don't see how Type mismatch is possible. Let's try "nothing but code" then.

    Use the below on your raw data -- values in columns A and B and nothing else.

    It'll prompt you for a row number so enter (say) 1000 to test. For each row it'll write the row number to H1 so if there's a prob that'll be where to look, in the first instance.

    Sub test()
    Range("D:H").ClearContents
    Range("D1") = Range("A1")
    Range("E1") = Range("B1") & ", "
    LastA = Range("A" & Rows.Count).End(xlUp).Row
    LastR = InputBox("Enter number of last row to process (max = " & LastA & ")")
    For Each Cell In Range("A2:A" & LastR)
    If WorksheetFunction.CountIf(Columns(4), Cell) = 0 Then
    Range("D" & Rows.Count).End(xlUp).Offset(1) = Cell
    Range("D" & Rows.Count).End(xlUp).Offset(, 1) = Cell.Offset(, 1) & ", "
    Else
    Range("H1") = Cell.Row
    res = Application.Match(Cell, Columns(4), 0)
    Cells(res, 5) = Cells(res, 5) & Cell.Offset(, 1) & ", "
    End If
    Next Cell
    End Sub
     
  9. mjb696

    mjb696 Thread Starter

    Joined:
    Apr 6, 2011
    Messages:
    4
    I think the mismatch was because I had = in one of the cells. Either way, what you have given me is superb and its solved my problem! Thanks loads!
     
  10. bomb #21

    bomb #21

    Joined:
    Jul 1, 2005
    Messages:
    8,546
    You're welcome ; thanks for marking this "solved". (y)
     
  11. Sponsor

As Seen On
As Seen On...

Welcome to Tech Support Guy!

Are you looking for the solution to your computer problem? Join our site today to ask your question. This site is completely free -- paid for by advertisers and donations.

If you're not already familiar with forums, watch our Welcome Guide to get started.

Join over 733,556 other people just like you!

Loading...
Thread Status:
Not open for further replies.

Short URL to this thread: https://techguy.org/990053

  1. This site uses cookies to help personalise content, tailor your experience and to keep you logged in if you register.
    By continuing to use this site, you are consenting to our use of cookies.
    Dismiss Notice