Concatenate, conditions, array formula?

[PSJupiter2]

Hello,

I have two sets of addresses. In one column there is a set of non duplicate house addresses. The second column has the mailing address of the owner of the house. I need to send letters to the home owners using a mail merge.

Physical Address	Mailing Address
1929 55 AVE S	1935 4 AVE NW
5527 20 ST S	1935 4 AVE NW
2168 58 AVE S	1935 4 AVE NW
2109 60 AVE S	1935 4 AVE NW
3436 32 ST S	1701 32 AVE S
3442 32 ST S	1701 32 AVE S
3228 15 AVE N	1701 32 AVE S
78 15 ST S	1701 32 AVE S
456 ELM CT S	1701 32 AVE S
1963 57 AVE S	1701 32 AVE S
1455 EAGLE DR	1455 EAGLE DR
6214 24 ST S	PO BOX 534
3817 22 ST S	PO BOX 534

<tbody>
</tbody>

If the data in column B is a duplicate value, I want to concatenate all of the corresponding values in column A into one column

Physical Address	Mailing Address
1929 55 AVE S 5527 20 ST S 2168 58 AVE S 2109 60 AVE S	1935 4 AVE NW
3436 32 ST S 3442 32 ST S 3228 15 AVE N 78 15 ST S 456 ELM CT S 1963 57 AVE S	1701 32 AVE S
1455 EAGLE DR	1455 EAGLE DR
6214 24 ST S 3817 22 ST S	PO BOX 534

<tbody>
</tbody>

The concatenate formula is something like:
=concatenate(A2,CHAR(10),A3,CHAR(10).....)

There are thousands of rows of data, so I don't want to manually type the above formula in.

I think I will be using some combination of: concatenate, index(match, vlookup, array formula, if

Can someone help me automate this or point me in a better direction?

Thank You

PSJupiter2

 

[jtakw]

Hi,

Assuming the Physical and/or Mailing addresses are always grouped together (meaning won't repeat again further down the list), and by "concatenate", you actually mean having the "Physical Address" in separate rows:

C2 formula copied down.

Excel 2010
	A	B	C
1	Physical Address	Mailing Address	New Mailing Address Column
2	1929 55 AVE S	1935 4 AVE NW	1935 4 AVE NW
3	5527 20 ST S	1935 4 AVE NW
4	2168 58 AVE S	1935 4 AVE NW
5	2109 60 AVE S	1935 4 AVE NW
6	3436 32 ST S	1701 32 AVE S	1701 32 AVE S
7	3442 32 ST S	1701 32 AVE S
8	3228 15 AVE N	1701 32 AVE S
9	78 15 ST S	1701 32 AVE S
10	456 ELM CT S	1701 32 AVE S
11	1963 57 AVE S	1701 32 AVE S
12	1455 EAGLE DR	1455 EAGLE DR	1455 EAGLE DR
13	6214 24 ST S	PO BOX 534	PO BOX 534
14	3817 22 ST S	PO BOX 534
15
16		Hide this Column
Sheet1

Cell Formulas
Range	Formula
C2	=IF(B1<>B2,B2,"")

Hide Column B:

Excel 2010
	A	C
1	Physical Address	New Mailing Address Column
2	1929 55 AVE S	1935 4 AVE NW
3	5527 20 ST S
4	2168 58 AVE S
5	2109 60 AVE S
6	3436 32 ST S	1701 32 AVE S
7	3442 32 ST S
8	3228 15 AVE N
9	78 15 ST S
10	456 ELM CT S
11	1963 57 AVE S
12	1455 EAGLE DR	1455 EAGLE DR
13	6214 24 ST S	PO BOX 534
14	3817 22 ST S
Sheet1

Cell Formulas
Range	Formula
C2	=IF(B1<>B2,B2,"")

 

[PSJupiter2]

Hello jtakw,

On one letter, I need the mail merge to list all of the "physical addresses" that correspond to the "mailing address".

This will prevent one property owner from getting an many individual letters.

Therefore, I believe I need the Physical addresses to be in one cell and separated by carriage returns.

Hi,

Assuming the Physical and/or Mailing addresses are always grouped together (meaning won't repeat again further down the list), and by "concatenate", you actually mean having the "Physical Address" in separate rows:

 

[jtakw]

OK, I can accomplish what you described by using 2 helper columns (that you can hide) like below:

Excel 2010
	A	B	C	D	E	F
1	Physical Address	Mailing Address	1st Helper	2nd Helper	New Physical Address Column	New Mailing Address Column
2	1929 55 AVE S	1935 4 AVE NW		1929 55 AVE S	1929 55 AVE S 5527 20 ST S 2168 58 AVE S 2109 60 AVE S	1935 4 AVE NW
3	5527 20 ST S	1935 4 AVE NW		1929 55 AVE S 5527 20 ST S	3436 32 ST S 3442 32 ST S 3228 15 AVE N 78 15 ST S 456 ELM CT S 1963 57 AVE S	1701 32 AVE S
4	2168 58 AVE S	1935 4 AVE NW		1929 55 AVE S 5527 20 ST S 2168 58 AVE S	1455 EAGLE DR	1455 EAGLE DR
5	2109 60 AVE S	1935 4 AVE NW	1	1929 55 AVE S 5527 20 ST S 2168 58 AVE S 2109 60 AVE S	6214 24 ST S 3817 22 ST S	PO BOX 534
6	3436 32 ST S	1701 32 AVE S		3436 32 ST S
7	3442 32 ST S	1701 32 AVE S		3436 32 ST S 3442 32 ST S
8	3228 15 AVE N	1701 32 AVE S		3436 32 ST S 3442 32 ST S 3228 15 AVE N
9	78 15 ST S	1701 32 AVE S		3436 32 ST S 3442 32 ST S 3228 15 AVE N 78 15 ST S
10	456 ELM CT S	1701 32 AVE S		3436 32 ST S 3442 32 ST S 3228 15 AVE N 78 15 ST S 456 ELM CT S
11	1963 57 AVE S	1701 32 AVE S	2	3436 32 ST S 3442 32 ST S 3228 15 AVE N 78 15 ST S 456 ELM CT S 1963 57 AVE S
12	1455 EAGLE DR	1455 EAGLE DR	3	1455 EAGLE DR
13	6214 24 ST S	PO BOX 534		6214 24 ST S
14	3817 22 ST S	PO BOX 534	4	6214 24 ST S 3817 22 ST S
Sheet1

Cell Formulas
Range	Formula
C2	=IF(B2<>B3,MAX(C$1:C1)+1,"")
D2	=IF(B2<>B1,A2,D1&CHAR(10)&A2)
E2	=IFERROR(VLOOKUP(SMALL(C$2:C$14,ROW(A1)),C$2:D$14,2,0),"")
F2	=IFERROR(INDEX(B$2:B$14,MATCH(SMALL(C$2:C$14,ROW(A1)),C$2:C$14,0)),"")

Formulas copied down.

 

[PSJupiter2]

Hi Jtakw,

This worked! Thank you for your quick reply.

Of note for anyone else using this method:

For this to work for you, the data set that is not to be grouped into one cell will need to be arranged in ascending order. In my case, because the mailing address is the "primary key" of sorts, they need to be ascended in order for the matching physical addresses to be grouped properly.

Thanks again for the help!

PSJupiter2

 

[jtakw]

You're welcome.

Actually, for the original data, the "Mailing Address" column can be in any sorted order, as long as it results in the "Physical Address" being grouped for that Mailing Address.