software

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
emailSheet2
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
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$100lt;gt;quot;quot;),0)),quot;quot;,IN
DEX(Sheet1!B$2:B$100,MATCH(1,(Sheet1!$A$2:$A$100=$ A2)*(Sheet1!$L$2:$L$100lt;gt;quot;
quot;),0)))
Copy G2 to I2

In I2, edit the index range part,
viz: .. INDEX(Sheet1!D$2$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
savefile.com/projects/236895
xdemechanik
---
quot;Dan Bquot; gt; wrote in message
...
gt; I have 2 sheets with customer contact information. The first list may
have
gt; several contacts under the same company, with their contact information,
gt; including email address. The second list (on a different tab) is just
gt; company information, no people, no email addresses. There are companies
on
gt; the first list that are not on the second. I want to copy any one email
gt; address, with first and last name to the second list when the company name
gt; on the second list matches one on the first.
gt;
gt; For example:
gt; Sheet1
gt; A B C....
gt; L
gt; Company First Name Last Name
gt; email
gt;
gt;
gt; Sheet2
gt; A B, C,D,... G H
gt; I
gt; Company First Name Last Name
gt; email
gt;
gt; Columns G,H,I on Sheet2 are blank.....(waiting for the right formula)
gt;
gt; What I need, to put the formula verbally, is: If sheet2 A:1= Sheet1 A:1,
and
gt; sheet1 L:1 is not blank, then copy sheet1 B:1,C:1 and L:1 to sheet2
G:1,H:1
gt; and I:1, respectively. BUT, I only want the first match it finds, not all
of
gt; them (if possible). I hope that makes sense
gt;
gt; Thanks for any help on this,
gt;
gt; Dan
gt;
gt;
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?

Thanksquot;Maxquot; gt; wrote in message
...
gt; One way which may suffice ..
gt;
gt; Assuming the source data in Sheet1 is within row2 - row100
gt;
gt; In Sheet2,
gt;
gt; Put in G2, array-enter (i.e. press CTRL SHIFT ENTER):
gt; =IF(ISNA(MATCH(1,(Sheet1!$A$2:$A$100=$A2)*(Sheet1! $L$2:$L$100lt;gt;quot;quot;),0)),quot;quot;,IN
gt; DEX(Sheet1!B$2:B$100,MATCH(1,(Sheet1!$A$2:$A$100=$ A2)*(Sheet1!$L$2:$L$100lt;gt;quot;
gt; quot;),0)))
gt; Copy G2 to I2
gt;
gt; In I2, edit the index range part,
gt; viz: .. INDEX(Sheet1!D$2$100,
gt;
gt; to point to col L instead (the email col in Sheet1),
gt; i.e. change it to: .. INDEX(Sheet1!L$2:L$100
gt; and array-enter the formula in I2 after editing
gt; (remember to array-enter!)
gt;
gt; Then re-select G2:I2, and copy down as far as required
gt;
gt; Cols G:I will return the desired results
gt;
gt; Adapt to suit ..
gt; --
gt; Max
gt; Singapore
gt; savefile.com/projects/236895
gt; xdemechanik
gt; ---
gt; quot;Dan Bquot; gt; wrote in message
gt; ...
gt;gt; I have 2 sheets with customer contact information. The first list may
gt; have
gt;gt; several contacts under the same company, with their contact information,
gt;gt; including email address. The second list (on a different tab) is just
gt;gt; company information, no people, no email addresses. There are companies
gt; on
gt;gt; the first list that are not on the second. I want to copy any one email
gt;gt; address, with first and last name to the second list when the company
gt;gt; name
gt;gt; on the second list matches one on the first.
gt;gt;
gt;gt; For example:
gt;gt; Sheet1
gt;gt; A B C....
gt;gt; L
gt;gt; Company First Name Last Name
gt;gt; email
gt;gt;
gt;gt;
gt;gt; Sheet2
gt;gt; A B, C,D,... G H
gt;gt; I
gt;gt; Company First Name Last Name
gt;gt; email
gt;gt;
gt;gt; Columns G,H,I on Sheet2 are blank.....(waiting for the right formula)
gt;gt;
gt;gt; What I need, to put the formula verbally, is: If sheet2 A:1= Sheet1 A:1,
gt; and
gt;gt; sheet1 L:1 is not blank, then copy sheet1 B:1,C:1 and L:1 to sheet2
gt; G:1,H:1
gt;gt; and I:1, respectively. BUT, I only want the first match it finds, not all
gt; of
gt;gt; them (if possible). I hope that makes sense
gt;gt;
gt;gt; Thanks for any help on this,
gt;gt;
gt;gt; Dan
gt;gt;
gt;gt;
gt;
gt;
quot;Dan Bquot; wrote:
gt; Thanks for you help. This is partially working. Its just not pulling the
gt; data over on all rows. I noticed that there some differences in the
company
gt; names, i.e. some abbreviations etc, so it is not always finding an exact
gt; match. But, I fixed some of those, and it still didn't pull those over.
gt; 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($A 2))*(TRIM(Sheet1!$L$2:$L$1
00)lt;gt;quot;quot;),0)),quot;quot;,INDEX(TRIM(Sheet1!B$2:B$100),MATCH (1,(TRIM(Sheet1!$A$2:$A$10
0)=TRIM($A2))*(TRIM(Sheet1!$L$2:$L$100)lt;gt;quot;quot;),0)))

(copy to I2, amend I2, then re-fill the formula as before)
--
Max
Singapore
savefile.com/projects/236895
xdemechanik
---
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
quot;Maxquot; gt; wrote in message
...
gt; quot;Dan Bquot; wrote:
gt;gt; Thanks for you help. This is partially working. Its just not pulling
gt;gt; the
gt;gt; data over on all rows. I noticed that there some differences in the
gt; company
gt;gt; names, i.e. some abbreviations etc, so it is not always finding an exact
gt;gt; match. But, I fixed some of those, and it still didn't pull those over.
gt;gt; Any ideas on that?
gt;
gt; There could be extraneous white spaces (leading, in-between or trailing
gt; spaces), which are throwing the matching off. We could try wrapping TRIM
gt; around to improve the robustness of the matching.
gt;
gt; Try instead in G2, array-entered:
gt; =IF(ISNA(MATCH(1,(TRIM(Sheet1!$A$2:$A$100)=TRIM($A 2))*(TRIM(Sheet1!$L$2:$L$1
gt; 00)lt;gt;quot;quot;),0)),quot;quot;,INDEX(TRIM(Sheet1!B$2:B$100),MATCH (1,(TRIM(Sheet1!$A$2:$A$10
gt; 0)=TRIM($A2))*(TRIM(Sheet1!$L$2:$L$100)lt;gt;quot;quot;),0)))
gt;
gt; (copy to I2, amend I2, then re-fill the formula as before)
gt; --
gt; Max
gt; Singapore
gt; savefile.com/projects/236895
gt; xdemechanik
gt; ---
gt;
gt;
Glad it helped, Dan !
Thanks for the feedback ..
--
Max
Singapore
savefile.com/projects/236895
xdemechanik
---
quot;Dan Bquot; gt; wrote in message
...
gt; That made a big difference. I think the rest is just getting
gt; exact matches in the company names.
gt; I'll just have to fix those manually.
gt; Thank you for your expertise. I wish I knew Excel as well as you!
gt;
gt; Thanks again,
gt;
gt; Dan