Get a value from an unknown number of cells awy

[jimrowland]

Every month, I import a fairly large csv file with records about a company that we provide ride services for, and the daily activity for that company. I need to get the day-of-the-week for each row. The "problem" is that each day's activity may have from 1 to x number of rows, and only the first row (for that day) has the date-string value. I have no problem converting the date-string into a day-of-the-week text value: I simply use

TEXT(B2,"dddd")

But I cannot "copy down" this formula, because of all of the blanks that occur in between each row with a date value.

Here is a screenshot of what I have, and what I am trying to do.

Each of the red-text weekday names were all done with the above formula. But all of the question marks highlighted need to also have the matching weekday name.

Option: Maybe I'm going about this all wrong. What I ULTIMATELY need is to know is: "how many total passengers did we move on Mondays"? (ALL Monday's, combined, for the whole month). Same for each day of the week. Right now, I manually copy/paste the weekday for all of the blank rows, and then I do the following:

=SUMIF('Raw Data'!$A:$A,"Monday",'Raw Data'!$H:$H)

There is a little bit of a glimmer of hope in that each day has a unique TripID, and each drive-leg for that specific day has a matching TripID. Perhaps it is possible to use these?

Thanks for any tips!

Jim

 

[Rick Rothstein]

Try using this formula in cell A2 copied down...

=TEXT(IF(B2="",MAX(B$2:B2),B2),"dddd")

 

[jimrowland]

Thanks, Rick. That worked, and is easy enough to do what I need!

Jim