Hi

Using Excel 2003 pro.
Two Questions please.

I regularly have a list of names and addresses sent over to me from another
computer.

It takes the form of a entry number "Say" LD1234 and then the name and
address is in the following cells on the same line.

Is it possible to highlight the whole block of the entry numbers and the
addresses probably 40 at a time and transfer them to another excel page ((I
know how to do the transfer bit)) with the following criterea.

A) I want to be able to automatically place an "N" in front of the entry
number so that "say" entry number LD1234 ends up in the new excel sheet as
NLD1234.

B) when I transfer the block of names and addresses etc over to the new
excel sheet, I want excel to somehow check each name at a time and compare it
to names already in the new excel sheet and if it is there then the full line
(including the entry number etc) is not transfered. Only the ones that are
not already on the new excel sheet get put over.

Net result is that I end up with a list off entry numbers, names and
addresses which are not duplicated within the new excel sheet.

I am sure that there must be a suitable formula to enable this but I am not
that clever to do it myself. If anyone can help, I would appreciate it.

Regards

Insert a new column after the one with LD1234 (let say this is A)
A formula such as ="N"&A1 will return NLD1234
Then you can use Copy followed by Paste Special | Values to turn the formula
into a value allowing you to delete the column A

Use a formula such as COUNTA(B:B,"B1") copied down the column will identify
the duplicates, allowing you to delete row just added that have same name.

The alternative is a VBA subroutine - are you prepared to work with this?

best wishes
--
Bernard V Liengme
Microsoft Excel MVP
http://people.stfx.ca/bliengme
remove caps from email

"chesjak" wrote in message
...
Hi

Using Excel 2003 pro.
Two Questions please.

I regularly have a list of names and addresses sent over to me from
another
computer.

It takes the form of a entry number "Say" LD1234 and then the name and
address is in the following cells on the same line.

Is it possible to highlight the whole block of the entry numbers and the
addresses probably 40 at a time and transfer them to another excel page
((I
know how to do the transfer bit)) with the following criterea.

A) I want to be able to automatically place an "N" in front of the entry
number so that "say" entry number LD1234 ends up in the new excel sheet as
NLD1234.

B) when I transfer the block of names and addresses etc over to the new
excel sheet, I want excel to somehow check each name at a time and compare
it
to names already in the new excel sheet and if it is there then the full
line
(including the entry number etc) is not transfered. Only the ones that are
not already on the new excel sheet get put over.

Net result is that I end up with a list off entry numbers, names and
addresses which are not duplicated within the new excel sheet.

I am sure that there must be a suitable formula to enable this but I am
not
that clever to do it myself. If anyone can help, I would appreciate it.

Regards

Hi Bernard Liengme

Many thanks for your reply.

What do you mean by ""The alternative is a VBA subroutine - are you prepared
to work with this? ""

From what you have suggested, it seems as if VBA is probably the way to go
but I don't know how to. Is it much of a job?

I really don't want to waste time on a long drawn out manual method over and
above highlighting the whole list in the original Excel sheet and transfering
all the list over to the new Excel sheet with the name checks etc all done
automatically.

Regards




"Bernard Liengme" wrote:

Insert a new column after the one with LD1234 (let say this is A)
A formula such as ="N"&A1 will return NLD1234
Then you can use Copy followed by Paste Special | Values to turn the formula
into a value allowing you to delete the column A

Use a formula such as COUNTA(B:B,"B1") copied down the column will identify
the duplicates, allowing you to delete row just added that have same name.

The alternative is a VBA subroutine - are you prepared to work with this?

best wishes
--
Bernard V Liengme
Microsoft Excel MVP
http://people.stfx.ca/bliengme
remove caps from email

"chesjak" wrote in message
...
Hi

Using Excel 2003 pro.
Two Questions please.

I regularly have a list of names and addresses sent over to me from
another
computer.

It takes the form of a entry number "Say" LD1234 and then the name and
address is in the following cells on the same line.

Is it possible to highlight the whole block of the entry numbers and the
addresses probably 40 at a time and transfer them to another excel page
((I
know how to do the transfer bit)) with the following criterea.

A) I want to be able to automatically place an "N" in front of the entry
number so that "say" entry number LD1234 ends up in the new excel sheet as
NLD1234.

B) when I transfer the block of names and addresses etc over to the new
excel sheet, I want excel to somehow check each name at a time and compare
it
to names already in the new excel sheet and if it is there then the full
line
(including the entry number etc) is not transfered. Only the ones that are
not already on the new excel sheet get put over.

Net result is that I end up with a list off entry numbers, names and
addresses which are not duplicated within the new excel sheet.

I am sure that there must be a suitable formula to enable this but I am
not
that clever to do it myself. If anyone can help, I would appreciate it.

Regards

Hi,

