Matching data and comparing for differences

Question

I'm dealing with a lot of data that I've had to copy over into other files. I want to make sure that I made no errors when copying the data into the other file.

I created a new Excel file and included copies of the two sheets I'm comparing. The data is NOT in the same order.

My first column is the part number for a specific product, and I want to find the same part number in the other sheet. My second column has the price, so I want to then compare the prices on both sheets to make sure they match.

I was thinking that an =IF(VLOOKUP( function would work, but I think I'm missing something. Maybe a combination of VLOOKUP and HLOOKUP? Maybe conditional formatting? I would appreciate any insights you have, thank you!

*Note: one sheet only lists the base part number, and the other sheet lists the part number with its variances. EX. the first sheet has V1000, the part number for a t-shirt, and the second sheet has that broken down further into different sizes: V1000-S, V1000-M, V1000-L. I think that means the function would have to look for an approximate match or a match that contains the base number.

Answer 1

Hi,

If the Part Number always has a length of 5, then write this formula in cell B2 of sheet3

=iferror(vlookup(A2,'sheet2'!$A$2:$B$8,2,0)=vlookup(left(A2,5),'sheet1'!$A$2:$C$4,3,0),"")

Hope this helps.

Answer 2

Now it's much easier.

Thanks!

So in this case yes, just an IF comparing the 2 procV

I will leave a print and a link to a demo worksheet for you based on your information.

Formula used in the J3 cell

=IF(VLOOKUP(I3;$A$3:$C$6;3;TRUE)=VLOOKUP(I3;$E$3:$G$10;2;FALSE);"TRUE";"FALSE")

--remembering that my separator is a semicolon.--

Download link:

https://we.tl/t-fUo5QWSu5B

Answer 3

Hi Andre,

Thanks for your response. I'm including pictures to illustrate what I'm talking about.

Here's an example of Sheet 1, which only includes the base product #. There are no repeats of part #s in this table.

SHEET 1
Part #	Description	Price
V1000	T-Shirt	$    29.99
V5678	Pants	$    32.99
V1234	Hat	$    12.99

Here's an example of Sheet 2, which includes the product variants. The part # is repeated with each variant.

SHEET 2
Part #	Price	Description
V5678-S	$      32.99	Pants
V5678-M	$      32.99	Pants
V5678-L	$      32.99	Pants
V1234	$      12.99	Hat
V1000-S	$      29.99	T-Shirt
V1000-M	$      29.99	T-Shirt
V1000-L	$      29.99	T-Shirt

As you can see, the columns are in different orders as well as the data itself. (The description column is only included to show that. No data needs to be pulled from that column.) Some products do not have variants.

I want to look the base product number up from Sheet 1 in Sheet 2 and make sure that the prices match for each of the variants.

I could create another sheet for results that would look like this:

SHEET 3
Part #	Does the price match?
V1000-S	TRUE
V1000-M	TRUE
V1000-L	TRUE

(If the price from Sheet 1 didn't match what I had listed for the price on Sheet 2, it would display FALSE)

Or any discrepancies could be highlighted directly in Sheet 2.

Hope that helped, let me know if you have more questions!

Answer 4

Hi

My name is André. I am an independent consultant.

>Before I can give you the right direction, I need to ask you a few questions.

First, are there items that repeat the part numbers in the table?

for example, will they have more than one line with the v1000 product for example?

second, you want to compare the part numbem of the first table ex V1000 with the data from the extended versions v1000-S in what way?

Having these answers, I can assemble the formula for you, but I'm starting to think, that loockup isn't going to be the best way.

Answer here so I can continue helping you.

André.