Compare between 2 tables

**dispro14** · 06-26-2014, 02:08 PM

Hi,
I would like to get some help with a macro that compare between 2 tables in 2 different sheets - BOM (Bill Of Materials) compare
And Creat a Table with all differences.

Table 1 (sheet1):

PartNum QTY Location
000 2 A1
111 3 B1
222 1 C1
333 4 D1

Table 2 (sheet2):

PartNum QTY Location
333 3 D1
000 2 A1
111 3 B2
222 1 C1
444 5 E6

Table with all differences (sheet 3):

PartNum (Ver1) PartNum (ver2) QTY (Ver1) QTY (Ver2) Location (Ver1) Location (Ver2)
111 111 3 3 B1 B2
333 333 3 4 D1 D1
Blank 444 Blank 5 Blank E6

**Richard Buttrey** · 06-26-2014, 08:11 PM

Hi,

The general method for comparing whether one list appears in another is to use a MATCH() function. Maybe wrapping it in an IFERROR for tidiness i.e. in general

=IFERROR(MATCH(A1,another_range,False),"No Match")

You can the filter the list for the No Match items.

**dispro14** · 06-27-2014, 03:38 AM

Hi Richard,
The problem is that the same PartNum can appear in more then one row but for different locations.
Also row 2 in sheet 1 can appear in other row in sheet 2.

Thanks