Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.newusers
|
|||
|
|||
Find Last Occurence
Worksheet with individuals down and dates across. Attendance is marked with
"x". Need to find last time individual was at work (last x) and return date in first column. |
#2
Posted to microsoft.public.excel.newusers
|
|||
|
|||
Find Last Occurence
=LOOKUP(2,1/(2:2="x"),1:1)
"bill78759" wrote: Worksheet with individuals down and dates across. Attendance is marked with "x". Need to find last time individual was at work (last x) and return date in first column. |
#3
Posted to microsoft.public.excel.newusers
|
|||
|
|||
Find Last Occurence
Thanks for the reply. Being a new user of Excel I couldn't get the formula to
work. What would the formula look like if the date array goes from AC7:BK7 the first row is Row 9 (ie: AC9:BK9) and the result would be displayed in BK8. I need the date of their attendance (corresponding to the last x in row). There are several x's scattered in the row. I plan to copy the formula down to get an answer for each student. I tried the formula wizard and it gave a different formula that returned a date but it was not the last attendance. "Teethless mama" wrote: =LOOKUP(2,1/(2:2="x"),1:1) "bill78759" wrote: Worksheet with individuals down and dates across. Attendance is marked with "x". Need to find last time individual was at work (last x) and return date in first column. |
#4
Posted to microsoft.public.excel.newusers
|
|||
|
|||
Find Last Occurence
Opps - Result would be displayed in BL9
"bill78759" wrote: Thanks for the reply. Being a new user of Excel I couldn't get the formula to work. What would the formula look like if the date array goes from AC7:BK7 the first row is Row 9 (ie: AC9:BK9) and the result would be displayed in BK8. I need the date of their attendance (corresponding to the last x in row). There are several x's scattered in the row. I plan to copy the formula down to get an answer for each student. I tried the formula wizard and it gave a different formula that returned a date but it was not the last attendance. "Teethless mama" wrote: =LOOKUP(2,1/(2:2="x"),1:1) "bill78759" wrote: Worksheet with individuals down and dates across. Attendance is marked with "x". Need to find last time individual was at work (last x) and return date in first column. |
#5
Posted to microsoft.public.excel.newusers
|
|||
|
|||
Find Last Occurence
Assuming the range contains only the TEXT entry X, otherwise the cells are
empty. Try this: =IF(COUNTIF(AC9:BK9,"x"),INDEX(AC7:BK7,MATCH("zzzz z",AC9:BK9)),"") Format as Date -- Biff Microsoft Excel MVP "bill78759" wrote in message ... Opps - Result would be displayed in BL9 "bill78759" wrote: Thanks for the reply. Being a new user of Excel I couldn't get the formula to work. What would the formula look like if the date array goes from AC7:BK7 the first row is Row 9 (ie: AC9:BK9) and the result would be displayed in BK8. I need the date of their attendance (corresponding to the last x in row). There are several x's scattered in the row. I plan to copy the formula down to get an answer for each student. I tried the formula wizard and it gave a different formula that returned a date but it was not the last attendance. "Teethless mama" wrote: =LOOKUP(2,1/(2:2="x"),1:1) "bill78759" wrote: Worksheet with individuals down and dates across. Attendance is marked with "x". Need to find last time individual was at work (last x) and return date in first column. |
#6
Posted to microsoft.public.excel.newusers
|
|||
|
|||
Find Last Occurence
Biff,
Thanks, I was able to get the equation to work. I did have to put a $ sign to lock the range of the dates. I appreciate your help. Bill PS- I did press the yes that it was helpful. "T. Valko" wrote: Assuming the range contains only the TEXT entry X, otherwise the cells are empty. Try this: =IF(COUNTIF(AC9:BK9,"x"),INDEX(AC7:BK7,MATCH("zzzz z",AC9:BK9)),"") Format as Date -- Biff Microsoft Excel MVP "bill78759" wrote in message ... Opps - Result would be displayed in BL9 "bill78759" wrote: Thanks for the reply. Being a new user of Excel I couldn't get the formula to work. What would the formula look like if the date array goes from AC7:BK7 the first row is Row 9 (ie: AC9:BK9) and the result would be displayed in BK8. I need the date of their attendance (corresponding to the last x in row). There are several x's scattered in the row. I plan to copy the formula down to get an answer for each student. I tried the formula wizard and it gave a different formula that returned a date but it was not the last attendance. "Teethless mama" wrote: =LOOKUP(2,1/(2:2="x"),1:1) "bill78759" wrote: Worksheet with individuals down and dates across. Attendance is marked with "x". Need to find last time individual was at work (last x) and return date in first column. |
#7
Posted to microsoft.public.excel.newusers
|
|||
|
|||
Find Last Occurence
You're welcome. Thanks for the feedback!
-- Biff Microsoft Excel MVP "bill78759" wrote in message ... Biff, Thanks, I was able to get the equation to work. I did have to put a $ sign to lock the range of the dates. I appreciate your help. Bill PS- I did press the yes that it was helpful. "T. Valko" wrote: Assuming the range contains only the TEXT entry X, otherwise the cells are empty. Try this: =IF(COUNTIF(AC9:BK9,"x"),INDEX(AC7:BK7,MATCH("zzzz z",AC9:BK9)),"") Format as Date -- Biff Microsoft Excel MVP "bill78759" wrote in message ... Opps - Result would be displayed in BL9 "bill78759" wrote: Thanks for the reply. Being a new user of Excel I couldn't get the formula to work. What would the formula look like if the date array goes from AC7:BK7 the first row is Row 9 (ie: AC9:BK9) and the result would be displayed in BK8. I need the date of their attendance (corresponding to the last x in row). There are several x's scattered in the row. I plan to copy the formula down to get an answer for each student. I tried the formula wizard and it gave a different formula that returned a date but it was not the last attendance. "Teethless mama" wrote: =LOOKUP(2,1/(2:2="x"),1:1) "bill78759" wrote: Worksheet with individuals down and dates across. Attendance is marked with "x". Need to find last time individual was at work (last x) and return date in first column. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
find 2nd occurence | Excel Discussion (Misc queries) | |||
Find the row number of name occurence | Excel Worksheet Functions | |||
Find Last Occurence in a Range with VBA | Excel Discussion (Misc queries) | |||
Trying to FIND lowercase or uppercase of target occurence | Excel Worksheet Functions | |||
Find first occurence in a list that's greater than a specific num | Excel Worksheet Functions |