Rounding, List, Compare, Match

[Curious]

How do I compare two lists of figures to match the same item? The hard
part is that some figures are results of rounding, like 12345 vs 12346
vs 12343, they all have the same order number but the different data
sources make them look different.

Thanks in advance (always, I have learned a lot from you guys)

 

[Bernie Deitrick]

wisemax,

Try setting up a column of formulas, along the lines of

=ROUND(A2/5,0)*5

Which rounds to the nearest five, as in your example, and then match on that
value along with the order number. A lot depends on what you want to pull
out:

Just the fact that it matches something uniquely:
=SUMPRODUCT((orderNumber = OrdeNumbers)*(RoundedVal = Rounded Values))=1

=SUMPRODUCT((H2 = $A$2:$A$10000)*(I2 = $B$2:$B$10000))=1
will return true if H2 matches something in column A, and I2 matches the
value in the same row, but in column B...

More specific descriptions of your layout would help us help you.


HTH,
Bernie
MS Excel MVP