I have one master excel sheet that contains different names of procedures. The observations are different instances in which each variable can occur.
The second sheet is one observation in which each variable has a value attached to them. Each one of these variables has a name that is similar to the procedures I mentioned above, though not exact.
I need a way to match each name from the second sheet to the first sheet, and then for it to be able to use the values that are associated with the name in the second sheet to be entered into its corresponding cell. demonstrated below. Sheet 1:
| Observation | xray | MRI |
|---|---|---|
| 1 | 100 | 250 |
| 2 | x | x |
| 3 | 200 | 300 |
Sheet 2: Observation 2:
| Procedure | Cost | NA |
|---|---|---|
| Xray3 | 175 | NaN |
| MRI Cardiac | 250 | NaN |
| Consent | 400 | NaN |
Output:
| Observation | xray | MRI | Consent |
|---|---|---|---|
| 1 | 100 | 250 | NaN |
| 2 | 175 | 250 | 400 |
| 3 | 200 | 300 | NaN |
So far I have looked into fuzzy matching but I am not sure how to go about this. Thanks in advance.