Posts by JohnnyBeck

Re: find duplicates with Vba when cells have more than 255 characters

Both of these are working with my initial test sheet. Thank you very much PCI and Jindon!

While I like the fact that Jindon's code gives different colors to make it easy to identify different sets of duplicates, this will be run on some massive workbooks that would have the potential to get really crazy looking. So I think I'll use PCI's solution initially.

Thanks again guys!

Re: find duplicates with Vba when cells have more than 255 characters

Can anyone help me with this?

Is there a way to identify duplicates within a range when the cells contain more than 255 characters?

Thanks guys!
j

Re: find duplicates with Vba when cells have more than 255 characters

Update:

I tried applying conditional formatting to duplicates in my concatenated column, but conditional formatting won't apply with that many characters either.

So basically, I really need help here. I need to identify when an entire row is duplicated in the worksheet.

Thanks!
j

Hello all. I have been developing a fairly involved macro that includes a portion to identify when an entire row has been duplicated somewhere in the worksheet. I have used a countif function with success.

However, if the entire row's data totals more than 255 characters, the Countif function craps out on me.

So here's the question, does anyone have a way to check for duplicates (highlight them) in VBA when the cell contents is over 255 characters?

Thanks!
Johnny

Re: Copy row based on cell value

If you have more than one cell in a row that meets the criteria, it will copy that row multiple times to the second sheet. Do you have any type of index number or unique identifier for each of the rows? If so, you can add code to remove the duplicates from the second sheet so you'll only see them once.

Re: Copy row based on cell value

Try this friend:

Sub test()
Dim rng As Range
Set rng = Sheets("Sheet1").UsedRange
For Each cell In rng
If cell.Value > 1 Then
cell.EntireRow.Copy Destination:=Sheets("Sheet2").Range("a65000").End(xlUp).Offset(1, 0)
End If
Next cell
    Sheets("Sheet2").Select
    Rows("1:1").Select
    Selection.Delete Shift:=xlUp
End Sub

Re: SpecialCells(xlcelltypevisible)

This may be oversimplifying the matter, but I would think that you could add an Else statement and Exit Sub or something along those lines.

Re: Compare data in multiple columns across 2 worksheets

I don't know what to tell you friend. It works on my computer. Don't forget, you have to make sure that your dates don't have the timestamp in them, or it won't find a match. Other than that, I'm at a loss. Very sorry.

Re: Compare data in multiple columns across 2 worksheets

I don't know what happened with the code window...but I added the coloring line to the code for you. Try that out.

Re: Compare data in multiple columns across 2 worksheets

Sub multmatch()Dim cell As RangeDim found As RangeDim nameColDim totalrows As Longtotalrows = ActiveSheet.UsedRange.Rows.CountnameCol = "$U$2:$U$" & totalrows For Each cell In Sheets("Sheet1").Range(nameCol)Set found = Sheets("TBP").Range(nameCol).Find(cell, LookAt:=xlWhole)For Each found In Sheets("TBP").Range(nameCol)If found & found.Offset(0, -16) & found.Offset(0, -18) Like cell & cell.Offset(0, -16) & cell.Offset(0, -18) Thencell.Offset(0, 5).Value = found.Offset(0, 5).Valuecell.Offset(0, 5).Interior.Color = 65535
End IfNext foundNext cellEnd Sub

Re: Compare data in multiple columns across 2 worksheets

Ok. This code works with your layout. However, be aware that I had to remove the timestamps in order for the dates to "match" since I'm comparing values of cells. I did that quickly using Text To Columns on the data tab in Excel. Also, on your example sheet, there were NO matches of data. I had to create a match or two to make sure the code was working with your layout. Either way, here it is:

Sub multmatch()


Dim cell As Range
Dim found As Range
Dim nameCol
Dim totalrows As Long


totalrows = ActiveSheet.UsedRange.Rows.Count
nameCol = "$U$2:$U$" & totalrows
 
For Each cell In Sheets("Sheet1").Range(nameCol)


Set found = Sheets("TBP").Range(nameCol).Find(cell, LookAt:=xlWhole)


