Check 4 duplicate Copy format values of orginal's row over dupicate's row

ice_cool · Jun 12, 2011

Is it possible for human race to come up with macro for excel 2007

which can check for duplicate and copy the format and whole row of original and paste it over duplicate/ duplicates.

Question for Advance Gurus:

Can i set range of cell to be copied from orginal to duplicate/dulicates

e.g if i want to copy format and values of first 3 cells of orginal and paste it over duplicate.

Example:

Orginal list

------------

happiness (Blue Color)

Wealth

Love (Red color)

Friendship

Success

New List:

---------

Greed

lust

love

money

happiness

Note:

there is dulplicate of love and happiness. Is it possibe for MACRO in Excel 2007 to copy the format of orginal i.e love (blue color) and paste it over duplicate, so both duplicate have same format.i.e font, color as of original.

thanks in Advance

Tia jOY

Hui · Jun 12, 2011

Tia

I don't know about the Human Race, but experienced Excel practitioners should be able to solve this problem for you.

Can you be more specific

Is this a fixed Range or one that changes

Is the location fixed or variable

What triggers the copy, user defined or under some condition ?

Why do you mention Duplicates, as the duplicates don't seem to have any correlation from the old to the new ranges in your example?

vijaySharma · Jun 12, 2011

Tia,

While you answer the questions put by Hui which are very valid... I made a few assumptions which are listed below.

1. You have both the lists in the same sheet

