Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Excel Macro to Identify Duplicates and Move Data

Posted on 2014-02-18
3
Medium Priority
?
1,366 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 1000 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 1000 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

Learn how to optimize MySQL for your business need

With the increasing importance of apps & networks in both business & personal interconnections, perfor. has become one of the key metrics of successful communication. This ebook is a hands-on business-case-driven guide to understanding MySQL query parameter tuning & database perf

Question has a verified solution.

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

This code takes an Excel list of URL’s and adds a header titled “URL List”. It then searches through all URL’s in column “A”, looking for duplicates. When a duplicate is found, it is moved to the top of the list. The duplicate URL’s are then highlig…
Freeze panes is an option within all variants of Excel to enable parts of a sheet to remain stationary when the cursor is in another part of the sheet. This is a very useful feature which is overlooked or under used.
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…
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…

715 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