Consolidate in Excel: Merge multiple sheets into one - Free Printable

Problem Description:

The task involves updating a Main table with data from a Lookup table. The updates include:
1. Updating existing items in the Main table with new prices and delivery statuses from the Lookup table.
2. Adding new items from the Lookup table that are not present in the Main table.

Solution Approach:

To solve this problem, we will follow these steps:

#### Step 1: Identify Existing Items
- Compare the `Item` column in the Main table with the `Item` column in the Lookup table.
- For items that exist in both tables, update their `Price` and `Delivery` status in the Main table based on the Lookup table.

#### Step 2: Add New Items
- Identify items in the Lookup table that are not present in the Main table.
- Append these new items to the Main table, including their `Price` and `Delivery` status.

#### Step 3: Implement the Solution
We will use Excel formulas or VBA (Visual Basic for Applications) to automate this process. Here, I will explain how to achieve this using Excel formulas.

---

Excel Formula-Based Solution

#### Step 1: Update Existing Items
1. Add a Helper Column for Matching Items:
- In the Main table, add a helper column (e.g., Column D) to check if an item exists in the Lookup table.
- Use the `VLOOKUP` function to find the corresponding `New price` and `Delivery` status for each item.

Formula for Column D (Helper Column):
```excel
=IFERROR(VLOOKUP(A2, LookupTable!A:B, 2, FALSE), "")
```
This formula looks up the item in the Lookup table and retrieves the `New price`.

2. Update Prices:
- In the `Price` column of the Main table (Column B), use the following formula to replace the old price with the new price if available:
```excel
=IF(D2<>"", D2, B2)
```
This formula checks if a new price is available in the helper column. If it is, it uses the new price; otherwise, it retains the old price.

3. Add Delivery Status:
- Add a new column (`Delivery`) in the Main table (Column C).
- Use the `VLOOKUP` function to retrieve the `Delivery` status for each item:
```excel
=IFERROR(VLOOKUP(A2, LookupTable!A:C, 3, FALSE), "")
```

#### Step 2: Add New Items
1. Identify New Items:
- Use the `COUNTIF` function to check if an item in the Lookup table exists in the Main table.
- If an item does not exist, append it to the Main table.

2. Append New Items:
- Manually or programmatically append the new items from the Lookup table to the Main table. Ensure that the new items are added below the existing items.

---

Implementation Example

#### Main Table (Before Updates):
| Item | Price |
|-------------|-------|
| Sweets | $2.50 |
| Biscuits | $3.00 |
| Ice-cream | $0.80 |
| Cakes | $4.20 |
| Croissants | $2.90 |
| Appie pies | $1.80 |
| Doughnuts | $0.70 |

#### Lookup Table:
| Item | New price | Delivery |
|-------------|-----------|------------|
| Sweets | $2.60 | Delivered |
| Strawberry | $5.50 | In transit |
| Honeyberry | $11.90 | Past due |
| Croissants | $3.00 | In transit |
| Doughnuts | $0.80 | Delivered |
| Appie pies | $1.50 | Past due |
| Biscuits | $2.90 | In transit |

#### Steps to Update:
1. Update Existing Items:
- For `Sweets`, update the price to `$2.60` and add `Delivered` in the `Delivery` column.
- For `Biscuits`, update the price to `$2.90` and add `In transit` in the `Delivery` column.
- For `Croissants`, update the price to `$3.00` and add `In transit` in the `Delivery` column.
- For `Doughnuts`, update the price to `$0.80` and add `Delivered` in the `Delivery` column.
- For `Appie pies`, update the price to `$1.50` and add `Past due` in the `Delivery` column.

2. Add New Items:
- Add `Strawberry` with price `$5.50` and `In transit` in the `Delivery` column.
- Add `Honeyberry` with price `$11.90` and `Past due` in the `Delivery` column.

#### Main Table (After Updates):
| Item | Price | Delivery |
|-------------|-------|------------|
| Sweets | $2.60 | Delivered |
| Biscuits | $2.90 | In transit |
| Ice-cream | $0.80 | |
| Cakes | $4.20 | |
| Croissants | $3.00 | In transit |
| Appie pies | $1.50 | Past due |
| Doughnuts | $0.80 | Delivered |
| Strawberry | $5.50 | In transit |
| Honeyberry | $11.90| Past due |

---

Final Answer

The updated Main table is as follows:

| Item | Price | Delivery |
|-------------|-------|------------|
| Sweets | $2.60 | Delivered |
| Biscuits | $2.90 | In transit |
| Ice-cream | $0.80 | |
| Cakes | $4.20 | |
| Croissants | $3.00 | In transit |
| Appie pies | $1.50 | Past due |
| Doughnuts | $0.80 | Delivered |
| Strawberry | $5.50 | In transit |
| Honeyberry | $11.90| Past due |

Boxed Final Answer:
\[
\boxed{
\begin{array}{|c|c|c|}
\hline
\text{Item} & \text{Price} & \text{Delivery} \\
\hline
\text{Sweets} & \$2.60 & \text{Delivered} \\
\text{Biscuits} & \$2.90 & \text{In transit} \\
\text{Ice-cream} & \$0.80 & \text{} \\
\text{Cakes} & \$4.20 & \text{} \\
\text{Croissants} & \$3.00 & \text{In transit} \\
\text{Appie pies} & \$1.50 & \text{Past due} \\
\text{Doughnuts} & \$0.80 & \text{Delivered} \\
\text{Strawberry} & \$5.50 & \text{In transit} \\
\text{Honeyberry} & \$11.90 & \text{Past due} \\
\hline
\end{array}
}
\]