Formula/Macro for Data Set

[daviieejay]

This may seem confusing, but this is what I am trying to do.

So I have a set of data and I either want to automate it (not sure how that will work out) or develop a formula that can provide an output satisfies a few conditions.

In each row, for example, if people do not attend, they get a zero. If they attend, they get a score. The first table shows the data, the second table shows my desired output.

Below lists the criteria and some examples (see attached for a visual):

A score of 7 or higher, I want a 1 to be populated for all times they show up until they actually get their first score over 7 and then it stops. You can see in Susan’s situation, she did not start going until March and reached a score of 7 or over in April, so a 1 populates in March and April and stops at the first instance of 7 or over.

Another scenario is where they do not get a score of 7 or over at all. In Frank’s case, you’ll see he does not show up until April, where he only scores a 3. In the output, I only want a zero to populate in those two months.

The scenario can occur when they attend (i.e. Martha) but does not score 7 or higher for another few months. In her case, she does not get that score until March, so I would like January, February and March.

The last scenario is where they do not show up at all. In that case, nothing populates.

Any help/creative ideas/acts of God are welcome! Thanks everyone for your time!

[FDibbins]

Please do not upload a picture of your file...rather, upload a sample of your workbook, showing what data you are working with, a few samples of your expected outcome is (manually entered is ok) and how you arrived at that. (exclude sensitive info). Pictures are pretty much impossible to edit, and no-one wants to re-type your data for you Also, not all members can upload picture files (Company firewalls and stuff)

[daviieejay]

There we go! Here's a worksheet, this better shows the layout of how I have it set up.

[millz]

Try this macro, run it on the sample workbook. If it works fine, change colStartand colCount accordingly to suit for bigger data set (if any).

Sub EF971859_Score()
    Dim colStart, colCount, r, i, k As Long
    
    colStart = 2 'change to whichever column data starts, A = 1, B = 2, etc. in this case column B, so 2
    colCount = 5 'change to how many months (columns) of data
    
    r = Range("A" & rows.Count).End(xlUp).row
    For i = 2 To r
        For k = 0 To colCount - 1
            If Cells(i, colStart + k).Value > 0 Then Cells(i, colStart + k + colCount).Value = 0
            If Cells(i, colStart + k).Value >= 7 Then
                Cells(i, colStart + colCount).Resize(1, colCount).Replace 0, 1
                Exit For
            End If
        Next
    Next
End Sub

[daviieejay]

It looks great; looks like a charm! Thank you very much!