Can this be done....?

[Dan B]

I have 2 sheets with customer contact information. The first list may have
several contacts under the same company, with their contact information,
including email address. The second list (on a different tab) is just
company information, no people, no email addresses. There are companies on
the first list that are not on the second. I want to copy any one email
address, with first and last name to the second list when the company name
on the second list matches one on the first.

For example:
Sheet1
A B C....
L
Company First Name Last Name
email


Sheet2
A B, C,D,... G H
I
Company First Name Last Name
email

Columns G,H,I on Sheet2 are blank.....(waiting for the right formula)

What I need, to put the formula verbally, is: If sheet2 A:1= Sheet1 A:1, and
sheet1 L:1 is not blank, then copy sheet1 B:1,C:1 and L:1 to sheet2 G:1,H:1
and I:1, respectively. BUT, I only want the first match it finds, not all of
them (if possible). I hope that makes sense

Thanks for any help on this,

Dan

[Max]

One way which may suffice ..

Assuming the source data in Sheet1 is within row2 - row100

In Sheet2,

Put in G2, array-enter (i.e. press CTRL+SHIFT+ENTER):
=IF(ISNA(MATCH(1,(Sheet1!$A$2:$A$100=$A2)*(Sheet1!$L$2:$L$100<>""),0)),"",IN
DEX(Sheet1!B$2:B$100,MATCH(1,(Sheet1!$A$2:$A$100=$A2)*(Sheet1!$L$2:$L$100<>"
"),0)))
Copy G2 to I2

In I2, edit the index range part,
viz: .. INDEX(Sheet1!D$2:D$100,

to point to col L instead (the email col in Sheet1),
i.e. change it to: .. INDEX(Sheet1!L$2:L$100
and array-enter the formula in I2 after editing
(remember to array-enter!)

Then re-select G2:I2, and copy down as far as required

Cols G:I will return the desired results

Adapt to suit ..
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"Dan B" <none@none.com> wrote in message
news:O67mkSQLGHA.1124@TK2MSFTNGP10.phx.gbl...
> I have 2 sheets with customer contact information. The first list may
have
> several contacts under the same company, with their contact information,
> including email address. The second list (on a different tab) is just
> company information, no people, no email addresses. There are companies
on
> the first list that are not on the second. I want to copy any one email
> address, with first and last name to the second list when the company name
> on the second list matches one on the first.
>
> For example:
> Sheet1
> A B C....
> L
> Company First Name Last Name
> email
>
>
> Sheet2
> A B, C,D,... G H
> I
> Company First Name Last Name
> email
>
> Columns G,H,I on Sheet2 are blank.....(waiting for the right formula)
>
> What I need, to put the formula verbally, is: If sheet2 A:1= Sheet1 A:1,
and
> sheet1 L:1 is not blank, then copy sheet1 B:1,C:1 and L:1 to sheet2
G:1,H:1
> and I:1, respectively. BUT, I only want the first match it finds, not all
of
> them (if possible). I hope that makes sense
>
> Thanks for any help on this,
>
> Dan
>
>

[Dan B]

Thanks for you help. This is partially working. Its just not pulling the
data over on all rows. I noticed that there some differences in the company
names, i.e. some abbreviations etc, so it is not always finding an exact
match. But, I fixed some of those, and it still didn't pull those over.
Any ideas on that?

Thanks


"Max" <demechanik@yahoo.com> wrote in message
news:ed8UP5RLGHA.964@tk2msftngp13.phx.gbl...
> One way which may suffice ..
>
> Assuming the source data in Sheet1 is within row2 - row100
>
> In Sheet2,
>
> Put in G2, array-enter (i.e. press CTRL+SHIFT+ENTER):
> =IF(ISNA(MATCH(1,(Sheet1!$A$2:$A$100=$A2)*(Sheet1!$L$2:$L$100<>""),0)),"",IN
> DEX(Sheet1!B$2:B$100,MATCH(1,(Sheet1!$A$2:$A$100=$A2)*(Sheet1!$L$2:$L$100<>"
> "),0)))
> Copy G2 to I2
>
> In I2, edit the index range part,
> viz: .. INDEX(Sheet1!D$2:D$100,
>
> to point to col L instead (the email col in Sheet1),
> i.e. change it to: .. INDEX(Sheet1!L$2:L$100
> and array-enter the formula in I2 after editing
> (remember to array-enter!)
>
> Then re-select G2:I2, and copy down as far as required
>
> Cols G:I will return the desired results
>
> Adapt to suit ..
> --
> Max
> Singapore
> http://savefile.com/projects/236895
> xdemechanik
> ---
> "Dan B" <none@none.com> wrote in message
> news:O67mkSQLGHA.1124@TK2MSFTNGP10.phx.gbl...
>> I have 2 sheets with customer contact information. The first list may
> have
>> several contacts under the same company, with their contact information,
>> including email address. The second list (on a different tab) is just
>> company information, no people, no email addresses. There are companies
> on
>> the first list that are not on the second. I want to copy any one email
>> address, with first and last name to the second list when the company
>> name
>> on the second list matches one on the first.
>>
>> For example:
>> Sheet1
>> A B C....
>> L
>> Company First Name Last Name
>> email
>>
>>
>> Sheet2
>> A B, C,D,... G H
>> I
>> Company First Name Last Name
>> email
>>
>> Columns G,H,I on Sheet2 are blank.....(waiting for the right formula)
>>
>> What I need, to put the formula verbally, is: If sheet2 A:1= Sheet1 A:1,
> and
>> sheet1 L:1 is not blank, then copy sheet1 B:1,C:1 and L:1 to sheet2
> G:1,H:1
>> and I:1, respectively. BUT, I only want the first match it finds, not all
> of
>> them (if possible). I hope that makes sense
>>
>> Thanks for any help on this,
>>
>> Dan
>>
>>
>
>

[Max]

"Dan B" wrote:
> Thanks for you help. This is partially working. Its just not pulling the
> data over on all rows. I noticed that there some differences in the
company
> names, i.e. some abbreviations etc, so it is not always finding an exact
> match. But, I fixed some of those, and it still didn't pull those over.
> Any ideas on that?

There could be extraneous white spaces (leading, in-between or trailing
spaces), which are throwing the matching off. We could try wrapping TRIM
around to improve the robustness of the matching.

Try instead in G2, array-entered:
=IF(ISNA(MATCH(1,(TRIM(Sheet1!$A$2:$A$100)=TRIM($A2))*(TRIM(Sheet1!$L$2:$L$1
00)<>""),0)),"",INDEX(TRIM(Sheet1!B$2:B$100),MATCH(1,(TRIM(Sheet1!$A$2:$A$10
0)=TRIM($A2))*(TRIM(Sheet1!$L$2:$L$100)<>""),0)))

(copy to I2, amend I2, then re-fill the formula as before)
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---

[Dan B]

That made a big difference. I think the rest is just getting exact matches
in the company names. I'll just have to fix those manually. Thank you for
your expertise. I wish I knew Excel as well as you!

Thanks again,

Dan



"Max" <demechanik@yahoo.com> wrote in message
news:eXNpLqcLGHA.3064@TK2MSFTNGP10.phx.gbl...
> "Dan B" wrote:
>> Thanks for you help. This is partially working. Its just not pulling
>> the
>> data over on all rows. I noticed that there some differences in the
> company
>> names, i.e. some abbreviations etc, so it is not always finding an exact
>> match. But, I fixed some of those, and it still didn't pull those over.
>> Any ideas on that?
>
> There could be extraneous white spaces (leading, in-between or trailing
> spaces), which are throwing the matching off. We could try wrapping TRIM
> around to improve the robustness of the matching.
>
> Try instead in G2, array-entered:
> =IF(ISNA(MATCH(1,(TRIM(Sheet1!$A$2:$A$100)=TRIM($A2))*(TRIM(Sheet1!$L$2:$L$1
> 00)<>""),0)),"",INDEX(TRIM(Sheet1!B$2:B$100),MATCH(1,(TRIM(Sheet1!$A$2:$A$10
> 0)=TRIM($A2))*(TRIM(Sheet1!$L$2:$L$100)<>""),0)))
>
> (copy to I2, amend I2, then re-fill the formula as before)
> --
> Max
> Singapore
> http://savefile.com/projects/236895
> xdemechanik
> ---
>
>

[Max]

Glad it helped, Dan !
Thanks for the feedback ..
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"Dan B" <none@none.com> wrote in message
news:#orWpWdLGHA.208@tk2msftngp13.phx.gbl...
> That made a big difference. I think the rest is just getting
> exact matches in the company names.
> I'll just have to fix those manually.
> Thank you for your expertise. I wish I knew Excel as well as you!
>
> Thanks again,
>
> Dan