CODEWITHSUNDEEP
excel-formula
Wicky Memon
Wicky Memon

Reputation: 89

How to merge data of two excel sheets into the third sheet with some cleansing operations

I have a homework assignment where I have to merge data of two excel sheets by performing some cleansing operations using formulas.

Sheet 1:

OrderID | Full Name | Customer Status

1001 Waqar Hussain Silver

2002 Ali Moin Gold

Sheet 2:

OrderID | First Name | Last Name | Customer Status

A1003 Junaid Ali 2

A2004 Kamran Hussain 1

Sheet 3:(Combined Sheet) - Expected

OrderID | Full Name | Customer Status

1001 Waqar Hussain Silver

2002 Ali Moin Gold

1003 Junaid Ali Silver

2004 Kamran Hussain Gold

Upvotes: 0

Views: 280

Answers (1)

Brett B
Brett B

Reputation: 36

There are probably a lot of ways to do this. First make sure the data is cleaned. If you are already 100% positive the data is clean you can skip this step. If you aren't sure it's better to be safe than sorry. For each column create a new column using the CLEAN and TRIM functions to remove any non-printable characters and any extra spaces. Something similar to =TRIM(CLEAN(A2)). Then drag the formula for each cell.

After this in order to merge the data together we need something to join on. The full name seems to make the most sense. On sheet two we'll write a new function to join the first name and last name together. The =CONCAT formula should work. =CONCAT(First Name, " " ,Last Name). Make sure to note the extra space added by the quote. That way it matches the Full Name from Sheet 1. Looks like we'll also need to strip out the letter from Order ID in sheet 2. I'm going to assume that all Order IDs are 5 characters long. If this isn't true then you'll need a different solution. You can use =RIGHT(A2,4). This will grab the right 4 characters from the text string.

At this point let's create a distinct list. Copy the Full Names from Sheet1 and Paste them on to sheet 3. Copy the Full Names we created on Sheet2 and Paste VALUES onto sheet 3 below the full names from sheet 1. Then select all the rows in the column and go to the Data tab. Click "Remove Duplicates". This will now generate a distinct list of values.

We can now merge the data together using an INDEX MATCH. There are lots of great tutorials on how to use INDEX match in combination. It's a little long to explain on this thread, but this is a great thread explaining how it works. It's worth taking 10 minutes to fully understand it because it is a formula you will use thousands of times throughout your life.

https://www.deskbright.com/excel/using-index-match/

Let me know if I can clarify anything.

Best,

Brett

Upvotes: 1

Related Questions