For the sake of discussion, let's say you have ten entries of the same
person, same address etc on lines 1-10. You want to eliminate this
redundancy and reduce
this to just one entry of that persons name and address. Is it possible for
me to
eliminate the other 9 lines and retain just 1 line?

Thank you.
--
Templar

Suppose your names are in column A and that you have a header row so
that your data starts on row 2. Insert a new column B, and put this
formula in B2:

=IF(B2=B1,"",B2)

Copy this down as far as required. You can then hide column A so that
you have the effect you wanted, but the full names are still there if
required for other formulae (eg producing summaries for each person).

If you really want to you can fix the values in column B and then
delete column A. To fix the values, highlight column B, click <copy,
then Edit | Paste Special | Values (check) | OK then <Esc.

Hope this helps.

Pete

On Oct 3, 11:22*am, Templar wrote:
For the sake of discussion, let's say you have ten entries of the same
person, same address etc on lines 1-10. *You want to eliminate this
redundancy and reduce
this to just one entry of that persons name and address. *Is it possible for
me to
eliminate the other 9 lines and retain just 1 line?

Thank you.
--
Templar

Thanks Pete. The only problem with this formula is that, while it does
remove the redundancies from column B, it does not remove the rest of the
line. There are about ten columns of information on each line relating to
each name and, although the names disappear (except for 1), the blanks are
accompanied by the other nine columns of information which had been
associated with the redundant name (which of course is information identical
to each redundant anme).

How can I leave a clean spreadsheet of just one line (10 columns of
information)
per unique single name?
--
Templar


"Pete_UK" wrote:

Suppose your names are in column A and that you have a header row so
that your data starts on row 2. Insert a new column B, and put this
formula in B2:

=IF(B2=B1,"",B2)

Copy this down as far as required. You can then hide column A so that
you have the effect you wanted, but the full names are still there if
required for other formulae (eg producing summaries for each person).

If you really want to you can fix the values in column B and then
delete column A. To fix the values, highlight column B, click <copy,
then Edit | Paste Special | Values (check) | OK then <Esc.

Hope this helps.

Pete

On Oct 3, 11:22 am, Templar wrote:
For the sake of discussion, let's say you have ten entries of the same
person, same address etc on lines 1-10. You want to eliminate this
redundancy and reduce
this to just one entry of that persons name and address. Is it possible for
me to
eliminate the other 9 lines and retain just 1 line?

Thank you.
--
Templar

You could apply Autofilter to column B, and select Blanks from the
filter pull-down. Then highlight all the visible rows except for your
header and click on Edit | Delete Rows. Then select All from the
filter pull-down to see what is left.

Hope this helps.

Pete

On Oct 3, 3:55*pm, Templar wrote:
Thanks Pete. *The only problem with this formula is that, while it does
remove the redundancies from column B, it does not remove the rest of the
line. *There are about ten columns of information on each line relating to
each name and, although the names disappear (except for 1), the blanks are
accompanied by the other nine columns of information which had been
associated with the redundant name (which of course is information identical
to each redundant anme).

How can I leave a clean spreadsheet of just one line (10 columns of
information)
per unique single name?
--
Templar



"Pete_UK" wrote:
Suppose your names are in column A and that you have a header row so
that your data starts on row 2. Insert a new column B, and put this
formula in B2:

=IF(B2=B1,"",B2)

Copy this down as far as required. You can then hide column A so that
you have the effect you wanted, but the full names are still there if
required for other formulae (eg producing summaries for each person).

If you really want to you can fix the values in column B and then
delete column A. To fix the values, highlight column B, click <copy,
then Edit | Paste Special | Values (check) | OK then <Esc.

Hope this helps.

Pete

On Oct 3, 11:22 am, Templar wrote:
For the sake of discussion, let's say you have ten entries of the same
person, same address etc on lines 1-10. *You want to eliminate this
redundancy and reduce
this to just one entry of that persons name and address. *Is it possible for
me to
eliminate the other 9 lines and retain just 1 line?

Thank you.
--
Templar- Hide quoted text -

- Show quoted text -

OK. Let's see here. I have column A with all the names in it. I have
column B to which I've applied your formula effectively and therefore have
blanks in which redundant names were located. I highlighted Column B and
applied "Autofilter".
Nothing happened. I then looked for the word "Blanks" in a pull down menu
but could not find the word "Blank" or, therefore, select it.

