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

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

Not open for further replies.
1. ### mjb696Thread 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

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

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

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" & 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

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. ### mjb696Thread 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

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

File size:
16 KB
Views:
49
7. ### mjb696Thread 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

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. ### mjb696Thread 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

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

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.

over 733,556 other people just like you!