Take the 2-minute tour ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

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:

Example of Spreadsheet 1, in reality it contains thousands of records, and has nothing to do with supply chains Example of Spreadsheet 1, in reality it contains thousands of records, and has nothing to do with price listings

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:

Data from spreadsheets 1 and 2, combined in one spreadsheet, sorted but not merged

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:

enter image description here

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.

share|improve this question

2 Answers 2

up vote 1 down vote accepted

For Numerical Data...

Try using the built-in Consolidate feature.

Let's say I have the ff. data in Sheet1 and Sheet2, respectively.

enter image description here

  1. Select a destination cell. Any cell/range will do as long as you'll have enough room for the result. I recommend creating a new sheet & highlighting cell A1.
  2. Go to Data > Consolidate. You may also press Alt+A, N.
  3. Choose Sum.
  4. Under Reference, click the select reference button & highlight the data in Sheet1 (including row labels and headers). Click Add.

    enter image description here

  5. Repeat the previous step but add the data in Sheet2 instead.

    enter image description here

  6. Under Use labels in, make sure that Top row & Left column are both checked. You'll have something like this:

    enter image description here

  7. Click OK.

Result:

enter image description here


For Various Types of Data...

  1. Use the Consolidate feature to get the unique row labels & column headers. Clear the data but leave the labels/headers:

    enter image description here

  2. Enter this rough formula into the left-most blank cell in the table and copy it across & down. Replace the named ranges to fit your data.

    =IFERROR(IFERROR( INDEX(sheet1_data,MATCH($A2,sheet1_rowlabels,0),MATCH(B$1,sheet1_headers,0)), INDEX(sheet2_data,MATCH($A2,sheet2_rowlabels,0),MATCH(B$1,sheet2_headers,0))), "")

    Where
    sheet1_data, sheet1_rowlabels & sheet1_headers refer to all the data (A1:C5), the first column (A1:A5) & first row (A1:C1) in Sheet1, respectively
    sheet2_data, sheet2_rowlabels & sheet2_headers refer to all the data (A1:D6), the first column (A1:A6) & first row (A1:D1) in Sheet2, respectively

  3. Format as desired.

    enter image description here

share|improve this answer
    
Thanks! It seems that the solution you've provided works only with numeral data. I've edited my question with more detailed information and figures, since my source spreadsheets contain mixed data of numbers and text. –  Khaloymes Apr 1 '13 at 9:14
    
@Khaloymes Edit your question with methods/formulas that you have already tried. –  Ellesa Apr 1 '13 at 9:19
1  
I've tried your suggestion and it works. The only change I made was writing $A$5:$C$5 when I refer to sheet1_data and respectively to the rest of the ranges, instead of plain A1:C5. –  Khaloymes Apr 12 '13 at 15:05

If I understand your question correctly, this can be handled in a straightforward way using VLOOKUPs.

First you will need to create an unduplicated, sorted list of the "Item" values that are found in the left-hand columns of both tables (along the lines you show in your "Final Result" example).

This can be done by:

  • Copying the Item list from the first table into column A of the sheet that you want to hold the final result
  • Copying the Item list from the second table and pasting it below the values you copied from the first table so you have one, long duplicated list
  • Sorting the duplicated list using Excel's built-in sort capability on the Home ribbon
  • Eliminating the duplicates by highlighting the list with the mouse and then selecting Data / Remove Duplicates from the ribbon

Then you can proceed to set up the final result table with the list of unique Item values as the row labels and the column labels copied from the two tables (again as you show in your Final Result example).

With this shell in place you can then enter the VLOOKUP functions that will pull the data in from the two tables. The idea is to use one set of VLOOKUPs to get the data from the first table and another set to get the data from the second table.

Here is the formula for the first cell in the Final Result table:

  =IFERROR(IF(VLOOKUP($A2,Sheet1!$A$2:$C$5,2,0)=0,"-",VLOOKUP($A2,Sheet1!$A$2:$C$5,2,0)),"-")

The syntax for the lookup is VLOOKUP( lookup_value, table_array, column_index_num, [range_lookup]). The last, optional parameter "range_lookup" specifies whether the match for the lookup will be approximate (the default) or exact.

So the VLOOKUPs in the formula (both VLOOKUPs are identical) look up the Item name in cell A2 in the first column of the range A2:C5 and return the corresponding value in the second column of the range, which is the Supplier column. Because the last argument in the VLOOKUP is set to 0 (or FALSE), the match must be exact.

VLookup Supplier in Table 1

The IFERROR, IF, and double use of VLOOKUP in the formula are needed to deal with the possibility that there is no match on the Item (which could happen if Item "name1" is in table 2 but not in table 1) or that there is a match on the Item, but the cell for the corresponding value is empty (which would otherwise return 0).

The lookup formula for the second column of the Final Result table differs from the first only in the column index number, which is set to 3 to return the contact information:

Vlookup Contact in Table 1

Exactly the same approach is used to fetch the data from the second input table, using a VLOOKUP that references the range for that table and the respective data columns that are to be returned. Note that I have set the formulas to return a dash ("-") if no match is found or if no return value is available (i.e., if the cell with the return value is empty). This could just as easily be changed to an empty string (""). Of course, once the formulas for the first row of the result table are set up with proper references to the table ranges and return columns, they would be copied down to the bottom of the Item list.

Vlookup Price in Table 2

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.