Automate checking of table B to reference table A

[okijuh123]

1. I want to check file B against A, determining if my combination of data in file B corresponds accordingly to file A. If the row in File B contains any error across the columns, I want to highlight it.

2. Both files have the same headers except for File B, there is a unique ID attached which I need. There can be several rows of B that corresponds to a single row of A. Meaning to say A contains unique rows and can contain duplicate rows with a Unique ID for each row. Is there any way to automate the checking process using Excel formulas or VBA? I need to highlight the rows in B that do not correctly match its respective row in A.

3. I need a formula to search file A for the combination and not just one row...E.g row 23 on File A may contain the combination I want the formula to see that row 3 on 'sheet 2' contains the same combination and return the yes or formula to see that the combination does not exist in rows 3 through 200 and return a NO and highlight it

TLDR:
Is there a way for me to automate checking? If the data combination in each row cannot be found in reference File A, I need to highlight it. This is a HR file, Drilling down from Macro to Micro organization breakdown.

 

[DanteAmor]

Hi and welcome to MrExcel.

If you have your two books like this:

tableB.xlsx
	A	B	C	D	E	F	G	H	I	J
1	Unique Id	group	country	unit	sub unit	Level1	Level2	Level3	Level4	Correct/Wrong
2	1	123	UK	A	AA	x	all	all
3	1	124	France	B	ABC	y	y2	y2a	all
4	1	125	UK	A	AA	z	x1	all
Sheet1

tableA.xlsx
	A	B	C	D	E	F	G
1	country	unit	sub unit	Level1	Level2	Level3	Level4
2	UK	A	AA	x	all	all
3	France	B	ABC	y	y2	y2a	all
4	UK	A	AA	x	x1	all
Sheet1

(By the way, your second example is correct, or I did not understand something. )

Try the following code, just update in the code the names of the 2 books and the names of the sheets of each book.

Sub checking_tableB_tableA()
  Dim shA As Worksheet, shB As Worksheet, dic As Object
  Dim a As Variant, b As Variant, c As Variant
  Dim i As Long, j As Long, strA As String, strB As String
  
  Set shA = Workbooks("TableA.xlsx").Sheets("Sheet1")
  Set shB = Workbooks("TableB.xlsx").Sheets("Sheet1")
  Set dic = CreateObject("Scripting.Dictionary")
  
  a = shA.Range("A2:G" & shA.Range("A" & Rows.Count).End(3).Row).Value2
  b = shB.Range("C2:I" & shB.Range("C" & Rows.Count).End(3).Row).Value2
  ReDim c(1 To UBound(b, 1), 1 To 1)
  
  For i = 1 To UBound(a, 1)
    For j = 1 To UBound(a, 2)
      strA = strA & "|" & a(i, j)
    Next
    dic(strA) = Empty
  Next
  
  For i = 1 To UBound(b, 1)
    For j = 1 To UBound(b, 2)
      strB = strB & "|" & b(i, j)
    Next
    If dic.exists(strB) Then
      c(i, 1) = "Correct"
    Else
      c(i, 1) = "Wrong"
    End If
  Next
  
  shB.Range("J2").Resize(UBound(c, 1)).Value = c
End Sub

Results on "tableB":

tableB.xlsx
	A	B	C	D	E	F	G	H	I	J
1	Unique Id	group	country	unit	sub unit	Level1	Level2	Level3	Level4	Correct/Wrong
2	1	123	UK	A	AA	x	all	all		Correct
3	1	124	France	B	ABC	y	y2	y2a	all	Correct
4	1	125	UK	A	AA	z	x1	all		Wrong
Sheet1

 

[okijuh123]

Hi DanteArmor,

My actual file has several more columns the in the example given, what do I have to change to make the VBA run?

 

[DanteAmor]

My actual file has several more columns the in the example given, what do I have to change to make the VBA run?

But what do you want to change, I do not know if the example I gave is correct, nor do you comment on whether the test with those data is correct.
If you want to adjust the macro, you would need to really know how your data is.
It would be great if you could put a more representative sample of your two sheets, use XL2BB tool to put a sample of your data (see my signature).

 

[okijuh123]

Hi and welcome to MrExcel.

If you have your two books like this:

