Last Occurance

[bill78759]

Have worksheet with dates across top and Names down the left side as a
attendance record. Need to examine each Name row to find that last time the
individual attended (attendance is indicated by x's in the row). The date
would be from the top row in the column corresponding to the x. I first
posted this in the New User group and have one response, but I need answer
asap. I know that most responders are volunteers and therefore answers are
given as time permits.

 

[Rick Rothstein]

You posted in a programming newsgroup, so here is a VB answer...

LastUsedColumnInRow2 = Worksheets("Sheet1").Cells(2,
Columns.Count).End(xlToLeft).Column

To do other rows, just change the 2 in the Cells property call to whatever
row number you want (I would presume that would be a For..Next index
counter. If, on the other hand, you were looking for a worksheet formula
solution, put this on Row 2 (assumed to be the first student's row) in
whatever column you will track the last attendance date in and copy it
down...

=INDEX(B$1:J$1,1,SUMPRODUCT(MAX((B2:J2<>"")*COLUMN(B2:J2)))-1)

Adjust the top end of the ranges (all the J's to whatever column letter(s)
your last possible date can be in). Note the that final -1 is needed because
the dates are assumed to start in Column B. If the dates actually start in
Column C, then change the -1 to -2.

 

[Hong Quach]

Hi Bill78759,

Let assume column A contain the list of names, the first contain the list of
dates, and column (date sorted ascendant from left to right).

Let assume Cells A2 to A6 contain the list of names. Cell B1, C1, D1, E1,
and F1 contain the list of date (ascending order)

In cell G1 Enter “Last Attendedâ€

The formula to get the last day to be enter in G2 to G6 for each row is as
follow (Change B2:F2 to B3:F3 for 2nd name and so on):

=INDIRECT("R1C"&MATCH("?",B2:F2,-1)+1,FALSE)

You can lookup how indirect() and match() function work. The +1 after
match() is to offset the 1st column to use for name.

I tested the code, and it works!

Hong Quach

 

[Rick Rothstein]

I gave you a "general" solution (looking for *any* text in the row), but you
said you were marking the attendance with an "X"... so here is a simpler
formula that keys off of that fact (for Row 2 in this example formula)...

=INDEX(B$1:J$1,1,MATCH("X",B2:J2,0))

Again, change the ranges to suit your set up and copy down.

 

[Rick Rothstein]

INDIRECT is a Volatile function and should be avoided where possible... in
this case, the non-Volatile INDEX function can be used instead. See my
second posting in this thread to see a formula solution using the INDEX
function.

 

[smartin]

Rick, Your first solution

=INDEX(B$1:J$1,1,SUMPRODUCT(MAX((B2:J2<>"")*COLUMN(B2:J2)))-1)

was better. The solution below hits the first occurrence of text in row
2, not the last as the OP requested.

 

[bill78759]

Thanks Rick. This works but does not give me the LAST occurrence. It gives me
the first occurrence. I am not sure how to use the first example you gave
but I do have another work sheet that has numbers instead of x. Of course I
get an error when there is no x, but I can solve that - the list is not that
I can't just replace the error message with blank. The actual cells involved
are Dates E1:AM1, First column is E2. and goes to E346.

 

[Rick Rothstein]

Hmm! You are right... what was I thinking (that's what I get for making a
last minute modification to a posting). Thanks for catching that.

 

[Rick Rothstein]

Yes, I made a mistake on that one. Here is the first formula modified for
the range you posted (always a good idea to give all your information in
your initial question)...

=IF(COUNTA(E2:AM2)=0,"",INDEX(E$1:AM$1,1,SUMPRODUCT(MAX((E2:AM2<>"")*COLUMN(E2:AM2)))-4))

Put this on Row 2 in the column you want the last date shown in and copy it
down. Note that I also added a section to suppress the error message if
there is no entries for the row in Columns E thru AM.

 

[Rick Rothstein]

It is still a good idea to avoid Volatile functions, but the example I
referred you to does not work correctly (see my latest answer to bill78759
for one that does).

 

[bill78759]

Thanks Rick. I could not get the formula to work. I am sure it was my error,
you did not have the layout of the sheet to work with and my attempt to make
adjustments were not successful. I did get a formula from T. Valko from my
post in new to Excel (I think that was the group name). I had to make some
minor changes (that I picked up from your formula). His was specific to
having "x" indicate attendance. I was under the impression that your formula
would work regardless what was used to record attendance. I have another
worksheet that uses numbers to indicate attendance (indicates the number in a
family attending). I have not gotten that to work yet. The file has the Dates
in Row 1 starting in Column AO and ending in AS. The results will be in
column AW. The names start in row 3. The numbers range from 1 to 5. I tried
both formulas you provided making adjustments for where the dates started,
etc. without success. Thanks for your help. I am doing this project for my
Church and think I have gotten in over my head. I was confident that I could
get help in the User Groups as I have in the past. I have used computers for
over 30 years as a user but not as a programmer so this is new to me. Also my
experience with spreadsheets was with Lotus. Not the same as Excel.

Bill

 

[Rick Rothstein]

If you want to take this off-line and send your workbook directly to me,
I'll see if I can find out why it isn't working. If you want to do that,
just remove the NO.SPAM stuff from my email address.

 

[bill78759]

Thanks - sent offline.

If you want to take this off-line and send your workbook directly to me,
I'll see if I can find out why it isn't working. If you want to do that,
just remove the NO.SPAM stuff from my email address.

 

[bill78759]

Thanks Rick. Rick fixed the problem when he got the data.

Bill