Combining Sheets

  • Thread starter Thread starter esquire808
  • Start date Start date
E

esquire808

Hello,

I am curious if it is possible to combine two different sheets both
have one common column.

here is what i would like to see happen *note C3 is the same on both
sheets*

A1 B2 C3 D4 & A1 B2 C3 D4

into

A1 B2 C3 D4 E4 F4 G4 H4


*shrug*

Thanks for any input


CB :confused:
 
I'm sure that what you want can be done. However, what you posted is not
enough to tell me what you want. Post back and state clearly what is on one
sheet and what is on the other sheet. Then state clearly what you want to
happen on what sheet. HTH Otto
 
ok, here goes nothing

Sheet #1

A1 B1
Address Name
______________
Blah1 Blah#
Blah2 Blah#
Blah3 Blah#

Sheet #2
A1 B1
D.O.B Address

______________
Blah9 Blah1
Blah8 Blah2
Blah7 Blah3


So my goal is combining both sheets 1 & 2 using one common column t
make a new sheet


Sheet #3
A1 B1 C1
Name Address D.O.B

________________________________________________
Blah# Blah1 Blah9
Blah# Blah2 Blah8
Blah# Blah3 Blah7



Hope this helps :
 
are they in the same order on both sheets or do you need this to be
intelligent?

if they're in the same order, just copy and paste by entire columns and
that should get what you want.. ?
 
One way ..

Assuming the source tables as shown are
in Sheet1 and Sheet2, data from row2 down

In Sheet3
-------
Assume col B contains:

Address
Blah1
Blah2
Blah3
etc

where data is from row2 down, i.e. in B2 down
and labels "Name" and "DOB" are in A1 and C1

Put in A2: =INDEX(Sheet1!B:B,MATCH(B2,Sheet1!A:A,0))
Copy down

Put in C2: =INDEX(Sheet2!A:A,MATCH(B2,Sheet2!B:B,0))
Copy down

Or perhaps better with error-trapping
to return blanks: "" for unmatched cases:

Put in A2:
=IF(ISNA(MATCH(B2,Sheet1!A:A,0)),"",
INDEX(Sheet1!B:B,MATCH(B2,Sheet1!A:A,0)))

Put in C2:
=IF(ISNA(MATCH(B2,Sheet2!B:B,0)),"",
INDEX(Sheet2!A:A,MATCH(B2,Sheet2!B:B,0)))

Copy down cols A and C as before
 
Hmm.

I was hoping that I could get it to match the data for me. If that dat
was indeed the same in on both sheets then would compile them into on
big sheet. Directions of that last post left me somewhat more in th
dark

Sorry im a noob :
 
.. Directions of that last post left me somewhat more in the dark

urrgh .. the specifics in the response weren't sufficient ? Your 2nd post
illustrated a common key field in Sheets 1 & 2 (Address) which was then used
in Sheet3 to pull in matching data from Sheets 1 and 2. Post a link to your
sample file
 
I don't have the ability to post things. I took some screenshots. Coul
I email them to ya
 
esquire808 said:
I don't have the ability to post things.
I took some screenshots. Could I email them to ya.
Click to expand...

If you want, email a zipped copy of the file (not the screenshots)
to: demechanik <at> yahoo <dot> com
 
Ok, I've got your file .. Here's one way to achieve what you're after
(I'll email the file with the construct below back to you shortly)

In "info usa list"
------
Use an empty col to the right, say col T

Put in T2:
=IF(ISNUMBER(MATCH(I2,SNO!E:E,0)),ROW(),"")
Copy T2 down to say, T20,
to cover the max expected data range in the table
(Leave T1 empty)

This creates a criteria col to match entries in the key col I ("Street
Address")against those addresses in col E in "SNO". This col will assign
arbitrary row#s where the addresses match. The row#s will be read by the
formulas we're going to put in a new Sheet1

In a new Sheet1
----------------
Paste the col headers from "info usa list" into A1:R1
Put in A2:
=IF(ISERROR(SMALL('info usa list'!$T:$T,ROWS($A$1:A1))),"",
INDEX('info usa list'!A:A,MATCH(
SMALL('info usa list'!$T:$T,ROWS($A$1:A1)),'info usa list'!$T:$T,0)))
Copy A2 across to R2, fill down to R20
(cover the same extent as done in col T in "info usa list")

The above will extract only those lines from "info usa list" which have
street addresses which match with those within "SNO"

Now to bring over the lines from "SNO"
-------------------------------
Paste the col headers from "SNO" into S1:AP1

Put in S2:
=IF(ISNA(MATCH($I2,SNO!$E:$E,0)),"",
INDEX(SNO!A:A,MATCH($I2,SNO!$E:$E,0)))
Copy S2 to AP2, fill down to AP20

The above will extract lines from "SNO" with street addresses matching those
extracted into col I in this new Sheet1, hence accomplishing the desired
"merge" from the 2 source sheets.
 
Max,

That was exactly what I needed to accomplish! I very much appreciate
the fact you took the time to help me via email as I was unable to
upload my files and not to mention I am a very inexperienced excel user
in comparison to your expertise.

Thank you

P.S
Thanks to those who run this forum as it has proved to be an invaluable
resource to the Excel users community.


CB
 
Back
Top