Loading...

Follow Contextures Blog - Excel Tips and Tutorials on Feedspot

Continue with Google
Continue with Facebook
or

Valid

If you’re planning a vacation trip, Excel can help. It’s a great place to keep your packing lists, and you can track your vacation spending too (if you really want to know the total!). I’ve just uploaded a new sample file that will show how far you’ll travel. Select cities, and formulas do a mileage lookup, with total distance from start to end.

Two City Mileage Lookup

The new workbook is based on a previous one, which showed the distance between two cities. Select a city name in each green cell, and see the distance between those two cities, in miles.

The distances come from a lookup table, shown below. Find the intersection of the two selected city names, and that is the distance between them.

The data in that table is from the Florida Dept. of Transportation.

Plan a Vacation Trip

The old workbook was handy if you were going from one city to another, and then straight back home. But what about a longer journey, with stops at multiple cities?

Maybe you’d like to plan a trip to a few vacation spots in Florida, and see how far you’ll travel. I’ve put arrows on a Florida map, to show our imaginary vacation route. The trip starts from Gainesville, gets down to Sarasota, and then back home.

NOTE: I found a copyright-free map on The National Map site, which is run by the U.S. Geological Survey. Do not visit that site if you are easily distracted – there are lots of fascinating data collections and maps there. You have been warned!

Total Distance for a Vacation Trip

In the new workbook, there are data validation drop down lists where you can choose up to 6 cities.

There are formulas in the next column, to do a lookup from the mileage table, and another formula shows a grand total.

The Lookup Formula

To do the mileage lookup, the “Miles” column has an INDEX/MATCH/MATCH formula in each row. Read more about INDEX and MATCH on my website.

Here is the formula in cell C5:

=IFERROR(INDEX($H$4:$Q$13,
MATCH(B4,$G$4:$G$13,0),
MATCH(B5,$H$3:$Q$3,0)),””)

  • The INDEX function returns a value from H4:Q13 (outlined in blue)
  • The first MATCH function returns the row of the first city (in B4), from the vertical list of cities in G4:G13 (purple)
  • The second MATCH function returns the column of the second city (in B5), from the horizontal list of cities in H3:Q3 (red)

NOTE: The IFERROR function puts an empty string in the Miles cell, if it can’t calculate the distance.

The distance from Gainesville to Jacksonville is 68 miles.

Get the Total Distance

The lookup formula from cell C5 is copied down to C9, to calculate the distance for each leg of the trip.

Then, in cell C11, there is a SUM function, to calculate the total miles for the trip.

=SUM(C5:C9)

Get the Mileage Lookup Workbook

To get the Mileage Lookup with Total Distance workbook, go to the Excel Sample Files page on my Contextures site. In the Functions section, look for FN0055 -Total Travel Distance Mileage Chart

The zipped file is in xlsx format, and does not contain macros.

________________

Read Full Article
Visit website
  • Show original
  • .
  • Share
  • .
  • Favorite
  • .
  • Email
  • .
  • Add Tags 

Last month, you saw J. Woolley’s technique to run command files from Excel hyperlinks. He created a SuperLink function too, that is better than Excel’s HYPERLINK function. Now he’s sharing a new technique, with better hyperlinks for Excel sheets.

Better Hyperlinks for Excel Sheets

In last month’s article, J. Woolley’s created special hyperlinks that make it easy to run command files from Excel. In his new technique, SHEET::NAME , J. Wooley has created better hyperlinks for Excel sheets.

With normal Excel hyperlinks, there are limitations:

  • Previous selection on the linked sheet is changed, because the hyperlink selects a specific range
  • Can’t link to a chart sheet, because it doesn’t have cells
  • If the linked cell is on a hidden sheet, the link fails silently

The SHEET::NAME hyperlinks overcome those limitations. These hyperlinks can:

  • activate a sheet, without changing the previous selection
  • activate a chart sheet, as well as worksheets
  • show a message if linked cell is on a hidden sheet

Get the Sheet Name Hyperlink Files

To get started, click this link to get the zipped folder with the Sheet Name files, and download it to a folder on your computer. SheetName.zip contains the following files:

  • TestSheetName.xlsm
  • Sample Check Register.xlsx
  • UseRunCommand.pdf
  • UseSheetName.pdf
  • UseSuperLink.pdf
  • ExcelBugReport.pdf
  • M_ProcessSheetName.bas
  • M_SuperLink.bas
Install the Sheet Name Files

First, before you unzip the file, you will probably need to unblock the downloaded file using its Properties dialog box.

