?
Solved

Excel Cell Matching

Posted on 2013-11-05
11
Medium Priority
?
156 Views
Last Modified: 2013-11-20
I have an Excel sheet that I want to match values from one column (col1) to another (col2) and output the row number(s) of matches to a third column.

Example:

row            col1                  col2                  col3
1            bill smith            john Thompson      2,4
2            todd sampson      bill smith            3
3            mike johnson      todd sampson      
4            owen keen            bill smith            5
5            alex wallace      owen keen

If the user in col1 is in col2, it needs to output every instance row number to col3. How do I do this?
Thanks.
0
Comment
Question by:dale_abrams
  • 3
  • 3
  • 2
  • +2
11 Comments
 
LVL 12

Expert Comment

by:tel2
ID: 39625935
Is this homework, Dale?
If not, what is it for?

Thanks.
tel2
0
 
LVL 1

Author Comment

by:dale_abrams
ID: 39625963
Tel2,
This is NOT homework. I am a professional and I am trying to match people by name from a couple of databases, while identifying potential people with duplicate names. Hence the multi-output in col3. If there are multi values, then it tells me I have to remove that person out.

Now do you have anything constructive to add?
-Dale
0
 
LVL 12

Expert Comment

by:tel2
ID: 39625994
Nothing constructive comes to mind, Dale.  I expect VBA will be required, but I'm better at interogation.

Thanks for the explanation.  Hopefully that will help other experts who may have been suspicious.
0
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
LVL 1

Author Comment

by:dale_abrams
ID: 39626040
I've requested that this question be deleted for the following reason:

I had a guy post to my question with some nonsense about homework. I asked him to post something constructive and he admits he has nothing constructive to say. I would like to delete this post so that I can repost. As my question appears as though someone has already answered it, nobody else is viewing/answering.
0
 
LVL 12

Expert Comment

by:Harry Lee
ID: 39626041
dale_abrams,

Don't jump too quick. Sorry that tel2 cannot give you straight answer. tel2 cannot help doesn't mean no one can help.

In fact, what you are asking for is not that simple. Excel does not provide any easy solution on that.

I have spent the last 30 minutes trying to work out something for you here.
0
 
LVL 1

Author Comment

by:dale_abrams
ID: 39626055
Hi Harry,
Thanks for your work on this. I have created a duplicate of this question at http://www.experts-exchange.com/Software/Office_Productivity/Office_Suites/MS_Office/Excel/Q_28286449.html. I'd be happy to look over any proposed solution you may have. Please post to the new question so this one can be reviewed by a moderator and shut down.
Thanks,
Dale
0
 
LVL 12

Expert Comment

by:Harry Lee
ID: 39626083
Dale, I have completed something for you.

Try out the macro.

Since it's doing a lots of loops, it will run very slow on long list.
Book1.xlsm
0
 
LVL 12

Expert Comment

by:tel2
ID: 39626101
Hi Dale,

> I had a guy post to my question with some nonsense about homework.
It's only nonsense to you, because you know it is not homework.  But experts are not mind readers, and because of the guidelines of this site, we should be comfortable that a question is not homework before answering (or at least before giving a complete answer).  That's the responsible thing to do.  Your question looked is if it could be homework to me.  Sound reasonable?

> I asked him to post something constructive and he admits he has nothing constructive to say.
True, unless you count the constructive questioning that I did in my first post, which, as stated, should hopefully "help other experts who may have been suspicious".  (In this case I decided that the VBA was more than I was willing to take on.)

> As my question appears as though someone has already answered it,...
Your question appears as though someone else has posted to it, not necessarily answered it.  Although some experts may avoid questions which already have a few posts, that is not necessarily the case.

> ...nobody else is viewing/answering.
Harry has proved this to be incorrect.
It's hard to tell who's viewing.
45 minutes is not very long to give experts a chance to respond, especially if you take time zones into consideration.
0
 
LVL 10

Expert Comment

by:mark_harris231
ID: 39626746
Dale,

Is it actually necessary to indicate the rows where duplicates occur, or do you just need to know that an entry is duplicated?

If the latter, then I would just use a COUNTIF statement in column 3:

=COUNTIF(B:B,A2)

Copy down as far as needed.

Alternately, if you just want to flag records for deletion, you could use the following IF statement:

=IF(AND(COUNTIF(A:A,B2)>0,COUNTIF(B:B,B2)>1),"Duplicate","")

This will put the word "Duplicate" beside every name in column B that 1) appears at least once in column A, and 2) appears more than once in column B.

Or, you might alternately use both.  See attached example.

You could also use the formulas in Conditional Formatting to highlight a cell that needs attention, which might present a little "cleaner" without the need for any helper columns.
DupeCount.xlsx
0
 
LVL 10

Assisted Solution

by:mark_harris231
mark_harris231 earned 750 total points
ID: 39626761
Updated spreadsheet with conditional formatting applied.  I left the other helper columns in place, but they could be deleted without affecting the conditional formatting.
DupeCount.xlsx
0
 
LVL 34

Accepted Solution

by:
Rob Henson earned 750 total points
ID: 39639124
This doesn't put all row matches in one cell but it does identify all.

Assuming data in columns A and B:

Column D =IFERROR(MATCH($A2,$B$2:$B$6,0),"")

This identifies first Match in column B

Column E and copied right as far as required:

=IF(COUNTIF($B$2:$B$6,$A2)<=COUNTA($C2:D2),"",IFERROR(MATCH($A2,INDIRECT("$B$"&D2+1&":$B$6"),0)+D2+1,""))

This counts the number of occurences of the name and compares with number of occurences already matched in previous columns. If already got all occurences returns blank otherwise shrinks the start of the data range to the previous matched row +1 so that it then finds the next match.

Thanks
Rob H
0

Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In a use case, a user needs to close an opened report by simply pressing the Escape (Esc) key. This can be done by adding macro code in Report_KeyPress or Report_KeyDown event.
MS Outlook undoubtedly is the most widely used email client.Its user-friendliness, cost effectiveness, and availability with Microsoft Office Suite make it the most popular email application.  Its compatibility with Microsoft applications like Exch…
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…
Enter Foreign and Special Characters Enter characters you can't find on a keyboard using its ASCII code ... and learn how to make a handy reference for yourself using Excel ~ Use these codes in any Windows application! ... whether it is a Micr…

621 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question