Excel formula problem

  osben 10:45 26 Oct 2008


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 the name 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.


  silverous 20:02 26 Oct 2008

To transfer data between two sheets, based on a condition and modify some of that data you are really looking at a macro.

I'm not sure how comfortable you are with amending macros but if I was to write it for you and say your cells were not in the same position as mine etc. it would need slight modification.

What happens once the 'new' entries have been transferred to the sheet with existing entries, should the new entries (including any that already existed) be deleted from the source area?

  silverous 20:28 26 Oct 2008

I've made a sample for you. Take a look at this (Download the file):

click here

You select the area (try selecting both entries on first sheet) then press ctrl+t and look at the results in the "Existing sheet" sheet.

  silverous 01:08 01 Nov 2008

So glad I took the time to do that !

  osben 06:54 01 Nov 2008

Hi Silverous

Suitably chastised. - I am sorry I didn't get back to you but I have been away for the past few days and only got back last night.

I did try what you included in your last thread and think I will give it a go. I do think that it will do the job.

Thank you once again and sorry If I apeared rude.


This thread is now locked and can not be replied to.

Elsewhere on IDG sites

Xiaomi Mi Mix 2 review

What went wrong at the Designs of the Year 2017

iPhone X news: Release date, price, new features & specs

Comment utiliser Live Photos ?