Excel - Merge or Join data from 2 lists

[jwpiii]

While I'm sure that this question has been asked and answered previously, I was unable to find the solution in FAQ's or searching previous threads. I also posted this in the Office forum and after countless hours of searching now think that it may be a VBA solution, hence my posting it here.

I have two different reports that are output to an Excel format from another piece of software. The format of the reports are the same and some of the data included on them is duplicated as well. What I would like to do is use one report as a master list and then compare the rows on the second report to the master and if the item description is not present already, append that item to the master and when complete resort the master alphabetically by description.

The items do not have unique identifying numbers but the descriptions are unique for each row.

The source of the master report is on a sheet that I name Female while the second report is copied to a sheet named Male. I also have 4 other sheets in the workbook that contain data from 4 different locations and a sheet named Market Analysis that creates a summary report for the market.

All of my reporting and calulations for the analysis work using just the data from Female correctly, but I have been unable to append the unique items from Male.

Column B is the Description and Column F is the Price. These are the only 2 columns that I would need data from. Data begins on Row 10 and continues. The data ends 2 rows above a description of Treatment Total, so I am currently using the MATCH function to find "Treatment Total" and then subtract 2 rows for last row of data.

My goal is to have the users of the market analysis workbook run the reports that are the source of the analysis and then copy and paste them into the appropriate sheets on the market analysis workbook without having to make any other changes or entries.

Thanks in advance for your assistance...

 

[SkipVought]

Hi,

Have you looked at MS Query? Using MS Query to get data from Excel faq68-5829

Skip,

_[red][/red]

 

[jwpiii]

Skip,

Thanks for the reply. I looked briefly at MS Query and at first glance it appears that the data in the report that is generated which my workbook would pull from in it's native format was not readable by MS Query. I recieve an error that the data source contains no visible tables.

I must admit that I am not familiar with MS Query. My goal is to minimize the number of steps from the original reports being generated to the analysis as much as possible. There will be four or five persons using the workbook in different markets and their skill sets will be novice at best.

I am amazed with all that I can make Excel do that it does not have the ability to combine/merge/join two like lists together without jumping thru a bunch of hoops.

If my assessment of the MS query is indeed correct, would this be an overly difficult task to complete via VBA?

Thanks again for your help!!

John

 

[SkipVought]

"No visible tables" may not be a problem, if you (when you get that message) hit the Options button and check ALL the selections (especially, System Tables)

Once you have your QueryTabel added to you sheet, joining the tables, you need only Date/Refresh Data. OR there are a number of Data/Import External Data/Data Range Properties properties that you select to automatically refresh.

Pretty simple for the users, eh?

Skip,

_[red][/red]

 

[jwpiii]

Thanks again, sorry for the delay as I was in meetings the rest of the day and this morning.

To reduce the interaction from the users of the analysis workbook rather than use MS Query I am going to try my hand at a VBA routine that will merge or join the two list automatically.

John

 

[SkipVought]

"To reduce the interaction from the users of the analysis workbook rather than use MS Query ..."

I don't get the correlation.

However, if you said that because you're not as familiar with MS Query as VBA, you're going to try your hand at a VBA routine, I can understand that.

Either method ought to be transparent to the user and require an equal amount of user interaction.

Skip,

_[red][/red]

 

[Bong]

How about this: for simplicity, call the master sheet, "a", and the other sheet, "b". On sheet "a" let's have:
abc unq1 1 hyg unq2 1
abc unq1 2 hyg unq2 2
abc unq1 3 hyg unq2 3
abc unq1 4 hyg unq2 4
abc unq1 5 hyg unq2 5
abc unq1 6 hyg unq2 6
abc unq1 7 hyg unq2 7
abc unq1 8 hyg unq2 8
abc unq1 9 hyg unq2 9
abc unq1 10 hyg unq2 10
abc unq1 11 hyg unq2 11
abc unq1 12 hyg unq2 12
abc unq1 13 hyg unq2 13
abc unq1 14 hyg unq2 14
abc unq1 15 hyg unq2 15
in 4 columns
on sheet "b":
abc unq1 12 hbc unq2 12
abc unq1 13 hbc unq2 13
abc unq1 14 hbc unq2 14
abc unq1 15 hbc unq2 15
abc unq1 16 hbc unq2 16
abc unq1 17 hbc unq2 17
abc unq1 18 hbc unq2 18
abc unq1 19 hbc unq2 19
abc unq1 20 hbc unq2 20
abc unq1 21 hbc unq2 21
Now let's have a subroutine:

