I have a Google Sheets document containing three tabs and I need assistance in merging information from two of the tabs into a summary tab.
The first tab includes sales data with the following format:
| Week Number |
Item Name |
Sold Quantity |
| 1 |
Item A |
10 |
| 1 |
Item B |
15 |
| 2 |
Item A |
8 |
The second tab features repair activities, structured like this:
| Week Number |
Item Name |
Repair Done |
| 1 |
Item A |
Fixed Gear |
| 1 |
Item A |
Replaced Screw |
| 1 |
Item A |
Adjusted Belt |
| 1 |
Item B |
Replaced Motor |
| 1 |
Item B |
Lubricated Chain |
| 2 |
Item A |
Fixed Panel |
| 2 |
Item A |
Tightened Bolt |
I want the third tab to present data in this way:
| Week Number |
Item Name |
Sold Quantity |
Repair Done |
| 1 |
Item A |
10 |
Fixed Gear |
| - |
- |
- |
Adjusted Belt |
| 1 |
Item B |
15 |
Replaced Motor |
| 2 |
Item A |
8 |
Fixed Panel |
I have tried using FILTER, UNIQUE, and VLOOKUP but haven’t been successful. Any recommendations on how to achieve the desired result?
I had this exact problem recently. IMPORTRANGE plus a custom script worked way better than trying to force spreadsheet functions to handle it. Since you’ve got multiple repairs per item/week, I’d write an Apps Script function that loops through both datasets and builds the summary table row by row. The script can show sales quantities on the first repair entry for each item/week combo, then just put dashes for the rest. Way cleaner than wrestling with standard functions, and it handles that one-to-many relationship between sales and repairs much better.
try ARRAYFORMULA with VLOOKUP. put your sales data first, then use another ARRAYFORMULA to pull matching repair records. you’ll probably need to adjust the ranges, but it beats doing individual lookups.
To consolidate data from your sales and repair tabs in Google Sheets, the QUERY function is highly effective. Start by adding a helper column in your repair activities tab to concatenate ‘Week Number’ and ‘Item Name.’ This permits easier reference when merging with the sales data. Utilize QUERY to extract unique sales entries, and then run a second QUERY to compile repair activities by item and week. This approach maintains clear visibility of sold quantities alongside the corresponding repairs, despite the complexity of the formulas.