You are here:

Excel/Concatenating & Matching

Advertisement


Question
QUESTION: Hi Tom!

I'm an excel noob and researching the answer to this question has given me a headache so far. Hopefully you can help.

I have an Excel workbook with a main data sheet and a sheet containing similar data.

Both sheets have an account number column and a suffix column which need to be concatenated then matched. So if the concatenated value of the main sheet matches the concatenated value of the second sheet, I want the results listed on a separate sheet as matching and non-matching groups.

On the main data sheet the account number is in column A, suffix in column B.

On the secondary sheet, the account number is in column I, suffix in column J.

Any help would be appreciated! Thank you!

ANSWER: David,

First, I will assume that you have excel 2007 or later

You didn't say (at least not so I could understand it) but I will assume the situation is that you have a master sheet (main data sheet) and a secondary sheet  with the secondary sheet having a subset of the data in the main sheet and you will have two categories listed against the main sheet - match and no match

So copy the main Data sheet to create the third sheet.  Delete all the data so that only the account number is in column A and the Suffix is in column B.

Assume the data starts in row 2

in C2 put this formula
=if(countifs(Secondary!$A:$A,$A2,Secondary!$B:$B,$B2)>0,"Match","No Match")

Now drag fill this down as far as you have data in this third sheet.

Each row will be marked as either having a match or not.  Now select columns A:C and sort the data on Column C.  (You can add additional keys to use a secondary key of A and then B if you want order within the match or no match categories).  


This should provide the two lists you want.

Note that there is a COUNTIF function and a COUNTIFS  function. I am suggesting the second function with an "S" on the End.  This supports multiple criteria and avoids having to do concatentation separately or using an array formula.  This function was not introduced until Excel 2007, so it can not be used in an earlier version of excel.

--
Regards,
Tom Ogilvy


--
Regards,
Tom Ogilvy


---------- FOLLOW-UP ----------

QUESTION: Hi Tom,

Thank you for the quick reply, and my apologies for being vague. Although I'm no stranger to Excel, doing certain things like this is a little confusing to me sometimes, but I'm slowly learning the ropes, and I appreciate your help. Yes, I am using Excel 2010. I tried what you suggested, and the result is a #VALUE! error.

What I have is a workbook consisting of 4 sheets. The first sheet has multiple columns with column A being account number and column B the account suffix. There is a column D where the company name belongs but is blank. Sheets 2 & 3 are not important. Sheet 4 also has the account number and suffix in columns A & B and company name in column I.

What I need to do is concatenate columns A & B in both sheets 1 & 4, then compare the account number + suffix to match the company name from sheet 4 to sheet 1. I would like the results to show matches and non-matches.

Hope that provides a better explanation.

Answer
David,

I don't believe I misunderstood the question.  So I have offered a solution I would expect to work.  since you say  it does not, then the best I can offer is to have you send the workbook to me and I will take a look and see where my expectations and your reality differ.  

you can send it to twogilvy@msn.com

if you prefer to do a concatenation, you can go to an adjacent column and enter

=A2&B2   say in cell C2  and drag fill down.  You can do this in both sheets.  

or you can use

=concatentate(A2,B2)

if you don't want to use the ampersand (which is the concatenation operator).

You can then use countif to test if there is a match or not.  You also probably want to make sure you don't have spaces in your data which may cause there not to be a match when there should be a match.

--
Regards,
Tom Ogilvy  
About Excel
This topic answers questions related to Microsoft Excel spreadsheet (or workbook) stand-alone or Mircrosoft Office Excel including Excel 2003, Excel 2007, Office 2000, and Office XP. You can get Excel help on Excel formulas(or functions), Excell macros, charting in Excel, advanced features, and the general use of Excel. This does not provide a general Excel tutorial nor the basics of using a spreadsheet. It provides specific answers to using Microsoft Excel only. If you do not see your Excel question answered in this area then please ask an Excel question here

Excel

All Answers


Answers by Expert:


Ask Experts

Volunteer


Tom Ogilvy

Expertise

Selected as an Excel MVP by Microsoft since 1999. Answering Excel questions in Allexperts since its inception in 2001. Able to answer questions on almost all aspects of Excel's internal capabilities. If seeking a VBA solution, please specify that in your question itself so I give you the answer you want. [Excel has weak protection - if you are distributing an application, I don't answer questions on how to protect your project from your users.]

Experience

Extensive experience.

Education/Credentials
Master of Science (MS) degree Operations Research (ORSA)

Awards and Honors
Microsoft MVP in Excel.

©2016 About.com. All rights reserved.