Sub mstLst()
    Dim s1, s2 As Worksheet
    Dim r1, r2 As Range
    Dim mstrAdd As New Collection
    Set s1 = ThisWorkbook.Sheets("a")
    Set s2 = ThisWorkbook.Sheets("b")
    cols1 = s1.UsedRange.Columns.Count
    rows1 = s1.UsedRange.Rows.Count
    cols2 = s2.UsedRange.Columns.Count
    rows2 = s2.UsedRange.Rows.Count
    Set r1 = s1.Range("b1:b" & rows1)
    Set r2 = s2.Range("b1:b" & rows2)
    For Each c2 In r2.Cells
        If (r1.Find(c2.Value) Is Nothing) Then
            mstrAdd.Add (c2.Row)
        End If
    Next
    rw = rows1 + 1
    For Each rwn In mstrAdd
        s2.Rows(rwn).EntireRow.Copy (s1.Rows(rw))
        rw = rw + 1
    Next
End Sub

After running this macro, sheet "a" looks like:
abc unq1 1 hyg unq2 1
abc unq1 2 hyg unq2 2
abc unq1 3 hyg unq2 3
abc unq1 4 hyg unq2 4
abc unq1 5 hyg unq2 5
abc unq1 6 hyg unq2 6
abc unq1 7 hyg unq2 7
abc unq1 8 hyg unq2 8
abc unq1 9 hyg unq2 9
abc unq1 10 hyg unq2 10
abc unq1 11 hyg unq2 11
abc unq1 12 hyg unq2 12
abc unq1 13 hyg unq2 13
abc unq1 14 hyg unq2 14
abc unq1 15 hyg unq2 15
abc unq1 16 hbc unq2 16
abc unq1 17 hbc unq2 17
abc unq1 18 hbc unq2 18
abc unq1 19 hbc unq2 19
abc unq1 20 hbc unq2 20
abc unq1 21 hbc unq2 21

Is that what you wanted?

_________________
Bob Rashkin

 

[jwpiii]

By Bob I think you got it!! That is exactly what I need to accomplish and in only 23 lines of code.

I shall output the 2 reports, create a test workbook and commence to totally confuse myself. I'm sure I shall have a question or two before all is said and done.

Off I go to edumacate myself in VBA.

Thanks Bob and to you Skip as well

John

 

[Bong]

It could actually be slightly fewer lines but it's then a little less clear what's happening:

Sub mstLst()
    Dim s1, s2 As Worksheet
    Dim r1, r2 As Range
    [s]Dim mstrAdd As New Collection[/s]
    Set s1 = ThisWorkbook.Sheets("a")
    Set s2 = ThisWorkbook.Sheets("b")
    [s]cols1 = s1.UsedRange.Columns.Count[/s]
    rows1 = s1.UsedRange.Rows.Count
    [s]cols2 = s2.UsedRange.Columns.Count[/s]
    rows2 = s2.UsedRange.Rows.Count
    Set r1 = s1.Range("b1:b" & rows1)
    Set r2 = s2.Range("b1:b" & rows2)
    [red]rw = rows1 + 1[/red]
    For Each c2 In r2.Cells
        If (r1.Find(c2.Value) Is Nothing) Then
            [s]mstrAdd.Add (c2.Row)[/s]
            [red]s2.Rows(c2.Row).EntireRow.Copy (s1.Rows(rw))
            rw = rw + 1[/red]
        End If
    Next
    [s]rw = rows1 + 1
    For Each rwn In mstrAdd
        s2.Rows(rwn).EntireRow.Copy (s1.Rows(rw))
        rw = rw + 1
    Next[/s]
End Sub

_________________
Bob Rashkin