Free Microsoft Excel 2013 Quick Reference

find duplicate in two worksheets and copy in another worksheet in excel macro

I am looking for help please with this problem in Excel. I need a macro that meets these conditions please:
I have and worksheets in an Excel workbook, and I need to do a search for duplicate items, duplicate items are imported from one server to my Excel worksheet "new items", I have another Excel worksheet "inventory" Excel in this fact sheet is all the items I have in my inventory, Select item from my column Worksheet "Inventory" as the source of my search, the first thing the macro has to do is select the cell (2,1), and do a search on the worksheet "new item" the column item cell (2.1), if the values are equal then the cells in the worksheet "new items" copies the entire cell row (2.1) to the worksheet "Item found," after copying entire row, delete the row of the worksheet "new items", and continue in the next cell (3.1) until you find all cells that match the value in cell (2.1) of "inventory.", and repeat same condition to end all item column in the worksheet "inventory.."

Thank you for your help


Post your answer or comment

comments powered by Disqus
Hi

Please find attached a workbook of sample data and the results im trying to achieve.

What I would like is a macro to run rather than currently sorting and deleting manually.

On the "data" worksheet is the raw data once pasted form another application and I have applied “text to columns” next what I normally do is remove duplicates based on column E then sort based on column J and F then manually remove all non duplicates.

There must be a duplicate in both J and F, all other data can be deleted.

If this could be put into a simple macro it would be fantastic as my data is many 1000’s of rows.

Thanks
David

Hello I cant seem to find a valid formula to check the data between two columns and high light the row if its a match.

here is my example.

Row 1 column A, Column B,
Row 2 smith,jane doe,john
Row 3 tribet,Jake smith,jane

I would like to verify data from column A to see if there are duplicates in column B and highlight that row if there are. so row two would be highlighted. I have tried different variations of conditional formatting but nothing seems to work. I have tried this formula as example =COUNTIF($A$1:$A$100,A1)>1

any help greatly appreciated.

I have attached a sample worksheet of what I am looking for. It is color coded.

For rows 2, 3, 4, 5 - column D and E are empty.
For rows 5, 6, 7, 8 - column D and E containt text.
I'm looking for matches in column A and column B.

What I want to do is search my entire spreadsheet. For any row where D and E are empty, I want to search for duplicates in column A and B. If a duplicate is found, I want column D and column E from that row to populate into the empty D and E row.

In my example, for example: column A and B have duplicates in row 2 and row 6. Because of that, I want row 6's column D and column E to populate in row 2's column D and column E. In row 5, column A has a duplicate with row 9, but their columns B do not. In this case, I do not want any info to populate.

The colors in my spreadsheet show how I want the data to match up based on what I stated above.

My real spreadsheet is much bigger (roughly 4,000 rows), and not every row has a duplicate.

I hope I am being clear. If not, please let me know and I will try and explain better.

I really appreciate any help someone could provide!

Thanks,

Brandon

Hi,

I have a sheet of row data from the result of a race.
The sheet is made of 10 columns plus a 2 column(K,L)/6 row table.
The small table contains the race final classification with car numbers.

I would like my macro to look in the small table, and for each of the values there, find them in column C.

Once L1 is found in column C, it should look in column J for values from 1 to 7

When L1 in C and 1 in J are found, I need to select all the rows that contain those two values and to copy them in another sheet.
In the other sheet, the L1/1 data should go in from B4

Then for L1/2 it should go in C4, L1/3 in D4... L1/7 in H4

Then the code looks for L2 in column C of my first sheet, looks for a 1, 2. 3...7 in column J, selects all the rows with the two values and copy them in I4 to O4

And so on until all L6/1,2,...7 values are found and copied in AK4 to AQ4

any ideas how to do it??