What do you thing?
--
Templar


"Pete_UK" wrote:

You could apply Autofilter to column B, and select Blanks from the
filter pull-down. Then highlight all the visible rows except for your
header and click on Edit | Delete Rows. Then select All from the
filter pull-down to see what is left.

Hope this helps.

Pete

On Oct 3, 3:55 pm, Templar wrote:
Thanks Pete. The only problem with this formula is that, while it does
remove the redundancies from column B, it does not remove the rest of the
line. There are about ten columns of information on each line relating to
each name and, although the names disappear (except for 1), the blanks are
accompanied by the other nine columns of information which had been
associated with the redundant name (which of course is information identical
to each redundant anme).

How can I leave a clean spreadsheet of just one line (10 columns of
information)
per unique single name?
--
Templar



"Pete_UK" wrote:
Suppose your names are in column A and that you have a header row so
that your data starts on row 2. Insert a new column B, and put this
formula in B2:

=IF(B2=B1,"",B2)

Copy this down as far as required. You can then hide column A so that
you have the effect you wanted, but the full names are still there if
required for other formulae (eg producing summaries for each person).

If you really want to you can fix the values in column B and then
delete column A. To fix the values, highlight column B, click <copy,
then Edit | Paste Special | Values (check) | OK then <Esc.

Hope this helps.

Pete

On Oct 3, 11:22 am, Templar wrote:
For the sake of discussion, let's say you have ten entries of the same
person, same address etc on lines 1-10. You want to eliminate this
redundancy and reduce
this to just one entry of that persons name and address. Is it possible for
me to
eliminate the other 9 lines and retain just 1 line?

Thank you.
--
Templar- Hide quoted text -

- Show quoted text -

In B1 you should have a downward pointing arrow, and if you click on
that you will get the filter choices:

(All)
(Top 10...)
(Custom...)
....
your names
....
(Blanks)
(Non-blanks)

You might need to scroll down the list to get to Blanks if you have a
lots of names, but then you just click on it. Only the rows which have
blanks in that column will be displayed (and the colour of the row
identifiers will change - if you look at these you will notice that
some are missing, as they have been filtered out, or temporarily
hidden). Then you can follow the other instructions.

Hope this helps.

Pete


On Oct 3, 4:47*pm, Templar wrote:
OK. *Let's see here. *I have column A with all the names in it. *I have
column B to which I've applied your formula effectively and therefore have
blanks in which redundant names were located. *I highlighted Column B and
applied "Autofilter".
Nothing happened. *I then looked for the word "Blanks" in a pull down menu
but could not find the word "Blank" or, therefore, select it. *

What do you thing?
--
Templar



"Pete_UK" wrote:
You could apply Autofilter to column B, and select Blanks from the
filter pull-down. Then highlight all the visible rows except for your
header and click on Edit | Delete Rows. Then select All from the
filter pull-down to see what is left.

Hope this helps.

Pete

On Oct 3, 3:55 pm, Templar wrote:
Thanks Pete. *The only problem with this formula is that, while it does
remove the redundancies from column B, it does not remove the rest of the
line. *There are about ten columns of information on each line relating to
each name and, although the names disappear (except for 1), the blanks are
accompanied by the other nine columns of information which had been
associated with the redundant name (which of course is information identical
to each redundant anme).

How can I leave a clean spreadsheet of just one line (10 columns of
information)
per unique single name?
--
Templar

"Pete_UK" wrote:
Suppose your names are in column A and that you have a header row so
that your data starts on row 2. Insert a new column B, and put this
formula in B2:

=IF(B2=B1,"",B2)

Copy this down as far as required. You can then hide column A so that
you have the effect you wanted, but the full names are still there if
required for other formulae (eg producing summaries for each person).

If you really want to you can fix the values in column B and then
delete column A. To fix the values, highlight column B, click <copy,
then Edit | Paste Special | Values (check) | OK then <Esc.

Hope this helps.

Pete

On Oct 3, 11:22 am, Templar wrote:
For the sake of discussion, let's say you have ten entries of the same
person, same address etc on lines 1-10. *You want to eliminate this
redundancy and reduce
this to just one entry of that persons name and address. *Is it possible for
me to
eliminate the other 9 lines and retain just 1 line?

Thank you.
--
Templar- Hide quoted text -

- Show quoted text -- Hide quoted text -

