The ColorIndex property has 56 different colors, shown below.

The color property holds up to 16 777 216 colors. I tried to color 16 columns with 1048576 rows each (16 * 1048576 = 16 777 216) using the color property but excel returned this error after 65277 cells.

The following macro lets you count background colors, however it won't count cells colored with conditional formatting.

Sub CountColors() 'This macro counts background colors in cell range 'http://www.get-digital-help.com/2017/03/30/count-colored-cells/ Dim IntColors() As Long, i As Integer Dim chk As Boolean Set rng = Application.InputBox("Select a cell range to count colors: ", , , , , , , 8) ReDim IntColors(0 To 2, 0) For Each cell In rng chk = False For c = LBound(IntColors, 2) To UBound(IntColors, 2) If cell.Interior.ColorIndex = IntColors(0, c) And cell.Interior.Color = IntColors(1, c) Then IntColors(2, c) = IntColors(2, c) + 1 chk = True Exit For End If Next c If chk = False Then IntColors(0, UBound(IntColors, 2)) = cell.Interior.ColorIndex IntColors(1, UBound(IntColors, 2)) = cell.Interior.Color ReDim Preserve IntColors(2, UBound(IntColors, 2) + 1) End If Next cell ReDim Preserve IntColors(2, UBound(IntColors, 2) - 1) Set WS = Sheets.Add WS.Range("A1") = "Color and count" WS.Range("B1") = "ColorIndex" WS.Range("C1") = "Color" j = 1 For i = LBound(IntColors, 2) To UBound(IntColors, 2) If IntColors(2, i) <> 0 Then WS.Range("A1").Offset(j).Interior.ColorIndex = IntColors(0, i) WS.Range("A1").Offset(j).Interior.Color = IntColors(1, i) WS.Range("A1").Offset(j) = IntColors(2, i) WS.Range("A1").Offset(j, 1) = IntColors(0, i) WS.Range("A1").Offset(j, 2) = IntColors(1, i) j = j + 1 End If Next i End Sub

- Select and copy code above (Ctrl+c)
- Open VB Editor (Alt+F11)
- Insert a new module to your workbook
- Paste code to code module

- Press Alt + F8 to see a list of macros
- Select CountColors
- Press "Run" button
- Select a cell range you want to count

The macro then creates a new sheet with cells in a column colored and their count, see picture below.

Value -4142 means No fill and -4105 is default color (white).

There is no way to quickly transfer cell formatting properties to an array so the macro is quite slow, it reads a cells property one by one. I don't recommend using this with larger cell ranges unless you are prepared to wait for a while.

]]>What can an excel defined table do for you? It will simplify your work with data sets, adding or removing data, filtering, sorting, readability using cell formatting, cell references, formulas and more. I will go through all this in greater detail, keep reading.

- Create table
- Name a table
- Edit data
- Sort data
- Filter data
- Structured references
- Formulas in a table
- Formatting
- Show totals
- Named ranges
- Charts
- Data Validation List
- Working with a filtered excel table
- Quickly navigate to a table

Follow these simple steps to convert a cell range to a table:

- Go to tab "Insert" on the ribbon
- Select your data set
- Click "Table" button on tab "Insert"
- Click "OK" button if your table has headers, if not deselect check box and click "OK". Excel will automatically create headers for you.
- You have built an excel table

Tip! Use short cut keys CTRL + T to quickly build a table.

I recommend you give the table and table headers descriptive names, for example it will be easier to identify cell references in formulas.

- Select a cell in your table
- Excel automatically navigates to tab "Design" on your ribbon
- Change table name
- Press Enter

Add data to a cell adjacent to the table and the table expands automatically.

Press tab on keyboard to move from one cell to another, if you press tab while having the most lower right cell in the table selected a new row is included. See picture below.

Right click on a cell in a table to open a menu, from there you can insert or delete rows and columns, also select columns and rows or all table data.

Sorting a table is easy, click on any black triangle located at each header, a menu appears allowing you to quickly sort data in a descending or ascending order.

An arrow next to the black triangle indicates sort order. Sort Z to A (descending) shows you an arrow pointing down.

You can also sort on multiple columns, follow these steps.

- Right click on a cell
- Click Sort and then click "Custom Sort..."

- Select column name to sort on and sort order then add more columns.

- Click OK button to apply sort settings to table

- Click on a black triangle next to any header
- Select values you want to filter
- Click OK button

See animated picture below.

Excel allows you to apply filters to multiple columns easily, repeat above steps with another column.

Use the search field to quickly find the value you want to filter, see picture below.

You are probably used to cell references like this one:

=SUM(E3:E6)

Creating a cell reference to a table column returns this instead, see picture below.

First the table name (Inventory) and then the column name enclosed with brackets [Price].

The amazing thing with structured references is that if you add or remove values to a table the structured reference stays the same, no need to update cell references. In other words, they are dynamic.

*Back to top*

The following example demonstrates what happens if I type a formula in an excel table. I want to multiply cell E3 with F3 in cell G3, see animated picture below.

Excel creates these structured cell references in cell G3 if I type = (equal sig) and then click on cell E3, type * (asterisk) and then click on cell F3:

