VBA Data sorting problem

[Beowulf]

If you could please help me with this data sorting problem.

I am a doctor who does research in haematological malignancies. My research database is a huge Excel workbook. It expanded so much that the data had to be in different sheets. When putting the data into the sheets I did something very stupid which I try to correct now (unsuccessfully).

My original mistake was to use different sheets for different kinds of events, instead of just adding events by the date to the next sheet. It is now very difficult to follow the history of one case, you have to jump back and forward between the sheets to do so. Every sheet has data for one kind of event for all cases. The rows in the sheets contain the case #, the date when the event happened and 135 variable columns . There are 375 rows in each sheet.

So now I have 9 sheets (Primary Event, Serious Event 1, Serious Event 2, Serious Event 3, Serious Event 4, Mild Event 1, Mild Event 2, Mild Event 3, Mild Event 4). I would like to change the sheets to be based on the dates when the events took place:

Event-1, Event-2, Primary Event, Event+2, Event+3, Event+4, Event+5, Event+6, Event+7

To illustrate the problem, let's say there are three cases, three variables and four sheets only – if you could solve this it would be applicaple to 375 cases, 137 variables and 9 sheets as well. Here’s how it looks like now:

Sheets(”Primary Event”)
Case   Date          Health  Kind of event
#1     25 May 1998   1       Primary
#2     1 June 2005   5       Primary
#3     12 Aug 2001   2       Primary

Sheets(”Serious Event 1”)
Case   Date          Health  Kind of event
#1     25 Dec 1995   2       Serious
#2     4 Oct 2005    3       Serious
#3     11 Sept 2003  4       Serious

Sheets(”Serious Event 2”)
Case   Date          Health  Kind of event
#1     25 Dec 1999   2       Serious
#2                           
#3

Sheets(”Mild Event 1”)
Case   Date         Health   Kind of event
#1     2 Nov 1998    5       Mild
#2     21 Nov 2005   5       Mild
#3

Note that Case #2 and #3 didn’t have a second Serious event and Case #3 did not have a mild event 1. Also note that Case #1 had a “Serious Event 1” prior to the Primary one.

I want to have my database to copy and sort the entire rows based on the date, and then paste the row to the relevant sheet. It would to look like this:

Sheets(“Event-1”)
Case   Date         Health   Kind of event
#1     25 Dec 1995   2       Serious
#2
#3

Sheets(“Primary Event”)(it will not change)
Case   Date         Health   Kind of event
#1     25 May 1998   1       Primary
#2     1 June 2005   5       Primary
#3     12 Aug 2001   2       Primary

Sheets(“Event+1”)
Case   Date         Health   Kind of event
#1     2 Nov 1998    5       Mild
#2     4 Oct 2005    3       Serious
#3     11 Sept 2003  4       Serious

Sheets(“Event+2”)
Case   Date         Health   Kind of event
#1     25 Dec 1999   2       Serious
#2     21 Nov 2005   5       Mild
#3

I have tried to achieve this with If...Then directives in VB, but they become extremely nested and I frankly get lost in the code. Is there a way to make a simpler VBA-code to copy these rows into the new sheets based on the event dates?

I would be extremely grateful for help,

Björn Wahlin MD

 

[mezr]

Hello Beowulf:

It's a little hard for me to completely understand what you are trying to achieve with the organization of this workbook. However, it seems to me that you may be able to accomplish what you're after by using only one sheet.

Perhaps you could include another column of data that lists the "type of event", and then use the "Autofilter" feature. The autofilter would allow you to view all data on a single case, only certain types of events, only events that fell within certain date ranges ... and on and on and on. You can have nearly unlimited options by filtering one or multiple columns of data.

To access this feature, highlight the column headings and at least one row of data beneath them. The on the menu bar select "Data > Filter > Autofilter". You will notice the small buttons that show up in each column heading. When you press these, you will be able to select what you would like to view.

I know that this is a redirection from your original thought about how to organize the workbook. But I think that this approach would be well worth you exploring to see if it isn't a better option that will allow for more flexibility in how you view the data as time goes on.

I hope this helps you. If not, please post back.

 

[pfarmer]

If you could please help me with this data sorting problem.

I am a doctor who does research in haematological malignancies. My research database is a huge Excel workbook. It expanded so much that the data had to be in different sheets. When putting the data into the sheets I did something very stupid which I try to correct now (unsuccessfully).

