Comparing Columns with Matching Results Listed in One Cell

[mako_cc]

Hi, I hope this has not been asked before, I have searched through many posts on comparing columns and have not seen an answer to this.

I would like to compare cells of text in two separate columns and place the matching text elements in a list (comma separated, new line separated, whatever works) in a single cell.

I have a lot of comparisons to make and I need a third dimension to capture the results. A list of matches in one cell containing the needed formula would be ideal.

Thanks in advance for the help. Please let me know if I have not given enough details of my problem.

-chad

[FDibbins]

Hi and welcome to the forum

This sounds like a job for...superman!!...lol no sorry, Im sure you can use vlooup() for this. But without seeing a sample of what you are working with, I cant be more specific

[mako_cc]

Thanks for the quick response. I will try to give a simplified example of what I need. If I had the following in columns A-D, I would like to be able to get the comparison results listed in E as shown below.

	A	B	C	D	E
1	x	x	y	x	x,z	(Comparison matches between columns A and B)
2	y	z	z	y	y,z	(Comparison matches between columns A and C)
3	z				x,y	(Comparison matches between columns A and D)
4					z	(Comparison matches between columns B and C)
5					x	(Comparison matches between columns B and D)
6					y	(Comparison matches between columns C and D)

I hope this helps explain what I need.

Thanks again,
Chad

[FDibbins]

It would make it easier to see if you uploaded a sample workbook, but I think I can work with what you have provided.

I'm having a hard time understandingthe logic here

1 x x y x x,z (Comparison matches between columns A and B)

how do you arrive at x,z?

[mako_cc]

Hmm, I guess its not as easy to explain as I thought.

I am looking to compare all of the values in column A with all of the values in column B and report the matches in one cell. So column A contains an x, y, and z in rows 1,2,3 respectively. Column B contains an x and z in rows 1 and 2 respectively. The matching values between the two columns are x and z, so I would like to list those in a cell somewhere else (in this case E1) as "x,z".

I am guessing a fairly simple VB function could be made to take in two column references/ranges and compare each cell of the first column to each cell of the second and produce a comma separated list of matches to place in the cell calling the function. Unfortunately I am not too VB savvy.

Thanks again,
Chad

[mako_cc]

I figured it out. Took a fwe minutes to become VBA savvy, but here it is in case anyone else is interested... probably not.

Function Conflicts(ListA As Range, ListB As Range)
Dim c As Range
Dim d As Range
Dim ListC As String

For Each c In ListA
For Each d In ListB
If d.Value = c.Value Then
If ListC = "" Then
ListC = c.Value
Else
ListC = ListC & Chr(10) & c.Value
End If
End If
Next d
Next c
Conflicts = ListC
End Function

Thanks again for the help,
Chad