Problem - When couples have different last names

[lburg801]

I'm a Newbie. Church secretary died, no one knows how to use database as is. I have re-entered data from scratch in new worksheet - all of it!

Because many couples do not share same last name, it was suggested that I create these columns First_Name1, Last_Name1, AND, (actually a column containing the conjunction "and") First_Name2, Last_Name2.

This design was based on creating mailing labels and a directory. To have it work in printing labes, etc, I was to leave Last_Name1 blank if the couple shared the same last name. A limited test was positive with data imported by word for mailing labels - or did they export to Word? - I am so green!.

Since the test, I have added new names to the database. I just did them all at the bottom of the worksheet assuming I could sort the whole database alphabetically when done.

I was looking for a way to sort by Last name. Since there are single members, the column I would have to use is Last_Name1 column - so I'm sure you are far ahead of me in seeing the flaws in this setup and can possibly understand my state of panic at this point.

Trudy

[Don Guillett]

Without too much thought how about a column for the couple and another
column on the SAME row if there are two
Brown, Joe Betty
Brown, Joe Smith, Ann

then you could use
=a2 & " " & b2

--
Don Guillett
SalesAid Software
[email protected]
"lburg801" <[email protected]> wrote in
message news:[email protected]...
>
> I'm a Newbie. Church secretary died, no one knows how to use database as
> is. I have re-entered data from scratch in new worksheet - all of it!
>
> Because many couples do not share same last name, it was suggested that
> I create these columns First_Name1, Last_Name1, AND, (actually a column
> containing the conjunction "and") First_Name2, Last_Name2.
>
> This design was based on creating mailing labels and a directory. To
> have it work in printing labes, etc, I was to leave Last_Name1 blank if
> the couple shared the same last name. A limited test was positive with
> data imported by word for mailing labels - or did they export to Word?
> - I am so green!.
>
> Since the test, I have added new names to the database. I just did them
> all at the bottom of the worksheet assuming I could sort the whole
> database alphabetically when done.
>
> I was looking for a way to sort by Last name. Since there are single
> members, the column I would have to use is Last_Name1 column - so I'm
> sure you are far ahead of me in seeing the flaws in this setup and can
> possibly understand my state of panic at this point.
>
> Trudy
>
>
> --
> lburg801
> ------------------------------------------------------------------------
> lburg801's Profile:
http://www.excelforum.com/member.php...o&userid=28338
> View this thread: http://www.excelforum.com/showthread...hreadid=479579
>

[Search33]

You could use a 'helper' column to sort by... for example
A:First_Name1, B:Last_Name1, C:AND, D:First_Name2, E:Last_Name2, F:SORT

F:=if(isblank(B1),E1,B1) and copy it down the column. This will put
Last_Name1 in F if it exists and Last_Name2 if it does not.
Note: the = sign needs to be at the beggining of the formula

I think this is the easiest way that you won't have to rework the whole
spreadsheet...again.

"Don Guillett" wrote:

> Without too much thought how about a column for the couple and another
> column on the SAME row if there are two
> Brown, Joe Betty
> Brown, Joe Smith, Ann
>
> then you could use
> =a2 & " " & b2
>
> --
> Don Guillett
> SalesAid Software
> [email protected]
> "lburg801" <[email protected]> wrote in
> message news:[email protected]...
> >
> > I'm a Newbie. Church secretary died, no one knows how to use database as
> > is. I have re-entered data from scratch in new worksheet - all of it!
> >
> > Because many couples do not share same last name, it was suggested that
> > I create these columns First_Name1, Last_Name1, AND, (actually a column
> > containing the conjunction "and") First_Name2, Last_Name2.
> >
> > This design was based on creating mailing labels and a directory. To
> > have it work in printing labes, etc, I was to leave Last_Name1 blank if
> > the couple shared the same last name. A limited test was positive with
> > data imported by word for mailing labels - or did they export to Word?
> > - I am so green!.
> >
> > Since the test, I have added new names to the database. I just did them
> > all at the bottom of the worksheet assuming I could sort the whole
> > database alphabetically when done.
> >
> > I was looking for a way to sort by Last name. Since there are single
> > members, the column I would have to use is Last_Name1 column - so I'm
> > sure you are far ahead of me in seeing the flaws in this setup and can
> > possibly understand my state of panic at this point.
> >
> > Trudy
> >
> >
> > --
> > lburg801
> > ------------------------------------------------------------------------
> > lburg801's Profile:
> http://www.excelforum.com/member.php...o&userid=28338
> > View this thread: http://www.excelforum.com/showthread...hreadid=479579
> >
>
>
>