If you don’t unblock the file, the SHEET::NAME technique might appear to work, but it will not work correctly.

Then, after the downloaded file is unblocked, unzip all files to a single folder.

It’s important to keep the files together, because the TestSheetName.xlsm workbook assumes it is in the same folder as the other files.

Use the Sheet Name Files

After the files are unzipped, open TestSheetName.xlsm in Excel; you will probably need to Enable Content for Macros.

  • NOTE: The files named M_ProcessSheetName.bas and M_SuperLink.bas have already been imported as VBA Modules. in TestSheetName.xlsm

On the Test Sheet, there are two sections with test hyperlinks.

  • Static Hyperlinks, created by Insert > Link
  • Dynamic Hyperlinks, created with the SuperLink function

There is a brief introduction at the top of each section, and the hyperlink text explains what each link does.

NOTE: The first link in each section opens a PDF file, with details on how the technique works.

Sheet Name Sample Sheets

There are 4 sample sheets in the TestSheetName.xlsm workbook. Three of the sample sheets (based on Excel 2016 templates) are visible:

  • Home Contents Inventory List
  • Inventory’s Value Chart
  • Room Lookup

And there is a hidden sample sheet:

  • Hidden Sheet

There’s nothing on that sheet, but it shows how the technique can work with hidden sheets too.

Test the Hyperlinks

Use the hyperlinks on the Test Sheet to test the SHEET::NAME technique — the hyperlink text explains what each link does.

For example, go to the Room Lookup sheet, and select one of the cells in the list. Then, go back to Test Sheet, and click one of the Room Lookup links.

NOTE: The links in shaded cells will not work.

The Room Lookup sheet is activated, and your previous selection on that sheet has not been changed.

Download the Sample Files

To get started, click this link to get the zipped folder with the Sheet Name files, and download it to a folder on your computer

Questions or Comments

If you have questions or comments about the Sheet Name hyperlinks technique, post in the Comments section below, and J. Woolley will try to help.

________________

Read Full Article
Visit website
  • Show original
  • .
  • Share
  • .
  • Favorite
  • .
  • Email
  • .
  • Add Tags 

When you create a pivot table, a default PivotTable Style is automatically applied. You can change to a different style, and you can even create custom pivot table styles. To help you keep track of the styles that you have, here’s a List All Pivot Table Styles macro.

Pivot Table Custom Styles

If you’re not sure how to create your own custom pivot table style, this short video shows the steps. Also, there are step-by-step written instructions on my Contextures site, on the Pivot Table Formatting page.

Better Format for Pivot Table Headings - YouTube

Default and Custom Pivot Table Styles

There are  about 85 built-in pivot table styles in my version of Excel – there might be more than that in your version, or fewer styles.

If you create custom pivot table styles, they’ll be added to that list too. The custom styles also appear at the top of the style gallery.

List All the Pivot Table Styles

To get a list of all the pivot table styles in the active workbook, use the macro that’s shown below. The macro adds a sheet to the workbook, with a list of the pivot table style settings.

The list shows the style name and number, whether it’s built in or custom, the header colour,  and inside border colour.

NOTE: Lots of black is used for the style formatting, so the macro shows a black dot, instead of filling the cell with black. I find that easier to read, and it also saves on printer toner, if you want to print the list!

Macro to List All Pivot Table Styles

This macro to list all Pivot Table styles is in the sample workbook that you can download. There are four other macros too, to list and set the styles.

Or, to use this macro in your own workbook, copy the code below to a regular code module. Then, add a worksheet button to run the macro, or run it from the Macros command on the Excel Ribbon’s View tab.

Sub StylesPTListALL()
Dim wb As Workbook
Dim lStyle As Long
Dim stl As TableStyle
Dim ws As Worksheet
Dim myRow As Long
Dim lClrH As Long
Dim lClrB As Long
Set wb = ActiveWorkbook
Set ws = Sheets.Add
On Error Resume Next

With ws
  .Range(Cells(1, 1), Cells(1, 5)).Value _
    = Array("Style", "Name", "BuiltIn", _
      "Header", "Borders")
End With

myRow = 2

