How to bring together data from two lists in excel when some categories have been combined in one of the lists

2 min read 25-10-2024

the ifix

How to bring together data from two lists in excel when some categories have been combined in one of the lists

When working with data in Excel, you may come across situations where you have two separate lists that you need to merge. However, one of the lists contains combined categories, making it challenging to consolidate the data effectively. This article will walk you through the process of bringing together data from these two lists while addressing the issue of merged categories.

Understanding the Problem

Imagine you have two lists of products. The first list contains individual product categories, while the second list has some categories combined. For example:

Original Code for the Problem

List A (Individual Categories):

| Product    | Category    |
|------------|-------------|
| Apple      | Fruit       |
| Carrot     | Vegetable   |
| Banana     | Fruit       |
| Lettuce    | Vegetable   |

List B (Combined Categories):

| Product    | Combined Category     |
|------------|-----------------------|
| Apple      | Fruits & Vegetables    |
| Carrot     | Fruits & Vegetables    |
| Spinach    | Vegetables             |

In this case, the issue arises because the first list uses distinct categories ("Fruit" and "Vegetable"), while the second list combines some of these categories into broader ones (e.g., "Fruits & Vegetables").

Steps to Combine the Data

Step 1: Identify Common Ground

The first step in merging the two lists is to identify how the combined categories map to individual categories. For instance:

"Fruits & Vegetables" includes both "Fruit" and "Vegetable".

Step 2: Create a Mapping Table

To efficiently merge these lists, create a new mapping table that clearly defines how the categories relate to one another.

Combined Category	Individual Categories
Fruits & Vegetables	Fruit, Vegetable

Step 3: Use Excel Functions

Now, you can use Excel functions like VLOOKUP or INDEX-MATCH to combine the data effectively.

Using VLOOKUP: Add a new column in List B for individual categories. You will use the VLOOKUP function to pull in categories from List A.
```
=VLOOKUP(A2, ListA!A:B, 2, FALSE)
```
Using INDEX-MATCH (more robust): In List B, use the following formula:
```
=INDEX(ListA!B:B, MATCH(A2, ListA!A:A, 0))
```

Step 4: Consolidate Data

Once you have the individual categories next to the combined categories, you can easily consolidate and analyze the data.

Practical Example

Imagine you want to summarize sales data based on product categories. By combining the two lists, you can create a pivot table that shows total sales per individual category, providing insights into how both combined and individual categories perform.

Example Pivot Table Setup

Create a new worksheet and select the merged data.
Go to Insert > PivotTable.
Set "Individual Categories" as rows and "Sales" as values to summarize data.

Conclusion

Combining data from two lists in Excel, especially when dealing with merged categories, is manageable with the right approach. By mapping the categories, utilizing Excel functions, and creating a summary with pivot tables, you can gain valuable insights from your data.

Useful Resources

By following this guide, you should be well-equipped to handle the task of merging lists in Excel, even when facing the challenge of combined categories. Happy Excel-ing!