Rows comparison and marking

SergioCM92

New Member
Joined
Apr 14, 2021
Messages
4
Office Version
  1. 2016
Platform
  1. Windows
Good morning/afternoon everybody!

So here I have a really big problem: I have to make comparison cell by cell in a lot of bill of materials for yesterday. Totally impossible to do it manually.

I tried with formula's but cannot get any result.

Here's the trouble:
I have to compare rows, cell per cell, between two excel documents.

I have to tell which cells have been erased, which added and which changed from a version to another.

Then mark them as erased from the previous version (red), added in the newest one (green) and changed (orange).

The structure would be like this, comparing from left until blank cell in the right limit.
1618418007409.png


Each document 800 rows, 9 columns. Comparison between 16 documents (1 to 1 but eight times). As you can see I'm totally screwed.

I tried to copy both versions in a third document (report of comparison), focusing only in the sheet of the documents where the materials appear, using VLOOKUP, but no results, only showing which ones are new.

Do you have any method to search for the similitude from the left cell and then compare value per value until the blank right cell and mark them somehow?

I would say that the only unique value is the Mechanical designator(column A). The rest can be duplicated.

If you have any advanced trick here it would save me from the fire..

Thanks so much in advance!
 

Excel Facts

Does the VLOOKUP table have to be sorted?
No! when you are using an exact match, the VLOOKUP table can be in any order. Best-selling items at the top is actually the best.

SergioCM92

New Member
Joined
Apr 14, 2021
Messages
4
Office Version
  1. 2016
Platform
  1. Windows
Ok tried to write it down:

Added:
-Start from first line of "previous sheet"
-Search in all range of current and find equal ID
-If Not found then red
-Next until finish

Erased:
-Start from first line of "Current sheet"
-Search in all range of previous and find equal ID
-If Not found then green
-Next until finish

Changed:
-Start first line previous
-Search in current
-compare column by column, and if some column is different, mark orange


CODE:


Sub Compare_BOMS()
' Erased

Dim w1 As Worksheet, w2 As Worksheet
Dim c As Range, a As Range, b As Range
Set w1 = Sheets("Previous")
Set w2 = Sheets("Current")
With w1
For Each c In .Range("A1", .Range("A" & Rows.Count).End(xlUp))
Set a = w2.Columns(1).Find(c.Value, LookAt:=xlWhole)
If Not a Is Nothing Then
w1.Cells(a.Row, a.Column).Font.Color = vbRed
End If
Next c
End With

' Added
With w2
For Each c In .Range("A1", .Range("A" & Rows.Count).End(xlUp))
Set a = w1.Columns(1).Find(c.Value, LookAt:=xlWhole)
If Not a Is Nothing Then
w2.Cells(a.Row, a.Column).Font.Color = vbGreen
End If
Next c
End With

'Changed
With w1
For Each c In .Range("A1", .Range("A" & Rows.Count).End(xlUp))
Set a = w2.Columns(1).Find(c.Value, LookAt:=xlWhole)
If a Then
For Each b In .Range(a.Column(2), .Range("2" & Columns.Count).End(x1Up))
If c.Column(b) <> a.Column(b) Then
w2.Cells(a.Row, b.Column).Font.Color = vbOrange
End If
Next b
'Compare

End If
Next c
End With


End Sub


Is bugged but I think I'm getting close.

If someone can bring me some light here I would be very grateful!
 

SergioCM92

New Member
Joined
Apr 14, 2021
Messages
4
Office Version
  1. 2016
Platform
  1. Windows
Almost have it! I can mark erased and added, but changed it gets bugged, any help¿?

1618559084028.png


Trying to at least do it from A1 to D1, but it's thought to be done in all range.
 

Watch MrExcel Video

Forum statistics

Threads
1,130,048
Messages
5,639,769
Members
417,111
Latest member
buyukbang

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