Filling blank cells from another record only if a unique identifier matches

Hello!

Here is my problem. I merged two data sets that were recorded separately using the same patients. I took the records unique to each dataset and merged them together to create one cohesive dataset. I now have patient records with blanks that need to be be amended with values directly above or below but must be from the correct patient. I tried to use the select blanks and fill trick, but because it sometimes pulls a record from a different patient, it doesn't work.

I have attached a small workbook displaying the problem. The cell in red is in the column where I tried the select blanks trick. As you can see, it doesn't work here; it draws from the patient above, but since it isn't the same patient, the data is incorrect.

The PID_VAL is unique to each person and if possible want to use that to pull the correct data. I also want to use this method for other columns, like relationships, where blanks are present.

Here is an example of the problem(s)
Example.CopyDuplicatesbasedonID.xlsx

**I also want to figure out how to deal with patients that have separate records but have different values for the same column. I highlighted the cells in orange. Is there any quick formula I can make in a helper column that will flag these patients?

Let me know if there are any more specifics required.

Thanks a bunch! his forum has been a tremendous help thus far!

Hi

Sort your data by ID, then by Insurance.

Find the first blank insurance cell (G11) and enter the formula

Please Login or Register  to view this content.

. Note this will have to be adjusted depending on the location of the first blank insurance item. Copy and paste into all the blank cells.

Now select J2 and enter the formula

Please Login or Register  to view this content.

Copy down to J35

HTH

rylo

Well that's just dandy!

I couldn't get your filtering to work, but I understood that formula as I was trying to use that myself. I had to filter by insurance first and then by ID. That way the blank insurances would be on top and then I used =IF(A9=A10,G9,"").

That second equation is awesome! I can use that for other filtering that I have to do now!