match-substitute

dear all,

i have some data in xls -
col A - patient hospital no (correct entry)
col B - corresponding patient age (correct entry)
col C - patient hospital no (correct entry)
col D - corresponding patient age (may be correct or incorrect entry)

Task: pl provide formula in col E so that:
- match hospital no. between col A & col C - ie., cell in col A is matched with cell in col C
- then match (corresponding) age in col B with age in col D for matched hosp no
- if for similar hospital no., if age does not match (incorrect entry), the correct age (from col B) is entered into col D - substituting the existing incorrect age

hope that is clear enough. pl see attached example for your understanding.

thanks for help.

Formula can return a value in the same cell where it is entered. Meaning, you cannot replace value in column D by a formula entered in column E. Are you fine using a macro?

oeldere..

WHERE MATCH IS FOUND, IF CORRESPONDING VALUE IN COL B DOES NOT MATCH VALUE IN COL D, SUBSTITUTE VALUE IN COL D WITH VALUE IN COL B.
FOR EG., A3 MATCHES WITH C2, BUT D2 IS NOT EQUAL TO B3, THEREFORE D2 IS CHANGED TO B3 I.E., = 23

Above formula is entered in col E and it wouldn't change value in col D.

Originally Posted by haripopuri

Formula can return a value in the same cell where it is entered. Meaning, you cannot replace value in column D by a formula entered in column E. Are you fine using a macro?

ok. pl send macro.

Thanks for your help.

Not perfect but this is what I could come up with quickly.

Please Login or Register  to view this content.

Not perfect but this is what I could come up with quickly.

Please Login or Register  to view this content.