My original mistake was to use different sheets for different kinds of events, instead of just adding events by the date to the next sheet. It is now very difficult to follow the history of one case, you have to jump back and forward between the sheets to do so. Every sheet has data for one kind of event for all cases. The rows in the sheets contain the case #, the date when the event happened and 135 variable columns . There are 375 rows in each sheet.

So now I have 9 sheets (Primary Event, Serious Event 1, Serious Event 2, Serious Event 3, Serious Event 4, Mild Event 1, Mild Event 2, Mild Event 3, Mild Event 4). I would like to change the sheets to be based on the dates when the events took place:

Event-1, Event-2, Primary Event, Event+2, Event+3, Event+4, Event+5, Event+6, Event+7

To illustrate the problem, let's say there are three cases, three variables and four sheets only – if you could solve this it would be applicaple to 375 cases, 137 variables and 9 sheets as well. Here’s how it looks like now:

Sheets(”Primary Event”)
Case   Date          Health  Kind of event
#1     25 May 1998   1       Primary
#2     1 June 2005   5       Primary
#3     12 Aug 2001   2       Primary

Sheets(”Serious Event 1”)
Case   Date          Health  Kind of event
#1     25 Dec 1995   2       Serious
#2     4 Oct 2005    3       Serious
#3     11 Sept 2003  4       Serious

Sheets(”Serious Event 2”)
Case   Date          Health  Kind of event
#1     25 Dec 1999   2       Serious
#2                           
#3

Sheets(”Mild Event 1”)
Case   Date         Health   Kind of event
#1     2 Nov 1998    5       Mild
#2     21 Nov 2005   5       Mild
#3

Note that Case #2 and #3 didn’t have a second Serious event and Case #3 did not have a mild event 1. Also note that Case #1 had a “Serious Event 1” prior to the Primary one.

I want to have my database to copy and sort the entire rows based on the date, and then paste the row to the relevant sheet. It would to look like this:

Sheets(“Event-1”)
Case   Date         Health   Kind of event
#1     25 Dec 1995   2       Serious
#2
#3

Sheets(“Primary Event”)(it will not change)
Case   Date         Health   Kind of event
#1     25 May 1998   1       Primary
#2     1 June 2005   5       Primary
#3     12 Aug 2001   2       Primary

Sheets(“Event+1”)
Case   Date         Health   Kind of event
#1     2 Nov 1998    5       Mild
#2     4 Oct 2005    3       Serious
#3     11 Sept 2003  4       Serious

Sheets(“Event+2”)
Case   Date         Health   Kind of event
#1     25 Dec 1999   2       Serious
#2     21 Nov 2005   5       Mild
#3

I have tried to achieve this with If...Then directives in VB, but they become extremely nested and I frankly get lost in the code. Is there a way to make a simpler VBA-code to copy these rows into the new sheets based on the event dates?

I would be extremely grateful for help,

Björn Wahlin MD

Maybe you want to consider something along the lines of this:

http://www.mrexcel.com/board2/viewtopic.php?t=168414&postdays=0&postorder=asc&start=10

Look for my name there and give it a try.

In my example there are 248 tabs with a variable number of rows on each tab. This combines them all into one master sheet, which you then can sort based on whatever column(s) you want. Each tab is a Company.

In my example I have hard coded a column called Contractor at column 6.

What I do with this column is create a list of Contractor Companies.

Now what I was thinking is that you could do the same. Each tab equals some sort of event, this would allow you to create a list of all the events.

Now you could define a name for this list and use that in a set of drop down boxes to list all patients with that kind of event. Or you could do the reverse and define a name for all patients. You then use this list to show all events for that patient.

If you have problems following this post back.

Perry

 

[Beowulf]

Dear mezr and Perry!

Many thanks for your replies. There are too many columns to put things into one sheet.

Basically, what I want is to rearrange my entire database from the present state, where each sheet contains a certain type of event, into time-line based sheets. That is, for each case (probably by VBA) have the date cell in each sheet analysed, and then have VBA realise the order of the given dates, then copy the case-rows to to new sheets based on the order of the dates.

The tricky part is the "have VBA realise the order of the dates", since I'm so lousy at programming I end up with extremely complex If...then directives.

The core of the problem for me is to have VBA comparing the given cell dates for every case without resorting to a ridiculous amount of if...then directives. It's easy enough to get the values and validity of the cell dates:

Public Sub hamtadat()
'STARAD = is the starting row (=case)
'RADNU = is calculated by a for next loop to the next case
'DATUMKOL = is the column where the date cell is (same in all sheets)