2. I have defined 2 named ranges... OrigStart and NewStart (which refer to the first entry in the list.

Below is the code..

[pre]

Code:

Option Explicit
Option Compare Text
Public OriginalCell As String
Public currentFoundCell As String &#39;will store the address here

Sub checkForDuplicates()

Sheets(&#34;Sheet1&#34;).Select
Range(&#34;OrigStart&#34;).Select

Do While ActiveCell.Value &#60;&#62; &#34;&#34;
OriginalCell = ActiveCell.Address
currentFoundCell = findThisInNewList(ActiveCell.Value)
If currentFoundCell &#60;&#62; &#34;&#34; Then
copyFormatTo currentFoundCell
End If
Range(OriginalCell).Select
ActiveCell.Offset(1, 0).Select
Loop
End Sub

Function findThisInNewList(searchWhat As String) As String
Sheets(&#34;Sheet1&#34;).Select
Range(&#34;NewStart&#34;).Select

Do While ActiveCell.Value &#60;&#62; &#34;&#34;
If ActiveCell.Value = searchWhat Then
findThisInNewList = ActiveCell.Address
Exit Do
End If
ActiveCell.Offset(1, 0).Select
Loop
End Function

Sub copyFormatTo(whichCell)

Range(OriginalCell).Select
Selection.Copy
Range(whichCell).Select
Selection.PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False
End Sub

[/pre]

~VijaySharma

vijaySharma · Jun 12, 2011

PS:

This code has assumed that there are no repeats in the new list at all...

If there are repeats we would need to modify the code..

~VijaySharma

vijaySharma · Jun 12, 2011

Tia,

The below code will take care of the duplicates (repeats) in the new list as well...

Thanks for a very good question...

[pre]

Code:

Option Explicit
Option Compare Text
Public OriginalCell As String
Public currentFoundCell As String &#39;will store the address here

Sub checkForDuplicates()

Sheets(&#34;Sheet1&#34;).Select
Range(&#34;OrigStart&#34;).Select

Do While ActiveCell.Value &#60;&#62; &#34;&#34;
OriginalCell = ActiveCell.Address
currentFoundCell = findThisInNewList(ActiveCell.Value)
Range(OriginalCell).Select
ActiveCell.Offset(1, 0).Select
Loop
End Sub

Function findThisInNewList(searchWhat As String) As String
Sheets(&#34;Sheet1&#34;).Select
Range(&#34;NewStart&#34;).Select

Do While ActiveCell.Value &#60;&#62; &#34;&#34;
If ActiveCell.Value = searchWhat Then

findThisInNewList = ActiveCell.Address
If ActiveCell.Offset(1, 0).Value = &#34;&#34; Then
If ActiveCell.Value = searchWhat Then
copyFormatTo findThisInNewList
Else
Exit Do
End If
Else
copyFormatTo findThisInNewList
End If
End If
ActiveCell.Offset(1, 0).Select
Loop
End Function

Sub copyFormatTo(whichCell)

Range(OriginalCell).Select
Selection.Copy
Range(whichCell).Select
Selection.PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False
End Sub

[/pre]

~VijaySharma

ice_cool · Jun 19, 2011

Thanks hui and vijaySharma for Warm Welcome and kind help

I Read your your message the moment you posted it

I Know little about Excel VBA and i don't understand your code.

I feel like being pest to ask you to explain the code.

I pasted your code in VBE and run it but nothing happens.

I know i m doing come thing wrong.

Hui and vijaySharma,

1# I was given 100's of sites(column B) multiple times daily, i have to check and type their Page Rank (column C), site moving up/down at google (column D) and keywords (Column E,F,G)

2# Many times, when i got new list, It have many sites which i have recently checked for Page Rank etc.

3# To save my time, I try duplicate check. Means if i there is any dulpicate site, it shows "False" value in column A. I am using this formula =ISNA(VLOOKUP(B870,$C$1:C869,1,FALSE))

4# This Help me out to extent that i know which site is duplicate and i just go to original site and copy its page rank, keywords etc in front of duplicate.

Note: Since I need to report different sets of sites to different clients, I have put values in front of Dulpicate, which i do manually

5# I need Guru who can give me VBA code, which i copy and paste in VBE and simply run it.

6# It will very helpful if, i can select say 100 range of cells containing new sites(column B e.g B501:B600), and once i run this VBA Code, it checks range (B1:B599), and if it find that new sites have any duplicates. It Simply copy the values, of Page Rank (column C), site moving up/down at google (column D) and keywords (Column E,F,G), and paste in front of duplicate.

7# There are some sites whose page rank is lower than required. So i simply highlight those sites with Red and the sites whose page rank is according to requirement, i highlight them to green.

Note: Its Green highlighted sites who have values of Page Rank (column C), site moving up/down at google (column D) and keywords (Column E,F,G).

Is it possible that VBA code can copy the Color of orginal and highlight the duplicate with that color

I have start learning VBA, I Promise that I Will help people on this forum.

Thanks In Advance

I will remember your help

God bless you both Hui and vijaySharma

I have fixed my eyes on this thread, i will instantly know when you guys will reply and i will give you instant feedback

Tia jOY

vijaySharma · Jun 21, 2011

Tia,

Is it possible for you to send over your file with sample data over so that the code can be adjusted to work on the real file.

sharma.vijay1 @ gmail.com

Regarding learning VBA... you can always enroll yourself on the VBA School at Chandoo.

~VijaySharma

ice_cool · Jun 23, 2011

Hi Sharma,

Thanks for extending your kind help.

I have emailed you with "example sheet" in attachment

plus i have rephrased the problem many times to make it really easy for you.

looking forward to your reply

thanks in advance

Tia jOY

vijaySharma · Jun 23, 2011

Hi Tia,

I have got your file and working on the same. Will revert once completed.

~VijaySharma

Check 4 duplicate Copy format values of orginal's row over dupicate's row

ice_cool

New Member

Hui

Excel Ninja

vijaySharma

Member

vijaySharma

Member

vijaySharma

Member

ice_cool

New Member

vijaySharma

Member

ice_cool

New Member

vijaySharma

Member

Check 4 duplicate Copy format values of orginal&#39;s row over dupicate&#39;s row

New Member

Excel Ninja

Member

Member

Member

New Member

Member

New Member

Member

Check 4 duplicate Copy format values of orginal's row over dupicate's row