magneticone asks:

Any idea how to adapt the formula to do a conditional (i.e. comma-separated) merge of the data from multiple columns, in the case that a single row has emails spread over several columns?

You can find magneticone´s question here: How to extract email addresses from a excel sheet

Answer:

Array formula in E2:

=INDEX($A$1:$C$3, SMALL(IF(ISNUMBER(SEARCH("@", $A$1:$C$3)), ROW($A$1:$C$3)-MIN(ROW($A$1:$C$3))+1, ""), ROW(A1)), (SMALL(IF(ISNUMBER(SEARCH("@", $A$1:$C$3)), (ROW($A$1:$C$3)-MIN(ROW($A$1:$C$3))+1)+COLUMN($A$1:$C$3)/1048576, ""), ROW(A1))-SMALL(IF(ISNUMBER(SEARCH("@", $A$1:$C$3)), ROW($A$1:$C$3)-MIN(ROW($A$1:$C$3))+1), ROW(A1)))*1048576) + CTRL + SHIFT + ENTER. Copycell E2 and paste it down as far as needed.

Every cell containing "@" is filtered into column E.

Concatenate cells in column E

Unfortunately you need some vba code to pull this one off: VBA Join function

Download excel template

Filter emails spread over several columns.xls
(Excel 97- 2003 Workbook *.xls)

Functions in this article:

IF(logical_test;[value_if:true];[value_if_false])
Checks whether a condition is met, and returns one value if TRUE, and another value if FALSE

SMALL(array,k) returns the k-th smallest row number in this data set.

ROW(reference) returns the rownumber of a reference

INDEX(array,row_num,[column_num])
Returns a value or reference of the cell at the intersection of a particular row and column, in a given range

COLUMN(reference) returns the column number of a reference

ISNUMBER(value)
Checks whether a value is a number and returns TRUE or FALSE

SEARCH() Returns the number of the character at which a specific character or text string is first found, reading left to right (not case sensitive)

Arielle asks: I have to search for a cell in a table and then display the column title.

search value in cell e1: AA

table in cells A1:C6

A1:x B1:y C1:z
A2:BB B2:CC C2:DD
A3:AA B3:GG C3:AA
A4:CC B4:BLANK C4:EE
A5:FF B5:BLANK C5:HH
A6:BLANK B6:BLANK C6:II

then the values to be displayed from the search would be: x in one cell and z in the next cell.

the display of the search values can either be in a row-(g1: x h1: z) or a column-(g1: x g2: z) Let me know if this type of search is possible, thanks!

Answer:

Array formula in E3:

=INDEX($A$1:$C$1, SMALL(IF(COUNTIF($E$1, $A$2:$C$6)>0, COLUMN($A$2:$C$6)), ROW(A1))) + CTRL + SHIFT+ ENTER. Copy cell E3 and paste it down as far as needed.

Download excel template

Search for a cell in a table and then display the column title.xls
(Excel 97- 2003 Workbook *.xls)

Functions in this article:

IF(logical_test;[value_if:true];[value_if_false])
Checks whether a condition is met, and returns one value if TRUE, and another value if FALSE

SMALL(array,k) returns the k-th smallest row number in this data set.

ROW(reference) returns the rownumber of a reference

INDEX(array,row_num,[column_num])
Returns a value or reference of the cell at the intersection of a particular row and column, in a given range

COUNTIF(range,criteria)
Counts the number of cells within a range that meet the given condition

COLUMN(reference) returns the column number of a reference

In our sequel about weekly schedule it is now time to create a recurring events.

This is what we have created in earlier posts:

• Weekly schedule template
• How to highlight specific time ranges
• How to find empty hours
• How to populate cells dynamically
• Setting up your work hours

Here is a picture of some example events on sheet "Schedule". The first event is recurring daily and the second event is recurring weekly.

How to setup data validation list in cell E3:E4 on sheet "Schedule"

Create a new sheet

1. Create new sheet named "Data"
2. There are threee options, all in each cell A1:A3. See picture below.
3. Cell range B1:B3 contains information about day intervals.

Create a named range

1. Select sheet "Data"
2. Select cell range A1:A3
3. Select tab "Formulas" on the ribbon
4. Click "Named range" button
5. Create a new named range A1:A3

Create Data validation list

1. Select sheet "Schedule"
2. Select cell E3
3. Select tab "Data" on the ribbon.
4. Click "Data validation"
5. Select "List"
6. Type your named range in "Source:" field.
7. Click OK!

Array formula

Array formula in C6:

=IFERROR(INDEX(Title, SMALL(IF(((C$4+$B6)>=Start)*((C$4+$B6)<End)+(Rec="Daily")*(C$4>=Start)*($B6>=TIMEVALUE(TEXT(Start, "tt:mm")))*($B6<TIMEVALUE(TEXT(End-(1/86400), "tt:mm")))+(Rec="Weekly")*(C$4>=Start)*(WEEKDAY(C$4)=WEEKDAY(Start))*(WEEKDAY(C$4)=WEEKDAY(End))*((C$4+$B6)>=Start)*($B6>=TIMEVALUE(TEXT(Start, "tt:mm")))*($B6<TIMEVALUE(TEXT(End-(1/86400), "tt:mm"))), ROW(Start)-MIN(ROW(Start))+1, ""), 1)), "") + CTRL + SHIFT + ENTER. Copy cell C6 and paste it into range C6:I29.

