Question regarding comparing two lists

[Thisguy]

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?

 

[NdNoviceHlp]

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

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]

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]

Sorry, I should have made it more clear. Here is my sheet:

Book1
	A	B	C	D
1	Item1		Item3
2	Item2		Item6
3	Item3		Item7
4	Item4
5	Item5
6	Item6
7	Item7
8	Item8
9	Item9
10	Item10
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]

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?
>

Sheets("Sheet2").Range("a" & 1).Value = _
Sheets("Sheet1").Range("c" & 1).Value

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]

I was trying to "water-down" what I was trying to do, but here is my sheet:

Book1.xls
	A	B	C	D	E	F	G	H	I	J	K
1	8							5
2	Account
3	Name	Left	Right	Concat	Sort	Rank
4	Account1	10		LeftAccount1	4	1		Account1	10
5	Account2		20	RightAccount2	7	4		Account3	50
6	Account3	50		LeftAccount3	5	2		Account5	60
7	Account4		5	RightAccount4	8	5		Account2		20
8	Account5	60		LeftAccount5	6	3		Account4		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]

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...

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.