Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Complex matching with ranges
I am not sure how to accomplish what I need to accomplish, and I'm not sure
it can be done in excel... It may need a VB code which I have no experience with. I have a workbook that contains 2 worksheets. Each worksheet is approx 20k rows of data. Sheet 1: Column A are numbers that can contain up to 4 digits. There are repeating numbers. An example is 1 = product type 1, 548 = product type 548, etc. Column B is formated as numbers and is in the form of XX.XX. These also may be repeating numbers. An example is 23.45, or 1100.50, etc. Column C is a concatenation of column A and B. It is read as text since there are 2 decimals inserted in the number. =A1$"."$B1, so the returned value is 1.23.45, or 548.1100.50, etc. Sheet 2 is formated the same as sheet 1. The values contained in the sheets are different. I need to use Sheet 2 values to match to sheet 1 values and return the closest value, higher or lower. Example: Sheet 2 Column A must match Sheet 1 Column A. Using sheet 2 column B I want to search sheet 1 column b and return the closest value +/- 1.00 to the sheet 2 column b value while having an exact match to column A. If Sheet 2 values a 1 25.03 1.25.03 And sheet 1 values a 1 24.78 1.24.78 1 25.33 1.25.33 The returned value should be 1.24.78 since it is only .25 from the lookup value and 1.25.33 is .30 from the lookup value. Can this be done? Thanks!!!! |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Complex matching with ranges
Steven,
Here is a solution that ignores your existing column C. Insert a new column C on sheet 2, and in cell C2, array enter (enter using Ctrl-Shift-Enter) this formula: =A2& "." & INDEX(Sheet1!B:B,SUM((MIN(IF(Sheet1!A1:A20000=A2,A BS(Sheet1!B1:B20000-B2),1000))=ABS(Sheet1!B1:B20000-B2))*ROW(Sheet1!A1:A20000))) and copy down to match. This will return the closest value (no matter how far away) unless there is an _exact_ tie (unlikely because of how numbers are represented in Excel's memory). HTH, Bernie MS Excel MVP "Steven M." wrote in message ... I am not sure how to accomplish what I need to accomplish, and I'm not sure it can be done in excel... It may need a VB code which I have no experience with. I have a workbook that contains 2 worksheets. Each worksheet is approx 20k rows of data. Sheet 1: Column A are numbers that can contain up to 4 digits. There are repeating numbers. An example is 1 = product type 1, 548 = product type 548, etc. Column B is formated as numbers and is in the form of XX.XX. These also may be repeating numbers. An example is 23.45, or 1100.50, etc. Column C is a concatenation of column A and B. It is read as text since there are 2 decimals inserted in the number. =A1$"."$B1, so the returned value is 1.23.45, or 548.1100.50, etc. Sheet 2 is formated the same as sheet 1. The values contained in the sheets are different. I need to use Sheet 2 values to match to sheet 1 values and return the closest value, higher or lower. Example: Sheet 2 Column A must match Sheet 1 Column A. Using sheet 2 column B I want to search sheet 1 column b and return the closest value +/- 1.00 to the sheet 2 column b value while having an exact match to column A. If Sheet 2 values a 1 25.03 1.25.03 And sheet 1 values a 1 24.78 1.24.78 1 25.33 1.25.33 The returned value should be 1.24.78 since it is only .25 from the lookup value and 1.25.33 is .30 from the lookup value. Can this be done? Thanks!!!! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Matching Columns - very complex | Excel Discussion (Misc queries) | |||
Complex Matching | Excel Discussion (Misc queries) | |||
Complex Matching | Excel Discussion (Misc queries) | |||
Can I use Dynamic Ranges to automate complex calculations? | Excel Discussion (Misc queries) | |||
Matching two column ranges with a date | Excel Worksheet Functions |