Help with a formula for concatenate and search/find with 3 columns

[Mel]

HELP!!!

I have been trying and trying...but no luck. Here is the issue..

I have in column D a name and in column E a name and in column A and address.

Now...in either column D or E there could be several instances where the
name appear several times. Keeping in mind that I have about 15,000 rows to
search/find.

D E A
name1 name4 address1
name1 name4 address2
name1 name4 address3
name1 name4 address4
name2 name5 address5
name2 name5 address6
name3, etc. name6, etc. address7, etc.

What I need to do is search column E for exact same name, search column D
for another name which will be different than the name in column E but will
have the same exact number of same name as in column D. Once the search is
complete I need to go to column A take those addresses in the same rows as
the same names and concatenate them into the first row where the first
address cell appears.


D E A
name1 name4 address1, address2, address3, address4
name1 name4
name1 name4
name1 name4
name2 name5 address5, address6
name2 name5
name3, etc. name6, etc. address7, etc.

These would all be real names and address and not name1, etc. that I am
working with.

PLEASE HELP....THERE HAS TO BE A WAY!!!

THANK YOU VEYR MUCH!!!!

 

[Myrna Larson]

My head is spinning.... but

You can insert 2 new columns on the right, say F and G, and in F2 write the
formula =COUNTIF($D$2:$D$15000,D2) and copy it down. In G2 write the formula
=COUNTIF($E$2:$E$15000,E2) and copy it down. That will give you the number of
times each name is repeated.

Then sort on columns F and G, which will get the number of repeats in order so
you can find matches.

I can't conceive of being able to match things up properly based strictly on
the number of repeats in columns D and E being the same. Is there really only
1 person (out of several thousand?) who is repeated 3 times? And why would a
name in column D that is repeated 4 times be expected to match a different
name in column E that also occurs 4 times?

What exactly is the data that you refer to as address1, address2, address3,
etc. Can you give an example of two different names that are supposedly the
same?

 

[Mel]

Now at work and actually looking...I only need to search the D column
D5 Alex Wolf & Co Inc
D6 Alex Wolf & Co Inc
D7 Alex Wolf & Co Inc
found these three...some times there is more sometimes there is less...

need a search for finding same names, exact spelling, etc. once found need
to go to column A where the addresses are...

A5 128-134 Willow St
A6 131 Riverside Dr,156 E 79th St,511-17 E 85th St,210 Riverside Dr,237-45 E
35th
St,250-70 W 94th St
A7 8811 Elmhurst Ave

and concatenate all addresses into A5 and do nothing with the column D but
just the orig. search and find...A6 and A7 would now be blank...

A5 128-134 Willow St, 131 Riverside Dr,156 E 79th St,511-17 E 85th St,210
Riverside Dr,237-45 E 35th St,250-70 W 94th St, 8811 Elmhurst Ave
A6
A7

Is there a way to do this without adding any more columns?

Sorry...my head was spinning with this all week...still can't come up with a
solution.

Thanks!!!!!

 

[Myrna Larson]

You would need a VBA macro to do this. You can't do it with formulas because
you want to modify multiple cells (in your example, change the text in A5 and
clear A6 and A7).

Now at work and actually looking...I only need to search the D column
D5 Alex Wolf & Co Inc
D6 Alex Wolf & Co Inc
D7 Alex Wolf & Co Inc
found these three...some times there is more sometimes there is less...

need a search for finding same names, exact spelling, etc. once found need
to go to column A where the addresses are...

A5 128-134 Willow St
A6 131 Riverside Dr,156 E 79th St,511-17 E 85th St,210 Riverside Dr,237-45 E
35th
St,250-70 W 94th St
A7 8811 Elmhurst Ave

and concatenate all addresses into A5 and do nothing with the column D but
just the orig. search and find...A6 and A7 would now be blank...

A5 128-134 Willow St, 131 Riverside Dr,156 E 79th St,511-17 E 85th St,210
Riverside Dr,237-45 E 35th St,250-70 W 94th St, 8811 Elmhurst Ave
A6
A7

Is there a way to do this without adding any more columns?

Sorry...my head was spinning with this all week...still can't come up with a
solution.

Thanks!!!!!

My head is spinning.... but

You can insert 2 new columns on the right, say F and G, and in F2 write the
formula =COUNTIF($D$2:$D$15000,D2) and copy it down. In G2 write the formula
=COUNTIF($E$2:$E$15000,E2) and copy it down. That will give you the number of
times each name is repeated.

Then sort on columns F and G, which will get the number of repeats in order so
you can find matches.

I can't conceive of being able to match things up properly based strictly on
the number of repeats in columns D and E being the same. Is there really only
1 person (out of several thousand?) who is repeated 3 times? And why would a
name in column D that is repeated 4 times be expected to match a different
name in column E that also occurs 4 times?

What exactly is the data that you refer to as address1, address2, address3,
etc. Can you give an example of two different names that are supposedly the
same?

 

[Myrna Larson]

If this is a one-time job, you can do it with formulas in 2 additional
columns, then paste the formula result over the original addresses. This
solution requires that the data be sorted by column D.

I will assume that we can use columns K and L for the formulas, and the first
data row is 5

In cell K5: =IF(D5=D6,A5&", "&K6,A5)
In cell L5: =IF(D5<>D4,K5,"")

Copy those formulas down as far as needed.

Then select L5 down through the last formula and Edit/Copy. Then select A5 and
Edit/Paste Special and select the Values option. Then you can delete columns K
and L.

Now at work and actually looking...I only need to search the D column
D5 Alex Wolf & Co Inc
D6 Alex Wolf & Co Inc
D7 Alex Wolf & Co Inc
found these three...some times there is more sometimes there is less...

need a search for finding same names, exact spelling, etc. once found need
to go to column A where the addresses are...

A5 128-134 Willow St
A6 131 Riverside Dr,156 E 79th St,511-17 E 85th St,210 Riverside Dr,237-45 E
35th
St,250-70 W 94th St
A7 8811 Elmhurst Ave

and concatenate all addresses into A5 and do nothing with the column D but
just the orig. search and find...A6 and A7 would now be blank...

A5 128-134 Willow St, 131 Riverside Dr,156 E 79th St,511-17 E 85th St,210
Riverside Dr,237-45 E 35th St,250-70 W 94th St, 8811 Elmhurst Ave
A6
A7

Is there a way to do this without adding any more columns?

Sorry...my head was spinning with this all week...still can't come up with a
solution.

Thanks!!!!!

My head is spinning.... but

You can insert 2 new columns on the right, say F and G, and in F2 write the
formula =COUNTIF($D$2:$D$15000,D2) and copy it down. In G2 write the formula
=COUNTIF($E$2:$E$15000,E2) and copy it down. That will give you the number of
times each name is repeated.

Then sort on columns F and G, which will get the number of repeats in order so
you can find matches.

I can't conceive of being able to match things up properly based strictly on
the number of repeats in columns D and E being the same. Is there really only
1 person (out of several thousand?) who is repeated 3 times? And why would a
name in column D that is repeated 4 times be expected to match a different
name in column E that also occurs 4 times?

What exactly is the data that you refer to as address1, address2, address3,
etc. Can you give an example of two different names that are supposedly the
same?