One way if you have a key field that is unique in each worksheet, but common
between the 3 worksheets.

(I'm gonna use Location as that unique key)

create a new worksheet
copy all the data in each of the Location columns to this new worksheet--all in
column A--no gaps and only one header.

Use the technique shown at Debra Dalgleish's site to create a list of unique
items in column B of that new sheet
http://www.contextures.com/xladvfilter01.html#FilterUR

Then delete column A.

Now use a bunch of =vlookup()'s to retrieve each of the fields you're interested
in from each of the other 3 worksheets.



wrote:

Hoping someone can give me some pointers with this....

I have three separate source workbooks (Workbook1,2,3)
Each workbook contains a single worksheet containing data - example
layout below;

Workbook1
Date, Location, Method, Weight, Date, PPM

Workbook2
Location, Method, Height, Colour, Weight, Date

Workbook3
PPM, Height, Location, Colour, Method, Date

How consolidate data from the three workbooks into one target workbook
mapping the data from the source workbook fields
to the target workbook fields;

TargetWorkbook
Date, Location, Colour, Weight, Method

I have been able to create a macro to copy and paste the first source
workbook over, but having difficulty getting the subsequent source data
to be mapped to the correct fields, and can't get the subsequent source
data to be pasted under the first lot of data (I dont know how many
rows of data will be in each source workbook)

--

Dave Peterson