Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Rounding, List, Compare, Match
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) |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Rounding, List, Compare, Match
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 "Curious" wrote in message ... 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) |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Compare List A to List B, Return List B Items Not in List A | Excel Programming | |||
Compare, Match & Sum HELP | Excel Discussion (Misc queries) | |||
Compare Value in Cell 1 to a List, Return Value if Match otherwise Return Null | Excel Discussion (Misc queries) | |||
rounding numbers for match function | Excel Worksheet Functions | |||
rounding numbers for match function | Excel Worksheet Functions |