[lburg801]

Is there a way to format the whole column or do I have to change the cell numbers and apply it to each cell in the column?

[QUOTE=Search33]You could use a 'helper' column to sort by... for example
A:First_Name1, B:Last_Name1, C:AND, D:First_Name2, E:Last_Name2, F:SORT

F:=if(isblank(B1),E1,B1) and copy it down the column. This will put
Last_Name1 in F if it exists and Last_Name2 if it does not.
Note: the = sign needs to be at the beggining of the formula

I think this is the easiest way that you won't have to rework the whole
spreadsheet...again.

"Don Guillett" wrote:
[color=blue]
> Without too much thought how about a column for the couple and another
> column on the SAME row if there are two
> Brown, Joe Betty
> Brown, Joe Smith, Ann
>
> then you could use
> =a2 & " " & b2
>
> --
> Don Guillett
> SalesAid Software
> [email protected]
> "lburg801" <[email protected]> wrote in
> message news:[email protected]...
> >
> > I'm a Newbie. Church secretary died, no one knows how to use database as
> > is. I have re-entered data from scratch in new worksheet - all of it!
> >
> > Because many couples do not share same last name, it was suggested that
> > I create these columns First_Name1, Last_Name1, AND, (actually a column
> > containing the conjunction "and") First_Name2, Last_Name2.
> >
> > This design was based on creating mailing labels and a directory. To
> > have it work in printing labes, etc, I was to leave Last_Name1 blank if
> > the couple shared the same last name. A limited test was positive with
> > data imported by word for mailing labels - or did they export to Word?
> > - I am so green!.
> >
> > Since the test, I have added new names to the database. I just did them
> > all at the bottom of the worksheet assuming I could sort the whole
> > database alphabetically when done.
> >
> > I was looking for a way to sort by Last name. Since there are single
> > members, the column I would have to use is Last_Name1 column - so I'm
> > sure you are far ahead of me in seeing the flaws in this setup and can
> > possibly understand my state of panic at this point.
> >
> > Trudy
> >
> >
> > --
> > lburg801
> > ------------------------------------------------------------------------
> > lburg801's Profile:
> http://www.excelforum.com/member.php...o&userid=28338
> > View this thread: http://www.excelforum.com/showthread...hreadid=479579
> >Is there a way to format the whole column or do I have to change the cell numbers and apply it to each cell in the column?

[Don Guillett]

Please don't SHOUT! It is considered to be bad netiquette.
Yes, click on the A or B or ? and then format.

