Compare 1 column where column B matches

[csimpson24]

Hi all,

So I have a very large spreadsheet which contains all of our parts and part descriptions. It was merge between old data and new data. I am needing to see which part's descriptions have change from then and now.

When B is a match (part number), compare D (Description) and write match or mismatch in E for both matching part number rows.

Any help would be appreciated.

Thank you,
Chris

 

[sheetspread]

Separate the old and new into tables if they're not already (by sorting then cut/paste) and something like:

Excel 2010
	A	B	C	D	E	F
1	Number	Description	Match?		Number	Description
2	34654	Left Rotor Axle	TRUE		34654	Left Rotor Axle
3	23654	Twin Die Cast	FALSE		23654	Twin Die Plate
Sheet8

Cell Formulas
Range	Formula
C2	=VLOOKUP(A2,$E$2:$F$3,2,0)=B2

 

[csimpson24]

Thank you SheetSpread, however, there will be several thousand parts in the new data that have been added since the old data...

 

[sheetspread]

Is there a label of old and new? Or a date you can sort on? If not, add a cumulative count:

Excel 2010
	A	B
7	34654	1
8	23654	1
9	34654	2
10	23654	2
Sheet8

Cell Formulas
Range	Formula
B7	=COUNTIF($A$7:A7,A7)

 

[csimpson24]

Sorry, I got pulled off of this for a few days but now back on it. Is there not a way, maybe in VBA, to script it? So starting at the beginning of the data row to row, get the first part number, find if that same part number exists in another row, compare the descriptions, and write Match or Mismatch in column D?

 

[sheetspread]

Vlookup is the easiest way, here's one that doesn't require splitting the data:

Excel 2010
	A	B	C
1	Number	Description	Match?
2	34654	Left Rotor Axle	TRUE
3	23654	Twin Die Cast	FALSE
4	34654	Left Rotor Axle	TRUE
5	23654	Twin Die Plate	FALSE
6	196543	Sparking Cable	TRUE
7	6655434	Distributor	TRUE
8	543423	Glow Plug	FALSE
9	196543	Sparking Cable	TRUE
10	6655434	Distributor	TRUE
11	543423	Spark Plug	FALSE
Sheet4

Cell Formulas
Range	Formula
C2	=B2=IFERROR(VLOOKUP(A2,$A$1:$B1,2,0),"")&IFERROR(VLOOKUP(A2,$A3:$B111,2,0),"")

 

[csimpson24]

Could it handle if it does not find another match to the part number? Meaning, the part number only exists once in the data?

 

[sheetspread]

Yes

 

[csimpson24]

So I would put this formula in C2 and then drag it down to the last row of data?

 

[csimpson24]

Here's my first result. Should be TRUE???

[TABLE="width: 316"]
<colgroup><col><col><col></colgroup><tbody>[TR]
[TD]Part[/TD]
[TD]Description[/TD]
[TD]Match?[/TD]
[/TR]
[TR]
[TD]30021.02[/TD]
[TD]ROUND HOT ROLLED 1/4"[/TD]
[TD="align: center"]FALSE[/TD]
[/TR]
[TR]
[TD]30021.02 [/TD]
[TD]ROUND HOT ROLLED 1/4" [/TD]
[TD="align: center"]FALSE[/TD]
[/TR]
</tbody>[/TABLE]