Merging to Rows of Data

I originally posted this in excel 2007 help but this might be a better place to ask it.

I have 2 different lists in excel.

The first one has all the business info, name, address, phone number, etc...
The next has the business phone number and rank in Google.

I combine both lists into 1 sheet and then add a function =IF(Phone Number is Equal to Phone Number above then Yes, if not No)

Is there a way to combine both rows when the answer to the IF function is Yes?

I have attached an excel file I have been trying to figure this out with.Excel Mege Test.xlsx

Now go back to your earlier thread in Excel 2007 and explain that you have posted here (include a link to this one) and that the thread is no longer valid, so that you can't be accused of double-posting.

Pete

You got it Pete

Sorry, I forgot to change the info over to sheet 1. Here's the new excel file with it in sheet 1Excel Mege Test.xlsx

As to your question, a4 should be no because N4 is not equal to N3. It's a different phone number.

It worked on the list of 4 results.

Then I tested it on 20 results and this is what the error message said: http://screencast.com/t/KRVu6NWD
Then when I hit debug this is what it highlighted: http://screencast.com/t/gb96v6w4hI

I attached the next list of 20 results. Excel Mege Test 2.xlsx

Okay...so it worked on the list of 20 and I went to try it on my list of 177 and got this error: http://screencast.com/t/6qpdfsqkO

When I hit debug it highlighted the name of the worksheet 'Sheet1'

I noticed that I had saved the csv file so it named the worksheet the name of the file.

When I renamed the worksheet to 'Sheet1' nothing changed.

Yes...renamed it exactly and ran it again and nothing. Just another bug.

I did copy and paste all the data into a new spreadsheet and run it and it worked fine.

Was just wondering why it didn't work when it was saved.

Another question.

I have another list: Denver Chiro Merged.xlsx

When I run the combine the row thing on this list it only combines
the top row. Here is the result of running this Macro: Denver Chiro Merged 2.xlsx

Thank you so much for your help with this!

Any ideas why it is only merging rows 1 & 2?

Sorry to keep asking. I have a deadline for tomorrow for my boss to merge a bunch of lists together.

How about this?

Option Explicit

Sub test()
    Dim myAreas As Areas, i As Long
    If Application.CountIf(Columns(1), "Yes") = 0 Then Exit Sub
    Application.ScreenUpdating = False
    Columns(1).Insert
    With Range("b2", Range("b" & Rows.Count).End(xlUp)).Offset(, -1)
        .Formula = "=if(and(b1=""Yes"",b2=""No""),if(a1=1,""a"",1),"""")"
        .Value = .Value
        On Error Resume Next
        .SpecialCells(2, 1).EntireRow.Insert
        .SpecialCells(2, 2).EntireRow.Insert
        On Error GoTo 0
    End With
    Columns(1).Delete
    Set myAreas = Columns(1).SpecialCells(2).Areas
    For i = myAreas.Count To 1 Step -1
        With myAreas(i).CurrentRegion
            If (.Rows.Count > 1) * (.Cells(.Rows.Count, 1).Value = "Yes") Then
                .Cells(.Rows.Count - 1, 2).Value = .Cells(.Rows.Count, 2).Value
                .Rows(.Rows.Count).EntireRow.Delete
            End If
        End With
    Next
    Range("a2", Range("a" & Rows.Count).End(xlUp)) _
    .SpecialCells(4).EntireRow.Delete
    Set myAreas = Nothing
    test
    Application.ScreenUpdating = True
End Sub

In the last example, the phone number in Row 3 is the same as Row 2.

So Row 3 should merge with Row 2.

Was it going the other way around before?

Where Row 2 would merge with Row 3? I guess it doesn't really matter.

When I run the Macro it just didn't merge the listings like it did before. I just noticed 1 listing merged.

jindon, where do I upload that code?

Try the attached.

Just click on A1.