For lStyle = 1 To wb.TableStyles.Count
  Set stl = wb.TableStyles(lStyle)
  If stl.ShowAsAvailablePivotTableStyle = True Then
    ws.Cells(myRow, 1).Value = lStyle
    ws.Cells(myRow, 2).Value = stl.NameLocal
    ws.Cells(myRow, 3).Value = stl.BuiltIn
    
    lClrH = stl.TableStyleElements _
        .Item(xlHeaderRow).Interior.Color
    If lClrH = 0 Then
      ws.Cells(myRow, 4).Value = "•"
    Else
      ws.Cells(myRow, 4).Interior.Color = lClrH
    End If
    
    lClrB = stl.TableStyleElements _
        .Item(xlWholeTable) _
          .Borders(xlInsideHorizontal).Color
    If lClrB = 0 Then
      ws.Cells(myRow, 5).Value = "•"
    Else
      ws.Cells(myRow, 5).Interior.Color = lClrB
    End If
    
    myRow = myRow + 1
  End If
Next lStyle

With ws
  .Range("A1:E1").Font.Bold = True
  .Range("G1").Value = "• = Black"
  .Columns("A:G").EntireColumn.AutoFit
  .Columns("C:E").HorizontalAlignment = xlCenter
  .Columns(6).ColumnWidth = 3.57
  .Range("A1").Select
End With

End Sub
Get the Sample Workbook

To get the sample workbook with pivot tables, custom styles, and more macros, go to the Pivot Table Styles Macros page on my Contextures website.

The zipped file is in xlsm format, and contains macros. Be sure to enable macros when you open the workbook, if you want to test the macros.

Pivot Table Tools

To save time when building, formatting and modifying your pivot tables, use the tools in my Pivot Power Premium add-in. With just a few clicks, you can:

  • copy the formatting from one pivot table, and apply it to another pivot table.
  • change all the values from Count to Sum
  • remove the “Sum of” from all the headings

and much more!

More Pivot Table Resources

Pivot Table Formatting

Classic Pivot Table Format

Copy Pivot Table Formatting

__________________

Read Full Article
Visit website
  • Show original
  • .
  • Share
  • .
  • Favorite
  • .
  • Email
  • .
  • Add Tags 

Do you use Excel to keep track of software subscriptions, or domain registrations, or other things with an expiry date? It’s important to keep track of those dates, so here’s an example of how to monitor expiry dates in Excel, and see what needs to be renewed soon.

Expiry Dates List

Here’s a simple list of expiry dates for Microsoft Office subscriptions. Those have to be renewed every year, so that you don’t arrive at the office one day, and find out that you can’t use Excel. Oh, the horror!

How Many Days to Expiry Date

To make it easy to see which subscriptions are expiring soon, I’ll use conditional formatting to highlight anything that will expire within the next 30 days.

I could make a conditional formatting rule that is based on the Expiry Date column, but I find it easier to check regular numbers, instead of dates.

So, I’ll add a new column, with a formula to calculate the number of days there are before the expiry date. In the screen shot below, you can see that new column – DaysToExpiry.

The formula in that column F subtracts the current date from the Expiry Date: =D3-TODAY()

The results look a bit strange though, when you hit the Enter key, to add that formula.

Fix the Results

Because the formula refers to a date cell, Excel “helps” us, by formatting the result as a date too.

A couple of the rows have expiry dates in the past, and those rows show number signs in the DaysToExpiry column. Excel can’t convert negative numbers to dates, so it shows those number signs instead.

To fix all the results:

  • Click in the heading of the DaysToExpiry column, to select all the table rows.
  • On the Home tab of the Excel Ribbon, choose General or Number as the Number Format.

Highlight Upcoming Expiry Dates

To make the upcoming expiry dates stand out, we’ll use conditional formatting. Based on the result in the DaysToExpiry column, we can highlight the expiry dates which are 30 days (or less) from today.

To add the conditional formatting:

  • Select all the data rows in the expiry date table
  • On the Home tab of the Excel Ribbon, click Conditional Formatting, then click New Rule
  • In the New Formatting Rule window, in the “Select a Rule Type” section, click “Use a formula to determine which cells to format”
  • In the Rule Description section, type this formula in the formula box:
    • =$F3<=30
    • NOTE: The formula uses an absolute reference to column F (DaysToExpiry), and a relative reference for the row number.
  • Click the Format button, and choose a fill colour to highlight the rows with upcoming Expiry dates.
  • Click OK to close the Format window, then click OK to close the New Rule window

The table rows with upcoming expiry dates are highlighted with the colour that you selected.

More Expiry Date Warnings

With conditional formatting on the expiry date list, you’ll quickly see which subscriptions need to be renewed soon. Just open the workbook every day, and renew those subscriptions, as soon as they’re highlighted. Then, remember to change the Expiry Date, so you’ll get a warning next year too.

