Question regarding comparing two lists

Thisguy

New Member
Joined
Mar 3, 2004
Messages
17
I think this is a pretty easy question, but I am not very good at marcos yet so I need some help.

Here is a macro from Miscrosoft's website to compare two lists and delete duplicate items:

Sub DelDups_TwoLists()
Dim iListCount As Integer
Dim iCtr As Integer

' Turn off screen updating to speed up macro.
Application.ScreenUpdating = False

' Get count of records to search through (list that will be deleted).
iListCount = Sheets("sheet2").Range("A1:A100").Rows.Count

' Loop through the "master" list.
For Each x In Sheets("Sheet1").Range("A1:A10")
' Loop through all records in the second list.
For iCtr = 1 To iListCount
' Do comparison of next record.
' To specify a different column, change 1 to the column number.
If x.Value = Sheets("Sheet2").Cells(iCtr, 1).Value Then
' If match is true then delete row.
Sheets("Sheet2").Cells(iCtr, 1).Delete xlShiftUp
' Increment counter to account for deleted row.
iCtr = iCtr + 1
End If
Next iCtr
Next
Application.ScreenUpdating = True
MsgBox "Done!"
End Sub


How would I change this so that the macro would compare two lists, and KEEP the duplicate items rather than deleting them?
 

Some videos you may like

Excel Facts

Best way to learn Power Query?
Read M is for (Data) Monkey book by Ken Puls and Miguel Escobar. It is the complete guide to Power Query.

NdNoviceHlp

Well-known Member
Joined
Nov 9, 2002
Messages
2,723
You didn't say what you really wanted to do? This code compares list A1-A5 to B1-B5 for matches and gives msgbox of any matches. Hope this gets you started. Dave
Code:
Sub DelDups_TwoLists()
Dim iListCount As Integer
Dim iCtr As Integer

' Turn off screen updating to speed up macro.
Application.ScreenUpdating = False

' Get count of records to search (comparison "B")
iListCount = Sheets("sheet1").Range("b1:b5").Rows.Count

' Loop through the "master" list (A).
For Each x In Sheets("Sheet1").Range("A1:a5")
' Loop through all records in the second list.
For iCtr = 1 To iListCount
' Do comparison of next record.
' To specify a different column, change 1 to the column number.
If x.Value = Sheets("Sheet1").Cells(iCtr, 2).Value Then
' If match is true then????
MsgBox "U Have a match   " & Sheets("Sheet1").Cells(iCtr, 2).Value
End If
Next iCtr
Next
Application.ScreenUpdating = True
MsgBox "Done!"
End Sub
 

Barrie Davidson

MrExcel MVP
Joined
Feb 10, 2002
Messages
2,330
Need some more information.

• Where is your data? What is the range?
• How do you determine duplicates (for example, based on a part number in column B)?
• Where do you want the duplicates stored? On another worksheet?
 

Thisguy

New Member
Joined
Mar 3, 2004
Messages
17
Sorry, I should have made it more clear. Here is my sheet:
Book1
ABCD
1Item1Item3
2Item2Item6
3Item3Item7
4Item4
5Item5
6Item6
7Item7
8Item8
9Item9
10Item10
11
Sheet1


The data and number of rows in Column A is fixed. The data in Col C appears automatically if certain conditions are met.

I want to be able to copy Column C to another sheet. The range in Col C could be C1:C2 or could be anything between C1:C10.

My thought was to copy Col A to the new sheet, and then run a macro to delete any items that did not appear in Col C of the source sheet.

I was trying to do a conditional copy & paste, but couldn't figure it out.

Any help would be greatly appreciated.
 

NdNoviceHlp

Well-known Member
Joined
Nov 9, 2002
Messages
2,723

ADVERTISEMENT

OK onward... what "certain conditions"? What does col "C" represent... sheet1 matches with sheet2? I think your notions re. copy etc. is probably unnecessary....of course this would be dependent upon what you're trying to achieve. It would help if you would clearly share the end game. The last post talks of deleting items... I thought that's what we were trying to avoid? "Conditional" and "Copy and Paste" are 3(Maybe 4) words that I don't like. Can't you just use the following approach to move your match info from sheet1 to sheet2?
>
Code:
Sheets("Sheet2").Range("a" & 1).Value = _
Sheets("Sheet1").Range("c" & 1).Value
Code:
re-read your post...are you trying to delete non matches in the end?  My guess ...delete the original unsorted then replace with the sorted. 
I think you will have to offer more info if we Tobans are gonna help you out.  Dave
 

Thisguy

New Member
Joined
Mar 3, 2004
Messages
17
I was trying to "water-down" what I was trying to do, but here is my sheet:
Book1.xls
ABCDEFGHIJK
185
2Account
3NameLeftRightConcatSortRank
4Account110LeftAccount141Account110 
5Account220RightAccount274Account350 
6Account350LeftAccount352Account560 
7Account45RightAccount485Account2 20
8Account560LeftAccount563Account4 5
9      
10      
Sheet2


Cols B & C are my input columns, and depending on what is inputted in those columns (i.e. in the "Left" column or "Right" column), the data appears in Cols H, I & J.

I want to be able to automatically copy the data that appears in Cols H, I and J to another sheet.
 

NdNoviceHlp

Well-known Member
Joined
Nov 9, 2002
Messages
2,723
Are you still looking for a VBA solution? If so, what event causes the information transfer (command button etc.). Do you want to delete the old information after transfer? Do you need to delete it... new info would replace the old wouldn't it? Lastly, where is it that you want to put this informationand successive information transfers? "another sheet" is kind of vague. Some sample VBA to avoid your spreadsheet formulas would look something like the following if you were inputting on sheet1 and transferring your data to sheet2...
Code:
Private Sub CommandButton1_Click()
Dim cnt As Integer, cnt2 As Integer, cnt3 As Integer
cnt2 = 0 ' change destination row address to suit
'left
For cnt = 4 To [sheet1!A1] ' A1 is your row cnt?
If Sheets("Sheet1").Range("b" & cnt).Value <> "" Then
cnt2 = cnt2 + 1
'change column/sheet address below to suit
Sheets("Sheet2").Range("a" & cnt2).Value = _
Sheets("Sheet1").Range("a" & cnt).Value
Sheets("Sheet2").Range("b" & cnt2).Value = _
Sheets("Sheet1").Range("b" & cnt).Value
End If
Next cnt

'right
For cnt3 = 4 To [sheet1!A1]
If Sheets("Sheet1").Range("c" & cnt3).Value <> "" Then
cnt2 = cnt2 + 1
'change column/sheet address below
Sheets("Sheet2").Range("a" & cnt2).Value = _
Sheets("Sheet1").Range("a" & cnt3).Value
Sheets("Sheet2").Range("c" & cnt2).Value = _
Sheets("Sheet1").Range("c" & cnt3).Value
End If
Next cnt3
End Sub
If you offer a bit more information, I'm pretty sure you will be able to accomplish your goal. A vba solution may not be needed but it seems that it would be fairly easy to do.
 

Watch MrExcel Video

Forum statistics

Threads
1,122,809
Messages
5,598,202
Members
414,218
Latest member
speedbit

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top