macro to copy and paste only if not duplicating in destination and then copy if empty

[steve78]

Hi ALL,

i would be so very grateful if you could assist me with matching my book to other data.

i have had to take a sample of other data for security reasons, however the object of what i am doing to to get my "ammend" data to match some data elsewhere
unfortunately i cant use the handy query data clean ups because the destination data is also very unique ( not clean )

the only thing i can see to do is create my own id of names (i have them in ammend)

however , for the attached workbook, what i am actually asking is:

Is it possible to be able to copy data (NAMES ((COL E)) ) and PASTE to a column (H) ONLY the names that are NOT already in the paste destination column. (also being H) ( so paste new records ONLY )

in terms of my spreadsheet

I Need to COPY all the names in COL E (not the header though)
and paste it to COL H (name)
However i only want pasted any names that ARE NOT ALREADY here in COL H ( so in actual terms that would be the names = t fold, d menance, c world and g fell ONLY but not the others )

and then

Can I have, in the AMMEND (I), column a formulae or something
that says IF this column (ammend) is EMPTY, then it will copy the contents of Column H (name) = ( so it would only copy the NEW records )

I would like to then run the whole process by creating a MACRO ( That runs the question 1 (copy and paste new records) , and then runs the question 2 (copy if empty)

is this possible ? dont worry about the macro part if that is not possible

your help is so much appreciated as this has stumbled me for a few days and now im just going round in circles

if i havent been clear or you have any questions then please do ask me, thanks very much, its very appreciated, sa

[PCI]

Is it what you need ?

Please Login or Register  to view this content.

[steve78]

spot on PCI it works !!, thanks very much.

although i am still missing a solution to the 2nd part, which is
"Can I have, in the AMMEND (I), column a formulae or something
that says IF this column (ammend) is EMPTY, then it will copy the contents of Column H (name) = ( so it would only copy the NEW records )"

i think i have worded this awkwardly though, what i am asking is

can i now ,magically somehow, COPY Column H9 (excluding the header) and paste it to I9. BUT .... ONLY PASTE IT IF the cell in I9 is empty.

i am having the most problems with this one, something about cells referencing themselves.

is that possible, if so, big thumbs up if could show me how.

then i assume i can just add the two macros together. thanks steve

[PCI]

The story of column AMMEND is included in the macro.
If there is no data in AMMEND column then values from column "H" will be pasted.
If it is not correct then give example of what you want

[steve78]

ok PCI. I see what you mean, and yes what you have done works but i
perhaps wasnt too clear on what i wanted. basically for it to be split into two different macros.

the first one works perfectly.
first one is
COPY NAME (E) to PASTE to NAME (H) but only for new names that are not already in H.
This works. but should be nothing connected to the ammend col at this stage.

After this is done.

is it possible to COPY col H (excluding header) and PASTE to col I BUT paste it ONLY if the cell it is pasting to is empty
so in my example it would copy ALL column h but would only in fact paste to I 7 to I 11, as they are empty

i am thinking , in theory, this would be much harder to do.

pls let me know if this is poss when u can, many many thanks, steve

[steve78]

so its the second thing i am stuck on. the bit after i say "after this is done"

[PCI]

Which values from column "H" will be pasted to column "I"

[steve78]

All of the Values in H but only pasted if the ammend cell is EMPTY

so for example

you see in I2 the cell HAS the value j l, ill need this left here and not pasted over.
and the same in cell I3 has the value "grant" i would need this left here and not pasted over
so anything where the IS a value in column I should not be pasted over

HOWEVER

any other cell in COL H that has a BLANK EMPTY cell next to it in COL I then those will need to be pasted over.

hope this make sense and can be done.

thanks, sa

[PCI]

Is it ...?

Please Login or Register  to view this content.

[steve78]

Yes that works perfectly PCI. thanks very much indeed. your a master !!!

[PCI]

You're welcome Steve, and thx for the Rep.
PCI