Problem - When couples have different last names

L

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 :confused:
 
D

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
 
S

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 said:
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
Click to expand...
 
L

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?
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.
Click to expand...
 
D

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 said:
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?
Click to expand...
 
L

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



Don said:
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]...
Click to expand...
 
D

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 said:
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
Click to expand...
 
S

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 said:
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
Click to expand...
 
Top