--
Don Guillett
SalesAid Software
[email protected]
"lburg801" <[email protected]> wrote in
message news:[email protected]...
>
> IS THERE A WAY TO FORMAT THE WHOLE COLUMN OR DO I HAVE TO CHANGE THE
> CELL NUMBERS AND APPLY IT TO EACH CELL IN THE COLUMN?
>
> Search33 Wrote:
> > You could use a 'helper' column to sort by... for example
> > A:First_Name1, B:Last_Name1, C:AND, D:First_Name2, E:Last_Name2,
> > F:SORT
> >
> > F:=if(isblank(B1),E1,B1) and copy it down the column. This will put
> > Last_Name1 in F if it exists and Last_Name2 if it does not.
> > Note: the = sign needs to be at the beggining of the formula
> >
> > I think this is the easiest way that you won't have to rework the
> > whole
> > spreadsheet...again.
> >
> > "Don Guillett" wrote:
> >
> > > Without too much thought how about a column for the couple and
> > another
> > > column on the SAME row if there are two
> > > Brown, Joe Betty
> > > Brown, Joe Smith, Ann
> > >
> > > then you could use
> > > =a2 & " " & b2
> > >
> > > --
> > > Don Guillett
> > > SalesAid Software
> > > [email protected]
> > > "lburg801" <[email protected]>
> > wrote in
> > > message
> >
news:[email protected]...[color=green]
> > > >
> > > > I'm a Newbie. Church secretary died, no one knows how to use
> > database as
> > > > is. I have re-entered data from scratch in new worksheet - all of
> > it!
> > > >
> > > > Because many couples do not share same last name, it was suggested
> > that
> > > > I create these columns First_Name1, Last_Name1, AND, (actually a
> > column
> > > > containing the conjunction "and") First_Name2, Last_Name2.
> > > >
> > > > This design was based on creating mailing labels and a directory.
> > To
> > > > have it work in printing labes, etc, I was to leave Last_Name1
> > blank if
> > > > the couple shared the same last name. A limited test was positive
> > with
> > > > data imported by word for mailing labels - or did they export to
> > Word?
> > > > - I am so green!.
> > > >
> > > > Since the test, I have added new names to the database. I just did
> > them
> > > > all at the bottom of the worksheet assuming I could sort the whole
> > > > database alphabetically when done.
> > > >
> > > > I was looking for a way to sort by Last name. Since there are
> > single
> > > > members, the column I would have to use is Last_Name1 column - so
> > I'm
> > > > sure you are far ahead of me in seeing the flaws in this setup and
> > can
> > > > possibly understand my state of panic at this point.
> > > >
> > > > Trudy
> > > >
> > > >
> > > > --
> > > > lburg801
> > > >
> > ------------------------------------------------------------------------
> > > > lburg801's Profile:
> > > http://www.excelforum.com/member.php...o&userid=28338
> > > > View this thread:
> > http://www.excelforum.com/showthread...hreadid=479579
> > > >IS THERE A WAY TO FORMAT THE WHOLE COLUMN OR DO I HAVE TO CHANGE THE
> > CELL NUMBERS AND APPLY IT TO EACH CELL IN THE COLUMN?
>
>
> --
> lburg801
> ------------------------------------------------------------------------
> lburg801's Profile:
http://www.excelforum.com/member.php...o&userid=28338
> View this thread: http://www.excelforum.com/showthread...hreadid=479579
>

[lburg801]

Don, I gather that my experimenting with font sizes is equated with shouting. I have some difficulty finding the new post, so it was more about me being confused. Pardon my netiquette faux pas. I need a blushing smilie to paste here.

Also, I may have used the wrong term when I said format. My question is there a way to apply a formula like the example you gave, since it refers to specific cells, to an entire column?

Thanks again,
Trudy

Please don't SHOUT! It is considered to be bad netiquette.
Yes, click on the A or B or ? and then format.

