Solved

Excel Macro to Identify Duplicates and Move Data

Posted on 2014-02-18
3
1,350 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
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 31

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

[Live Webinar] The Cloud Skills Gap

As Cloud technologies come of age, business leaders grapple with the impact it has on their team's skills and the gap associated with the use of a cloud platform.

Join experts from 451 Research and Concerto Cloud Services on July 27th where we will examine fact and fiction.

Question has a verified solution.

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

This article describes how you can use Custom Document Properties to store settings and other information in your workbook so that they will be available the next time you open the workbook.
Ever visit a website where you spotted a really cool looking Font, yet couldn't figure out which font family it belonged to, or how to get a copy of it for your own use? This article explains the process of doing exactly that, as well as showing how…
This Micro Tutorial demonstrates in Microsoft Excel how to consolidate your marketing data by creating an interactive charts using form controls. This creates cool drop-downs for viewers of your chart to choose from.
If you’ve ever visited a web page and noticed a cool font that you really liked the look of, but couldn’t figure out which font it was so that you could use it for your own work, then this video is for you! In this Micro Tutorial, you'll learn yo…

627 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