merging 2 spreadsheets with different rows

Question

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 Spreadsheet 1.

Each one has client IDs and the task we do for that client. Spreadsheet 2 has a rating column for those client IDs/tasks. Spreadsheet 1 has the column but it is blank for all. Spreadsheet 1 also has additional rows in it for tasks that do not have a rating. Those should be left blank in the final spreadsheet.

How can I merge these 2 spreadsheets to get the info of the missing column info into the final spreadsheet?

oliverscheurich · Answer

Connie64131&nbsp;=IFERROR(INDEX($H$3:$H$12,MATCH(1,(A3=$F$3:$F$12)*(B3=$G$3:$G$12),0)),"")You can apply this formula for the sample data. The formula can be adapted to the actual size of the data and additional columns don't interfere with the formula. Enter&nbsp;the formula with ctrl+shift+enter if you don't work with Office 365 or Excel for the web or Excel 2021.&nbsp;

connie64131 · Answer

OliverScheurich&nbsp;&nbsp;Where do I put that formula at?

oliverscheurich · Answer

Connie64131&nbsp;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.

connie64131 · Answer

OliverScheurich&nbsp;&nbsp;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.&nbsp;&nbsp;

oliverscheurich · Answer

Connie64131&nbsp;=IFERROR(INDEX(Table1[Complexitiy level],MATCH(1,([@[Client Number]]=Table1[Client Number])*([@[Task Type Name]]=Table1[Task Type Name]),0)),"")&nbsp;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&nbsp;"Complexity level" column which is an advantage of working with dynamic tables.

Forum Discussion

merging 2 spreadsheets with different rows

Share

Resources