If you want additional warnings, my sample file also shows how to make a hyperlink appear, in a SendEmail column. You could click that to send an email about the expiring subscription, if someone else is supposed to renew it. Or, have a link to the website where you go to renew the subscription.

Expiry Dates Summary

My sample file also has a Summary sheet — it shows the total count of expiry dates, and the number that are expiring soon.

If you don’t want to open the Expiry Dates workbook every day, you could link those summary cells to another workbook – one that you DO open every day.

Download the Sample File

Get the sample Excel file for this tutorial from my Contextures website. Go to the Excel Sample Files page, and in the Conditional Formatting section, look for CF0008 – Expiry Date Warning. The zipped file is in xlsx format, and does not contain any macros.

And while you’re on the Sample Files page, take a look around. There are lots of other files there – you might find some other interesting techniques to try.

________________________

________________________

Read Full Article
Visit website
  • Show original
  • .
  • Share
  • .
  • Favorite
  • .
  • Email
  • .
  • Add Tags 

Last year, J. Woolley shared his technique to run command files from Excel hyperlinks. He has improved how this works, and now you can get his latest version of the files. It has a SuperLink function too, that is better than Excel’s HYPERLINK function.

Original Run Command Files From Excel

Click here to see the original article, that describes how the first version works, and why J. Woolley created it, to make it easy to run command files from Excel.

New and Improved Version

Since I posted that article, J. Wooley has made significant improvements to this technique. For example:

  • RUN::COMMAND can be included in a hyperlink cell’s text value or in its ScreenTip (or both)
  • Multiple RUN::COMMANDs can be processed for a single hyperlink cell
  • Defining the path for each RUN::COMMAND is now simplified

J. Wooley also developed a new user-defined function (UDF) named SuperLink. This function supports RUN::COMMAND , and it can be used independently too, as a better way to create hyperlinks. (The SuperLink UDF details are described further down in this article.)

Get the Run Command Files

To get started, click this link to get the zipped folder with the Run Command files, and download it to a folder on your computer. RunCommand.zip contains the following files:

  • TestRunCommand.xlsm
  • Simple.vbs
  • Simple.cmd
  • UseRunCommand.pdf
  • UseSuperLink.pdf
  • ExcelBugReport.pdf
  • M_ProcessRunCommand.bas
  • M_SuperLink.bas
Install the Run Command Files

First, before you unzip the file, you will probably need to unblock the downloaded file using its Properties dialog box.

If you don’t unblock the file, the RUN::COMMAND technique might appear to work, but it will not work correctly.

Then, after the downloaded file is unblocked, unzip all files to a single folder. The TestRunCommand.xlsm workbook assumes it is in the same folder as the other files.

Use the Run Command Files

After the files are unzipped, open TestRunCommand.xlsm in Excel; you will probably need to Enable Content for Macros.

  • NOTE: The files named M_ProcessRunCommand.bas and M_SuperLink.bas have already been imported as VBA Modules. in TestRunCommand.xlsm

On the Tests sheet, there is an explanation in cells C1:D6 and there are 3 columns with hyperlinks:

  • B – Standard Hyperlinks
  • C – links created with Excel’s HYPERLINK function
  • D – Standard hyperlinks with RUN::Commands

Use the hyperlinks in column D to test the RUN::COMMAND  technique — the hyperlink text explains what each link does.

Excel HYPERLINK Function Issues

Excel’s HYPERLINK function lets you create links, but it has several shortcomings:

  • It does not create an Excel Hyperlink object.
  • It does not populate the worksheet’s Hyperlinks collection.
  • It does not trigger the worksheet’s FollowHyperlink event.
  • It returns a “shortcut” that looks like a text string but should not be treated as one.
  • A cell using the HYPERLINK function has a default ScreenTip that cannot be changed.
  • The Insert > Link (or Ctrl+K) dialog is not available for a cell containing the HYPERLINK function.
  • If a cell with a Hyperlink object defined using Ctrl+K is edited to add the HYPERLINK function, that HYPERLINK “shortcut” will be ignored in favor of the original Hyperlink object.
SuperLink User Defined Function

The SuperLink UDF, that J. Woolley created, resolves those HYPERLINK function  issues.

On the Test sheet of the TestRunCommand.xlsm workbook, there is a section with examples for the SuperLink function, and an introduction in merged cells A19:D21.

For a detailed explanation of the User Defined Function, read the SuperLink PDF file that is included in the download folder. You can open that file with Test Number 12 link, on the Tests sheet.

To test the SuperLink UDF, use the links that are set up on the Test sheet, in cells B23:D29. Those formulas show how the SuperLink UDF can be used to support application the updated RUN::COMMAND technique.