- Show quoted text -

OK. It seems to have worked. Below I've block copied a small portion of the
document. I'm just wondering what the #REF! means in column B?
Column A is titled "First Name" and Column B is titled "Sorted". Note that
the
name from Column A sometimes appears in Colunm B and other times I see
#REF!

First Name Sorted
ABBITT ABBITT
ABEL #REF!
ABITT ABITT
ABRAMS #REF!
ABREGO #REF!
ABROMITIS #REF!
ACEVEDO #REF!
ACKER #REF!
ACKERMAN #REF!
ACKLEY #REF!
ADAIRE ADAIRE




--
Templar


"Pete_UK" wrote:

In B1 you should have a downward pointing arrow, and if you click on
that you will get the filter choices:

(All)
(Top 10...)
(Custom...)
....
your names
....
(Blanks)
(Non-blanks)

You might need to scroll down the list to get to Blanks if you have a
lots of names, but then you just click on it. Only the rows which have
blanks in that column will be displayed (and the colour of the row
identifiers will change - if you look at these you will notice that
some are missing, as they have been filtered out, or temporarily
hidden). Then you can follow the other instructions.

Hope this helps.

Pete


On Oct 3, 4:47 pm, Templar wrote:
OK. Let's see here. I have column A with all the names in it. I have
column B to which I've applied your formula effectively and therefore have
blanks in which redundant names were located. I highlighted Column B and
applied "Autofilter".
Nothing happened. I then looked for the word "Blanks" in a pull down menu
but could not find the word "Blank" or, therefore, select it.

What do you thing?
--
Templar



"Pete_UK" wrote:
You could apply Autofilter to column B, and select Blanks from the
filter pull-down. Then highlight all the visible rows except for your
header and click on Edit | Delete Rows. Then select All from the
filter pull-down to see what is left.

Hope this helps.

Pete

On Oct 3, 3:55 pm, Templar wrote:
Thanks Pete. The only problem with this formula is that, while it does
remove the redundancies from column B, it does not remove the rest of the
line. There are about ten columns of information on each line relating to
each name and, although the names disappear (except for 1), the blanks are
accompanied by the other nine columns of information which had been
associated with the redundant name (which of course is information identical
to each redundant anme).

How can I leave a clean spreadsheet of just one line (10 columns of
information)
per unique single name?
--
Templar

"Pete_UK" wrote:
Suppose your names are in column A and that you have a header row so
that your data starts on row 2. Insert a new column B, and put this
formula in B2:

=IF(B2=B1,"",B2)

Copy this down as far as required. You can then hide column A so that
you have the effect you wanted, but the full names are still there if
required for other formulae (eg producing summaries for each person).

If you really want to you can fix the values in column B and then
delete column A. To fix the values, highlight column B, click <copy,
then Edit | Paste Special | Values (check) | OK then <Esc.

Hope this helps.

Pete

On Oct 3, 11:22 am, Templar wrote:
For the sake of discussion, let's say you have ten entries of the same
person, same address etc on lines 1-10. You want to eliminate this
redundancy and reduce
this to just one entry of that persons name and address. Is it possible for
me to
eliminate the other 9 lines and retain just 1 line?

Thank you.
--
Templar- Hide quoted text -

- Show quoted text -- Hide quoted text -

- Show quoted text -

That is because you still have the formulae in column B (rather than
fixing the values first) and those formulae are refering to cells
which you have now deleted. However, if you put your cursor B2 and
double-click the fill handle (the small black square in the bottom
corner of the cursor outline), then the formula will be copied down
and you should now get the same name in column B as you have in column
A.

However, if you don't need column B you can just highlight it and Edit
| Delete to remove it, so you will end up with the same data layout
that you had originally but with those duplicate rows removed.

Hope this helps.

Pete

On Oct 3, 6:01*pm, Templar wrote:
OK. *It seems to have worked. *Below I've block copied a small portion of the
document. *I'm just wondering what the #REF! means in column B?
Column A is titled "First Name" and Column B is titled "Sorted". *Note that
the
name from Column A sometimes appears in Colunm B and other times I see
#REF!

First Name * * * Sorted
ABBITT *ABBITT
ABEL * *#REF!
ABITT * ABITT
ABRAMS *#REF!
ABREGO *#REF!
ABROMITIS * * * #REF!
ACEVEDO #REF!
ACKER * #REF!
ACKERMAN * * * *#REF!
ACKLEY *#REF!
ADAIRE *ADAIRE

