Comparing columns of data

  • Thread starter Thread starter Joe Gieder
  • Start date Start date
J

Joe Gieder

I trying to compare two columns of data on one sheet with
two columns of data on another and then write either
unchanged or updated in the formula column. Can someone
show please how to do this. I tried
=IF(MATCH((I3*R3)=('ENG C1 Priced BOM'!$H$423*'ENG C1
Priced BOM'!$R$423),0),"Unchanged","Updated")
but it doesn't seem to work.

Thanks for the help
Joe
 
Hi
try
=IF(I3 & R3= 'ENG C1 Priced BOM'!$H$423 & 'ENG C1 Priced
BOM'!$R$423),"Unchanged","Updated")

or (depending on the type of your values):
=IF(I3 * R3= 'ENG C1 Priced BOM'!$H$423 * 'ENG C1 Priced
BOM'!$R$423),"Unchanged","Updated")
 
Hi Frank,
I'm sorry, I wanted to use a range and I should have said
=IF(I3 * R3= 'ENG C1 Priced BOM'!$I$3:$I$789 * 'ENG C1
Priced BOM'!$R$3:$R$789),"Unchanged","Updated")
I tried your example and using the ranges and got Updated
on those that were both changed and unchanged. Did I do
something wrong?

Joe
 
Hi Joe
and what are you trying to compare?? if any of this rows in your range
contain the same values in one row. If yes you may try the following
array formula (entered with CTRL+SHIFT+ENTER)
=IF(ISNUMBER(MATCH(I3&R3,'ENG C1 Priced BOM'!$I$3:$I$789 & 'ENG C1
Priced BOM'!$R$3:$R$789,0)),"unchaged","changed")

or if you have numbers you may use the following array formula
=IF(ISNUMBER(MATCH(TEXT(I3,"0,00" & TEXT(R3,"0,00"),TEXT('ENG C1 Priced
BOM'!$I$3:$I$789,"0,00") & TEXT('ENG C1 Priced BOM'!$R$3:$R$789,
"0,00"),0)),"unchaged","changed")
 
Thanks Frank, The top formula worked great.
Joe
-----Original Message-----
Hi Joe
and what are you trying to compare?? if any of this rows in your range
contain the same values in one row. If yes you may try the following
array formula (entered with CTRL+SHIFT+ENTER)
=IF(ISNUMBER(MATCH(I3&R3,'ENG C1 Priced BOM'!$I$3:$I$789 & 'ENG C1
Priced BOM'!$R$3:$R$789,0)),"unchaged","changed")

or if you have numbers you may use the following array formula
=IF(ISNUMBER(MATCH(TEXT(I3,"0,00" & TEXT(R3,"0,00"),TEXT ('ENG C1 Priced
BOM'!$I$3:$I$789,"0,00") & TEXT('ENG C1 Priced BOM'! $R$3:$R$789,
"0,00"),0)),"unchaged","changed")



--
Regards
Frank Kabel
Frankfurt, Germany



.
Click to expand...
 
Back
Top