Questions or Comments

If you have questions or comments about the Run Command Files technique, post in the Comments section below, and J. Woolley will try to help.

_____________________

________________

Read Full Article
Visit website
  • Show original
  • .
  • Share
  • .
  • Favorite
  • .
  • Email
  • .
  • Add Tags 

Welcome to the new home of my Contextures Excel Blog! It used to be part of my main Excel site, but the blog needed more room.  I had a long “to do” list for the blog move, and Excel made it easy to cross off tasks when I finished them.

Smooth Moves

Does any move ever go smoothly? I don’t think so! Something always gets broken, or left behind, or falls off the truck on the way to the new place.

In this move, comments were lost on blog posts from mid-2014 to 2018. The articles from those years didn’t import correctly, so I had to manually upload them from my original files. Thanks, Windows Live Writer, for storing all those articles! I’ll try to get the comments back though, because they have lots of valuable information.

Links from the old blog should automatically redirect to the same article on this new blog, so I hope you find everything that you need. If you run into any problems, please let me know.

Make a To Do List

I couldn’t have made the blog move without Excel though! It’s a great place to make lists, and I had lots of lists.

It’s satisfying to cross off tasks when you’ve finished them, so you can see your progress. That’s easy to do with a pencil, if your list is on paper. But how can you cross off tasks in Excel?

Cross Off Tasks

Here’s a quick trick that will cross off tasks automatically, without any macros. This might slow down a large list, but is handy for small- or medium-sized lists.

In the screen shot below, you can see a simple task list. There’s a due date in column A, and the task name in column B. When the task is completed, put an “x”, or the date, or anything else, in column C.

As soon as a row has something in column C, the text in that row is crossed out, and the font changes to medium grey colour. That makes it easier to focus on the tasks that still need to be finished.

How It Works

To cross off tasks automatically, I set up a simple conditional formatting rule on the entire table. The rule checks column C, in each row, to see if it is not empty – “” is an empty string.

=$C2<>””

The formatting for that rule changes the font to Strikethrough, with grey font colour. None of the other formatting (fill colour or borders), is changed.

Conditional Formatting Examples

There are more Conditional Formatting examples on my website, and this example is in the first file in the download section.

If you’re just getting started with conditional formatting, see the introduction page.

_______________________

_______________________

Read Full Article
Visit website
  • Show original
  • .
  • Share
  • .
  • Favorite
  • .
  • Email
  • .
  • Add Tags 

How can you copy a pivot table custom style to a different workbook? There isn’t a built-in way to do that, but there is a workaround solution. There are instructions that worked in Excel 2013, and earlier versions (way back to Excel 2007). Those stopped working, unfortunately, but there’s an easy way to do this in Excel 2016 too.

Create a Pivot Table Custom Style

Excel comes with many built-in PivotTable Styles, in Light, Medium and Dark colour themes. You can quickly apply any of those styles to the selected pivot table – just open the style palette, and click on the style that you want to apply.

You can’t change those built-in styles, but you can create your own pivot table custom style, based on a built-in style. Then, modify your custom style, with the formatting that you want.

This video shows how to create a custom style from an existing style, and make changes to its formatting.

Better Format for Pivot Table Headings - YouTube

Copy Custom Style in Excel 2013

If you’re using Excel 2013 or earlier, this short video shows how to copy one of your fancy custom styles into a different workbook.

There are written instructions on my website, if you’d rather read than watch.

NOTE: If you’re using Excel 2016, see the instructions in the next section.

Copy a Custom PivotTable Style - YouTube

Copy Custom Style in Excel 2016

Thanks to Annie Cushing from Annielytics.com, who let me know that the old method  to copy a pivot table custom style wasn’t working in Excel 2016.

With a bit of experimenting, I found a new way to copy the custom style. It’s even easier than the old way, and it worked in Excel 2016 and Excel 2013.

  1. Open the workbook (A) with the pivot table that has the custom style applied.
  2. Open the workbook (B) where you want to add that custom style
  3. Position the workbooks, so you can see the sheet tabs in both files
  4. Press the Ctrl key, and drag a copy of the pivot table sheet from workbook A, into the workbook B.
  5. The custom style is now copied into the new workbook, and you can see it in the PivotTable Style palette.
  6. In workbook B, you can delete the sheet that you copied from workbook A.

More Pivot Table Format Tips

For more pivot table formatting tips, go to the Excel Pivot Table Format page on my Contextures site.

