Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Match Data on different lines
Hi.
I have a document with bills imported from two different databases. I'm trying to compare the value from for example C2 with H2 and if it isn't a match i need to compare C2 with H3, H4 etc, until it fines a match (the problem is that the data from one database misses about 500 of 2000 bills). Is this possible to do with a macro or advanced filter? Henrik |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Match Data on different lines
Henrik,
This formula =MATCH(C2,H:H,False) will return the row number of the first match, or an Error if there is no match. You can then use that number to return another value, like this, from, say, column I: =INDEX(I:I,MATCH(C2,H:H,False)) You can also trap the error to tidy things up: =IF(ISERROR(MATCH(C2,H:H,False)),"No match found",MATCH(C2,H:H,False)) HTH, Bernie MS Excel MVP "Henrik T" <Henrik wrote in message ... Hi. I have a document with bills imported from two different databases. I'm trying to compare the value from for example C2 with H2 and if it isn't a match i need to compare C2 with H3, H4 etc, until it fines a match (the problem is that the data from one database misses about 500 of 2000 bills). Is this possible to do with a macro or advanced filter? Henrik |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Match Data on different lines
Do the two imported files have a matching field - e.g. invoice number,
account number, etc.? If they don't, this will be entirely manual, since we don't know why H2 would match C2 etc. Assuming they have a matching field(s), you could: =IF(ISNA(Vlookup(Value To Match, Range of data on second sheet with matching column the first column, X (replace X with the column that has the $ value for the row we match on), False)), "No Match", IF (Vlookup(Value To Match, Range of data on second sheet with matching column the first column, X (replace X with the column that has the $ value for the row we match on), False) = Cell on First sheet that has $ value to compare with, "Data Values Equal", "Data Values Not Equal")) Basically, you will first see if we have a match on the key field - if we don't, the ISNA test will return the "No Match" message. If we do match on the key field, then it will compare the $ values and either return "Data Values Equal" or "Data Values Not Equal". You could, of course, get even fancier and replace the "Not equal" text with the variance of the two values. "Henrik T" wrote: Hi. I have a document with bills imported from two different databases. I'm trying to compare the value from for example C2 with H2 and if it isn't a match i need to compare C2 with H3, H4 etc, until it fines a match (the problem is that the data from one database misses about 500 of 2000 bills). Is this possible to do with a macro or advanced filter? Henrik |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Selecting most recent data within several lines of data | Excel Discussion (Misc queries) | |||
24 lines between data | Excel Discussion (Misc queries) | |||
Inserting Lines or Copying lines with formulas but without data | Excel Discussion (Misc queries) | |||
index,match,match on un-sorted data | Excel Worksheet Functions | |||
The match and lookup functions can find literal data but not the same data referenced from a cell | Excel Discussion (Misc queries) |