Consolidate information from Column B Based on Info In Column A

[Guest]

Suppose I have Column A that contains a company's name and in Column B is a
stockholder in that company. However, the program that provides this
information only puts one shareholder in each cell in column B. For example:

Microsoft Bob Jones
Microsoft John Doe
Microsoft Jimmy Doogan

This goes on for over 100 rows.

I am trying to figure out a way that the information from column B can be
entered again into one cell based on if it the people are the owners of stock
of the same company. In the example above I would like Column C to have one
box that says Microsoft and Column D that says Bob Jones, John Doe, Jimmy
Doogan. Any help you can provide would be appreciated. I have little
experience with VBA so if your recommendation is to use VBA please try and
explain what each line item is telling the computer so I can try and learn
from it in the future. Thanks, Chris

 

[Guest]

Here's a function that will do it. You'll need to press ALT F11, Insert
Module and paste it into the current workbook


Function CompanyNames(myCompany As String, myNameRange As Range, myOffset As
Long)
Dim myName As Range
CompanyNames = ""
For Each myName In myNameRange
If myName.Offset(0, myOffset) = myCompany Then
If Len(CompanyNames) > 0 Then
CompanyNames = CompanyNames & ", " & myName
Else
CompanyNames = myName
End If
End If
Next myName

End Function

In the cell where you want the info, put this:

=companynames("Microsoft",C1:C5,-1)

The first value is for the company name, the second value is for the range
of NAMES (not company names), the third range is the offset from the name
range to the company range. In your case, keep it at -1. It's not perfect,
but it will do what you want.