There are written instructions and videos, that show how to create and copy PivotTable Styles, keep pivot table formatting, and other tips.

___________________________

____________________________

Read Full Article
Visit website
  • Show original
  • .
  • Share
  • .
  • Favorite
  • .
  • Email
  • .
  • Add Tags 

Aside from staring at them closely, how can you compare two cells in Excel? Here are a few functions and formulas that check the contents of two cells, to see if they are the same. We’ll start with a simple check, then move up the formula ladder, for more complex comparisons.

Easy Way to Compare Two Cells

The quickest way to compare two cells is with a formula that uses the equal sign.

  • =A2=B2

If the cell contents are the same, the result is TRUE. (Upper and lower case versions of the same letter are treated as equal).

Ignore Extra Spaces

If you just want to compare two cells, but aren’t concerned about leading spaces, trailing spaces, or extra spaces, use the TRIM function to remove them, for one or both of the cells.

  • =TRIM(A2)=TRIM(B2)

That can help if you’re trying to match text strings to the values in an imported list, such as this VLOOKUP example.

Compare Two Cells Exactly

If you need to compare two cells for contents and upper/lower case, use the EXACT function. As its name indicate, that function can check for an exact match between text strings, including upper and lower case. It doesn’t test the formatting though, so it won’t detect if one cell has some or all of the characters in bold, and the other cell doesn’t.

  • =EXACT(A2,B2)

See more EXACT function examples in my 30 Excel Functions series.

Partially Compare Two Cells

Sometimes you don’t need a full comparison of two cells – you just need to check the first few characters, or a 3-digit code at the end of a string.

To compare characters at the beginning of the cells, use the LEFT function. For example, check the first 3 characters:

  • =LEFT(A2,3)=LEFT(B2,3)

To compare characters at the end of the cells, use the RIGHT function. For example, check the last 3 characters:

  • =RIGHT(A2,3)=RIGHT(B2,3)

You can combine LEFT or RIGHT with TRIM, if you’re not concerned about the space characters:

  • =RIGHT(TRIM(A2),3)=RIGHT(TRIM(B2),3)

And combine LEFT or RIGHT with EXACT, to check if upper/lower case match too. This formula will ignore extra spaces, but checks the case:

  • =EXACT(RIGHT(TRIM(A2),3),RIGHT(TRIM(B2),3))
How Much Do Cells Match?

Finally, here’s a formula from UniMord, who needs to know how much of a match there is between two cells. Are the first 5 characters the same? The first 10? What percent of the string in A2, starting from the left, is matched in cell B2?

Here’s a sample list, where the addresses in column A and B and being compared.

Get the Text Length

The first step in calculating the percent that the cells match is to find the length of the address in column A. This formula is in cell C2:

  • =LEN(A2)
Get the Match Length

The formula in column D is doing the hard work. It finds how many characters, starting from the left in each cell, are a match. Lower and upper case are not compared.

  • =SUMPRODUCT(
        –(LEFT(A3,
        ROW(INDIRECT(“A1:A” & C3)))
        =LEFT(B3,
        ROW(INDIRECT(“A1:A” &C3)))))
How the Match Len Formula Works

The INDIRECT function creates a reference to a range of cells, starting from cell A1. The range ends in column A, in the row that matches the length calculated in column C. So, in row 2, that range is A1:A9.

The ROW function returns the row for each of the rows in that range. That’s why we use ROW/INDIRECT, instead of just referring to the length in cell C2.

In this screen shot, I’ve used the F9 key to calculate that part of the formula, and you can see the row numbers.

Then, the LEFT functions return the characters that are 1, 2, 3…characters to the left in each cell. In this screen shot, I’ve calculated both of the LEFT functions, and you can see that there is a match for lengths 1 through 9.

However, if I do the same thing in row 5, only the first character is a match. After that, the characters are different in the two cells.

The equal sign compares the values for characters 1 through 5 in this example, and returns TRUE if they match, and FALSE if they do not match.

The double minus sign converts each TRUE to a 1, and each FALSE to a zero.

Finally, the SUMPRODUCT function adds up those numbers, to give the number of characters, from the left, that match. In row 5, that total is 1

Get the Percent Match

Once the length and match length have been calculated, it’s easy to find the percent matched. This formula is in cell E2, to compare the lengths:

  • =D2/C2

There is a 100% match in row 2, and only a 20% match, starting from the left, in row 5.

Thanks, UniMord, for sharing your formula to compare two cells, character by character.

More Ways to Compare Two Cells

Here are a few more articles that show examples of how to compare two cells – either the full content, or partial content.

