I'd like to start this question by apologizing, there are numerous questions about merging excel rows, still, after browsing the web AND the list of "Questions that may already have your answer", I still don't know how to solve my problem.
EDIT: It seems that possible solutions may differ based on the data type within the different spreadsheets I'd like to merge. In this case, it seems that using the "Consolidate" function solves the problem once all data records (but the most left column) are numeral. My problem is figuring how to find a solution for mixed data of numbers and text (text may contain non-English letters as well). Question was edited in order to provide better examples.
I have two spreadsheets with different data. The only similarity is in the values of the very left column:
I've already put the data from both spreadsheets into one, by pasting the values from the most left column from one of the spreadsheets beneath the values of the most left column from the second spreadsheet. For the rest of the values, I've pasted them to the right, so the columns from different spreadsheets do not mix. After doing that, I've sorted the spreadsheet by the values from the most left column and got a result similar to the simplified example in the illustration:
What I want to do with this spreadsheet, is to eliminate the duplicate rows, and merge the data from duplicate rows, based on the most left column. A corresponding example is provided in the illustration:
Some important notes:
I prefer to work with the source spreadsheets, rather than combining them to one intermediate unmerged spreadsheet, but I welcome any solution that will help me to achieve my goal.
Data records (as shown in the figures above) can be incomplete, and contain both numbers and text. Also, data records can be wrong, so I can't assume that a given column will always have numbers or text (for example, the Shipping column of spreadsheet 2).
There are rows which most left column value is present in only one of the spreadsheets (for example, "name5" only comes from spreadsheet 1 while "name3" and "name6" only come from spreadsheet 2).
I'm trying to avoid using VB macros for this, and prefer to use built-in functions already present in Excel.
On the other hand, exporting to CSV and using regular expressing is something I'm willing to consider, if there is no way to accomplish this task using Excel built-in tools. In this case, the question may need to find a new home at StackOverflow.