MrExcel Publishing
Your One Stop for Excel Tips & Solutions

UNIQUE From Non-Adjacent Columns

November 15, 2018 - by Bill Jelen

UNIQUE From Non-Adjacent Columns

The other day, I was about to create a unique combination of two non-adjacent columns in Excel. I usually do this with Remove Duplicates or with Advanced Filter, but I thought I would try to do it with the new UNIQUE function coming to Office 365 in 2019. I tried several ideas and none would work. So, I went to the master of Dynamic Arrays, Joe McDaid, for assistance. The answer is pretty cool, and I am sure I will forget it, so I am documenting it for you and for me. I am sure, two years from now, I will Google how to do this and realize "Oh, look! I am the one who wrote the article about this!"

Before getting to the UNIQUE function, take a look at what I am trying to do. I want every unique combination of Sales Rep from column B and Product from column C. Normally, I would follow these steps:

  1. Copy the headings from B1 and D1 to a blank section of the worksheet
  2. From B1, choose Data, Filter, Advanced
  3. In the Advanced Filter dialog, choose Copy To A New Location
  4. Specify the headings from Step 1 as the Output range
  5. Choose the box for Unique Values Only
  6. Click OK
Copy the two headings to a blank section that becomes the output range
Copy the two headings to a blank section that becomes the output range

The result is every unique combination of the two fields. Note that the Advanced Filter does not sort the items - they appear in the original sequence.

continue reading »

More Reading

Find Latitude and Longitude for Each City in Excel

Find Latitude and Longitude for Each City in Excel »

November 9, 2018 - by Bill Jelen

Say that you have a list of cities in Excel and need to know the latitude and longitude for each city. A new Geography Data Type feature coming to Office 365 will make this easy. Check the Data tab in the Excel ribbon. Do you have a new Data Type category with Stocks and Geography?

Stock Data Types in Excel

Stock Data Types in Excel »

November 7, 2018 - by Bill Jelen

Microsoft is adding Stock and Geography data types to Excel. While I covered geography data types on Monday, today is a discussion of stock data types. This feature is only available to people with an Office 365 subscription - it will never be included if you purchase Excel 2019, or Excel 2016 or Excel 2013.

read more articles »

IMA Data Analytics Leveraging Excel

Featured Products

Power Pivot and Power BI

Power Pivot and Power BI »

Power Pivot for Excel and its close cousin Power BI Desktop are Microsoft’s tightly-related pair of revolutionary analytical tools – tools that are fundamentally changing the way organizations work with data.

Excel Dynamic Arrays Straight to the Point

Excel Dynamic Arrays Straight to the Point »

The new Dynamic Array Functions are just one side-effect of an effort to completely rewrite the Calculation Engine in Excel. Joe McDaid and the rest of the CALC team have the laid the groundwork for all future functions in Excel. Yes, the first crop of SORT, SORTBY, FILTER, UNIQUE, SEQUENCE and RANDARRAY are awesome and powerful, but they are just the first of many new functions that will come to Office 365 over the coming years.

Power Excel With MrExcel - 2017 Edition

Power Excel With MrExcel - 2017 Edition »

This is the print book edition of "Power Excel with MrExcel - 2017 Edition" - by Bill Jelen. Master Pivot Tables, Subtotals, Visualizations, VLOOKUP, Power BI and Data Analysis.

Pivot Table Data Crunching: Microsoft Excel 2016

Pivot Table Data Crunching: Microsoft Excel 2016 »

Use Excel 2016 pivot tables and pivot charts to produce powerful, dynamic reports in minutes instead of hours… understand exactly what’s going on in your business… take control, and stay in control! Even if you’ve never created a pivot table before, this book will help you leverage all their amazing flexibility and analytical power.

100 Excel Simulations

100 Excel Simulations »

Covering a variety of Excel simulations, from gambling to genetics, this introduction is for people interested in modeling future events, without the cost of an expensive textbook.

Excel JavaScript UDFs Straight to the Point

Excel JavaScript UDFs Straight to the Point »

JavaScript custom functions - UDFs can be used like any other native functions or UDFs in Excel. This book shows the process of creating JavaScript UDFs in Excel Developer Preview for Office Insider program subscribers.

see more products »