__________________________

___________________

Read Full Article
Visit website
  • Show original
  • .
  • Share
  • .
  • Favorite
  • .
  • Email
  • .
  • Add Tags 

Have you seen the articles that blame Excel for all kinds of business errors? In some cases, problems occur because rows were hidden, and that distorted the data analysis. To help avoid those problems, I created a sample file that shows an Excel hidden data warning, if rows or columns are hidden.

Help, Not Prevention

This solution should help you spot hidden rows and columns, but don’t depend solely on this when you’re doing critical work.
Use visual checks for filtered or hidden rows, or hidden columns, and remember to look for hidden sheets too.
As always, our goal is to make things idiot resistant, not idiot proof.

Hidden Row Warning

Here’s how I set up the Excel hidden data warnings.
At the top of the sheet, in cell B2, there’s a formula to check for hidden rows. It counts the missing rows, whether they’re hidden manually, or by a filter.
In this screen shot, no filters have been applied to the table, but rows 12:13 were manually hidden, so the formula result is 2.

Hidden Rows Formula

Here’s the formula that’s in cell B2:
=COUNT(Table1[OrderCount]) – AGGREGATE(2,5,Table1[OrderCount])
First, the formula counts all the numbers in the OrderCount column. That column has a simple formula that returns a 1 in each row.
=1
Counting those cells should give an accurate count of the number of rows in the table.
=COUNT(Table1[OrderCount])

Count the Visible Rows

Next, the AGGREGATE function counts the visible cells in the OrderCount column.
AGGREGATE(2,5,Table1[OrderCount])
AGGREGATE uses function type 2 ( COUNT), and is set to ignore hidden rows (option 5).

Number of Hidden Rows

In this example, there are 21 rows in the table (COUNT) and 19 visible rows (AGGREGATE)
To find the number of hidden rows, subtract the visible rows from the total count, and the result is 2 hidden rows.

Hidden Column Warning – Attempt 1

Unfortunately, AGGREGATE doesn’t work for columns, just rows, so how can you tell if columns are hidden?
A hidden column would have zero width, so I used the CELL function to check the cell widths in the top row, cells A1:J1.
=IF(CELL(“width”,A1)>1,1,0)
Then, in cell B3, a formula subtracts the sum of those cells, from the count of the cells.
=COUNT(A1:J1)-SUM(A1:J1)
In theory, that solution works, but the results didn’t automatically update if columns were hidden or unhidden. In this screen shot

  • D and E have been unhidden, but are still showing zeros
  • F, G and H are hidden, but are still calculating as 1
  • The Hidden Columns total shows 2, instead of 3

To see the correct number of hidden columns, you can press F9 to recalculate.

Hidden Column Warning – Attempt 2

An Excel hidden data warning isn’t too helpful, if you have to remember to recalculate.
But, as a conditional formatting rule in cells A1:J1, it seems to work nicely. I’ve only tested in a small file though, so your results might be different.
With cells A1:J1 selected, I created a new formatting rule, using this formula:
=CELL(“width”,B1)=0
A cell turns yellow, if the cell to its right is hidden (0 width).
In this screen shot, columns D:E and H:I are hidden. As a result, cells C1 and G1 have yellow fill colour, based on the conditional formatting rule.

Warning About the Warning

Charles Williams found that conditional formats “are not executed at a calculation unless they are on the visible portion of the screen“.
So, if you try this Excel hidden data warning technique:

  • Be sure to lock the top row.
  • Recalculate too, just to be sure that the correct cells are coloured.
  • Before you do any critical data analysis, do a visual check to see if any rows or columns  or entire sheets are hidden.

Remember, as the old saying goes, it’s better to be safe, than to read about your catastrophic errors on the internet.

Download the Excel Hidden Data Warning File

To download the Excel hidden data warning workbook, go to the Conditional Formatting Examples page on my Contextures site. In the Download section, click on the link for the Hidden Data Warning sample.
The zipped file is in xlsx format, and does not contain any macros.
________________

Excel Hidden Data Warning


________________________

Read Full Article
Visit website
  • Show original
  • .
  • Share
  • .
  • Favorite
  • .
  • Email
  • .
  • Add Tags 

As you know, Excel can do almost anything! Recently, I changed the server that my site and blog are on, and Excel helped with the IP Addresses. And since it’s that time of year, let’s see how Excel can calculate Easter dates for us.

Easter Dates Calculation

