Dear You who are reading this,
I'm a medical student and I'm not really good with Excel. For my thesis, I have a big database and now I reached a problem where there could be an easy way but on my own, I can only copy&paste some thousands of rows of data.
I have a database that is a list of selected patients with all their ED visits. Every single diagnosis (text format and BNO code) is written in a new row in Excel. Every patient has one row (at least) where they have a COVID-19 related BNO (three different letter&number value covers this, right now it's formatted so for manual copy&paste, they are easy to spot).
My job now is to get the row of their COVID-19 related visit into a new Excel table - but to the same row. I need to put all the other diagnoses that they might have had during the same visit (on the 1st picture, this is the red dotted area). They may have no additional rows with diagnoses or they may have 2-5 more rows even (hence the free space on the 2nd picture, some patients has that area filled up).
Then still in the same row in the new table, comes the part of their 2nd visit (the green dotted area on the 1st picture). This can be one diagnosis aka one row or once again, multiple ones from the first table that needs to be copied.
The 2nd picture shows what the singular row would look like in the new Excel sheet. (The blurred area is the patient's name (1st column in both tables, the 2nd table only contains them once obviously) or their unique identifying numbers).
Some patients have e.g. 20 rows in the first table, and out of these I only need 1.) the rows related to the COVID-19 BNO (which is on the same date - date is in the 4th column of the copied area in the 1st picture) then 2.) their next visit (so all diagnoses that they have with the next possible date. Some patients have 2 different dates with COVID-19 BNO, in this case, I need the one where there is another, non-COVID-19 BNO ED visit date following the COVID-19 related visit (if there are multiple like this, then the first one).
The example pictures:
(as I'm a new user, I can only upload one picture, the upper part would be the first and the down part the 2nd one.)
My counsellor advised me to ask this community, I hope my question is written in the correct way. I'm new here, I'm sorry if I wrote something badly. I can do this manually but if there could be an easier way then I'd like to try, thank you to anyone who spares a thought on this matter!