--
Don Guillett
SalesAid Software
[email protected]
"lburg801" <[email protected]> wrote in
message news:[email protected]...
>
> IS THERE A WAY TO FORMAT THE WHOLE COLUMN OR DO I HAVE TO CHANGE THE
> CELL NUMBERS AND APPLY IT TO EACH CELL IN THE COLUMN?
>
> Search33 Wrote:
> > You could use a 'helper' column to sort by... for example
> > A:First_Name1, B:Last_Name1, C:AND, D:First_Name2, E:Last_Name2,
> > F:SORT
> >
> > F:=if(isblank(B1),E1,B1) and copy it down the column. This will put
> > Last_Name1 in F if it exists and Last_Name2 if it does not.
> > Note: the = sign needs to be at the beggining of the formula
> >
> > I think this is the easiest way that you won't have to rework the
> > whole
> > spreadsheet...again.
> >
> > "Don Guillett" wrote:
> >
> > > Without too much thought how about a column for the couple and
> > another
> > > column on the SAME row if there are two
> > > Brown, Joe Betty
> > > Brown, Joe Smith, Ann
> > >
> > > then you could use
> > > =a2 & " " & b2
> > >
> > > --
> > > Don Guillett
> > > SalesAid Software
> > > [email protected]
> > > "lburg801" <[email protected]>
> > wrote in
> > > message
> >
news:[email protected]...[color=green]
> > > >
> > > > I'm a Newbie. Church secretary died, no one knows how to use
> > database as
> > > > is. I have re-entered data from scratch in new worksheet - all of
> > it!
> > > >
> > > > Because many couples do not share same last name, it was suggested
> > that
> > > > I create these columns First_Name1, Last_Name1, AND, (actually a
> > column
> > > > containing the conjunction "and") First_Name2, Last_Name2.
> > > >
> > > > This design was based on creating mailing labels and a directory.
> > To
> > > > have it work in printing labes, etc, I was to leave Last_Name1
> > blank if
> > > > the couple shared the same last name. A limited test was positive
> > with
> > > > data imported by word for mailing labels - or did they export to
> > Word?
> > > > - I am so green!.
> > > >
> > > > Since the test, I have added new names to the database. I just did
> > them
> > > > all at the bottom of the worksheet assuming I could sort the whole
> > > > database alphabetically when done.
> > > >
> > > > I was looking for a way to sort by Last name. Since there are
> > single
> > > > members, the column I would have to use is Last_Name1 column - so
> > I'm
> > > > sure you are far ahead of me in seeing the flaws in this setup and
> > can
> > > > possibly understand my state of panic at this point.
> > > >
> > > > Trudy
> > > >
> > > >
> > > > --
> > > > lburg801
> > > >
> > ------------------------------------------------------------------------
> > > > lburg801's Profile:
> > > http://www.excelforum.com/member.php...o&userid=28338
> > > > View this thread:
> > http://www.excelforum.com/showthread...hreadid=479579
> > > >IS THERE A WAY TO FORMAT THE WHOLE COLUMN OR DO I HAVE TO CHANGE THE
> > CELL NUMBERS AND APPLY IT TO EACH CELL IN THE COLUMN?
>
>
> --
> lburg801
> ------------------------------------------------------------------------
> lburg801's Profile:
http://www.excelforum.com/member.php...o&userid=28338
> View this thread: http://www.excelforum.com/showthread...hreadid=479579
>

[Don Guillett]

I would NOT (shouting here for emphasis) apply a formula to an entire
column. Just drag down as far as needed or use a macro to do it for you,
similar to this:

Sub putformula()
Set frng = Range("h8:h" & cells(rows.count,"a").End(xlUp).Row)
With frng
.Formula = "=a2 & " " & b2"

'uncomment line below to leave result only
' .Formula = .Value
End With
End Sub

