google-sheetsspreadsheet
Find the list of different values in 2 tables by id key
I have 2 tables with the same IDs but different values for some cells. I want to find the different values on the new tab in the following format: Id, sheet1_field, sheet2_field, sheet1_data, sheet2_data.
What is the best way to do this?
Please find the example and the expected result in the following spreadsheet - https://docs.google.com/spreadsheets/d/1W9aLJZoypoFT7xyKdpk1rXhTL1BVruZpbpoPhzKswmU/edit?gid=1041107819#gid=1041107819
Solution
You may try:
=let(Λ,tocol(,1), reduce(Λ,Sheet1!B2:index(Sheet1!E:E,match(,0/(Sheet1!A:A<>""))),lambda(a,x,vstack(if(iserr(+a),Λ,a),let(r,row(x),c,column(x),z,index(Sheet1!A:A,r),
Σ,vlookup(z,choosecols(Sheet2!A:E,1,c),2,),if(x=Σ,Λ,hstack(z,index(Sheet1!1:1,c),index(Sheet2!1:1,c),x,Σ)))))))
- Can we hide column in sheet using google apps script?
- JavaScript function in Google spreadsheets to send QR codes to emails
- Google Sheet Concatenate + Filter
- Google Sheets QUERY is returning with a no column found errors
- Get eventID for Google Calendar Event
- How can I find the number of concordant/discordant pairs for a given cell in Google Sheets?
- COUNTIF Statements: Range Across All Sheets + Cell Reference as Criterion
- Writing a googlefinance wrapper
- Getting a macro to run using checkbox in google sheets
- Auto expanding formula in google sheets to the length of a dynamic range
- Issue in Auto close Modal Dialog - Google App-Script
- CSV extracted automatically into a sheet. Need help for filtering
- How can I sum top 5 values from column in Google Sheets
- Remove duplicates based on one column and keep latest entry in Google sheets (while ignoring blank entries in that column)
- Find the list of different values in 2 tables by id key
- VAT Checker Apps Script
- Script to lock a different tab sheet everyday
- Trigger python code from Google spreadsheets?
- Function to convert Google Sheet Table into one row
- Google Sheet Script 2 on edit function #NUM! error
- Google Sheet SUMIF returning an error! statement
- Same code behaving differently inside and outside function
- How to use VLOOKUP() + QUERY() + IMPORTRANGE() with multiple conditions?
- How to find value vertically and horizontally, using formula in Sheets?
- Return SUM of all cells that fit TWO CRITERIA related by POSITION
- Sum column until first empty cell
- Extract Data from Multiple Sheets with Similar Names
- How many time based trigger can your add-on run on a document / user?
- ARRAYFORMULA option for checking for cell values on each row of Google Sheet
- Filter a range by array