Matching and Moving Data From One Spreadsheet to Another?

Hi all!

I *was* very proficient with Excel back in the days when you could use
the old command macro language, but haven't really had the chance to
get up to speed with Excel and the VBA (?) based language now used -
I'm not even sure I've described that accurately. Anyway...

I've got two spreadsheets of large enough size that I'd rather not do
this process by hand. One spreadsheet is pretty much a "subset" of the
other based on a few columns, e.g. "Name", "Address", "Age". I'd like
to take a row from the "subset" spreadsheet and copy the data
corresponding to "Name", "Address", "Age", etc. and paste it into the
"master" spreadsheet. The end result would be all rows found in the
"master" with additional data found in "subset" would be updated.

Here's an abbreviated example:

MASTER SPREADSHEET
col1 col2 col3 col4
a 1 2 3
a 4 7 2
b 5 6 7
c 8 9 0

SUBSET SPREADSHEET
col1 col2 col3 col4
a 1 z y
c 8 q r

RESULTING SPREADSHEET
col1 col2 col3 col4 col5 col6
a 1 2 3 z y
c 8 9 0 q r

Does that make sense? How do I approach this? Macros? Excel database
functions which I haven't had the opportunity to play with.

Thanks for reading!
Dennis

It looks like you're matching up on both columns A and B--kind of an =vlookup()
with two keys.

If that's the case, you can use something like:

=index(othersheet!$c$1:$c$100,
match(1,(a2=othersheet!$a$1:$a$100)*(b2=othersheet!$b$1:$b$100),0))
(one cell)

This is an array formula. Hit ctrl-shift-enter instead of enter. If you do it
correctly, excel will wrap curly brackets {} around your formula. (don't type
them yourself.)

Adjust the range to match--but you can't use the whole column.

This returns the value in othersheet column C when column A and B (of
othersheet) match A2 and B2 of the sheet with the formula.

And you can add more conditions by just adding more stuff to that product
portion of the formula:

=index(othersheet!$d$1:$d$100,
match(1,(a2=othersheet!$a$1:$a$100)
*(b2=othersheet!$b$1:$b$100)
*(c2=othersheet!$c$1:$c$100),0))

(still an array formula)

[email protected] wrote:
>
> Hi all!
>
> I *was* very proficient with Excel back in the days when you could use
> the old command macro language, but haven't really had the chance to
> get up to speed with Excel and the VBA (?) based language now used -
> I'm not even sure I've described that accurately. Anyway...
>
> I've got two spreadsheets of large enough size that I'd rather not do
> this process by hand. One spreadsheet is pretty much a "subset" of the
> other based on a few columns, e.g. "Name", "Address", "Age". I'd like
> to take a row from the "subset" spreadsheet and copy the data
> corresponding to "Name", "Address", "Age", etc. and paste it into the
> "master" spreadsheet. The end result would be all rows found in the
> "master" with additional data found in "subset" would be updated.
>
> Here's an abbreviated example:
>
> MASTER SPREADSHEET
> col1 col2 col3 col4
> a 1 2 3
> a 4 7 2
> b 5 6 7
> c 8 9 0
>
> SUBSET SPREADSHEET
> col1 col2 col3 col4
> a 1 z y
> c 8 q r
>
> RESULTING SPREADSHEET
> col1 col2 col3 col4 col5 col6
> a 1 2 3 z y
> c 8 9 0 q r
>
> Does that make sense? How do I approach this? Macros? Excel database
> functions which I haven't had the opportunity to play with.
>
> Thanks for reading!
> Dennis

--

Dave Peterson