Many thanks in advance if you can help me!! (and otherwise I'll keep trying but my head is about to explode... )

I have two columns of information. I'd like to find the duplicates in column B and remove them from column A. How can I do this? Any help is greatly appreciated.

-ExcelNub

Hi,
I hope it's not as difficult as it appears to me: I want to find duplicates in a big sheet:
#. column 1 - column 2
#. Name - date.birth
1. Mr.X - 20.11.1985
2. Mr.Y - 20.05.1983
3. Mr.X - 21.12.1954
4. Mr.X - 20.11.1985

The function should find/mark the ones with the same name and birthday in the same row. In the ex. above that would be 1 and 4. I've tried a lot of stuff and it works very well as long as it is only one cell in one row that you compare - but I need to compare the content of two cells.

Any one has any ideas?
[Excel 2003]

Hi there, i'm currently using this macro, which is helping me find data in one sheet and copying it to another. However I now need to search for the data across multiple worksheets and then copy it to one worksheet.

Dim LSearchRow As Integer
Dim LCopyToRow As Integer
Dim LSearchValue As String

On Error GoTo Err_Execute

LSearchValue = InputBox("Please enter a value to search for.", "Enter value")

Sheets("V.Sat").Select
Range("A1").Select

'Start search in row 4
LSearchRow = 2

'Start copying data to row 2 in Sheet2 (row counter variable)
LCopyToRow = 2

While Len(Range("A" & CStr(LSearchRow)).Value) > 0

'If value in column E = LSearchValue, copy entire row to Sheet2
If Range("E" & CStr(LSearchRow)).Value = LSearchValue Then

'Select row in Sheet1 to copy
Rows(CStr(LSearchRow) & ":" & CStr(LSearchRow)).Select
Selection.Copy

'Paste row into Sheet2 in next row
Sheets("Get Info").Select
Rows(CStr(LCopyToRow) & ":" & CStr(LCopyToRow)).Select
ActiveSheet.Paste

'Move counter to next row
LCopyToRow = LCopyToRow + 1

'Go back to Sheet1 to continue searching
Sheets("V.Sat").Select

End If

LSearchRow = LSearchRow + 1

Wend

'Position on cell A3
Application.CutCopyMode = False
Range("A3").Select

MsgBox "All matching data has been copied."

Basically, once it's finished searching on the v.sat worksheet, it then moves to the q.sat, neither, q.dis and v.dis worksheets.

Any help would be really appreciated.

Hello all,
I am trying to write a macro that finds data in column "A" and copies it to column "B". For example, search column "A" for a cell containing text "ID", if it exists copy to corresponding cell in Column "B". If not then do nothing. Can this be done with a Lookup?

I have just started using Excel 2007. I can't find out how to move and copy a worksheet like I could using Excel 2003. Any help would be greatly appreciated!

1. I am looking to compare between two cell in different worksheets, and copy the result in a different worksheets.
Worksheet one "Inventory" has unique item# in one column, Worksheet two " orders" has duplicate item # in 1 column
a. I want that the macro do a mach-search on item# column in “inventory” worksheets it my referent worksheets, if the mach-search is true I want to copy the row that contains the cell item# in “orders” to the new worksheets, delete the row after item# from “orders worksheets” was copy in the new worksheets, and continue the mach-search on the next row on item# from “inventory”

Hello,

I could really use some help! I've got 65000 items in an excel sheet. Long explanation short:
Product code lists in Column B and Column AA

I need to find products that are in Column B that also exist in Column AA

When there is a Product in Column B that is also in Column AA I want the information from Column D,F,G and H (in the same row as the duplicate product shown in Column B) to be copied into Columns AM, AN, AO and AP in the same row that is the duplicate part from Column B in Column AA... see the sample! The desired result is in red.

THANK YOU THANK YOU THANK YOU!

I have two columns of data that are different lengths. I want to take the data in column A and find duplicates in Column B and then have whatever is not a duplicate in Column B show up as a list in Column C.

I have copied the two columns of data into one sheet. Would there also be a way to do this across two sheets?

Thanks for the help.

Hi guys,

I have this code to compare two worksheets and copy the rows that are missing in the activesheet from the next sheet.

Sub Old_2_New()
' Assumes:
' -(new)  sheet is selected and "Old" sheet is to the right of the (new) sheet
' - data rows starts on row 3 on both sheets
' - no dummy (non-data) rows below the last data row

 Dim DestinationWS, SourceWS As Worksheet
 Dim bRowExists As Boolean
 Dim iInsertedRows As Integer
 
 Set DestinationWS = ActiveSheet
 Set SourceWS = ActiveSheet.Next
 
 Application.ScreenUpdating = False
 
 LastRowSource = SourceWS.Cells.Find(what:="*", After:=[A1], searchorder:=xlByRows,
searchdirection:=xlPrevious).Row
 LastRowDestination = DestinationWS.Cells.Find(what:="*", After:=[A1], searchorder:=xlByRows,
searchdirection:=xlPrevious).Row
 
 iInsertedRows = 0
 
 For iRowIndexSource = 3 To LastRowSource
   bRowExists = False
   For iRowIndexDestination = 3 To LastRowDestination
     If DestinationWS.Cells(iRowIndexDestination, 1) = SourceWS.Cells(iRowIndexSource, 1) And _
        DestinationWS.Cells(iRowIndexDestination, 2) = SourceWS.Cells(iRowIndexSource, 2) And _
        DestinationWS.Cells(iRowIndexDestination, 3) = SourceWS.Cells(iRowIndexSource, 3) And _
        DestinationWS.Cells(iRowIndexDestination, 4) = SourceWS.Cells(iRowIndexSource, 4) And _
        DestinationWS.Cells(iRowIndexDestination, 5) = SourceWS.Cells(iRowIndexSource, 5) Then
       bRowExists = True
          Exit For
     End If
   Next iRowIndexDestination
   If Not bRowExists Then 'We found a row in the Source WS that dosn't exist in the destination sheet!
     DestinationWS.Rows(LastRowDestination + 1).Insert (xlShiftDown)
     SourceWS.Rows(iRowIndexSource).Copy
     DestinationWS.Paste (DestinationWS.Rows(LastRowDestination + 5))
     iInsertedRows = iInsertedRows + 1
   End If
 Next iRowIndexSource
  
 Application.ScreenUpdating = True

 MsgBox iInsertedRows & " unique rows were copied to this sheet"
End Sub

This code works great BUT here is the tricky thing though that I need your help with.

if there is a match, I want to copy the values in columns 10 to 14, 16, 19 to 23, 25, 28 to 32 and 34). So the values in the source worksheet ends up in the right column in the destination worksheet.

