Solved: Need Help - Duplicate entries

[Amanda1]

Hi

I'm not too much of an expert on Excel, but I can usually manage. This one however has got me stumped and I'd really appreciate some help.

I have a workbook with two sheets. Sheet 1, Col A contains date, Col B contains amount & Col C contains a text entry. Sheet 2 is the same other than the text entry is in Col D instead of C.

There are thousands of rows in each sheet, and probably only 10% of the rows are identical in both sheets i.e. date, amount & text entry are exact matches. (It doesn't matter where in the sheets the identical rows are - the criteria is that the three cells in the same row are identical) What I need is to find the exact matches and if possible populate them onto another sheet (in same workbook). I have tried every function etc that I can possibly think of - all to no avail. Can anyone help a damsel in distress?

Cheers & take care
Amanda

[mvidas]

Hi Amanda,

Give the following subroutine a try, should do what you need![vba]Sub Amanda()
Dim WS1 As Worksheet, WS2 As Worksheet, WS3 As Worksheet
Dim DataRG1 As Range, DataRG2 As Range, DataArr1(), DataArr2()
Dim R1 As Long, R2 As Long, TempEntry1 As String, TempEntry2 As String
Dim Delim As String, RW As Long

Set WS1 = Sheets("Sheet1") 'sheet with your data in A:C
Set WS2 = Sheets("Sheet2") 'sheet with your data in A,B,D
Set WS3 = Sheets("Sheet3") 'sheet to copy the duplicates to
Set DataRG1 = WS1.Columns("A:C")
Set DataRG2 = WS2.Range("A:B,D")
RW = 1 'first row to paste to on WS3

Set DataRG1 = Intersect(WS1.UsedRange, DataRG1)
Set DataRG2 = Intersect(WS2.UsedRange, DataRG2)
Delim = Chr(0)
DataArr1 = DataRG1.Value
With Sheets.Add
DataRG2.Copy .Range("A1")
DataArr2 = .UsedRange.Value
Application.DisplayAlerts = False
Application.ScreenUpdating = False
.Delete
Application.ScreenUpdating = True
Application.DisplayAlerts = True
End With
For R1 = 1 To UBound(DataArr1, 1)
TempEntry1 = DataArr1(R1, 1) & Delim & DataArr1(R1, 2) & Delim & DataArr1(R1, 3)
For R2 = 1 To UBound(DataArr2, 1)
TempEntry2 = DataArr2(R2, 1) & Delim & DataArr2(R2, 2) & Delim & DataArr2(R2, 3)
If TempEntry1 = TempEntry2 Then
DataRG1.Rows(R1).Copy WS3.Range("A" & RW)
RW = RW + 1
Exit For
End If
Next
Next
End Sub[/vba]Matt

[Amanda1]

Hi Matt,

Looks great, (not sure that I understand exactly what each bit of the script is doing, but that will be my next lesson working it all out). I'm about to give it a try - I'll let you know - keep your fingers crossed for me.

Cheers Amanda

[Amanda1]

Matt, You are a star!!!!!!!!!

I've kept your script carefully as a master - now I've copied it and I'm going to see if I'm clever enough to be able to adapt it for some other big workbooks that the staff have got. (Should be good for a laugh).

I'm working through your script trying to understand what each command line means - hence I'm indulging in lots of "looking up" at the moment.

Again, thank you so much - you have actually just saved nine people 6 hours each per day - now I can get them onto something that will add value!

(If I didn't want it to return the actual entries in the cells, but rather to give the cell address of the match - e.g A786 B785 etc - is there a way to do that?)

Take care & have a brilliant day. Amanda

[mdmackillop]

Hi Amanda,
Adding .Addesss after the range reference will return the address. Check it in the VB help file for different formats eg .Address gives $A$1 style .Address(0,0) gives A1 style

[Amanda1]

OK, bear with me - I'm a bit lost. Understanding Matts script is obviously easy for you, but unfortunately it's another language to me. I follow the logic of your advice, (thank you), but if I must put it exactly after the range reference in the script - I'm obviously putting it somewhere that doesn't work, cause nothing is being populated. What would really make my day is if in column D of the worksheet being created with the matched entries, it would populate the row that the entries originated from. i.e. currently it gives me exactly what I need with A,B,C being populated, but how do I change the script to make col D also show me the original row and still keep Col A,B,C populated?

Now you can see why I said - bear with me. Thanks for your help!

[mvidas]

Glad to help Amanda!

As far as putting the row in there, add the second line here after the first (near the end of the code):[vba] DataRG1.Rows(R1).Copy WS3.Range("A" & RW)
WS3.Range("D" & RW) = DataRG1.Rows(R1).Row[/vba]

As far as the code goes, it does use a couple more advanced techniques, but nothing too advanced. Feel free to ask any questions about how something works or why I did it the way I did. In a nutshell, the code basically does this:

- Dimension variables

- Set worksheet, range, and row-to-paste variables

- Create arrays with data from ranges
--- 'DataRG2' spans 3 columns in 2 areas (A:B,D), so I create a new worksheet first, copy the range to it onto columns A:C, then transfer the data from that continuous range into the array. I do this because it is quicker than actually looping through the range (depending on range size at least, for small ranges there probably wouldn't be a time difference) and working with arrays instead of cells uses less memory, runs faster.

- Loop through array, comparing each entry (A&B&C) from array 1 to each entry (A&B&D) in array 2
--- if they match, I copy over the cells to the 3rd worksheet

As I said, if you have any questions, please dont hesitate to ask
Matt

[Amanda1]

Hi Matt,

Again, you've been brilliant to me. I've been working through your script today and trying to understand what each line etc does - probably worked out about 80% of it & you have just added the missing 20%. I have never been involved with script writing/development before - so things like arrays etc were definitely Chinese to me, but after today they are down to plain Greek. (Goal is to eventually get them as readable as English).

There are a lot of staff that I'm helping who are very basic Excel users - problem is that their knowledge is so fundamental and the amount of data they have to process daily is so immense that they haven't got time to get into training. Hence I'm trying to make their workload easier, free some time up for them & then I can start getting their knowledge up.

I'm busy writing some small scripts to automate more of what they all do, and I'm doing each one is sections - rationale being that then I can attach each one to buttons for them and they can run the scripts as they need to.

Don't go too far away from your PC though, because after you produced that script so quickly, I'd love to take you up on your offer to explain something to me if I get really stuck on the rest I'm doing.

Again, thank you for all your hard work for me!

Amanda

[mvidas]

As I said, I'm glad to help! This site is great because if you get stuck on something writing your buttons, post a quick question here and you'll have your answer. You can also post the code you do come up with, we might be able to find a faster or better way to do it, so you can use the better methods on your next coding.
I check the site regularly, so I'll definately be around to help if you need it. But if I happen to be away, rest assured there are many good people here

Also, since you're new to the VBAX community (welcome!, by the way), you can go to the top of the thread, go to Thread Tools, and choose "Mark Thread Solved" - this helps clean up the forum. Don't worry though, we all stay subscribed to the question, and you can always post a followup question at any time, even to solved threads

[mdmackillop]

Hi Amanda,
I see Matt has solved your first problem. As he says, we're happy to assist with follow ups or new questions as appropriate. If you have any code or sample data, you can post this by zipping it and attach it using Manage Attachments found by clicking the Go Advanced button. It's always easier create a solution if we have data types/layouts etc. to see.
Regards
MD

[Amanda1]

Perfect.

Thanks for the advice - I'll certainly follow it.

I must say that I think this site is great - not only did I get a quick and brilliant solution to my problem, but I was paging through it last night on the new posts and learnt something else I didn't know, which, very helpfully, solves another problem for me. I can see I about to become a confirmed regular.

I do really appreciate everyones help and advice.

Take care & thanks again

Amanda