Google sheets: How to merge two Google sheets with duplicate keys?
I have two sheets, each containing a table with duplicate keys. I want to create a third sheet that merges these two tables such that each row represents a cross-product of the data for each key.
Here's an example of my data:
Sheet1:
| Key | Data |
|---|---|
| A | 1 |
| B | 2 |
| C | 3 |
| A | 4 |
| D | 5 |
Sheet2:
| Key | Data |
|---|---|
| B | 6 |
| C | 7 |
| D | 8 |
| A | 9 |
| A | 10 |
I want to create a third sheet that looks like this:
Merged:
| Key | Data (Sheet1) | Data (Sheet2) |
|---|---|---|
| A | 1 | 9 |
| A | 1 | 10 |
| A | 4 | 9 |
| A | 4 | 10 |
| B | 2 | 6 |
| C | 3 | 7 |
| D | 5 | 8 |
How can I achieve this? Thanks in advance
I have been trying an INDEX and MATCH combination, but given that keys are duplicated I only obtain the first value.
Solution
I put everything in one sheet, so it's more easily viewable, please adapt the range so it represents the two sheets:
=LET(keys,TOCOL(UNIQUE(A2:A),1),
REDUCE(HSTACK("Key","Data1","Data2"),keys,LAMBDA(a,key,
VSTACK(a,REDUCE(TOCOL(,1),FILTER(B2:B,A2:A=key),LAMBDA(b,each1,
VSTACK(b,BYROW(FILTER(E2:E,D2:D=key),LAMBDA(each2, HSTACK(key,each1,each2))))))))))
- Allow script to edit locked cells
- How to join unique strings in their own column with QUERY?
- How do I use XLOOKUP for multiple criteria?
- How to use AppendRow
- How to code "Next Friday" in Google Sheets?
- How can I resolve this `Error: You must select all cells in a merged range to merge or unmerge them.` while merging cells through App Script?
- Casting method asTextItem throws error for Google form item
- Counting blank cells by arrayformula
- ARRAYFORMULA with GOOGLEFINANCE function
- Conditional formatting depending on two cells
- How do I find the highest sum of multiple cells in column A where the sum is based on multiple cells matching in column B in Google Sheets?
- Returning the last input entry
- Draw and filter Charts in App Script Web app
- Google Sheets - select "most recent " cell in a column and return corresponding value in same row
- Byrow formula with reference to last row and last column
- How Do I Give rows in a table of contents a key. Then look up the key Later using the data from the rows in table of contents?
- Duplicate whole spreadsheet using Google Sheets API
- Array Formula to omit certain values
- Google Sheets Query While Removing Duplicates
- NAME error hiperlink function Apps Script Google Sheets
- Three-dimensional referencing
- Can I use a named range as column headers?
- Trying to get Google App Script Code to Acknowledge Cells In A Column in Excel to do Individual Math
- How to get the price of a stock at a particular date from Google Finance?
- How to Split a String Up into Individual Characters
- How to integrating Google Sign-In into your google script web app
- Formula is currently double-counting a cell
- Get dropdown options in google sheets in table
- Search for targets and indicate where they come from
- One-to-one assignment in Google sheets with data validation