Help

[Snehal]

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 ??

 

[Yogi Anand]

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)

 

[Yogi Anand]

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
	B	C	D	E	F	G	H	I	J
1	DB	Num1	Num2		DB
2	KayBaker	1	1		KayBaker			TRUE
3	123SunnyStreet	1	1		123SunnyStreet
4	BayCityMI40000	1	1		BayCityMI40000
5		0	0		MaryJones				source
6	JenFellows	1	2		505SunnySide				criteria
7	123SunnyStreet	2	2		SeaSideMN55555				copyto
8	BayCityMI40000	2	2		RamuPatel
9		0	0		111Meadows
10	RonBaker	1	3		NewYorkNY11111
11	123SunnyStreet	3	3		RonTaylor
12	BayCityMI40000	3	3		215Adams
13		0	0		RochesterHillsMI48888
14	MaryJones	1	1
15	505SunnySide	1	1
16	SeaSideMN55555	1	1
17		0	0
18	RickJones	1	2
19	505SunnySide	2	2
20	SeaSideMN55555	2	2
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.

 

[Yogi Anand]

hi Snehal:

Here is another approach that filters all the names with the same address together followed by the associated address ...

Book1
	B	C	D	E	F	G
1
2
3
4	DB		DB
5	KayBaker		KayBaker		TRUE
6	123SunnyStreet		JenFellows			TRUE
7	BayCityMI40000		RonBaker
8			123SunnyStreet
9	JenFellows		BayCityMI40000			source
10	123SunnyStreet		MaryJones			criteria
11	BayCityMI40000		505SunnySide			copyto
12			SeaSideMN55555			blankCells
13	RonBaker		RickJones
14	123SunnyStreet		505SunnySide
15	BayCityMI40000		SeaSideMN55555
16			RamuPatel
17	MaryJones		111Meadows
18	505SunnySide		NewYorkNY11111
19	SeaSideMN55555		RonTaylor
20			215Adams
21	RickJones		RochesterHillsMI48888
22	505SunnySide
23	SeaSideMN55555
24
25	RamuPatel
26	111Meadows
27	NewYorkNY11111
28
29	RonTaylor
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.

 

[Snehal]

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

 

[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

 

[Yogi Anand]

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!

 

[Snehal]

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

 

[Yogi Anand]

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?

 

[Snehal]

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