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

The reports are going to be used by regional managers located throughout the country. My goal is to have them run the reports to be copied and pasted into the market analysis workbook without having to make any other changes or entries.

Thanks in advance for your assistance...

 

[yogia]

Hi jwpiii:

Although I wish from your description, I had understood what you are trying to accomplish more clearly, however, I do feel that one of the ways would be to use AdvancedFilter.

So, if the data in Worksheet Female is all correct and you need to pick up some data that is in the Worksheet Male but is not in Worksheet Female, you could append the data from Male to Female, and Filter the data for unique values in all of the columns.

Does it make sense? If you need to discuss this frther, please post back and then let us take it from there.

Yogi Anand, D.Eng, P.E.
Energy Efficient Building Network LLC

http://www.energyefficientbuild.com

 

[SkipVought]

So what's your response to my answers regarding MS Query in your other posting?

Skip,

_[red][/red]

 

[jwpiii]

Thanks for the suggestions and my apologies for the delay in responding.

To accomplish my goal of keeping user interaction with the workbook to a minimum, I am going to try my hand at a VBA routine that will merge or join the two lists automatically.

Failing that then I will probably try Skip's solution using MS Query.

Again, thanks for the suggestions.

John

 

[SkipVought]

A simple JOIN query will require no VBA code at all.

Skip,

_[red][/red]