For Each found In Sheets("TBP").Range(nameCol)
If found & found.Offset(0, -16) & found.Offset(0, -18) Like cell & cell.Offset(0, -16) & cell.Offset(0, -18) Then
cell.Offset(0, 5).Value = found.Offset(0, 5).Value


End If
Next found
Next cell


End Sub

Re: Compare data in multiple columns across 2 worksheets

I'm working on it for you now. Am I correct that you now want the Paid Amount from column Z to be updated from column Z on the TBP sheet?

Re: „Not enough system ressources to display completely"

I'm sorry I don't still have the link. But, I learned about the issue from Microsofts website. It's apparently an issue with Excel and how it handles resources. I had done a general search with the error I was getting at Google and found Microsofts page that addressed the issue.

Re: Compare data in multiple columns across 2 worksheets

I'm out.

Re: Compare data in multiple columns across 2 worksheets

Is the second sheet longer than the first?

Also, I thought you said you wanted column Z from the second sheet to be placed in column AN on the first sheet. Is it the other way around?

I just looked at your scrubbed sheet. Dude, the names are not in column C as you said they were.

Am I now looking at an accurate picture of the sheet you want? Are you wanting to use the names in column K to be the basis of the comparison??

I can do this, but I need accurate directions bro.

Re: „Not enough system ressources to display completely"

Also, make sure all of your tabs are zoomed to the same level. 100 percent or 90 or 80...any level is fine. They just need to all be the same. Even hidden sheets.

Re: Compare data in multiple columns across 2 worksheets

Quote from lawsonrw;560285

Checked 1000 lines of column NA and there has been no change... also, if I wanted to make the output appear in column AN, how do I modify the code to do that?

AN is what I meant. I mis-typed that.

Re: Compare data in multiple columns across 2 worksheets

Yea that's what I meant. You should see a change in column NA of your Sheet 1 since that's where you wanted to put the data from column Z of your other sheet. Only if all 3 criteria match though (Name, colE, colU)

Re: Compare data in multiple columns across 2 worksheets

Assuming that the Name (which is your constant) is in column C, this should work:

Sub multmatch()


Dim cell As Range
Dim found As Range
Dim nameCol
Dim totalrows As Long


totalrows = ActiveSheet.UsedRange.Rows.Count
nameCol = "$C$2:$C$" & totalrows
 
For Each cell In Sheets("Sheet1").Range(nameCol)


Set found = Sheets("Sheet2").Range(nameCol).Find(cell, LookAt:=xlWhole)


For Each found In Sheets("Sheet2").Range(nameCol)
If found & found.Offset(0, 2) & found.Offset(0, 18) Like cell & cell.Offset(0, 2) & cell.Offset(0, 18) Then
cell.Offset(0, 37).Value = found.Offset(0, 23).Value


End If
Next found
Next cell


End Sub

Re: Compare data in multiple columns across 2 worksheets

Quote from lawsonrw;560267

Okay, so I changed the code to

Code

Sub multmatch()
Dim cell As Range
Dim found As Range
Dim nameCol
Dim totalrows As Long
totalrows = ActiveSheet.UsedRange.Rows.Count
nameCol = "$C$2:$C$" & totalrows


For Each cell In Sheets("Sheet 1").Range(nameCol)
Set found = Sheets("TBP").Range(nameCol).Find(cell, LookAt:=xlWhole)
For Each found In Sheets("TBP").Range(nameCol)
If found & found.Offset(0, -1) & found.Offset(0, -2) Like cell & cell.Offset(0, -1) & cell.Offset(0, -2) Then
cell.Offset(0, 1).Value = found.Offset(0, 1).Value
End If
Next found
Next cell
End Sub

Display More

The change I needed to make was to put a space between "Sheet1" to make it "Sheet 1" Sorry for the confusion. That said, it appears the script will execute THANKS A TON! But where is the output? I hit run, it did its thing for a while, but I don't see anything different. Do I need to have a certain cell highlighted or is there some other step I'm missing?

Now that you have the error fixed, we'll still need to adjust for the columns NOT being A, B, C, D as you stated in your original example. This is why specifics make such a huge difference. I'll adjust the code and post it in just a few minutes.