Forum Discussion
merging 2 spreadsheets with different rows
Can you attach screenshots of your worksheets (which show all relevant information) or your file both without sensitive data? This way i can make a suggestion on how to adapt the formula to your actual database.
As you can see the client name, client number, task type name are the same on both spreadsheets but the first spreadsheets has a lot of additional columns. The complexity level is the same but that is what needs to be inputted from the second spreadsheet into the first spreadsheet. Unfortunately, the first spreadsheet has additional clients listed that are new this year so it is not a simple copy and paste matter. The client number and task type name have to match from the first and second spreadsheet and then add the complexity level. I hope this makes sense. I am not a beginner at Excel but I also am not advanced enough to know how to do this and where to put the formulas to make this occur.
=IFERROR(INDEX(Table1[Complexitiy level],MATCH(1,([@[Client Number]]=Table1[Client Number])*([@[Task Type Name]]=Table1[Task Type Name]),0)),"")
I've made a small sample file. The formula is in the first row of the "Complexity level" column of the first spreadsheet. The formula is dynamically filled down the "Complexity level" column which is an advantage of working with dynamic tables.
- I have to put my tables into this equation to link it. which part of this equation =IFERROR(INDEX(Table1[Complexitiy level],MATCH(1,([@[Client Number]]=Table1[Client Number])*([@[Task Type Name]]=Table1[Task Type Name]),0)),"") is from table 2 where the info is at? Is it the MATCH?
=IFERROR(INDEX(Table1[Complexity level],MATCH(1,([@[Client Number]]=Table1[Client Number])*([@[Task Type Name]]=Table1[Task Type Name]),0)),"")
All information from Table1 (highlighted in red) is from the second spreadsheet from where the complexity level is pulled.
If the name of your table in the second spreadsheet is "SecondSheetTable" then this should work:
=IFERROR(INDEX(SecondSheetTable[Complexity level],MATCH(1,([@[Client Number]]=SecondSheetTable[Client Number])*([@[Task Type Name]]=SecondSheetTable[Task Type Name]),0)),"")
If you don't work with Office 365 or Excel for the web or Excel 2021 you'll have to enter the formula as an arrayformula by pressing ctrl+shift+enter.