i'm attaching a workbook that shows what I mean. All help/ideas is appreciated.

Looking forward to your help with this issue!

OK, I know, Another delete duplicates question.... There are a ton of examples here but I've yet to find one I can impliment with my limited skills....

In the included workbook I've got a sheet that needs to be updated every time a userform is completed. So I intend to run a macro after the data is entered to find duplicates, and delete the older row.

so, column A must match then column C must match then look in columns AK & AL to find the newest entry to keep. I also need to delete any rows where column A is empty(light Green), no matter what other columns have data.

In my example sheet all the yellow's match for column A but the bright yellow's are the only "keepers", because one has a different number in "C" and the other is the newest.

I hope you can help because this would put a "bow" on this project for me.

Also remember that this is just part of a much bigger, multi-sheet book so I will need to I.D. this sheet in the code.

I'll also need to re-sort this after, but I think I can handle that bit of code.

Thanks in advance, you guys are GREAT!

I have 12 separate months of data, each in their own spreadsheet. I want to
simply copy/paste all of the data from the 12 separate spreadsheets into one
master worksheet, then find all of the duplicates and somehow identity them
so I can manually remove the duplicates (or automate a way to 'eliminate'
them). To slightly complicate this, let's say the data has 6 columns. The
first 4 columns can have duplicates while the date in columns 5 and 6 may
vary. A simple way to describe this would be to "Find duplicates in column
"A" and after the first unique value, turn all other duplicate rows yellow".

So far I have copied/pasted the data into one master spreadsheet, then I
have sorted the data which made the duplicates one on top of the other. I
can see the duplicates and elimiate them one by one, however I have thousands
of rows, so I want to automate it somehow. Here is an example:

A B D E F
1 111111 Lamaya 1319.00 359.00 354.60
2 222222 John 2755.81 286.06 0.00
3 333333 Steve 2873.12 0.00 85.00
4 333333 Steve 2873.12 44.20 0.00
5 333333 Steve 2873.12 0.00 368.30
6 444444 Gail 2450.00 0.00 23.98
7 555555 Joe 1086.57 887.87 226.30
8 555555 Joe 1086.57 665.21 0.00
9 666666 Bob 96.40 0.00 201.30
10 777777 Jenn 2075.00 5531.00 101.20
11 777777 Jenn 2075.00 2040.00 20.30
12 777777 Jenn 2075.00 1020.00 512.30
13 777777 Jenn 2075.00 119.00 71.00
14 888888 Peter 391.30 0.00 1.99
15 888888 Peter 391.30 0.00 35.03
16 999999 Tony 3077.00 110.12 0.00

In this example I want to 'eliminate (or somehow idenity) rows 4, 5, 8, 11,
12, 13 and 15 as they are the duplicates.

I think I could do a conditional format, but I don't know if that will do
what I need. Possibly a macro? I'm ok with macro's and not afraid to play
with them, I'm just not a programmer and have hit a dead end. Possibly have
another worksheet that picks each unique date in column A and ignores the
duplicates?

Thanks,

Steve

I have 2 worksheets of data for a mailing list I'm doing. The first
worksheet has criteria pulled from one set of software and the other is
from a separate system. What I want to do is find which account numbers
from Sheet 2 appear on Sheet 1. Those that match get deleted from both
Sheet 1 and 2 put into a new worksheeet (Sheet 3), leaving me with no
duplicates in Sheet 1, and whatever is left in Sheet 2 is discarded.
I'll then use Sheet 1 for one mailing, and Sheet 3 for the other.

Any ideas?

--
darkwood
------------------------------------------------------------------------
darkwood's Profile: http://www.excelforum.com/member.php...o&userid=29948
View this thread: http://www.excelforum.com/showthread...hreadid=541624

hello all,

