PDA

View Full Version : Comparing string differences between two separate column of sheets???



imso
07-18-2011, 09:04 PM
Sorry for not able to insert url because i'm rather new to this forum. :(

Here's a brief description basically, I just want my macros to compare Part Number (column C) from both sheets and find out the differences. And when a string differences is detected between both sheets it will highlight the row on both sheet of BOM-list to indicate to the user the differences in the Part-number(column C). But that is a problem too as seen in the images there is some rows with "space" which the loop have to take care of to prevent comparing an empty string thus giving wrong result.

Sorry for my poor command of English and explanation if its not clear to you. Can someone guide me on this i'm rather aimless on where or how to start and i have to complete this within a week without prior knowledge on excel-VBA programming understanding. :(

imso
07-18-2011, 09:23 PM
http://www.ozgrid.com/forum/attachment.php?attachmentid=39671&d=1310987473

http://www.ozgrid.com/forum/attachment.php?attachmentid=39672&d=1310987474

Above i have attached two images that i have captured from my excel document (Sheet1, Sheet 2)

CatDaddy
07-19-2011, 10:02 AM
for future reference you can attach workbooks :)

imso
07-19-2011, 05:44 PM
I have uploaded the file you are referring to.

CatDaddy
07-20-2011, 09:34 AM
Ok, i have something working in the right direction...do you want to highlight product numbers that are unique ot one of the sheets?

could you give another workbook of what you want it to look like when you are finished? maybe a small one would be fine

CatDaddy
07-20-2011, 10:22 AM
Sub comparePartNums()
Dim ws1Rng As Range, ws2Rng As Range
Dim cell As Range, cell2 As Range
Dim flag As Range
Set ws1Rng = ActiveWorkbook.Sheets(1).Range("C21:C864")
Set ws2Rng = ActiveWorkbook.Sheets(2).Range("C21:C887")
For Each cell In ws1Rng
If cell <> Empty Then
Set flag = ws2Rng.Find(cell)
On Error GoTo 0
If flag Is Nothing Then
cell.EntireRow.Interior.Color = RGB(255, 0, 0)
End If
End If

Next cell
For Each cell2 In ws2Rng
If cell2 <> Empty Then
Set flag = ws1Rng.Find(cell2)
On Error GoTo 0
If flag Is Nothing Then
cell2.EntireRow.Interior.Color = RGB(255, 0, 0)
End If
End If

Next cell2

End Sub

imso
07-20-2011, 08:19 PM
Hi CatDaddy,

Thanks for your help.

But may i ask you how do i this spreadsheet to do something more dynamic and recyclable for more user to use? By allowing the user to import 2 excel related BOM-list files from the computer into sheet2 and 3 before i run the above code to find the differences?

Eg: Something like having two CommandButton's which can import all the excel BOM-list data information into sheet 2/3 without making any changes to the original? Before running any comparison test and a reset button for removeing all the content in sheet 2/3?

I have an attachment below. Thanks for your help :)

Regards,
imso

imso
07-21-2011, 02:17 AM
Hi CatDaddy,
So far I only manage to code this much unfortunately.. The part on importing excel sheet and copy on sheet 2 and 3 i'm kinna stuck not knowing how?

Private Sub ComparisonButton_Click()
ComparisonButton.Enabled = False
ResetButton.Enabled = True
Call Differences
End Sub

Private Sub ImportButton1_Click()
If (fileLabel1.Caption = "") Or (fileLabel2.Caption = "") Then
ComparisonButton.Enabled = False
Else
ComparisonButton.Enabled = True
End If
End Sub

Private Sub ImportButton2_Click()
If (fileLabel1.Caption = "") Or (fileLabel2.Caption = "") Then
ComparisonButton.Enabled = False
Else
ComparisonButton.Enabled = True
End If
End Sub

Private Sub ResetButton_Click()
ComparisonButton.Enabled = False
ResetButton.Enabled = False
End Sub

imso
07-21-2011, 07:12 PM
Hi CatDaddy,

This is the code i have coded so far without much progress unfortunately. Can you give me some suggestions on how to implement the above mention functions?

Private Sub ComparisonButton_Click()
ComparisonButton.Enabled = False
Call Differences
ResetButton.Enabled = True
End Sub

Private Sub ImportButton1_Click()
If fileLabel1.Caption = "" Then
ComparisonButton.Enabled = False
ElseIf fileLabel1.Caption <> "" And fileLabel2.Caption <> "" Then
ComparisonButton.Enabled = True
End If
End Sub

Private Sub ImportButton2_Click()
If fileLabel2.Caption = "" Then
ComparisonButton.Enabled = False
ElseIf fileLabel1.Caption <> "" And fileLabel2.Caption <> "" Then
ComparisonButton.Enabled = True
End If
End Sub

Private Sub ResetButton_Click()
ComparisonButton.Enabled = False
ResetButton.Enabled = False
Sheet2.Cells.Clear
Sheet3.Cells.Clear
End Sub
Regards,
imso