tableB.xlsx
	A	B	C	D	E	F	G	H	I	J
1	Unique Id	group	country	unit	sub unit	Level1	Level2	Level3	Level4	Correct/Wrong
2	1	123	UK	A	AA	x	all	all
3	1	124	France	B	ABC	y	y2	y2a	all
4	1	125	UK	A	AA	z	x1	all
Sheet1

tableA.xlsx
	A	B	C	D	E	F	G
1	country	unit	sub unit	Level1	Level2	Level3	Level4
2	UK	A	AA	x	all	all
3	France	B	ABC	y	y2	y2a	all
4	UK	A	AA	x	x1	all
Sheet1

(By the way, your second example is correct, or I did not understand something. )

Try the following code, just update in the code the names of the 2 books and the names of the sheets of each book.

Sub checking_tableB_tableA()
  Dim shA As Worksheet, shB As Worksheet, dic As Object
  Dim a As Variant, b As Variant, c As Variant
  Dim i As Long, j As Long, strA As String, strB As String
 
  Set shA = Workbooks("TableA.xlsx").Sheets("Sheet1")
  Set shB = Workbooks("TableB.xlsx").Sheets("Sheet1")
  Set dic = CreateObject("Scripting.Dictionary")
 
  a = shA.Range("A2:G" & shA.Range("A" & Rows.Count).End(3).Row).Value2
  b = shB.Range("C2:I" & shB.Range("C" & Rows.Count).End(3).Row).Value2
  ReDim c(1 To UBound(b, 1), 1 To 1)
 
  For i = 1 To UBound(a, 1)
    For j = 1 To UBound(a, 2)
      strA = strA & "|" & a(i, j)
    Next
    dic(strA) = Empty
  Next
 
  For i = 1 To UBound(b, 1)
    For j = 1 To UBound(b, 2)
      strB = strB & "|" & b(i, j)
    Next
    If dic.exists(strB) Then
      c(i, 1) = "Correct"
    Else
      c(i, 1) = "Wrong"
    End If
  Next
 
  shB.Range("J2").Resize(UBound(c, 1)).Value = c
End Sub

Results on "tableB":

tableB.xlsx
	A	B	C	D	E	F	G	H	I	J
1	Unique Id	group	country	unit	sub unit	Level1	Level2	Level3	Level4	Correct/Wrong
2	1	123	UK	A	AA	x	all	all		Correct
3	1	124	France	B	ABC	y	y2	y2a	all	Correct
4	1	125	UK	A	AA	z	x1	all		Wrong
Sheet1

I am experiencing some errors with the For Loop line: strA = strA & "|" & a(i, j). Is there any way for me to highlight the errors that do not match? For example if Level1 Column is wrong but all the other columns are matching, highlight the Level1 Column. Some of my data is complete in File B and I am trying to highlight them. So far I have done it manually via CountIfs.

 

[DanteAmor]

I am experiencing some errors with the For Loop line: strA = strA & "|" & a(i, j). Is there any way for me to highlight the errors that do not match? For example if Level1 Column is wrong but all the other columns are matching, highlight the Level1 Column. Some of my data is complete in File B and I am trying to highlight them. So far I have done it manually via CountIfs.

Is it a error in the macro?
What does the error message say?

I insist, you can put a representative sample of your data. Sorry but it is difficult for me to understand what you need without clear examples.

 

[okijuh123]

But what do you want to change, I do not know if the example I gave is correct, nor do you comment on whether the test with those data is correct.
If you want to adjust the macro, you would need to really know how your data is.
It would be great if you could put a more representative sample of your two sheets, use XL2BB tool to put a sample of your data (see my signature).

The dataset I have provided above is correct, just that I have another two more columns Level5, Level6 which is further drill down of Organisational Structure. I want to be able to highlight the cell if there is no close matching row in Reference File A.

 

[okijuh123]

I ran the Macro using a XLSM, I decided to placed FileB into A on sheet2 and adjusted the macro accordingly. It gave me a run time error, running of out string space

 

[DanteAmor]

I insist, you can put a representative sample of your data. Sorry but it is difficult for me to understand what you need without clear examples.

I can't modify the macro, because I don't know what the result you are looking for.