'DIA = diagnosis sheet

'REC1 = mild event 1 sheet
'REC2 = mild event 2 sheet... and so on

'TRA1 = serious event 1 sheet... and so on

'DIADATUM = date of diagnosis
'REC1DATUM = date of mild event 1... and so on

'REC1DATOK = 1 means that sheet REC1 contains a valid date for the 'specifik case.
'... and so on

DIADATUM = DIA.Cells(STARAD + RADNU, DATUMKOL)

If IsDate(REC1.Cells(STARAD + RADNU, DATUMKOL)) Then
REC1DATUM = REC1.Cells(STARAD + RADNU, DATUMKOL)
REC1DATOK = 1
End If

If IsDate(REC2.Cells(STARAD + RADNU, DATUMKOL)) Then
REC2DATUM = REC2.Cells(STARAD + RADNU, DATUMKOL)
REC2DATOK = 1
End If

If IsDate(REC3.Cells(STARAD + RADNU, DATUMKOL)) Then
REC3DATUM = REC3.Cells(STARAD + RADNU, DATUMKOL)
REC3DATOK = 1
End If

If IsDate(REC4.Cells(STARAD + RADNU, DATUMKOL)) Then
REC4DATUM = REC4.Cells(STARAD + RADNU, DATUMKOL)
REC4DATOK = 1
End If

If IsDate(TRA1.Cells(STARAD + RADNU, DATUMKOL)) Then
TRA1DATUM = TRA1.Cells(STARAD + RADNU, DATUMKOL)
TRA1DATOK = 1
End If

If IsDate(TRA2.Cells(STARAD + RADNU, DATUMKOL)) Then
TRA2DATUM = TRA2.Cells(STARAD + RADNU, DATUMKOL)
TRA2DATOK = 1
End If

If IsDate(TRA3.Cells(STARAD + RADNU, DATUMKOL)) Then
TRA3DATUM = TRA3.Cells(STARAD + RADNU, DATUMKOL)
TRA3DATOK = 1
End If

If IsDate(TRA4.Cells(STARAD + RADNU, DATUMKOL)) Then
TRA4DATUM = TRA4.Cells(STARAD + RADNU, DATUMKOL)
TRA4DATOK = 1
End If
arrange
End Sub

By this simple code I will get the dates (if they are valid) of the case investigated. The problems come with the "arrange" subroutine at the end. Here's where my problems are. The comparison is an ant hill of if...thens. Is there a way to "simultaneously" compare, or order, the dates of the case? A way to make VBA realise the order with a glance on all the maximum 9 date values of each particular case.

Thank you again for your time

Björn

 

[mezr]

Hello again, Björn:

The rows in the sheets contain the case #, the date when the event happened and 135 variable columns . There are 375 rows in each sheet.

I don't understand. There are 256 columns in Excel. Based on your above statement it apears as though you are working with 137 columns, and therefore should not have a problem working with this data on one sheet. Are the column headings the same on each sheet?

Also, if you really do need to have multiple sheets, can you not just use the "Sort" feature to organize your data and then manually copy and paste into new sheets? Sometimes manual solutions actually get the job done faster than trying to perfect what may be a complicated code solution.

 

[Beowulf]

don't understand. There are 256 columns in Excel. Based on your above statement it apears as though you are working with 137 columns

No, there are 9 * 137 columns in the workbook. I will look into the the data array business and see if I can learn something from that. If I can't find the solution (which I know must exist), I will have to it manually, which would take a lot of time and not as reliable as an automatic method.

 

[pfarmer]

don't understand. There are 256 columns in Excel. Based on your above statement it apears as though you are working with 137 columns

No, there are 9 * 137 columns in the workbook. I will look into the the data array business and see if I can learn something from that. If I can't find the solution (which I know must exist), I will have to it manually, which would take a lot of time and not as reliable as an automatic method.

The way I first read your post is that you have 9 sheets with 137 columns each. The code I showed would take all those sheets and create a master sheet with 137 columns with as many occupied rows as all of the 9 sheets have combined. That is assuming every sheet has the same heading.

Once you create that master sheet then you can do whatever you desire with it.

In my example I take a workbook that someone keeps their information in concerning contractors. I installed the macro into that workbook to create the master sheet. I then use another workbook to poll that master sheet over a network (I only have read only access to the directory it sits in). From that workbook I grab the contractor companies, their employees and various dates associated with each employee. I could however also grab that information and then send it to other sheets in a workbook based on various criteria.

