# Help

#### Snehal

##### New Member
I have an excel database of people with their names and addresses. We want to send a letter to each family, so I need to somehow find a function of getting multiple addresses to one ie I have a column of addresses in which there may be a family of 4 followed by a family of 3 then a family of 4 etc, but I need to send the letter to only one family member from each of the families. How do I do this ??

### Excel Facts

Customize Quick Access Toolbar. From All Commands, add Speak Cells or Speak Cells on Enter to QAT. Select cells. Press Speak Cells.
Hi Snehal:

Welcome to MrExcel Board!

It will be nice to see how exactly is your data laid out -- how about using HTMLmaker (COLO's Cool utility downloadable from MrExcel page) to post some sample data -- and then let us take it from there.

(In the mean time I will try to work with some assumptions about how your data might be laid out)

Hi Snehal:

Here is a formula based approach.

I assumed your names are in column B starting with row2, and that each address has 3 rows, followed by a blank row. With this assumtion, your source DB would look like in column B of the following illustration ...
Book1
BCDEFGHIJ
1DBNum1Num2DB
2KayBaker11KayBakerTRUE
3123SunnyStreet11123SunnyStreet
4BayCityMI4000011BayCityMI40000
500MaryJonessource
6JenFellows12505SunnySidecriteria
7123SunnyStreet22SeaSideMN55555copyto
8BayCityMI4000022RamuPatel
10RonBaker13NewYorkNY11111
11123SunnyStreet33RonTaylor
1300RochesterHillsMI48888
14MaryJones11
15505SunnySide11
16SeaSideMN5555511
1700
18RickJones12
19505SunnySide22
20SeaSideMN5555522
Sheet7 (2)

In column C I used the following formula ... =COUNTIF(\$B\$2:B2,B2)
and copied it as far down as needed.

In column D, I used the following formula ... =IF(C1=0,C3,C2)
and copied it down as far as needed

Then I used Advanced Filter using the criteria in cells I1:I2

The formula in cell I2 is ... =D2=1

and I filtered the records in column B to meet the specified criteria and copied those records to column F

Please post back if it helps -- otherwise explain a little further and then let us take it from there.

hi Snehal:

Here is another approach that filters all the names with the same address together followed by the associated address ...
Book1
BCDEFG
1
2
3
4DBDB
5KayBakerKayBakerTRUE
6123SunnyStreetJenFellowsTRUE
7BayCityMI40000RonBaker
8123SunnyStreet
9JenFellowsBayCityMI40000source
10123SunnyStreetMaryJonescriteria
11BayCityMI40000505SunnySidecopyto
12SeaSideMN55555blankCells
13RonBakerRickJones
14123SunnyStreet505SunnySide
15BayCityMI40000SeaSideMN55555
16RamuPatel
18505SunnySideNewYorkNY11111
19SeaSideMN55555RonTaylor
21RickJonesRochesterHillsMI48888
22505SunnySide
23SeaSideMN55555
24
25RamuPatel
27NewYorkNY11111
28
29RonTaylor
Sheet7 (3)

The formula in cell F5 is ... =NOT(B5=B9)
and formula in cell G6 is ... =NOT(B5=B1)

And of course you may use VBA to accomplish this task.

Thanks Yogi, The layout is as follows:
Column A has a unique ID number
Column B has Surnames
Column C has first names
Column D has addresses with street numbers and names ie 88 Robert st
Column E has Suburbd or towns ie Windsor
Column F has post codes
etc etc with other details

I'll try to download the program you mentioned so that I can send you a smple of the databasea.

Snehal

Hi Yogi,
Got the HTMLmaker, but am stuck when I try to convert the spreadsheet. I get the error message about " cant find project or library", but when I go to Tools > Reference, the reference is not highlighted and thus cannot be clicked. Our Database is simple as mentioned in the previous email.

I hope thats fo help to you

Thanks again
Snehal

Snehal said:
Hi Yogi,
Got the HTMLmaker, but am stuck when I try to convert the spreadsheet. I get the error message about " cant find project or library", but when I go to Tools > Reference, the reference is not highlighted and thus cannot be clicked. Our Database is simple as mentioned in the previous email.

I hope thats fo help to you

Thanks again
Snehal
Hi Snehal:

Once you have downloaded HTMLmaker (COLO's Cool utility), it has to be installed as an Add-in -- there are a number of posts on the Board and additional help references on MrExcel Board on this subject.

By the way, if the Add-in is successfully installed, you will see a menu item HT<u>M</u>L added to the main menu bar just left of Help.

Good Luck!

Hi Yogi,
Sorry about all this, but I have got the add-in in Excel and when I go to the HTML on the bar and try to convrt the sheet to HTML, I get the error message that compile error: cant find project or library. Anyway, if you look at the previous you will hopefully understand how the spreadsheet is setup.

Thanks again

snehal

Snehal said:
Thanks Yogi, The layout is as follows:
Column A has a unique ID number
Column B has Surnames
Column C has first names
Column D has addresses with street numbers and names ie 88 Robert st
Column E has Suburbd or towns ie Windsor
Column F has post codes
etc etc with other details

I'll try to download the program you mentioned so that I can send you a smple of the databasea.

Snehal
Hi Snehal

Need some clarification -- does a family with multiple names and the same address have one single ID for the entire family, or is there a separate Id for each family member's name and address?

Hi Yogi,
The spreadsheet looks something like this: The commas mean a new column:
Line 1
0001, Jones, Alan, 88 Robert rd, sydney, 2000
Line 2
0012, Jones, Stan, 88 Robert rd, sydney, 2000
Line 3
0007, Smith, Alex, 10 Darl ave, North sydney, 2001
Line 4
0007, Smith, Lisa, 10 Darl ave, North sydney, 2001

and so on.
I need to narrow this down to 1 address for each family

Thanks

snehal

Replies
5
Views
137
Replies
2
Views
75
Replies
1
Views
111
Replies
5
Views
154
Replies
0
Views
79

1,203,187
Messages
6,053,995
Members
444,696
Latest member
VASUCH

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back