It’s Easter this Sunday, but if you’d like to verify that, or find out when Easter will be next year, use one of the Easter date calculations that Jerry Latham shared here, a few years ago.

  • There are several worksheet formulas, and notes on their limitations.
  • There are also four User Defined Functions, that use different methods for calculating the Easter date.

In the screen shot below, the following formula is in cell B4:

=DOLLAR((“4/”&B2)/7+MOD(19*MOD(B2,19)-7,30)*14%,)*7-6

You can download a workbook the code and formulas, from my Contextures website.

Excel and IP Addresses

As I mentioned earlier, I recently had my Contextures site and this blog moved to a different server. I hope that it is faster, and has minimal down time.

The only casualty that I’ve found so far is the comment section on this blog. Unfortunately, comments that were posted during the loooong transition time (March 14th to March 25th), were lost in the shuffle.

Other than that, things seem to be okay, but please let me know if you notice anything missing, or broken.

IP Address Conversion

One of the key steps for moving to a new servers was to create DNS records for the them.

The form had boxes for the IPv4 address and the IPv6 address. Uh-oh!

Fortunately, I found examples of the different formats.

  • IPv4: 192.168.99.1
  • IPv6: 0:0:0:0:0:ffff:c0a8:6301

To get that IPv6 address, I used an online converter.

How Are IPs Converted?

Looking at those two IP addresses, I couldn’t see how one was converted to the other. More Googling took me to this page that explains the conversion.

  • Each chunk of the IPv4 address is converted from decimal to hex, to get the IPv6 version.
  • The hex version each converted chunk becomes 2 characters
  • Those 8 characters are at the end of the IPv6 address.

When I used the online conversion tool, all the IPv6 addresses started with the same string:

  • 0:0:0:0:0:ffff:

According to Wikipedia, that is the prefix used when PIv4 is mapped to an IPv6 address

Convert the IP Addresses in Excel

Fortunately, my registrar said that the IPv6 version wasn’t required, so I just entered my IPv4 addresses.

But even though I didn’t need one, why not build an IPv4 to IPv6 converter in Excel? It can convert Decimal to Hex, and it’s better than an online tool! Am I right?

Here’s a screen shot of the first few columns on the conversion sheet.

NOTE: This project was just for fun, and might not be accurate for what you need. If you need an IPv4 address converted to IPv6 format, check with your registrar or your hosting company.

Conversion Formulas

There are 3 sets of formulas for the conversion, and a final formula to pull the pieces together.

Dots

These formulas locate the 3 dots in the IPv4 address

  1. =FIND(“.”,[@IPv4])
  2. =FIND(“.”,[@IPv4],[@Dot01]+1)
  3. =FIND(“.”,[@IPv4],[@Dot02]+1)

IP Numbers

These formulas to pull out the decimal numbers, between the dots

  1. =–LEFT([@IPv4],[@Dot01]-1)
  2. =–MID([@IPv4],[@Dot01]+1,[@Dot02]-[@Dot01]-1)
  3. =–MID([@IPv4],[@Dot02]+1,[@Dot03]-[@Dot02]-1)
  4. =–REPLACE([@IPv4],1,[@Dot03],””)

Hex

These formulas convert each decimal number to hex

  1. =TEXT(DEC2HEX([@IP01]),”00″)
  2. =TEXT(DEC2HEX([@IP02]),”00″)
  3. =TEXT(DEC2HEX([@IP03]),”00″)
  4. =TEXT(DEC2HEX([@IP04]),”00″)

IPv6 Address

The formula in column B combines all the pieces, and starts with the mapping prefix (MapPre). The result is changed to lower case

  1. =LOWER(MapPre &[Hex01]&[Hex02]&”:”&[Hex03]&[Hex04])

Convert from IPv6 to IPv4

Then, because why not, I made another set of formulas to convert IPv6 addresses to IPv4.

This was easier, because each chunk is equal length – there’s no need to find the location of each chunk.

These formulas use the HEX2DEC function, to convert the 2-digit hex codes to decimal numbers. You can download the sample file, to see the formulas.

Get the IP Addresses Sample File

To see all the formulas for the IP address conversion, go to the Excel Sample Files page on my Contextures site.

In the Functions section, look for FN0054 – Convert IP Addresses IPv4 to IPv6. The zipped file is in xlsx format, and does not contain any macros.

____________________

______________________

Read Full Article
Visit website

Read for later

Articles marked as Favorite are saved for later viewing.
close
  • Show original
  • .
  • Share
  • .
  • Favorite
  • .
  • Email
  • .
  • Add Tags 

Separate tags by commas
To access this feature, please upgrade your account.
Start your free month
Free Preview