So are the 137 columns the same for all 9 sheets?

Perry

 

[pfarmer]

Hello again, Björn:

The rows in the sheets contain the case #, the date when the event happened and 135 variable columns . There are 375 rows in each sheet.

I don't understand. There are 256 columns in Excel. Based on your above statement it apears as though you are working with 137 columns, and therefore should not have a problem working with this data on one sheet. Are the column headings the same on each sheet?

Also, if you really do need to have multiple sheets, can you not just use the "Sort" feature to organize your data and then manually copy and paste into new sheets? Sometimes manual solutions actually get the job done faster than trying to perfect what may be a complicated code solution.

Since he is dealing with dates he could possibly, at least initially name the sheets by dates (months, etc.). Then you simply go down the list on the master sheet and pick up the months and transfer everything that matches to the tab and then do a sort on the tab.

I one of my projects I take in orders based on month, day of month, order of day, and year. There is a number assigned to these orders that pertain to this. For example 05-365-1 is an order that is for the year 2005, the 365th day of the year and order #1. Since I have a book for each month each has tabs that correspond to the day of the month, each have about 150 rows. The first available row is number 6. so therefore I take this number and assign the order to row number 5+order number on tab #31.

Works fine. I do this manually now. Once copied to the correct sheet I then put a 'o' into a column on the master sheet indicating which ones I have manually copied (could be automated fairly easily by looking for the 'o' as well). Once copied to the individual sheets various manual updates take place indicating the current status of the order, including modifications such as updated requester, dates, etc. This information is then copied back to the master sheet. in the appropriate columns. The master sheet data is then saved in a text file at various intervals. There is an option to manually refresh the master sheet from this file with logic built in to make sure that an update master sheet has been saved first.

I don't see where this really is that much different in concept from what I understand from the post, but maybe I am understanding it incorrectly.

Also I am not under a delusion that at least parts of this should go to database for storage purposes. I am not so sure the good doctor shouldn't be considering the idea of taking what he currently has and move it into a database application, especially since it sounds like it may grow.

Perry

 

[Fat Cat]

A bit long winded, but try the following;

(1) insert a blank sheet into your workbook and rename it as "temp"
(2) stick a CommandButton onto the "temp" sheet anywhere except range "A1:B9"

(3) copy and paste the code below to execute when the CommandButton is clicked.

This assumes that you would only ever have 1 date that is earlier than the Primary Event. It will fail if you have 2 dates for a Patient that are earlier than the Primary Event. On a semantic point, can you really call it a Primary Event if they have had an earlier Event ?

Private Sub CommandButton1_Click()

Dim firstRow As Long
Dim lastRow As Long

Dim shtIN(1 To 9) As String
Dim shtOUT(1 To 8) As String

Dim order(0 To 9) As Integer
Dim numDates As Integer
Dim tmp2 As Integer

shtOUT(1) = "Event-1"           'these are the names for the sorted sheets
shtOUT(2) = "Event+1"
shtOUT(3) = "Event+2"
shtOUT(4) = "Event+3"
shtOUT(5) = "Event+4"
shtOUT(6) = "Event+5"
shtOUT(7) = "Event+6"
shtOUT(8) = "Event+7"

'lets make some sheets for the sorted data
For i = 1 To 8
    Sheets.Add                              'make a new sheet
    ActiveSheet.Name = shtOUT(i)            'rename it while it has the focus
    ActiveSheet.Cells(1, 1) = "Case"        'add some headers across the top row
    ActiveSheet.Cells(1, 2) = "Date"
    ActiveSheet.Cells(1, 3) = "Health"
    ActiveSheet.Cells(1, 4) = "Kind of Event"
Next i

shtIN(1) = "Primary Event"           'these are the names of your existing sheets
shtIN(2) = "Serious Event 1"
shtIN(3) = "Serious Event 2"
shtIN(4) = "Serious Event 3"
shtIN(5) = "Serious Event 4"
shtIN(6) = "Mild Event 1"
shtIN(7) = "Mild Event 2"
shtIN(8) = "Mild Event 3"
shtIN(9) = "Mild Event 4"

firstRow = 2                        'change to row number containing "#1", I assume its the 2nd row here
lastRow = Sheets("Primary Event").Range("A65536").End(xlUp).Row     'find the last row with data in column A