=[@['#]]*[@Price]

@ (at) means cell value on same row as formula.

Excel also calculates the remaining cells in column G automatically, see animated picture above.

Creating a reference to the entire excel table and headers returns this: =Inventory[#All]

A reference to data in table looks like this: =Inventory

A reference to a table column returns: =Inventory[Warehouse]

A reference to a column header only looks like this: =Inventory[[#Headers],[Warehouse]]

*Back to top*

- Select a cell in excel table
- Go to tab "Design" on the ribbon
- Hover over a table style and see your table change

- If you like it click on it to select it
- Click the black triangle to se even more table styles

You can also build your own table style.

- Select a table cell
- Go to tab "Design"
- Click black triangle

- Click "New Table Style..."
- Enter a name for your table style
- Select a table element you want to change
- Click "Format" button
- Format as you like
- Click OK button twice

- Click on a cell in an excel table
- Go to tab "Design"

- Click "Total Row" check box

- Click cell G7 and then on black triangle

- You can change how value in cell G7 is calculated, the menu has these formulas: Average, Count, Count Numbers, Max, Min, Sum, StdDev, Var and More Functions.
- If you click "More Functions" a dialog box opens with formulas to choose from.

The Name Manager contains a list of all named ranges and excel tables.

- Click "Formula" tab on the ribbon
- Click "Name Manager"

There are only tables in this workbook so the dialog box shows table names, no named ranges.

Combine chart and table to make use of dynamic cell references while filtering data.

More details here: How to create a dynamic chart

*Back to top*

The following animation shows you a data validation list linked to a table.

Read this post if you are interested in the details:

How to use a table name in data validation lists and conditional formatting formulas

*Back to top*

If you try to use a filtered table as a data source in a formula you are in for trouble, see animated picture below.

As you can see above the SUM function sums all values in table regardless of filtered or not.

I have written a few articles about this:

- Highlight duplicates in a filtered excel defined table
- Count unique distinct values in a filtered table
- Highlight unique values in a filtered excel table
- Populate a list box with visible unique values from an excel table (vba)
- Highlight unique values in a filtered excel table
- Extract unique distinct values from a filtered table (udf and array formula)
- Vlookup visible data in a table and return multiple values

Excel lets you quickly focus on a table if you type the table name in the name box.

]]>Hi Oscar,Your formula works great, however, I was wondering if there is capability to add another countif criteria so that it produces a random unique number different from the one above AS WELL AS the one to the left.

I have 7 groups, 7 tables, and 7 rotations for which each group will move to a different table.

With your formula, I am able to successfully assign each group a random order of the 7 tables to rotate too with no repeats (however I am unable to guarantee that for each rotation only one group is at each table), OR I am able to produce a schedule that has max one group per table per rotation, but then I am not able to have each group have no repeats for the tables they are rotating too.

Is there a way to tweak the formula to satisfy both requirements where each group has a random 1-7 table rotation, AND for each rotation there is only one group per table? Thanks!! Appreciate the help.

**Answer**

This is not as easy as it may seem. I first tried using the same technique as in the post but the formula returned errors which is really not surprising if you think of it. I will explain why later in this post but first you need to know how the original formula works.

The original formula uses the COUNTIF function to take previous values vertically into account and return a new unique random number between 1 and 7. This works well, unique random numbers between 1 to 7 are produced in column B.

**Array formula in cell B5:**

=IF(ROW(3:3)<8, LARGE((COUNTIF($B$2:B4, ROW($1:$7))=0)*ROW($1:$7), RANDBETWEEN(1, SUM(--(COUNTIF($B$2:B4, ROW($1:$7))=0)))), "")

I tweaked the formula so it also considers previous values horizontally to make sure a table never has a duplicate group number, see picture below.

Here is the entire table, the formulas return almost always a #num error somewhere in the table. Press F9 to recalculate values if you have the attached workbook open.

So why is this happening? Look at cell D9, number 4 and 1 is to the left of cell D9. This means that only numbers 2,3,5,6 or 7 can be populated in cell D9 or there will be a duplicate on row 9.

In this case, the values above cell D9 are 7,4,5,3,6 and 2 so the only value left is 1 or there will be a duplicate in column D. Number 1 is not possible in cell D9 so the formula returns a #NUM error.

The formulas above cell D9 do not take this into consideration while producing a random value, they only look back at previous values. It is not possible for the formulas to look at cells below or to the right because excel calculates each cell in this order.

It begins with the upper most left cell containing a formula, in this case B2 and then continues with the next cell below and so on, until there are no more formulas in that column. Excel then continues with the first formula in the next column, in this case cell C2.

My second attempt to solve this problem is to move each value one cell for each rotation, the picture below shows how in the second rotation.

The problem with this approach is that only the first rotation has random values, the remaining rotations are not random. However, this technique makes it absolutely certain that a table has no repeats. Perhaps it is random enough?

**Formula in cell C3:**

=INDEX($B$3:$B$9, IF(MATCH(B3, $B$3:$B$9, 0)=7, 1, MATCH(B3, $B$3:$B$9, 0)+1))

To make it even more random I changed the formula so the first value in each column is random (row 3) but the sequence is the same as in rotation 1 (column B). As before, it takes previous values horizontally into account when calculating a new unique value.

**Array formula in cell B3:**

=IF(ROW(1:1)<8, LARGE((COUNTIF($B$2:B2, ROW($1:$7))=0)*ROW($1:$7), RANDBETWEEN(1, SUM(--(COUNTIF($B$2:B2, ROW($1:$7))=0)))), "")

Copy cell B3 and paste to cell range B4:B9.

**Array formula in cell C3:**

=IF(ROW(1:1)>1, INDEX($B$3:$B$9, IF(MATCH(C2, $B$3:$B$9, 0)=7, 1, MATCH(C2, $B$3:$B$9, 0)+1)), LARGE((COUNTIF($B3:B3, ROW($1:$7))=0)*ROW($1:$7), RANDBETWEEN(1, SUM(--((COUNTIF($B3:B3, ROW($1:$7)))=0)))))

Copy cell C3 and paste to cell range C3:H9.

The above picture shows you January 2017, simply enter the project name in column A and the hours in cell range B5:AF32. Row 4 contains dates for January, row 3 the weekdays and row 2 the week numbers. Saturday and Sundays are colored grey.

The Summary sheet, see picture below, contains all projects entered in all month sheets and a total, both for the month and project. A quite large array formula extracts all project names in column B, you don't need to do that manually.

A simple SUMIF function sums the values from each monthly sheet, excel takes care of that too. There is no vba in this workbook, you can find the download link below.

If you change the year on sheet "Summary" you will notice that the monthly sheets change accordingly, you don't need to change weekdays, week number or color weekends on each sheet, excel will do it for you.

Any suggestions for improvement?

The workbook has a summary sheet and sheets for each month in a year. The summary sheet allows you to enter the year in cell H1, I also changed the font size for that cell.

The first monthly sheet is January, this sheet will be the template for the remaining months. This means I will copy this sheet and change the date for each sheet.

Cell D1 contains this formula: =Summary!H1

This lets you change the year on the summary sheet and all other monthly sheets will be instantly updated.

Formula in cell I1: =DATE(D1,1,1)

Select cell I1 and press CTRL + 1 to open the formatting cells dialog box. Change formatting category to "Custom".

Change Type to MMMM;@

If this is not working you need to find out your regional settings in Windows and change MMMM accordingly. This web page from Microsoft explains how to use the formatting dialog box.

This is what cell I1 now looks like:

Time to add dates,type 1 to 31 in cell range B4:AH4. I also change the cell width to 21 pixels. Type "Project" in cell A4, cells below contains project names.

Add text "Total:" to cell AG4 and use this formula in cell AG5:=SUM(B5:AF5)

Copy cell AG5 and paste to cell range AG6:AG32. See picture below.

Add text "Total:" to cell A33 and use this formula in cell B33:=SUM(B5:B32)

Copy cell A33 and paste to cell range B33:AF33. See picture above.

To make this sheet easier to read I want the row above dates to contain the abbreviation of days of the week. I am using this formula in cell B3:

=IF(MONTH($B$4)=MONTH(B4), CHOOSE(WEEKDAY(B4,2),"M","T","W","T","F","S","S"),""). See picture below.

I also want the row above days of the week to contain the week number. Formula in cell B2:

=WEEKNUM(B4,1)

But I only want that number above a date when a new week starts.

Formula in cell C2:

=IF((WEEKNUM(C4,1)<>WEEKNUM(B4,1))*(MONTH($B$4)=MONTH(B4)), WEEKNUM(C4,1),"")

Copy cell C2 and paste to D2:AF2. See pic below.

The following conditional formatting formula highlights weekends gray:

(WEEKDAY(B$4,2)>5)*(MONTH(B$4)=MONTH($B$4))

- Select cell range B4:AF32
- Go to tab Home on the ribbon
- Click "Conditional Formatting" button and then click "New Rule.."
- Click "Use a formula to determine which cells to format"
- Paste above formula to field "Format values where this formula is true:"

- Click "Format" button
- Go to tab "Fill"
- Pick a color

- Click OK button

Worksheet "January" now looks like this:

The conditional formatting changes depending what month and year it is, this is not something you have to manually do.

Next thing is the grid pattern, here is the conditional formatting formula:

=IF($AI$3="Off",,MONTH(B$4)=MONTH($B$4))

To build formatting formula, repeat above steps 1 to 8 except instead of picking a color, go to tab "Border" and click "Outline, then click OK button. See picture below.

Worksheet January is now ready, time to copy the worksheet and create worksheets for the remaining months.

- Right click on sheet "January"
- Click "Move or Copy..."
- Select "move to end" and "Create a copy"
- Click Ok button

Repeat above steps until you have a worksheet for each month in a year.

- Rename the sheet after January to February
- Change formula in cell I1 to =DATE(D1,2,1)

Note that February is the second month in a year and the second argument in the formula is then 2.

Repeat steps 1 and 2 above for all remaining worksheets.

]]>

A regular expression is a sequence of characters that define a search pattern, according to Wikipedia.

This is the custom function I am using to extract cell references from a formula.

Function ExtractCellRefs(c As Range) As String regexpattern = "" With CreateObject("vbscript.regexp") .Global = True .MultiLine = True .IgnoreCase = False .Pattern = regexpattern Set Results = .Execute(c.Formula) End With If Results.Count <> 0 Then With Results For d = 0 To .Count - 1 Rstr = Rstr & .Item(d) & "," Next End With ExtractCellRefs = Left(Rstr, Len(Rstr) - 1) Else ExtractCellRefs = "No Matches" End If End Function

The tricky part is the regular expression and I am a beginner at this, feel free to simplify my expression.

A cell reference can be anything from A1 to XFD1048576 so to match that the reg exp becomes:

[A-Z]{1,3}[0-9]{1,7}

[A-Z] matches any upper case letter from A to Z.

[A-Z]{1,3} matches 1 or up to 3 lower and upper case letters from A to Z. Example, XFD1048576 contains three letters.

[0-9]{1,7} matches 1 or more up to 7 digits from 0 to 9. Example, XFD1048576 contains 7 digits.

A cell reference can also be absolute or relative or both and the $ sign tells which it is.

\$?[A-Z]{1,3}\$?[0-9]{1,7}

\ (backslash) escapes the character that follows

\$ allows us to use the character $, if I had not used the \ (backslash) $ (dollar sign) had been taken for a match at end of string, I don't want that to happen.

\$? the question mark matches zero or one of the pattern defined before it, in this case $ (dollar sign)

The picture below shows the matches for above expression \$?[A-Z]{1,3}\$?[0-9]{1,7}

It also works for this simple formula: =SUM(XFD1048576, $A$1,A$1,$A1), it returns these cell references: XFD1048576,$A$1,A$1,$A1

**Cell reference to a cell range**

The above regular expression finds only cell refs to a single cell, how do we find a solution to that?

A cell ref to a single cell looks like this =A1, a cell ref to a cell range may look like this =A1:C3

\$?[A-Z]{1,3}\$?[1-9]{1,7}(:\$?[A-Z]{1,3}\$?[1-9]{1,7})?

The new part is

(:\$?[A-Z]{1,3}\$?[0-9]{1,7})?

( (parentheses) groups an expression

: a cell ref to a cell range contains a colon :

\$?[A-Z]{1,3}\$?[0-9]{1,7} is the same as before, it matches letters and digits

(:\$?[A-Z]{1,3}\$?[0-9]{1,7}) the parentheses groups the expression

(:\$?[A-Z]{1,3}\$?[0-9]{1,7})? the question mark matches zero or one of the pattern defined before it, in this case the group

**Cell references to other sheets**

A cell reference to another sheet always ends with a ! (exclamation mark), the question mark matches zero or one of the pattern defined before it, in this case the ! (exclamation mark)

This is a new group so I am leaving out the previous expression for now, I will add it later. The regular expression is now !?

The sheet name may have lower and upper letters from A to Z and also numbers 0 to 9, the regular expression is [a-zA-Z0-9]{1,99}!?

If there is a blank space in the sheet name excel automatically surrounds the sheet name with two ' (apostrophe character), the expression becomes '?[a-zA-Z0-9]{1,99}'?!?

\s is any space character, this is what we have now '?[a-zA-Z0-9\s]{1,99}'?!?

'? the question mark matches zero or one of the pattern defined before it, in this case the ' (apostrophe character)

**Cell references to other workbooks**

There may also be cell references to other workbooks, it would be nice to find them as well.

A reference to a cell range in another workbook has the workbook name surrounded by these characters [].

('?[a-zA-Z0-9\s\[\]]{1,99})?'?!? , \ (backslash) escapes the character that follows in this case \[\]

The file name has a dot between the file name and the extension, ('?[a-zA-Z0-9\s\[\]\.]{1,99})?'?!?

The final expression is

('?[a-zA-Z0-9\s\[\]\.]{1,99})?'?!?\$?[A-Z]{1,3}\$?[0-9]{1,7}(:\$?[A-Z]{1,3}\$?[0-9]{1,7})?

Can it be made smaller?

I am sure there are characters allowed in a filename or sheet name that I have not considered in this post but I believe it will be easy to add those as well.

This does not take care of named ranges in a formula but it would not be hard to build a list of named ranges and then check if the formula contains named ranges.

Hello Oscar,

What code is needed to cause cells in Columns F - I to fill with the contents of Columns C - E when a cell in Column B includes a numeric value?

What code is needed to cause cells in Columns F - I to fill with the contents of Columns C - E when a cell in Column B includes a numeric value?

**Answer:**

The data set above contains random characters, some of the cells in column B contains numeric values, as well.

**Array formula in cell F2:**

=INDEX($B$2:$E$6, SMALL(IF(MMULT(IFERROR(SEARCH({1, 2, 3, 4, 5, 6, 7, 8, 9, 0}, $B$2:$B$6), 0), ROW($A$1:$A$10)), MATCH(ROW($B$2:$B$6), ROW($B$2:$B$6)), ""), ROWS($A$1:A1)), COLUMNS($A$1:A1))

- Copy formula above
- Doubleclick on cell F2
- Paste formula
- Press and hold CTRL + SHIFT
- Press Enter

If you did this correctly, the formula in the formula bar now begins with a curly bracket and ends with a curly bracket, like this: {=formula}

Don't enter these curly brackets yourself, they will appear if you did the above steps.

Copy cell F2 and paste to cell range F2:I6.

**Step 1 - Look for values in a cell range**

SEARCH({1, 2, 3, 4, 5, 6, 7, 8, 9, 0}, $B$2:$B$6)

becomes

SEARCH({1, 2, 3, 4, 5, 6, 7, 8, 9, 0}, {"ab12"; "abc"; "def"; "a21b"; "cde"})

and returns this array:

{3, 4, #VALUE!, #VALUE!, #VALUE!, #VALUE!, #VALUE!, #VALUE!, #VALUE!, #VALUE!; #VALUE!, #VALUE!, #VALUE!, #VALUE!, #VALUE!, #VALUE!, #VALUE!, #VALUE!, #VALUE!, #VALUE!; #VALUE!, #VALUE!, #VALUE!, #VALUE!, #VALUE!, #VALUE!, #VALUE!, #VALUE!, #VALUE!, #VALUE!; 3, 2, #VALUE!, #VALUE!, #VALUE!, #VALUE!, #VALUE!, #VALUE!, #VALUE!, #VALUE!; #VALUE!, #VALUE!, #VALUE!, #VALUE!, #VALUE!, #VALUE!, #VALUE!, #VALUE!, #VALUE!, #VALUE!}

**Step 2 - Remove errors**

IFERROR(SEARCH({1, 2, 3, 4, 5, 6, 7, 8, 9, 0}, $B$2:$B$6), 0)

becomes

IFERROR({3, 4, #VALUE!, #VALUE!, #VALUE!, #VALUE!, #VALUE!, #VALUE!, #VALUE!, #VALUE!; #VALUE!, #VALUE!, #VALUE!, #VALUE!, #VALUE!, #VALUE!, #VALUE!, #VALUE!, #VALUE!, #VALUE!; #VALUE!, #VALUE!, #VALUE!, #VALUE!, #VALUE!, #VALUE!, #VALUE!, #VALUE!, #VALUE!, #VALUE!; 3, 2, #VALUE!, #VALUE!, #VALUE!, #VALUE!, #VALUE!, #VALUE!, #VALUE!, #VALUE!; #VALUE!, #VALUE!, #VALUE!, #VALUE!, #VALUE!, #VALUE!, #VALUE!, #VALUE!, #VALUE!, #VALUE!}, 0)

and returns

{3, 4, 0, 0, 0, 0, 0, 0, 0, 0; 0, 0, 0, 0, 0, 0, 0, 0, 0, 0; 0, 0, 0, 0, 0, 0, 0, 0, 0, 0; 3, 2, 0, 0, 0, 0, 0, 0, 0, 0; 0, 0, 0, 0, 0, 0, 0, 0, 0, 0}

**Step 3 - Return the matrix product of two arrays**

MMULT(IFERROR(SEARCH({1, 2, 3, 4, 5, 6, 7, 8, 9, 0}, $B$2:$B$6), 0), ROW($A$1:$A$10))

becomes

MMULT({3, 4, 0, 0, 0, 0, 0, 0, 0, 0; 0, 0, 0, 0, 0, 0, 0, 0, 0, 0; 0, 0, 0, 0, 0, 0, 0, 0, 0, 0; 3, 2, 0, 0, 0, 0, 0, 0, 0, 0; 0, 0, 0, 0, 0, 0, 0, 0, 0, 0}, ROW($A$1:$A$10))

becomes

MMULT({3, 4, 0, 0, 0, 0, 0, 0, 0, 0; 0, 0, 0, 0, 0, 0, 0, 0, 0, 0; 0, 0, 0, 0, 0, 0, 0, 0, 0, 0; 3, 2, 0, 0, 0, 0, 0, 0, 0, 0; 0, 0, 0, 0, 0, 0, 0, 0, 0, 0}, {1;2;3;4;5;6;7;8;9;10})

and returns

{11;0;0;7;0}

**Step 4 - Check whether a condition is met**

IF(MMULT(IFERROR(SEARCH({1, 2, 3, 4, 5, 6, 7, 8, 9, 0}, $B$2:$B$6), 0), ROW($A$1:$A$10)), MATCH(ROW($B$2:$B$6), ROW($B$2:$B$6)), "")

becomes

IF({11;0;0;7;0}, MATCH(ROW($B$2:$B$6), ROW($B$2:$B$6)), "")

becomes

IF({11;0;0;7;0}, {1;2;3;4;5}, "")

and returns {1;"";"";4;""}

**Step 5 - Return the k-th smallest value in array**

SMALL(IF(MMULT(IFERROR(SEARCH({1, 2, 3, 4, 5, 6, 7, 8, 9, 0}, $B$2:$B$6), 0), ROW($A$1:$A$10)), MATCH(ROW($B$2:$B$6), ROW($B$2:$B$6)), ""), ROWS($A$1:A1))

becomes

SMALL({1;"";"";4;""}, ROWS($A$1:A1))

becomes

SMALL({1;"";"";4;""}, 1)

and returns 1.

**Step 6 - Return a value of the cell at the intersection of a particular row and column**

=INDEX($B$2:$E$6, SMALL(IF(MMULT(IFERROR(SEARCH({1, 2, 3, 4, 5, 6, 7, 8, 9, 0}, $B$2:$B$6), 0), ROW($A$1:$A$10)), MATCH(ROW($B$2:$B$6), ROW($B$2:$B$6)), ""), ROWS($A$1:A1)), COLUMNS($A$1:A1))

becomes

=INDEX($B$2:$E$6, 1, COLUMNS($A$1:A1))

becomes

=INDEX($B$2:$E$6, 1, 1)

becomes

=INDEX({"ab12", "PEN", "YPT", "KVF"; "abc", "ZLZ", "KIK", "HQX"; "def", "CJI", "YMI", "STC"; "a21b", "TQW", "XHA", "UBM"; "cde", "YZX", "GLT", "TED"}, 1, 1)

and returns ab12 in cell F2.

Filter records containing a value.xlsx

If you rather want to use an excel table filter, follow these instructions

]]>? (question mark) - Matches any single character

* (asterisk) - Matches zero or more characters

# (number or hash sign) - Any single digit

A1A* - You can also use a string combined with characters above to build a pattern. This matches a string beginning with or equals A1A.

[abc] - Characters enclosed in brackets allows you to match any single character in the string.

[!abc] - The exclamation mark (!) matches any single character not in the string.

[A-Z] - The hyphen lets you specify a range of characters.

Add *Option compare binary* or *Option compare text *before any macros or custom functions in you code module to change how string comparisons are made.

The default setting is *Option compare binary. *Use *Option compare text *to make the comparison case-insensitive but put the code in a separate module so other macros/functions are not affected.

To learn more, read this article: Option Compare Statement

The LIKE operator returns a boolean value, TRUE or FALSE depending on if the pattern is a match or not.

This simple custom function lets you specify a pattern and compare it to a cell value. If there is a match, the function returns TRUE. If not, FALSE.

Function Compare(c As Range, pttrn As String) As Boolean Compare = c Like pttrn End Function

Copy the code above and paste it to a code module in the VB Editor, if you want to use it.

The picture below demonstrates the custom function above. It takes the string in cell A2 and compares it to the pattern in cell B2.

A question mark (?) matches any single character.

Value in cell A2 ABC matches A?C, TRUE is returned in cell D2.

Value in cell A3 ABCD does not match pattern A?D. BC are two characters, a question mark matches any single character. FALSE is returned in cell D3.

Value ABCD matches ?BC? and TRUE is returned in cell D4.

The pattern tells you that the first three characters must be AAA and then the * (asterisk) matches zero or more characters. AAAC is a match to pattern AAA* and the custom function returns TRUE in cell D2.

aaa* does not match pattern AAAC. aaa is not equal to AAA. LIKE operator is case sensitive unless you change settings to Option Compare Text.

(*) matches zero or more characters, DDC23E matches DD*E.

# matches a single digit. To match multiple digits use multiple #.

123 matches 12#, TRUE is returned in cell D2.

123 does not match 1#, number sign matches any single digit.

123 matches #2#.

The following three examples use asterisks, question marks and number signs combined.

Remember brackets match any single character you specify. A hyphen lets you compare a range of characters.

The following custom function allows you to extract cell values using the LIKE operator.

**Array fomula in cell C2:C6:**

=SearchPattern(A2:A15,B2)

Function SearchPattern(c As Range, pttrn As String) Dim d as String For Each cell In c If cell Like pttrn Then d = d & cell & "," Next cell SearchPattern = Application.Transpose(Split(d, ",")) End Function

This custom function allows you to look for a pattern in a column and return the corresponding value in another column.

**Array fomula in cell D2:D6:**

=SearchCol(A2:A15,B2:B15,C2)

Function SearchCol(b As Range, c As Range, pttrn As String) Dim a As Long, d as String a = b.Cells.CountLarge For i = 1 To a If b.Cells(i) Like pttrn Then d = d & c.Cells(i) & "," Next i SearchCol = Application.Transpose(Split(d, ",")) End Function

You can use the question (?) mark and asterisk (*) in many excel functions.

If you need to use even more complicated patterns excel allows you to use regular expressions, see this thread:

How to use Regular Expressions (Regex) in Microsoft Excel both in-cell and loops

The picture above shows a weekly stock bar chart of Microsoft and Caterpillar. One bar shows you the highest price and the lowest price during that specific week, it also shows the closing price which is the price of the last deal done that week.

- Rearrange columns like this: high, low and close

- Select columns High, Low and close
- Go to tab "Insert" on the ribbon
- Click "Other Charts" and then "High-Low-Close"

- A chart is inserted on your active sheet

- Right click on chart and click on "Select Data..."
- Click Edit button below Horizontal (Category) Axis Labels
- Select your date range

- Click OK button twice

- Right click on chart again
- Click on "Select Data"
- Click on "Add" button below Legend Entries (Series)
- Select High column for the other stock you want plotted

- Click OK twice
- Select the chart
- Go to tab "Layout" on the ribbon
- Select series 4

- Click "Format Selection" below Series 4
- Change to "Secondary Axis"

- Click Close
- Right click on chart again and click on "Select Data..."
- Add Low and Close series
- Go to tab "Layout" on the ribbon
- Select "Series 4"
- Click on "Lines" button and then "High-Low" Lines

- Select "Series 6" (Close)
- Click "Format Selection"
- Go to "Marker Options", choose built-in marker type and the sixth symbol from the top.

- Select size 3
- Go to "Marker Line Color", select "Solid Line" and finally pick a color. I chose dark blue.

- Select "High Low Lines 2"

- Select "Solid Line" and select a color

- Click Close

You now know how to change the color of high low lines and the closing line. Change the line color of all series (High, Low and Close) on the primary axis.

I chose black. Now delete the secondary axis and remove entries on the legend except "Close" and "Series 6". Change the name of series "Close" to Microsoft and "Series 6" to Caterpillar.

How easy is it to modify this for recurring tasks (weekdays, weekly, monthly, quarterly and yearly) and maybe show a monthly view? Times are less important than just showing what is due on what day.

I made a calendar shown below, monthly view. The picture is resized to fit this blog, click to see the original size. This calendar is more advanced than the template I made year 2011.

The form next to the calendar allows you to add events. Enter time and event name and then click button "Add".

If there are more events on a single day than can be displayed, the last line tells you ...more.... See picture below for an example.

Select that cell and all events are shown in a table next to the calendar.

You can easily edit or delete an event by clicking a link in column Time, see picture above. The link takes you to the record on sheet "Schedule", see picture below.

Here you can edit or delete the record as you please.

The buttons above the calendar lets you go to next or previous month, there is also a button that takes you to the current month, button "Today"

Days before and after selected month are grayed out. Current day is highlighted orange. The following picture shows you this.

The best I could do is creating a formula that calculates the upcoming recurring event. Events after that are not shown until the date has passed.

**Monthly**

Array formula in cell H4:

=IF(DAY(TODAY())>3, DATE(YEAR(TODAY()), MONTH(TODAY())+1, 3)+11/24, DATE(YEAR(TODAY()), MONTH(TODAY()), 3)+11/24)

**Weekly**

Array formula in cell H5:

=TODAY()+IF(WEEKDAY(TODAY())<=3, 3-WEEKDAY(TODAY()), (10-WEEKDAY(TODAY())))+15/24

**Daily**

Array formula:

=TODAY()+17/24

Anyone got a better idea?

This workbook contains macros and a custom function.

]]>- Basic schedule
- Round-robin tournament
- Double round-robin tournament
- Macro
- Download workbook
- How to use custom function

According to wikipedia a round-robin tournament is a competition where all plays all. Excel is a great platform for building a round-robin tounament table and keeping scores.

You can use these custom functions below for creating a table for tennis, soccer, chess, bridge or whatever sport/competition schedule you want. At the very end of this post are instructions on how to use the custom functions. Let's start.

The following vba code creates a schedule where each team plays once against another team.

Function roundrobin(rng As Range) 'Get Digital Help http://www.get-digital-help.com/ 'Define variables Dim tmp() As Variant, k As Long Dim i As Long, j As Long 'ReDimension tmp variable ReDim tmp(1 To (rng.Cells.Count / 2) * (rng.Cells.Count - 1), 1 To 2) k = 1 'Schedule everyone with everyone For i = 1 To rng.Cells.Count For j = i + 1 To rng.Cells.Count tmp(k, 1) = rng.Cells(i) tmp(k, 2) = rng.Cells(j) k = k + 1 Next j Next i 'Return array roundrobin = tmp End Function

As you can see it is not very complicated and the first team has home matches all the time. But if home and away doesn't matter this could useful.

Another bad thing with this custom function is that it doesn't split the schedule into rounds. A team can't play twice in the same round, obviously.

Also if you want the schedule to be somewhat random, this custom function is not for you.

The next custom function takes care of these three issues.

This custom function creates a round-robin tournament. It tries to distribute home and away rounds evenly and teams are randomly placed in the schedule.

Function RoundRobin2(rng As Range) 'This custom function adds a team automatically if the number of teams is uneven. 'Get Digital Help http://www.get-digital-help.com/ Dim tmp() As Variant, k As Long, l As Integer Dim i As Long, j As Long, a As Long, r As Long Dim rngA As Variant, Stemp As Variant, Val As Long Dim res, rngB() As Variant, result As String 'Transfer cell values to an array rngA = rng.Value 'Check if the number of teams are even If rng.Cells.Count Mod 2 = 0 Then rngB = rng.Value l = 0 Else ReDim rngB(1 To UBound(rngA) + 1, 1 To 1) For i = 1 To UBound(rngA) rngB(i, 1) = rngA(i, 1) Next i rngB(UBound(rngB, 1), 1) = "-" l = 1 End If ReDim tmp(1 To ((rng.Cells.Count + l) / 2) * (rng.Cells.Count + l - 1), 1 To 3) 'Randomize array rngB = RandomizeArray1(rngB) Val = (UBound(rngB, 1) / 2) 'Build schedule For i = 2 To UBound(rngB, 1) a = 1 For r = 1 To (UBound(rngB, 1) / 2) tmp(r + Val * (i - 2), 1) = i - 1 If (i - 1) Mod 2 = 1 Then tmp(r + Val * (i - 2), 2) = rngB(a, 1) tmp(r + Val * (i - 2), 3) = rngB(UBound(rngB, 1) - a + 1, 1) Else tmp(r + Val * (i - 2), 2) = rngB(UBound(rngB, 1) - a + 1, 1) tmp(r + Val * (i - 2), 3) = rngB(a, 1) End If a = a + 1 Next r 'switch places for all values except the first one For j = 2 To UBound(rngB, 1) - 1 Stemp = rngB(j, 1) rngB(j, 1) = rngB(j + 1, 1) rngB(j + 1, 1) = Stemp Next j Next i RoundRobin2 = tmp End Function

This user defined function creates a random schedule split into rounds, home and away are also somewhat evenly distributed through the schedule.

This table shows you how many times 6 teams play home and away for the entire tournament.

To make sure every team has as many home as away rounds competitors play each other twice. It is a "double" round-robin tournament. The way it works is all play all twice, once home and once away.

Function doubleroundrobin(rng As Range) 'Get Digital Help http://www.get-digital-help.com/ Dim tmp() As Variant, k As Long, l As Integer Dim i As Long, j As Long, a As Long, r As Long Dim rngA As Variant, Stemp As Variant, Val As Long Dim res, rngB() As Variant, result As String, cc As Long 'Transfer values to an array rngA = rng.Value 'Check if the number of teams are even If rng.Cells.Count Mod 2 = 0 Then rngB = rng.Value l = 0 Else ReDim rngB(1 To UBound(rngA) + 1, 1 To 1) For i = 1 To UBound(rngA) rngB(i, 1) = rngA(i, 1) Next i rngB(UBound(rngB, 1), 1) = "-" l = 1 End If cc = ((rng.Cells.Count + l) / 2) * (rng.Cells.Count + l - 1) ReDim tmp(1 To cc * 2, 1 To 3) 'Randomize array rngB = RandomizeArray1(rngB) Val = (UBound(rngB, 1) / 2) 'Build schedule For i = 2 To UBound(rngB, 1) a = 1 For r = 1 To (UBound(rngB, 1) / 2) tmp(r + Val * (i - 2), 1) = i - 1 If (i - 1) Mod 2 = 1 Then tmp(r + Val * (i - 2), 2) = rngB(a, 1) tmp(r + Val * (i - 2), 3) = rngB(UBound(rngB, 1) - a + 1, 1) Else tmp(r + Val * (i - 2), 2) = rngB(UBound(rngB, 1) - a + 1, 1) tmp(r + Val * (i - 2), 3) = rngB(a, 1) End If a = a + 1 Next r For j = 2 To UBound(rngB, 1) - 1 Stemp = rngB(j, 1) rngB(j, 1) = rngB(j + 1, 1) rngB(j + 1, 1) = Stemp Next j Next i 'Copy schedule and change home to away and vice versa, this makes it a double round-robin tournament For i = cc + 1 To cc * 2 tmp(i, 1) = UBound(rngB, 1) - 1 + tmp(i - cc, 1) tmp(i, 2) = tmp(i - cc, 3) tmp(i, 3) = tmp(i - cc, 2) Next i doubleroundrobin = tmp End Function

Here is a table that shows you teams play as many home as away games.

The macros in the workbook below allows you to create a match schedule. Go to sheet Macro and follow instructions.

Add teams or players to column A, then click "Round-robin tournament" button or "Double round-robin tournament".

A match schedule is created on a new sheet.

Conditional formatting separates rounds with a line, it makes the table easier to read.

Round-robin tournament

Sub rr() Dim Lrow As Long Dim rng As Range, tmp() As Variant Dim ws As Worksheet Application.ScreenUpdating = False Lrow = Worksheets("Macro").Range("A" & Rows.Count).End(xlUp).Row Set rng = Worksheets("Macro").Range("A2:A" & Lrow) tmp = RoundRobin2(rng) 'Insert new sheet Set ws = Sheets.Add ws.Range("A1") = "Round" ws.Range("B1") = "Home" ws.Range("C1") = "Away" ws.Range("A2").Resize(UBound(tmp, 1), 3) = tmp ws.Range("A1").Resize(UBound(tmp, 1) + 1, 3).InsertIndent 1 Columns("A:C").EntireColumn.AutoFit ws.Range("A1:C1000").Select Selection.FormatConditions.Add Type:=xlExpression, Formula1:="=$A1<>$A2" Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority With Selection.FormatConditions(1).Borders(xlBottom) .LineStyle = xlContinuous .TintAndShade = 0 .Weight = xlThin End With Selection.FormatConditions(1).StopIfTrue = False Application.ScreenUpdating = True End Sub

Double round-robin tournament

Sub droundrobin() Dim Lrow As Long Dim rng As Range, tmp() As Variant Dim ws As Worksheet Application.ScreenUpdating = False Lrow = Worksheets("Macro").Range("A" & Rows.Count).End(xlUp).Row Set rng = Worksheets("Macro").Range("A2:A" & Lrow) tmp = doubleroundrobin(rng) 'Insert new sheet Set ws = Sheets.Add ws.Range("A1") = "Round" ws.Range("B1") = "Home" ws.Range("C1") = "Away" ws.Range("A2").Resize(UBound(tmp, 1), 3) = tmp ws.Range("A1").Resize(UBound(tmp, 1) + 1, 3).InsertIndent 1 Columns("A:C").EntireColumn.AutoFit ws.Range("A1:C1000").Select Selection.FormatConditions.Add Type:=xlExpression, Formula1:="=$A1<>$A2" Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority With Selection.FormatConditions(1).Borders(xlBottom) .LineStyle = xlContinuous .TintAndShade = 0 .Weight = xlThin End With Selection.FormatConditions(1).StopIfTrue = False Application.ScreenUpdating = True End Sub

This function moves values in an array randomly.

Function RandomizeArray1(Arr As Variant) Dim temp Dim i As Long, j As Long, k As Long Dim result As Variant For k = LBound(Arr, 1) To UBound(Arr, 1) result = result & Arr(k, 1) & " " Next k result = result & vbNewLine For i = LBound(Arr, 1) To UBound(Arr, 1) j = Application.WorksheetFunction.RandBetween(LBound(Arr, 1), UBound(Arr, 1)) temp = Arr(j, 1) Arr(j, 1) = Arr(i, 1) Arr(i, 1) = temp For k = LBound(Arr, 1) To UBound(Arr, 1) result = result & Arr(k, 1) & " " Next k result = "" Next i RandomizeArray1 = Arr End Function

If you want the vba code in your own workbook, do this.

- Press Alt-F11 to open visual basic editor
- Click Module on the Insert menu
- Copy and paste all custom functions above to the code module

- Exit visual basic editor (Alt+Q)
- Save your workbook as an *.xlsm file

Now you can use the custom functions. Type your teams in a column. Select a blank cell range, 3 columns wide and many rows, you can extend this later if not all rounds show up.

Type =doubleroundrobin(cell_ref_to_your_teams), press and hold CTRL + Shift. Press Enter. Release all keys.

]]>