--
Templar



"Pete_UK" wrote:
In B1 you should have a downward pointing arrow, and if you click on
that you will get the filter choices:

(All)
(Top 10...)
(Custom...)
....
your names
....
(Blanks)
(Non-blanks)

You might need to scroll down the list to get to Blanks if you have a
lots of names, but then you just click on it. Only the rows which have
blanks in that column will be displayed (and the colour of the row
identifiers will change - if you look at these you will notice that
some are missing, as they have been filtered out, or temporarily
hidden). Then you can follow the other instructions.

Hope this helps.

Pete

On Oct 3, 4:47 pm, Templar wrote:
OK. *Let's see here. *I have column A with all the names in it. *I have
column B to which I've applied your formula effectively and therefore have
blanks in which redundant names were located. *I highlighted Column B and
applied "Autofilter".
Nothing happened. *I then looked for the word "Blanks" in a pull down menu
but could not find the word "Blank" or, therefore, select it. *

What do you thing?
--
Templar

"Pete_UK" wrote:
You could apply Autofilter to column B, and select Blanks from the
filter pull-down. Then highlight all the visible rows except for your
header and click on Edit | Delete Rows. Then select All from the
filter pull-down to see what is left.

Hope this helps.

Pete

On Oct 3, 3:55 pm, Templar wrote:
Thanks Pete. *The only problem with this formula is that, while it does
remove the redundancies from column B, it does not remove the rest of the
line. *There are about ten columns of information on each line relating to
each name and, although the names disappear (except for 1), the blanks are
accompanied by the other nine columns of information which had been
associated with the redundant name (which of course is information identical
to each redundant anme).

How can I leave a clean spreadsheet of just one line (10 columns of
information)
per unique single name?
--
Templar

"Pete_UK" wrote:
Suppose your names are in column A and that you have a header row so
that your data starts on row 2. Insert a new column B, and put this
formula in B2:

=IF(B2=B1,"",B2)

Copy this down as far as required. You can then hide column A so that
you have the effect you wanted, but the full names are still there if
required for other formulae (eg producing summaries for each person).

If you really want to you can fix the values in column B and then
delete column A. To fix the values, highlight column B, click <copy,
then Edit | Paste Special | Values (check) | OK then <Esc.

Hope this helps.

Pete

On Oct 3, 11:22 am, Templar wrote:
For the sake of discussion, let's say you have ten entries of the same
person, same address etc on lines 1-10. *You want to eliminate this
redundancy and reduce
this to just one entry of that persons name and address. *Is it possible for
me to
eliminate the other 9 lines and retain just 1 line?

Thank you.
--
Templar- Hide quoted text -

- Show quoted text -- Hide quoted text -

- Show quoted text -- Hide quoted text -

- Show quoted text -

I just did what you suggested and I got the #REF! down the whole row.
--
Templar


"Pete_UK" wrote:

That is because you still have the formulae in column B (rather than
fixing the values first) and those formulae are refering to cells
which you have now deleted. However, if you put your cursor B2 and
double-click the fill handle (the small black square in the bottom
corner of the cursor outline), then the formula will be copied down
and you should now get the same name in column B as you have in column
A.

However, if you don't need column B you can just highlight it and Edit
| Delete to remove it, so you will end up with the same data layout
that you had originally but with those duplicate rows removed.

Hope this helps.

Pete

On Oct 3, 6:01 pm, Templar wrote:
OK. It seems to have worked. Below I've block copied a small portion of the
document. I'm just wondering what the #REF! means in column B?
Column A is titled "First Name" and Column B is titled "Sorted". Note that
the
name from Column A sometimes appears in Colunm B and other times I see
#REF!

First Name Sorted
ABBITT ABBITT
ABEL #REF!
ABITT ABITT
ABRAMS #REF!
ABREGO #REF!
ABROMITIS #REF!
ACEVEDO #REF!
ACKER #REF!
ACKERMAN #REF!
ACKLEY #REF!
ADAIRE ADAIRE

--
Templar



"Pete_UK" wrote:
In B1 you should have a downward pointing arrow, and if you click on
that you will get the filter choices:

(All)
(Top 10...)
(Custom...)
....
your names
....
(Blanks)
(Non-blanks)