I've trying to solve something for a while but I can't find a proper solution. The problem is this. I've got two files where people enter data in, two seperate files. The first file is just a list of names. The second file is a list where people enter there name and for example the number of hours they work. Now I want to create a new file that publishes per name (from the first file) the days and number of hours worked.

Attached is a file with some data as an example of how the data is presented and how I want to present it. The three worksheets are three files in the real case, but that's not the problem.

I tried to use the vlookup function, but this function stops after it found one entry. I've also tried to made a loop with the vlookup function, but this didn't work either.

Hopefully the problem as described here is clear. Thanks in advance for any help or ideas!

I have 2 worksheets of data for a mailing list I'm doing. The first worksheet has criteria pulled from one set of software and the other is from a separate system. What I want to do is find which account numbers from Sheet 2 appear on Sheet 1. Those that match get deleted from both Sheet 1 and 2 put into a new worksheeet (Sheet 3), leaving me with no duplicates in Sheet 1, and whatever is left in Sheet 2 is discarded. I'll then use Sheet 1 for one mailing, and Sheet 3 for the other.

Any ideas?

find duplicates in excel 2007 from two different spreadsheets. I have to modify a report weekly and I am looking for a way to search for duplicates without having to use CTL+F. Data is added to the master and I am saving it on my desktop and cutting and pasting.

Hi,
Can someone help me with this, I am trying to find the duplicates in a Column C and if a duplicate is found then it needs to check for the Letter "a" (same row)in the Column A. If "a" is found then it needs to clear the dupicate value (In column C).

A B C 1 2 a 1 3 4 2 5 a 4

Hi Guys,

Please help with he following:
I need to Compare two adjacent cells in two columns and if both adjacent cells have data then msgbox, otherwise just continue entering data. Let's say I have a date in C4 and if I try to enter another date in D4 then message box promting that C4 already has data, if C4 is emty then do nothing.

I've looked so many forums and couldn't find a macro, so ended up posting here.
Please help!

Hi there,

i've been trying for a few days now to figure out a way to find duplicates in two ranges i have, then delete them from one of the ranges. i've had no luck.

Basically, I've got 2 columns - A & K - Column A contains the names of work colleagues - Column K contains the names of colleagues also, but in a different format e.g. A = Ablett Terrie L, K = Ablett Terrie.

I'm trying to write a macro that would let me search all of the names in Column A with the Names in Column K, Then remove any Duplicates it finds in Column A, along with the next 6 cells to the right of it, then delete the empty cells it creates.

Now, Column A will always be around 1500 rows, whereas column K may sometimes have less than this.

Is this possible?? I'd appreciate some help.

Thanks

I need a macro that will search two columns and locate any duplicates between the two columns. Here is an example of my data:

3051CD2A22A1AS1E5M5CNJ3-1199WDAB3AFFW71DC00 3051SFPDS010W3M0800D31AA1A2KDM5C5 3051CG4A04A1AH3E5CN 475HP1EKLUGMT 03031-0313-0013 3051CD2A03A1AH2E5CN 475HP1EKLUGMT 3144PD1A1E5M5Q4CNB4

The first column is column "A". The second column is column "B". In column "C", I want the output to be 475HP1EKLUGMT in the cell 2. The macro needs to run until the last used cell in each column has been checked for duplicates.

Thanks,
J

Macro, find value from open workbook in closed workbook and copy adjacent cells?
I am no master of VBA and tried to record macro’s to do the job, but couldn’t get the job done. Couldn’t even make sense of putting together some macro’s found on the forums. I need help in doing the following. After many fruitless hours, can someone come to the rescue.
The following is the basic info I think you will need and what I want done.

“OPEN” = Open Workbook (C:…/PNF Trading); Sheet 1
“Closed” = Closed Workbook (C:…/Today); Today [Have only one sheet]

In “OPEN”
1. Select A1
2. Insert row
3. Copy A2:N2 and Paste to A1:N1

In “Closed”
1. Find the value of Cell B1 in “Open”, here in Column A of “Closed”
2. From address of result of Find (A*), Copy Adjacent Cells in Column F, G, H, I, J, K in the same row (*)

In “Open”
1. Paste Copied Values in C1

Thank you
Pieter

Ok,
so what I want to automate is that I have two workbooks, one will be the master and the other will be the updated. I want to be able to open the updated workbook, select each sheet in that book and copy the data into the master workbook to the corresponding worksheets.
So I end up with master books sheets all match the updated books sheets with the exception of a front rollup sheet which can be hidden before the macro is run. The front page will contain macros and formulas referencing the sheets in the rest of the book and I cannot link due to this book being distributed as a standalone workbook.
All help welcome.
Patrick


No luck finding an answer? You could always try Google.