Join two worksheets

[Jim]

Is there any way to join two different worksheets -
especially with different structures?

I would like to create one super-worksheet with
information from two (or more) other worksheets, so that
the same rows from each appear in the new worksheeet
associated with the same persons.

This is like a logical "join" in databases.
Perhaps by exporting the worksheets into Access?

 

[Nikos Yannacopoulos]

Jim,

Doing it with Access is certainly the best way, as far as
I'm concerned.
Just one suggestion you might want to consider: if your
spreadsheets keep changing (which I assume they do), you
could avoid the import to Access every time you want to
refresh the "master" by simply linking the worksheets as
tables in Access, so the latter can read directly from
them. That way it is also "updated on-line".
The downside is, if your spreadsheets are quite big, it
may get slow. Worth experimenting?

Nikos

 

[Jim]

Nikos,

Thank you for the suggestion - you confirm my fear that
there is no way to do it in Excel.
Your idea of linking is a good and practical one.
(I sometimes wonder if i shouldn't just use Access.)
Greetings to sunny Greece!

Jim

 

[Dave Peterson]

There's always ways. (Might not be the best/easiest, but there's a way.)

I'd create a third worksheet.

Copy the key values from both worksheets into a giant list.
Use Data|filter|Advanced Filter to extract the unique entries
Debra Dalgleish has notes at:
http://www.contextures.com/xladvfilter01.html#FilterUR

Then use a bunch of =vlookup()'s to return the individual fields from the
original workbooks.