The manual method Bernard suggested is pretty quick. Without a background
in VBA Programming, I wouldn't got there if I were you. Bernard probably
mentioned it because if you know programming you could automate the task.


--
Thanks,
Shane Devenshire


"chesjak" wrote:

Hi Bernard Liengme

Many thanks for your reply.

What do you mean by ""The alternative is a VBA subroutine - are you prepared
to work with this? ""

From what you have suggested, it seems as if VBA is probably the way to go
but I don't know how to. Is it much of a job?

I really don't want to waste time on a long drawn out manual method over and
above highlighting the whole list in the original Excel sheet and transfering
all the list over to the new Excel sheet with the name checks etc all done
automatically.

Regards




"Bernard Liengme" wrote:

Insert a new column after the one with LD1234 (let say this is A)
A formula such as ="N"&A1 will return NLD1234
Then you can use Copy followed by Paste Special | Values to turn the formula
into a value allowing you to delete the column A

Use a formula such as COUNTA(B:B,"B1") copied down the column will identify
the duplicates, allowing you to delete row just added that have same name.

The alternative is a VBA subroutine - are you prepared to work with this?

best wishes
--
Bernard V Liengme
Microsoft Excel MVP
http://people.stfx.ca/bliengme
remove caps from email

"chesjak" wrote in message
...
Hi

Using Excel 2003 pro.
Two Questions please.

I regularly have a list of names and addresses sent over to me from
another
computer.

It takes the form of a entry number "Say" LD1234 and then the name and
address is in the following cells on the same line.

Is it possible to highlight the whole block of the entry numbers and the
addresses probably 40 at a time and transfer them to another excel page
((I
know how to do the transfer bit)) with the following criterea.

A) I want to be able to automatically place an "N" in front of the entry
number so that "say" entry number LD1234 ends up in the new excel sheet as
NLD1234.

B) when I transfer the block of names and addresses etc over to the new
excel sheet, I want excel to somehow check each name at a time and compare
it
to names already in the new excel sheet and if it is there then the full
line
(including the entry number etc) is not transfered. Only the ones that are
not already on the new excel sheet get put over.

Net result is that I end up with a list off entry numbers, names and
addresses which are not duplicated within the new excel sheet.

I am sure that there must be a suitable formula to enable this but I am
not
that clever to do it myself. If anyone can help, I would appreciate it.

Regards

Hi Shane

Thanks for your reply.

I'll give it a try.

Regards


"ShaneDevenshire" wrote:

Hi,

The manual method Bernard suggested is pretty quick. Without a background
in VBA Programming, I wouldn't got there if I were you. Bernard probably
mentioned it because if you know programming you could automate the task.


--
Thanks,
Shane Devenshire


"chesjak" wrote:

Hi Bernard Liengme

Many thanks for your reply.

What do you mean by ""The alternative is a VBA subroutine - are you prepared
to work with this? ""

From what you have suggested, it seems as if VBA is probably the way to go
but I don't know how to. Is it much of a job?

I really don't want to waste time on a long drawn out manual method over and
above highlighting the whole list in the original Excel sheet and transfering
all the list over to the new Excel sheet with the name checks etc all done
automatically.

Regards




"Bernard Liengme" wrote:

Insert a new column after the one with LD1234 (let say this is A)
A formula such as ="N"&A1 will return NLD1234
Then you can use Copy followed by Paste Special | Values to turn the formula
into a value allowing you to delete the column A

Use a formula such as COUNTA(B:B,"B1") copied down the column will identify
the duplicates, allowing you to delete row just added that have same name.

The alternative is a VBA subroutine - are you prepared to work with this?

best wishes
--
Bernard V Liengme
Microsoft Excel MVP
http://people.stfx.ca/bliengme
remove caps from email

"chesjak" wrote in message
...
Hi

Using Excel 2003 pro.
Two Questions please.

I regularly have a list of names and addresses sent over to me from
another
computer.

It takes the form of a entry number "Say" LD1234 and then the name and
address is in the following cells on the same line.

Is it possible to highlight the whole block of the entry numbers and the
addresses probably 40 at a time and transfer them to another excel page
((I
know how to do the transfer bit)) with the following criterea.

A) I want to be able to automatically place an "N" in front of the entry
number so that "say" entry number LD1234 ends up in the new excel sheet as
NLD1234.

B) when I transfer the block of names and addresses etc over to the new
excel sheet, I want excel to somehow check each name at a time and compare
it
to names already in the new excel sheet and if it is there then the full
line
(including the entry number etc) is not transfered. Only the ones that are
not already on the new excel sheet get put over.

Net result is that I end up with a list off entry numbers, names and
addresses which are not duplicated within the new excel sheet.

I am sure that there must be a suitable formula to enable this but I am
not
that clever to do it myself. If anyone can help, I would appreciate it.

Regards