Forum Discussion
merging 2 spreadsheets with different rows
I have 2 spreadsheets. Spreadsheet 1 has a lot of data (35 columns and 1600+ rows). Spreadsheet 2 does not have as much data (4 columns and 1400+ rows) but one of the columns needs to be merged into ...
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?