You might need to scroll down the list to get to Blanks if you have a
lots of names, but then you just click on it. Only the rows which have
blanks in that column will be displayed (and the colour of the row
identifiers will change - if you look at these you will notice that
some are missing, as they have been filtered out, or temporarily
hidden). Then you can follow the other instructions.

Hope this helps.

Pete

On Oct 3, 4:47 pm, Templar wrote:
OK. Let's see here. I have column A with all the names in it. I have
column B to which I've applied your formula effectively and therefore have
blanks in which redundant names were located. I highlighted Column B and
applied "Autofilter".
Nothing happened. I then looked for the word "Blanks" in a pull down menu
but could not find the word "Blank" or, therefore, select it.

What do you thing?
--
Templar

"Pete_UK" wrote:
You could apply Autofilter to column B, and select Blanks from the
filter pull-down. Then highlight all the visible rows except for your
header and click on Edit | Delete Rows. Then select All from the
filter pull-down to see what is left.

Hope this helps.

Pete

On Oct 3, 3:55 pm, Templar wrote:
Thanks Pete. The only problem with this formula is that, while it does
remove the redundancies from column B, it does not remove the rest of the
line. There are about ten columns of information on each line relating to
each name and, although the names disappear (except for 1), the blanks are
accompanied by the other nine columns of information which had been
associated with the redundant name (which of course is information identical
to each redundant anme).

How can I leave a clean spreadsheet of just one line (10 columns of
information)
per unique single name?
--
Templar

"Pete_UK" wrote:
Suppose your names are in column A and that you have a header row so
that your data starts on row 2. Insert a new column B, and put this
formula in B2:

=IF(B2=B1,"",B2)

Copy this down as far as required. You can then hide column A so that
you have the effect you wanted, but the full names are still there if
required for other formulae (eg producing summaries for each person).

If you really want to you can fix the values in column B and then
delete column A. To fix the values, highlight column B, click <copy,
then Edit | Paste Special | Values (check) | OK then <Esc.

Hope this helps.

Pete

On Oct 3, 11:22 am, Templar wrote:
For the sake of discussion, let's say you have ten entries of the same
person, same address etc on lines 1-10. You want to eliminate this
redundancy and reduce
this to just one entry of that persons name and address. Is it possible for
me to
eliminate the other 9 lines and retain just 1 line?

Thank you.
--
Templar- Hide quoted text -

- Show quoted text -- Hide quoted text -

- Show quoted text -- Hide quoted text -

- Show quoted text -

The formula in B2 should be:

=IF(A2=A1,"",A2)

and this should give you the same name as is in A2 - perhaps you
already had #REF in B2 with a formula like:

