| Home |
| Search |
| Today's Posts |
|
|
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Sort and Match Cells
"scott" wrote: That seems to work except when I add a cell to either Column A or B, forcing the cells down, the formulas in Column C change. My other problem is that I will never know how many rows will contain values in Column A. Can you help me change the formulas relative cell references to fix problem #1 and suggest a method to deal with problem #2? "Joel" wrote in message ... I found the problem with the formula. I'm using just column B for table instead of A & b. =IF(ISNA(VLOOKUP(A1,B$1:B$9,1,FALSE)),"",A1) do you still need the steps below or will the formula solve the problems? "scott" wrote: Steps: 1. Either with code or manually, cell A2 and below will move down a cell 2. New value will be entered into blank A2 3. Either with code or manually, cell B2 and below will move down a cell 4. New value will be entered into blank B2 5. Steps 1 to 4 will be repeated maybe 20 to 30 times each day. Rules: 1. No value will be added into Column B that doesn't exist in Column A. 2. Column A will always contain values that haven't been entered into Column B. 3. No duplicates 4. As far as orphans, the cell next to it just needs to be blank, like my example in LISTING 2. "Nigel" wrote in message ... I guess one of the pieces of information that is missing before a solution could be suggested is how is new data being loaded? Is the entire worksheet being replaced or are new data being appended? Will the 2nd column always have a matching partner in 1st column, if not what do you want to do with any orphan records, also could there be duplicates in 1st, 2nd or both columns? -- Regards, Nigel "scott" wrote in message ... I'm trying to think of a way to "sort" or "match" two columns of cells based on if the values of the rows are equal. For examle, LISTING 1 below shows cell B2 equal to 196650. I need a way with code to "sort" or "match" that value with it's equal in column A. So my results would end up looking like LISTING 2 below, where the 196650 value gets moved to cell B4. These are bill of laiding codes that will come in each day, so column A's values would always be increasing as well as column B's billed numbers would (i.e. cells would be shifting down). Can someone suggest any functions that may exist that could take the value of a cell and match it to the correct cell like I described below? I'm mainly an Access database programmer, but have done a few Excel apps before, but I just can't get a game plan in my mind on how to come up with a solution for this problem. Any advice or guidance would be appreciated. LISTING 1: BOL BOL Billed 196618 196650 196625 196650 196663 196663 196669 196686 196686 196694 196694 196699 196699 196711 LISTING 2: BOL BOL Billed 196618 196625 196650 196650 196663 196663 196669 196686 196686 196694 196694 196699 196699 196711 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| sort & match | Excel Worksheet Functions | |||
| Sort by Match | Excel Programming | |||
| Match and sort | Excel Worksheet Functions | |||
| Match and sort | Excel Programming | |||
| How to match and sort | Excel Discussion (Misc queries) | |||
Hybrid Mode