--
Don Guillett
SalesAid Software
[email protected]
"lburg801" <[email protected]> wrote in
message news:[email protected]...
>
> Don, I gather that my experimenting with font sizes is equated with
> shouting. I have some difficulty finding the new post, so it was more
> about me being confused. Pardon my netiquette faux pas. I need a
> blushing smilie to paste here.
>
> Also, I may have used the wrong term when I said format. My question is
> there a way to apply a formula like the example you gave, since it
> refers to specific cells, to an entire column?
>
> Thanks again,
> Trudy
>
>
>
> Don Guillett Wrote:
> > Please don't SHOUT! It is considered to be bad netiquette.
> > Yes, click on the A or B or ? and then format.
> >
> > --
> > Don Guillett
> > SalesAid Software
> > [email protected]
> > "lburg801" <[email protected]>
> > wrote in
> > message news:[email protected]...
> > >
> > > IS THERE A WAY TO FORMAT THE WHOLE COLUMN OR DO I HAVE TO CHANGE THE
> > > CELL NUMBERS AND APPLY IT TO EACH CELL IN THE COLUMN?
> > >
> > > Search33 Wrote:
> > > > You could use a 'helper' column to sort by... for example
> > > > A:First_Name1, B:Last_Name1, C:AND, D:First_Name2, E:Last_Name2,
> > > > F:SORT
> > > >
> > > > F:=if(isblank(B1),E1,B1) and copy it down the column. This will
> > put
> > > > Last_Name1 in F if it exists and Last_Name2 if it does not.
> > > > Note: the = sign needs to be at the beggining of the formula
> > > >
> > > > I think this is the easiest way that you won't have to rework the
> > > > whole
> > > > spreadsheet...again.
> > > >
> > > > "Don Guillett" wrote:
> > > >
> > > > > Without too much thought how about a column for the couple and
> > > > another
> > > > > column on the SAME row if there are two
> > > > > Brown, Joe Betty
> > > > > Brown, Joe Smith, Ann
> > > > >
> > > > > then you could use
> > > > > =a2 & " " & b2
> > > > >
> > > > > --
> > > > > Don Guillett
> > > > > SalesAid Software
> > > > > [email protected]
> > > > > "lburg801"
> > <[email protected]>
> > > > wrote in
> > > > > message
> > > >
> >
news:[email protected]...[color=darkred]
> > > > > >
> > > > > > I'm a Newbie. Church secretary died, no one knows how to use
> > > > database as
> > > > > > is. I have re-entered data from scratch in new worksheet - all
> > of
> > > > it!
> > > > > >
> > > > > > Because many couples do not share same last name, it was
> > suggested
> > > > that
> > > > > > I create these columns First_Name1, Last_Name1, AND, (actually
> > a
> > > > column
> > > > > > containing the conjunction "and") First_Name2, Last_Name2.
> > > > > >
> > > > > > This design was based on creating mailing labels and a
> > directory.
> > > > To
> > > > > > have it work in printing labes, etc, I was to leave Last_Name1
> > > > blank if
> > > > > > the couple shared the same last name. A limited test was
> > positive
> > > > with
> > > > > > data imported by word for mailing labels - or did they export
> > to
> > > > Word?
> > > > > > - I am so green!.
> > > > > >
> > > > > > Since the test, I have added new names to the database. I just
> > did
> > > > them
> > > > > > all at the bottom of the worksheet assuming I could sort the
> > whole
> > > > > > database alphabetically when done.
> > > > > >
> > > > > > I was looking for a way to sort by Last name. Since there are
> > > > single
> > > > > > members, the column I would have to use is Last_Name1 column -
> > so
> > > > I'm
> > > > > > sure you are far ahead of me in seeing the flaws in this setup
> > and
> > > > can
> > > > > > possibly understand my state of panic at this point.
> > > > > >
> > > > > > Trudy
> > > > > >
> > > > > >
> > > > > > --
> > > > > > lburg801
> > > > > >
> > > >
> > ------------------------------------------------------------------------
> > > > > > lburg801's Profile:
> > > > > http://www.excelforum.com/member.php...o&userid=28338
> > > > > > View this thread:
> > > > http://www.excelforum.com/showthread...hreadid=479579
> > > > > >IS THERE A WAY TO FORMAT THE WHOLE COLUMN OR DO I HAVE TO CHANGE
> > THE
> > > > CELL NUMBERS AND APPLY IT TO EACH CELL IN THE COLUMN?
> > >
> > >
> > > --
> > > lburg801
> > >
> > ------------------------------------------------------------------------
> > > lburg801's Profile:
> > http://www.excelforum.com/member.php...o&userid=28338
> > > View this thread:
> > http://www.excelforum.com/showthread...hreadid=479579
> > >
>
>
> --
> lburg801
> ------------------------------------------------------------------------
> lburg801's Profile:
http://www.excelforum.com/member.php...o&userid=28338
> View this thread: http://www.excelforum.com/showthread...hreadid=479579
>

[Search33]

Once you type

=if(isblank(B1),E1,B1)

into the correct column and put the correct row numbers, all you need to do
is click on the bottom right corner of the cell and drag it down the column
to the bottom of your list.

To sort the list: Highlight Columns A through F
Click Data --> Sort
Change the top "Sort by" to Column F and choose Ascending or Descending and
click ok.

- Search