=IF(A2=#REF,"",A2)

because you had deleted A1. Then just copy the correct formula down
and you should get all the other names in column B.

But, as I said, you don't need column B anymore so you can just delete
it - this is often referred to as a helper column, and you use it just
temporarily to help you achieve your aims.

Hope this helps.

Pete

On Oct 3, 8:19*pm, Templar wrote:
I just did what you suggested and I got the #REF! *down the whole row.
--
Templar



"Pete_UK" wrote:
That is because you still have the formulae in column B (rather than
fixing the values first) and those formulae are refering to cells
which you have now deleted. However, if you put your cursor B2 and
double-click the fill handle (the small black square in the bottom
corner of the cursor outline), then the formula will be copied down
and you should now get the same name in column B as you have in column
A.

However, if you don't need column B you can just highlight it and Edit
| Delete to remove it, so you will end up with the same data layout
that you had originally but with those duplicate rows removed.

Hope this helps.

Pete

On Oct 3, 6:01 pm, Templar wrote:
OK. *It seems to have worked. *Below I've block copied a small portion of the
document. *I'm just wondering what the #REF! means in column B?
Column A is titled "First Name" and Column B is titled "Sorted". *Note that
the
name from Column A sometimes appears in Colunm B and other times I see
#REF!

First Name * * * Sorted
ABBITT *ABBITT
ABEL * *#REF!
ABITT * ABITT
ABRAMS *#REF!
ABREGO *#REF!
ABROMITIS * * * #REF!
ACEVEDO #REF!
ACKER * #REF!
ACKERMAN * * * *#REF!
ACKLEY *#REF!
ADAIRE *ADAIRE

--
Templar

"Pete_UK" wrote:
In B1 you should have a downward pointing arrow, and if you click on
that you will get the filter choices:

(All)
(Top 10...)
(Custom...)
....
your names
....
(Blanks)
(Non-blanks)

You might need to scroll down the list to get to Blanks if you have a
lots of names, but then you just click on it. Only the rows which have
blanks in that column will be displayed (and the colour of the row
identifiers will change - if you look at these you will notice that
some are missing, as they have been filtered out, or temporarily
hidden). Then you can follow the other instructions.

Hope this helps.

Pete

On Oct 3, 4:47 pm, Templar wrote:
OK. *Let's see here. *I have column A with all the names in it. *I have
column B to which I've applied your formula effectively and therefore have
blanks in which redundant names were located. *I highlighted Column B and
applied "Autofilter".
Nothing happened. *I then looked for the word "Blanks" in a pull down menu
but could not find the word "Blank" or, therefore, select it. *

What do you thing?
--
Templar

"Pete_UK" wrote:
You could apply Autofilter to column B, and select Blanks from the
filter pull-down. Then highlight all the visible rows except for your
header and click on Edit | Delete Rows. Then select All from the
filter pull-down to see what is left.

Hope this helps.

Pete

On Oct 3, 3:55 pm, Templar wrote:
Thanks Pete. *The only problem with this formula is that, while it does
remove the redundancies from column B, it does not remove the rest of the
line. *There are about ten columns of information on each line relating to
each name and, although the names disappear (except for 1), the blanks are
accompanied by the other nine columns of information which had been
associated with the redundant name (which of course is information identical
to each redundant anme).

How can I leave a clean spreadsheet of just one line (10 columns of
information)
per unique single name?
--
Templar

"Pete_UK" wrote:
Suppose your names are in column A and that you have a header row so
that your data starts on row 2. Insert a new column B, and put this
formula in B2:

=IF(B2=B1,"",B2)

Copy this down as far as required. You can then hide column A so that
you have the effect you wanted, but the full names are still there if
required for other formulae (eg producing summaries for each person).

If you really want to you can fix the values in column B and then
delete column A. To fix the values, highlight column B, click <copy,
then Edit | Paste Special | Values (check) | OK then <Esc.

Hope this helps.

Pete

On Oct 3, 11:22 am, Templar wrote:
For the sake of discussion, let's say you have ten entries of the same
person, same address etc on lines 1-10. *You want to eliminate this
redundancy and reduce
this to just one entry of that persons name and address. *Is it possible for
me to
eliminate the other 9 lines and retain just 1 line?

Thank you.
--
Templar- Hide quoted text -

- Show quoted text -- Hide quoted text -

- Show quoted text -- Hide quoted text -

- Show quoted text -- Hide quoted text -

- Show quoted text -

DataFilterAdvanced Filter.

Check "Unique records only" and "Copy to another location".

Select a cell below the data range to copy to.

Delete the original data after the copy to is successful.


Gord Dibben MS Excel MVP

On Fri, 3 Oct 2008 03:22:03 -0700, Templar
wrote:

For the sake of discussion, let's say you have ten entries of the same
person, same address etc on lines 1-10. You want to eliminate this
redundancy and reduce
this to just one entry of that persons name and address. Is it possible for
me to
eliminate the other 9 lines and retain just 1 line?

Thank you.

Pete: Thanks for all your help. I ended up deleting the column. It seems
to be working just fine. I greatly appreciate your help. Thanks again.
--
Templar


"Pete_UK" wrote:

The formula in B2 should be:

=IF(A2=A1,"",A2)

and this should give you the same name as is in A2 - perhaps you
already had #REF in B2 with a formula like:

=IF(A2=#REF,"",A2)

because you had deleted A1. Then just copy the correct formula down
and you should get all the other names in column B.

But, as I said, you don't need column B anymore so you can just delete
it - this is often referred to as a helper column, and you use it just
temporarily to help you achieve your aims.

Hope this helps.

Pete

On Oct 3, 8:19 pm, Templar wrote:
I just did what you suggested and I got the #REF! down the whole row.
--
Templar



"Pete_UK" wrote:
That is because you still have the formulae in column B (rather than
fixing the values first) and those formulae are refering to cells
which you have now deleted. However, if you put your cursor B2 and
double-click the fill handle (the small black square in the bottom
corner of the cursor outline), then the formula will be copied down
and you should now get the same name in column B as you have in column
A.

However, if you don't need column B you can just highlight it and Edit
| Delete to remove it, so you will end up with the same data layout
that you had originally but with those duplicate rows removed.

Hope this helps.

Pete

On Oct 3, 6:01 pm, Templar wrote:
OK. It seems to have worked. Below I've block copied a small portion of the
document. I'm just wondering what the #REF! means in column B?
Column A is titled "First Name" and Column B is titled "Sorted". Note that
the
name from Column A sometimes appears in Colunm B and other times I see
#REF!

First Name Sorted
ABBITT ABBITT
ABEL #REF!
ABITT ABITT
ABRAMS #REF!
ABREGO #REF!
ABROMITIS #REF!
ACEVEDO #REF!
ACKER #REF!
ACKERMAN #REF!
ACKLEY #REF!
ADAIRE ADAIRE

--
Templar

"Pete_UK" wrote:
In B1 you should have a downward pointing arrow, and if you click on
that you will get the filter choices:

(All)
(Top 10...)
(Custom...)
....
your names
....
(Blanks)
(Non-blanks)

You might need to scroll down the list to get to Blanks if you have a
lots of names, but then you just click on it. Only the rows which have
blanks in that column will be displayed (and the colour of the row
identifiers will change - if you look at these you will notice that
some are missing, as they have been filtered out, or temporarily
hidden). Then you can follow the other instructions.

Hope this helps.

Pete

On Oct 3, 4:47 pm, Templar wrote:
OK. Let's see here. I have column A with all the names in it. I have
column B to which I've applied your formula effectively and therefore have
blanks in which redundant names were located. I highlighted Column B and
applied "Autofilter".
Nothing happened. I then looked for the word "Blanks" in a pull down menu
but could not find the word "Blank" or, therefore, select it.

What do you thing?
--
Templar

"Pete_UK" wrote:
You could apply Autofilter to column B, and select Blanks from the
filter pull-down. Then highlight all the visible rows except for your
header and click on Edit | Delete Rows. Then select All from the
filter pull-down to see what is left.

Hope this helps.

Pete

On Oct 3, 3:55 pm, Templar wrote:
Thanks Pete. The only problem with this formula is that, while it does
remove the redundancies from column B, it does not remove the rest of the
line. There are about ten columns of information on each line relating to
each name and, although the names disappear (except for 1), the blanks are
accompanied by the other nine columns of information which had been
associated with the redundant name (which of course is information identical
to each redundant anme).

How can I leave a clean spreadsheet of just one line (10 columns of
information)
per unique single name?
--
Templar

"Pete_UK" wrote:
Suppose your names are in column A and that you have a header row so
that your data starts on row 2. Insert a new column B, and put this
formula in B2:

=IF(B2=B1,"",B2)

Copy this down as far as required. You can then hide column A so that
you have the effect you wanted, but the full names are still there if
required for other formulae (eg producing summaries for each person).

If you really want to you can fix the values in column B and then
delete column A. To fix the values, highlight column B, click <copy,
then Edit | Paste Special | Values (check) | OK then <Esc.

Hope this helps.

Pete

On Oct 3, 11:22 am, Templar wrote:
For the sake of discussion, let's say you have ten entries of the same
person, same address etc on lines 1-10. You want to eliminate this
redundancy and reduce
this to just one entry of that persons name and address. Is it possible for
me to
eliminate the other 9 lines and retain just 1 line?

Thank you.
--
Templar- Hide quoted text -

- Show quoted text -- Hide quoted text -

- Show quoted text -- Hide quoted text -

- Show quoted text -- Hide quoted text -

- Show quoted text -

Well, I'm glad we finally got there in the end !!

I didn't realise at the beginning that you were trying to get rid of
duplicate records - the easiest way (if you have other files to do in the
future) is to use Advanced Filter, as Gord describes in his post.

Pete

"Templar" wrote in message
...
Pete: Thanks for all your help. I ended up deleting the column. It
seems
to be working just fine. I greatly appreciate your help. Thanks again.
--
Templar


"Pete_UK" wrote:

The formula in B2 should be:

=IF(A2=A1,"",A2)

and this should give you the same name as is in A2 - perhaps you
already had #REF in B2 with a formula like:

=IF(A2=#REF,"",A2)

because you had deleted A1. Then just copy the correct formula down
and you should get all the other names in column B.

But, as I said, you don't need column B anymore so you can just delete
it - this is often referred to as a helper column, and you use it just
temporarily to help you achieve your aims.

Hope this helps.

Pete

On Oct 3, 8:19 pm, Templar wrote:
I just did what you suggested and I got the #REF! down the whole row.
--
Templar



"Pete_UK" wrote:
That is because you still have the formulae in column B (rather than
fixing the values first) and those formulae are refering to cells
which you have now deleted. However, if you put your cursor B2 and
double-click the fill handle (the small black square in the bottom
corner of the cursor outline), then the formula will be copied down
and you should now get the same name in column B as you have in
column
A.

However, if you don't need column B you can just highlight it and
Edit
| Delete to remove it, so you will end up with the same data layout
that you had originally but with those duplicate rows removed.

Hope this helps.

Pete

On Oct 3, 6:01 pm, Templar wrote:
OK. It seems to have worked. Below I've block copied a small
portion of the
document. I'm just wondering what the #REF! means in column B?
Column A is titled "First Name" and Column B is titled "Sorted".
Note that
the
name from Column A sometimes appears in Colunm B and other times I
see
#REF!

First Name Sorted
ABBITT ABBITT
ABEL #REF!
ABITT ABITT
ABRAMS #REF!
ABREGO #REF!
ABROMITIS #REF!
ACEVEDO #REF!
ACKER #REF!
ACKERMAN #REF!
ACKLEY #REF!
ADAIRE ADAIRE

--
Templar

"Pete_UK" wrote:
In B1 you should have a downward pointing arrow, and if you click
on
that you will get the filter choices:

(All)
(Top 10...)
(Custom...)
....
your names
....
(Blanks)
(Non-blanks)

You might need to scroll down the list to get to Blanks if you
have a
lots of names, but then you just click on it. Only the rows which
have
blanks in that column will be displayed (and the colour of the
row
identifiers will change - if you look at these you will notice
that
some are missing, as they have been filtered out, or temporarily
hidden). Then you can follow the other instructions.

Hope this helps.

Pete

On Oct 3, 4:47 pm, Templar
wrote:
OK. Let's see here. I have column A with all the names in it.
I have
column B to which I've applied your formula effectively and
therefore have
blanks in which redundant names were located. I highlighted
Column B and
applied "Autofilter".
Nothing happened. I then looked for the word "Blanks" in a
pull down menu
but could not find the word "Blank" or, therefore, select it.

What do you thing?
--
Templar

"Pete_UK" wrote:
You could apply Autofilter to column B, and select Blanks
from the
filter pull-down. Then highlight all the visible rows except
for your
header and click on Edit | Delete Rows. Then select All from
the
filter pull-down to see what is left.

Hope this helps.

Pete

On Oct 3, 3:55 pm, Templar
wrote:
Thanks Pete. The only problem with this formula is that,
while it does
remove the redundancies from column B, it does not remove
the rest of the
line. There are about ten columns of information on each
line relating to
each name and, although the names disappear (except for 1),
the blanks are
accompanied by the other nine columns of information which
had been
associated with the redundant name (which of course is
information identical
to each redundant anme).

How can I leave a clean spreadsheet of just one line (10
columns of
information)
per unique single name?
--
Templar

"Pete_UK" wrote:
Suppose your names are in column A and that you have a
header row so
that your data starts on row 2. Insert a new column B,
and put this
formula in B2:

=IF(B2=B1,"",B2)

Copy this down as far as required. You can then hide
column A so that
you have the effect you wanted, but the full names are
still there if
required for other formulae (eg producing summaries for
each person).

If you really want to you can fix the values in column B
and then
delete column A. To fix the values, highlight column B,
click <copy,
then Edit | Paste Special | Values (check) | OK then
<Esc.

Hope this helps.

Pete

On Oct 3, 11:22 am, Templar
wrote:
For the sake of discussion, let's say you have ten
entries of the same
person, same address etc on lines 1-10. You want to
eliminate this
redundancy and reduce
this to just one entry of that persons name and
address. Is it possible for
me to
eliminate the other 9 lines and retain just 1 line?

Thank you.
--
Templar- Hide quoted text -

- Show quoted text -- Hide quoted text -

- Show quoted text -- Hide quoted text -

- Show quoted text -- Hide quoted text -

- Show quoted text -