Combining Unique Names from Multiple Lists with Copilot in Excel

Posted on:  02/08/2025

Introduction to Copilot in Excel

Excel continues to evolve, and one of its most exciting new features is Copilot. This AI-powered assistant is revolutionizing the way we work with data, making complex tasks simpler and more intuitive. Today, we're going to explore how Copilot can help us combine unique names from multiple lists, a task that's often needed but can be tricky to execute manually.

I have two lists of names in front of me, and as you might expect, there's some overlap between them. While previously, we looked at finding unique names in one list that weren't in the other, today's challenge is different. We need to combine these lists, but we only want each name to appear once in our final list. In other words, we're aiming for a consolidated list of unique names.

Setting Up the Tables

Before we dive into using Copilot, let's take a quick look at our setup. I've prepared two columns of names and named them as tables to make our work easier.

• Column A is named "table1"
• Column B is named "table2"

Naming your tables isn't just for organization—it can make your formulas cleaner and easier to understand, especially when working with Copilot.

Combining Unique Names with Copilot

Now, let's see if Copilot can help us with our task. Here's what we're going to do:

1. Go to the Home tab
2. Click on Copilot
3. Ask Copilot to combine the unique names from table1 and table2 into a new range or table

After entering our request, Copilot processes it and comes back with a response. Interestingly, while Copilot can't directly perform the action for us at this time, it does provide us with the correct formula to achieve our goal. This is a perfect example of how Copilot can guide us even when it can't complete the task autonomously.

Understanding the Copilot-Suggested Formula

The formula Copilot suggests uses two powerful Excel functions: VSTACK and UNIQUE. Let's break it down:

=UNIQUE(VSTACK(table1,table2))

This formula does exactly what we need:

• VSTACK combines the two tables vertically
• UNIQUE then filters out any duplicate entries

It's a concise and elegant solution to our problem. Even if you're not familiar with these functions, Copilot has done the hard work of figuring out the correct approach.

Manual Method Using VSTACK and UNIQUE

For those who want to understand the process better or need to do this manually, let's walk through the steps:

1. Select cell E2 (or any empty cell where you want your results)
2. Enter the formula: =UNIQUE(VSTACK(table1,table2))
3. Press Enter, and Excel will populate the unique, combined list

To illustrate how VSTACK works on its own, let's try it separately:

1. In cell F3, enter: =VSTACK(table1,table2)
2. Press Enter

You'll notice that VSTACK returns everything from both tables, including duplicates. For instance, you might see "Chris Menard" twice in this list. This is where the UNIQUE function comes in handy, removing those duplicates to give us our desired result.

The Power of Copilot in Excel

What's truly impressive here is how Copilot provided us with the correct function using UNIQUE and VSTACK. Even for experienced Excel users, remembering every function and its perfect application can be challenging. Copilot bridges that gap, offering solutions that might not be immediately obvious to us.

This showcase of Copilot's capabilities is just the tip of the iceberg. It's becoming an invaluable tool for Excel users of all levels, from beginners to advanced. By suggesting formulas and approaches, Copilot is not just solving problems but also teaching us new ways to work with Excel.

Upcoming Live Stream: Copilot in Excel

If you're excited about Copilot and want to learn more, I've got great news! On February 21st at noon Eastern time, I'll be hosting a live stream focused entirely on Copilot in Excel. This session will be streamed live on both YouTube and LinkedIn, giving you multiple ways to tune in and learn.

During this live stream, we'll dive deeper into Copilot's capabilities, explore more complex scenarios, and I'll be there to answer your questions in real-time. Whether you're just getting started with Copilot or looking to push its limits, this session will have something for everyone.

Categories

• Blog » Microsoft » Microsoft Copilot