"lburg801" wrote:
[color=blue]
>
> Don, I gather that my experimenting with font sizes is equated with
> shouting. I have some difficulty finding the new post, so it was more
> about me being confused. Pardon my netiquette faux pas. I need a
> blushing smilie to paste here.
>
> Also, I may have used the wrong term when I said format. My question is
> there a way to apply a formula like the example you gave, since it
> refers to specific cells, to an entire column?
>
> Thanks again,
> Trudy
>
>
>
> Don Guillett Wrote:
> > Please don't SHOUT! It is considered to be bad netiquette.
> > Yes, click on the A or B or ? and then format.
> >
> > --
> > Don Guillett
> > SalesAid Software
> > [email protected]
> > "lburg801" <[email protected]>
> > wrote in
> > message news:[email protected]...
> > >
> > > IS THERE A WAY TO FORMAT THE WHOLE COLUMN OR DO I HAVE TO CHANGE THE
> > > CELL NUMBERS AND APPLY IT TO EACH CELL IN THE COLUMN?
> > >
> > > Search33 Wrote:
> > > > You could use a 'helper' column to sort by... for example
> > > > A:First_Name1, B:Last_Name1, C:AND, D:First_Name2, E:Last_Name2,
> > > > F:SORT
> > > >
> > > > F:=if(isblank(B1),E1,B1) and copy it down the column. This will
> > put
> > > > Last_Name1 in F if it exists and Last_Name2 if it does not.
> > > > Note: the = sign needs to be at the beggining of the formula
> > > >
> > > > I think this is the easiest way that you won't have to rework the
> > > > whole
> > > > spreadsheet...again.
> > > >
> > > > "Don Guillett" wrote:
> > > >
> > > > > Without too much thought how about a column for the couple and
> > > > another
> > > > > column on the SAME row if there are two
> > > > > Brown, Joe Betty
> > > > > Brown, Joe Smith, Ann
> > > > >
> > > > > then you could use
> > > > > =a2 & " " & b2
> > > > >
> > > > > --
> > > > > Don Guillett
> > > > > SalesAid Software
> > > > > [email protected]
> > > > > "lburg801"
> > <[email protected]>
> > > > wrote in
> > > > > message
> > > >
> > news:[email protected]...
> > > > > >
> > > > > > I'm a Newbie. Church secretary died, no one knows how to use
> > > > database as
> > > > > > is. I have re-entered data from scratch in new worksheet - all
> > of
> > > > it!
> > > > > >
> > > > > > Because many couples do not share same last name, it was
> > suggested
> > > > that
> > > > > > I create these columns First_Name1, Last_Name1, AND, (actually
> > a
> > > > column
> > > > > > containing the conjunction "and") First_Name2, Last_Name2.
> > > > > >
> > > > > > This design was based on creating mailing labels and a
> > directory.
> > > > To
> > > > > > have it work in printing labes, etc, I was to leave Last_Name1
> > > > blank if
> > > > > > the couple shared the same last name. A limited test was
> > positive
> > > > with
> > > > > > data imported by word for mailing labels - or did they export
> > to
> > > > Word?
> > > > > > - I am so green!.
> > > > > >
> > > > > > Since the test, I have added new names to the database. I just
> > did
> > > > them
> > > > > > all at the bottom of the worksheet assuming I could sort the
> > whole
> > > > > > database alphabetically when done.
> > > > > >
> > > > > > I was looking for a way to sort by Last name. Since there are
> > > > single
> > > > > > members, the column I would have to use is Last_Name1 column -
> > so
> > > > I'm
> > > > > > sure you are far ahead of me in seeing the flaws in this setup
> > and
> > > > can
> > > > > > possibly understand my state of panic at this point.
> > > > > >
> > > > > > Trudy
> > > > > >
> > > > > >
> > > > > > --
> > > > > > lburg801
> > > > > >
> > > >
> > ------------------------------------------------------------------------
> > > > > > lburg801's Profile:
> > > > > http://www.excelforum.com/member.php...o&userid=28338
> > > > > > View this thread:
> > > > http://www.excelforum.com/showthread...hreadid=479579
> > > > > >IS THERE A WAY TO FORMAT THE WHOLE COLUMN OR DO I HAVE TO CHANGE
> > THE
> > > > CELL NUMBERS AND APPLY IT TO EACH CELL IN THE COLUMN?
> > >
> > >
> > > --
> > > lburg801
> > >
> > ------------------------------------------------------------------------
> > > lburg801's Profile:
> > http://www.excelforum.com/member.php...o&userid=28338
> > > View this thread:
> > http://www.excelforum.com/showthread...hreadid=479579
> > >
>
>
> --
> lburg801
> ------------------------------------------------------------------------
> lburg801's Profile: http://www.excelforum.com/member.php...o&userid=28338
> View this thread: http://www.excelforum.com/showthread...hreadid=479579
>
>