link columns

[Guest]

I want to have columns A and B (as what a layperson might call a "set") with C, which has the e-mail addresses. Now, I have my existing example below, but I want to "match" my short roster in columns A and B with the unexpurgated list of all emails in columnC. My short roster has fewer "smiths" than all smiths who have email here. Is there a way I can do this --and write a formula to do it? Thanks!

Name Number All Pop emails
Smith A 112 (e-mail address removed)
Smith F 117 (e-mail address removed)
Smith J 119 (e-mail address removed)
Smith K 120 (e-mail address removed)
Smith L 123 (e-mail address removed)
Smith M 125 (e-mail address removed)

 

[Max]

If I've read your intent correctly, this might work:

Taking your sample data indicated,
i.e. Names in col A, Email in col C

Put in say, E2: =SUBSTITUTE(TRIM(A2)," ","")

Copy E2 down as many rows
as you have data in col A

(this removes the space in-between the names in col A,
to enable use for the matching in col D)

Put in D2:
=IF(ISNA(OFFSET($A$1,MATCH(LEFT(C2,SEARCH("@",C2)-1),E:E,0)-1,0)),"--",OFFSE
T($A$1,MATCH(LEFT(C2,SEARCH("@",C2)-1),E:E,0)-1,0))

Copy D2 down as many rows
as you have data in col C (email add.)

Col D will return the match of the email in col C
with the names in col A,
i.e. the names found matched in col A will be indicated in col D

Unmatched email in col C will be returned as "--" in col D

--
Rgds
Max
xl 97
----------------------------------
Use xdemechanik <at>yahoo<dot>com for email
-----------------------------------------

I want to have columns A and B (as what a layperson might call a "set")

with C, which has the e-mail addresses. Now, I have my existing example
below, but I want to "match" my short roster in columns A and B with the
unexpurgated list of all emails in columnC. My short roster has fewer
"smiths" than all smiths who have email here. Is there a way I can do
this --and write a formula to do it? Thanks!

 

[Guest]

I enjoyed trying out the formulae! Seeing how cells can be transformed by a string of numbers and letters is amazing. Here's what we got:

Name Number Email
Smith A 112 (e-mail address removed) Smith A Smith
Smith F 117 (e-mail address removed) -- Smith
Smith J 119 (e-mail address removed) Smith F Smith
Smith K 120 (e-mail address removed) Smith J Smith
Smith L 123 (e-mail address removed) -- Smith
Smith M 125 (e-mail address removed) Smith K Smith

New question: Can I keep the cells (about 10 columns across) associated with its original name. I'm so 'new' to much of this. All I can see is that the e-mail becomes the 'fixed' (because it is the longer list)--is there a way I can ensure all the information with "Smith A" (in addition to the ID # 112, the address and such that are in the Smith A row? Or do I lose that with the MATCH? Sorry to be so needy! I never learned this in school (and I should have listened to my father: "Computers," he said. "The future is in computers.").
Any help appreciated.

 

[Max]

Not sure whether I'm "reading" your new Q correctly,
but here goes...

It may be neater to set it out this way:

In your original sheet, say Sheet1
--------------------------------------
Insert a new col to the left of col A

Put in the "new" A2: =SUBSTITUTE(TRIM(B2)," ","")
Copy A2 down col A until the last row of data in col B

[The above will not "disrupt" the set-up in your original sheet, it only
shifts everything one column to the right -- so your '10 columns across"
will be retained]

Cut > Paste the entire "All Pop emails" column from Sheet1 and put into a
new sheet, say in Sheet2 in col A, viz.:
----------------------------------------------------
All Pop emails
(e-mail address removed)
(e-mail address removed)
(e-mail address removed)
(e-mail address removed)
(e-mail address removed)
(e-mail address removed)
etc

Put in B2 (of this Sheet2):

=IF(ISNA(OFFSET(Sheet1!$B$1,MATCH(LEFT(A2,SEARCH("@",A2)-1),Sheet1!A:A,0)-1,
0)),"--",OFFSET(Sheet1!$B$1,MATCH(LEFT(A2,SEARCH("@",A2)-1),Sheet1!A:A,0)-1,
0))

Copy B2 down col B as many rows as you have data in col A

Put in C2 (of this Sheet2):

=IF($B2<>"--",OFFSET(Sheet1!$A$1,MATCH($B2,Sheet1!$B:$B,0)-1,COLUMN()-1),"--
")

Copy C2 across as many cols as you have data in Sheet1
Then copy down as many rows as there is data in col A

Cols C, D, E, F ... etc will retrieve the data in Sheet1
corresponding to the matched names found in col B
for the email in col A

[When done, just copy > paste the col labels over
from your Sheet1 to complete the labelling]

Any unmatched names in col B will have "--" right across
the entire row, so it's quite clear

Sheet2 can now be used as a alternate reference

You can also do a Data > Filter > Autofilter on say, col B
to filter by the name

--
Rgds
Max
xl 97
----------------------------------
Use xdemechanik <at>yahoo<dot>com for email
-----------------------------------------

I enjoyed trying out the formulae! Seeing how cells can be transformed by

a string of numbers and letters is amazing. Here's what we got:

Name Number Email
Smith A 112 (e-mail address removed) Smith A SmithA
Smith F 117 (e-mail address removed) -- SmithF
Smith J 119 (e-mail address removed) Smith F SmithJ
Smith K 120 (e-mail address removed) Smith J SmithK
Smith L 123 (e-mail address removed) -- SmithL
Smith M 125 (e-mail address removed) Smith K SmithM

New question: Can I keep the cells (about 10 columns across) associated

with its original name. I'm so 'new' to much of this. All I can see is
that the e-mail becomes the 'fixed' (because it is the longer list)--is
there a way I can ensure all the information with "Smith A" (in addition to
the ID # 112, the address and such that are in the Smith A row? Or do I
lose that with the MATCH? Sorry to be so needy! I never learned this in
school (and I should have listened to my father: "Computers," he said. "The
future is in computers.").

 

[Max]

Just to add-on a little..

For a cleaner look, you might want to suppress zeros from showing in Sheet2

To do so:

 

[Max]

If the email addresses under the "All Pop emails" column are in the form:

(e-mail address removed)
(e-mail address removed)
(e-mail address removed)
etc

where the names are separated by dots

Change the formula in the line (for Sheet1):

Put in the "new" A2: =SUBSTITUTE(TRIM(B2)," ","")
to

Put in the "new" A2: =SUBSTITUTE(TRIM(B2)," ",".")

Copy A2 down col A until the last row of data in col B

The formulas in Sheet2 should work now.