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

Learn to Write DAX

Learn to Write DAX »

Data analysis expressions (DAX) is the formula language of PowerPivot and Power BI. Simply reading a book is normally not enough to help Excel users learn DAX skills – you need to complete exercises and get plenty of practice to make the transition.

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.

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.

Excel Video Medley

Excel Video Medley »

Excel Video Medley covers everything from Pivot Tables to VLOOKUP to VBA. Learn how to create scientific graphs. Use Excel for statistics.

Supercharge Excel When You Learn to Write DAX For Power Pivot

Supercharge Excel When You Learn to Write DAX For Power Pivot »

Hands-on book to learn and master the DAX language!

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.

see more products »