Now the first event is recurring every day and the second event is recurring every week.

The grey cells are hours outside workhours.

Named ranges

Start (E2:E5)
End (F2:F5)
Title (B3:B5)

Download excel template

Schedule recurring events-in-a-weekly-schedule.xlsx
(Excel 2007 Workbook *.xlsx)

Functions in this article:

IF(logical_test;[value_if:true];[value_if_false])
Checks whether a condition is met, and returns one value if TRUE, and another value if FALSE

SMALL(array,k) returns the k-th smallest row number in this data set.

ROW(reference) returns the rownumber of a reference

INDEX(array,row_num,[column_num])
Returns a value or reference of the cell at the intersection of a particular row and column, in a given range

MATCH(lookup_value;lookup_array; [match_type]
Returns the relative position of an item in an array that matches a specified value

IFERROR(value;value_if_error) Returns value_if_error if expression is an error and the value of the expression itself otherwise

WEEKDAY(serialnumber;[return_type])
Returns a number from 1 to 7 identifing the day of the week of a date

MIN(number1,[number2])
Returns the smallest number in a set of values. Ignores logical values and text

Hours outside workhours are filled with grey using conditional formattting, except weekends.

Conditional formatting formula applied on cell range C6:I29:

=AND(OR($B6<$C$31, $B6>=$C$32), AND(WEEKDAY(C$4)<7, WEEKDAY(C$4)>1))

This formula checks if the weekday in C4:I4 is 2,3,4,5 or 6. (Monday to Friday) and if the time in cell range B6:B29 is outside workhours specified in cell C31 and C32. If formula returns TRUE, the cell is filled grey.

Previous blog posts about weekly schedule:

• Weekly schedule template in excel
• Highlight specific time ranges in a weekly schedule in excel
• Find empty hours in a weekly schedule in excel
• Populate cells dynamically in a weekly schedule in excel

Download excel sample file
Highlight work hours-in-a-weekly-schedule.xlsx
(Excel 2007 Workbook *.xlsx)

Named ranges

Start (E2:E5)
End (F2:F5)
Title (B3:B5)

Functions in this article:

WEEKDAY(serialnumber;[return_type])
Returns a number from 1 to 7 identifing the day of the week of a date

AND(logical1, logical2, ..)
Checks whether all arguments are TRUE and returns TRUE if all arguments are TRUE

OR(logical1, logical2, ..)
Checks whether any of the arguments are TRUE or FALSE and returns FALSE if  all arguments are FALSE.

In this post we are going add one more function to a weekly schedule. We are going to create an array formula to populate cells with information from a schedule sheet.

Here is a picture of the schedule sheet:

From the above picture we understand:

On the 1 of August from 8:00 AM to 10:00 AM the word "Meeting" will populate two cells on weekly schedule.

On the 1 of August from 1:00 PM to 3:00 PM the word "Design" will populate two cells on weekly schedule.

On the 3 of August from 12:00 PM to 4:00 PM the word "Strategies" will populate four cells on weekly schedule.

Here is a picture of the weekly schedule and the populated cells:

Now let us see what happens if we change the date in cell F2 to 9-Aug-2010.

Cell range C4:I4 have new dates. They are dynamic and change depending on the value in cell F2.

The cells in C6:I29 are all empty now. They are also dynamic and change depending on the dates  on cell range C4:I4. The cells are empty because nothing is scheduled on these dates. See picure of schedule sheet at the top.

Array formula in C4:

=$F$2-WEEKDAY($F$2;1)+1 + Enter

Formula in D4:

C4+1 + Enter. Copy cell C5 and paste it into cells E4:I4

Array formula in C6:

=IFERROR(INDEX(Title, SMALL(IF(((C$4+$B6)>=Start)*((C$4+$B6)<End), ROW(Start)-MIN(ROW(Start))+1, ""), 1)), "") + CTRL + SHIFT + ENTER. Copy cell C6 and paste it into cell range C6:I29.

Conditional formatting

I filled populated cells using conditional formatting.

Named ranges

Start (E2:E5)
End (F2:F5)
Title (B3:B5)

Download excel template

Populate-time-ranges-in-a-weekly-schedule.xlsx
(Excel 2007 Workbook *.xlsx)

Functions in this article:

IF(logical_test;[value_if:true];[value_if_false])
Checks whether a condition is met, and returns one value if TRUE, and another value if FALSE

SMALL(array,k) returns the k-th smallest row number in this data set.

ROW(reference) returns the rownumber of a reference

INDEX(array,row_num,[column_num])
Returns a value or reference of the cell at the intersection of a particular row and column, in a given range

MATCH(lookup_value;lookup_array; [match_type]
Returns the relative position of an item in an array that matches a specified value

IFERROR(value;value_if_error) Returns value_if_error if expression is an error and the value of the expression itself otherwise

WEEKDAY(serialnumber;[return_type])
Returns a number from 1 to 7 identifing the day of the week of a date

MIN(number1,[number2])
Returns the smallest number in a set of values. Ignores logical values and text