Fill cells with discharge dates

[stephenedwardbennett]

Hi,

Hope you can help.

I work as an analyst in a hospital.

I have 3 columns in my report that are important to my work:
Column 1 - Patient number Column 2 - Admission date Column 3 - Discharge date

I can pull off a separate report from the system which has details of discharged patients:
Column 1 - Discharge date Column 2 - Hospital number

I need a formula to quickly fill Column 3 with discharge dates for patients who have discharged and have it not affect any other cells.

[mehmetcik]

Attach a sample workbook. Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are shown, mock them up manually if necessary.

Remember to desensitize the data.

Click on GO ADVANCED and then scroll down to Manage Attachments to open the upload window.

[stephenedwardbennett]

Hopefully it has attached.

I was hoping there would be a formula I can use and then record as a macro to end up with the "after" result.

[stephenedwardbennett]

Anyone able to help?

[bebo021999]

Try in cell F6 sheet After:

=IFERROR(INDEX(Discharges!A:A,MATCH(D6,Discharges!B:B,0)),"")

Drag down

[Roland84]

Hi,

In the second report you run, are you able to run the admission date as well? If so, you should be able to create your own visit ID, by using:
=CONCATENATE(Hospital number,Admission Date). If you do the same for the Second report, you can then do a VLOOKUP formula, which will compare the 2 unique IDs and find the Discharge date. I have amended the file you attached, and if you can run the report this way it should work.

I have also assumed your COUNTA formula in column G for Discharge should only count those who HAVE been discharged, I have amended this as it will count the formulas as being not blank as well.

Two other points, 1) are these real hospital numbers? If so these should not be put on a public website, in future make sure they are obviously fake e.g. F111111, F111112 etc. 2) I assume you use both A and F Hospital numbers, and the others start with a 0. If you change the column formatting to text before you copy these over they will retain the 0 at the start.

[stephenedwardbennett]

Mega.

Thank you very much bebo

[Roland84]

Try in cell F6 sheet After:

=IFERROR(INDEX(Discharges!A:A,MATCH(D6,Discharges!B:B,0)),"")

Drag down

If patients have more than one admission in the time period, this will not differentiate them.