For i = firstRow To lastRow      'copy over patient ID's to each of the new output sheets
    For k = 1 To 8
        Sheets(shtOUT(k)).Cells(i, 1).Value = Sheets("Primary Event").Cells(i, 1).Value
    Next k
    
    order(0) = 0
    numDates = 0
    Sheets("temp").Range("A1:B9").ClearContents
    
    For k = 1 To 9                                          'for each ID # find out how many dates we have
        If Sheets(shtIN(k)).Cells(i, 2) <> "" Then
            numDates = numDates + 1
            tmp1 = Sheets(shtIN(k)).Cells(i, 2)
            Sheets("temp").Cells(numDates, 1) = tmp1        'write dates to temp sheet
            Sheets("temp").Cells(numDates, 2) = k           'write sheet codes to temp sheet
        End If
    Next k
    
    'now sort the dates on the temp sheet
    
    Sheets("temp").Range("A1:B9").Sort Key1:=Range("A1"), Order1:=xlAscending, Header:=xlGuess, _
    OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
    DataOption1:=xlSortNormal
    
    'now read the sorted date order back in and copy data to the new sheets
    
    Select Case numDates
    Case 1              'do nothing we only have a Primary Event for this guy. Will appear as blank row in Event sheets
                        
    Case 2
        If Sheets("temp").Cells(1, 2) > 1 Then          'copy to Event-1 sheet
            tmp2 = Sheets("temp").Cells(1, 2).Value
            Sheets(shtOUT(1)).Cells(i, 2) = Format(Sheets(shtIN(tmp2)).Cells(i, 2), "dd mmm yyyy")
            For j = 3 To 137
                Sheets(shtOUT(1)).Cells(i, j) = Sheets(shtIN(tmp2)).Cells(i, j)
            Next j
        Else                                            'copy to Event+1 sheet
            tmp2 = Sheets("temp").Cells(2, 2).Value
            Sheets(shtOUT(2)).Cells(i, 2) = Format(Sheets(shtIN(tmp2)).Cells(i, 2), "dd mmm yyyy")
            For j = 3 To 137
                Sheets(shtOUT(2)).Cells(i, j) = Sheets(shtIN(tmp2)).Cells(i, j)
            Next j
        End If
        
    Case Is > 2         'have more than 2 events
    
        If Sheets("temp").Cells(1, 2) > 1 Then          'copy to Event-1 sheet
            tmp2 = Sheets("temp").Cells(1, 2).Value
            Sheets(shtOUT(1)).Cells(i, 2) = Format(Sheets(shtIN(tmp2)).Cells(i, 2), "dd mmm yyyy")
            For j = 3 To 137
                Sheets(shtOUT(1)).Cells(i, j) = Sheets(shtIN(tmp2)).Cells(i, j)
            Next j
            
            For k = 3 To numDates                       'copy rest to other sheets
                tmp2 = Sheets("temp").Cells(k, 2).Value
                Sheets(shtOUT(k - 1)).Cells(i, 2) = Format(Sheets(shtIN(tmp2)).Cells(i, 2), "dd mmm yyyy")
                For j = 3 To 137
                    Sheets(shtOUT(k - 1)).Cells(i, j) = Sheets(shtIN(tmp2)).Cells(i, j)
                Next j
            Next k
        Else                                            'copy rest to other sheets
            For k = 2 To numDates
                tmp2 = Sheets("temp").Cells(k, 2).Value
                Sheets(shtOUT(k)).Cells(i, 2) = Format(Sheets(shtIN(tmp2)).Cells(i, 2), "dd mmm yyyy")
                For j = 3 To 137
                    Sheets(shtOUT(k)).Cells(i, j) = Sheets(shtIN(tmp2)).Cells(i, j)
                Next j
            Next k
        End If
    End Select
Next i
    
End Sub

 

[Beowulf]

Dear friends,

Thank you all for your kind and very helpful replies. I finally did a "crude" solution using some new columns where the dates from the different sheets were nominally ordered. Using Excel's autofilter I could then copy rows by their nominal order number to new sheets that were named Event1, Event2 and so on. This solution only worked for a proportion of the cases, due to my rotten data structure, so after a while I had to do it entirely manually. It took 8 hours, and of course I managed to do make some wrong copies (I checked that afterwards by comparing, in every case, that the consecutive dates increased with the sheet number.)

I can't say how grateful I am to your kindness. And how impressed I am with your vast expertise.

I guess the moral of this story is I should learn Access for better handling of databases.

Thank you all

Björn Wahlin
MD