Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

Excel Macro to Identify Duplicates and Move Data

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

 [eBook] Windows Nano Server

Download this FREE eBook and learn all you need to get started with Windows Nano Server, including deployment options, remote management
and troubleshooting tips and tricks

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.
After seeing numerous questions for Dynamic Data Validation I notice that most have used Visual Basic to solve the problem. This suggestion is purely formula based and can be used in multiple rows.
This Micro Tutorial will demonstrate in Google Sheets how to use the HYPERLINK function to create live links inside your spreadsheet.
Many functions in Excel can make decisions. The most simple of these is the IF function: it returns a value depending on whether a condition you describe is true or false. Once you get the hang of using the IF function, you will find it easier to us…

885 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