Excel/Concatenating & Matching
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!
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
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.
---------- 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.
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 email@example.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
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.