Solved

Excel Macro to Identify Duplicates and Move Data

Posted on 2014-02-18
3
1,315 Views
Last Modified: 2014-02-21
Hello Experts,

I'm trying to achieve the following in excel.

I have four columns. Columns A, B, C, D.

Columns A, C, and D have data. B is empty.

I want to accomplish two things. I want to identify and highlight matching values in columns A and C. When there is an match, I want the data that's in Column D, that is in the same row as the cell that was matched in column C, and I want that value copied and pasted into Column B (empty columns) in the row as the corresponding/matching value in Column A.

I hope is clear. Is this possible to do with a macro or with any addons?
0
Comment
Question by:grindsmygeaqrs
3 Comments
 
LVL 19

Assisted Solution

by:regmigrant
regmigrant earned 250 total points
ID: 39869109
You need to clarify if the matching cells in a and c are in the same row - as implied by your question. if they are not you need to explain what to do if there is more than one match for a given pair. Also is it possible to  sort columns a and c so that the pairs are in the same rows; is a subset of c. and so on - perhaps posting a sample would be a good idea.

However - taking the simple case
highlighting the cells that match (in the same row) can be done with conditional formatting:-
start in cell a1 (if that's the top of your list) and go to format, conditional formatting, (or home tab, styles, conditional format if using ribbon). Select new rule and choose 'enter a formula to decide..'. In the formula box type "=$a1=$c1" (without quotes) then change the 'format' to, for example, fill the cell with green. press enter. with a1 selected choose past format and copy the format down the entire list. Those with a matching c should now be green.

To retrieve the data from D into B use (in b1)
=if($a1=$c1,$d1,"") and copy that formula down the list

if the data in a is a subset of c

Conditional format to highlight a cell in A that matches any cell in C
put this into the 'use formula'
=not(isna(vlookup($a1,$C1:$C2000,1,0))) and change the format and change the $C2000 to match the last row of your list
use paste format to copy that down the column

to get the data from D into B  - in B1 put
=iferror(vlookup($a1,$C1:$D2000,2,0),"")
again you need to change D2000 to match the last row of you list.

if C is a subset of A - its the same formulas but switch a for c in each example
0
 
LVL 29

Accepted Solution

by:
gowflow earned 250 total points
ID: 39870747
here is the file to do this.

1) Put this formula in B1 and fill down to end of data
=IF(AND(A1=C1,A1<>"",C1<>""),D1,"")

2) Use Conditinal formatting
A- Select Col A click on conditional formatting / Manage Rules choose New Rule choose last one by formula and paste this formula
=AND(A1=C1,A1<>"",C1<>"")
Choose a color press on format
press ok and then close conditional Format.

B- Select Col C and do the same as above creating the same rule for Col C with same color if you want.

Regards
gowflow
Similar-col.xlsx
0
 

Author Closing Comment

by:grindsmygeaqrs
ID: 39876780
Tah! Thank you!
0

Featured Post

Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Some code to ensure data integrity when using macros within Excel. Also included code that helps secure your data within an Excel workbook.
Do you use a spreadsheet like Microsoft's Excel?  Have you ever wanted to link out to a non excel file on your computer or network drive?  This is the way I found to do it!
Graphs within dashboards are meant to be dynamic, representing data from a period of time that will change each time the dashboard is updated with new data. Rather than update each graph to point to a different set within a static set of data, t…
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…

803 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question