Solved

Excel Macro to Identify Duplicates and Move Data

Posted on 2014-02-18
3
1,321 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

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

This article descibes how to create a connection between Excel and SAP and how to move data from Excel to SAP or the other way around.
Some code to ensure data integrity when using macros within Excel. Also included code that helps secure your data within an Excel workbook.
This Micro Tutorial will demonstrate how to use longer labels with horizontal bar charts instead of the vertical column chart.
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.

828 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