Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Consolidate information from Column B Based on Info In Column A
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 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Consolidate information from Column B Based on Info In Column A
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. -- HTH, Barb Reinhardt "Consol. Info from One Column to another" wrote: 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
looking up cell value based off of row and column information | Excel Discussion (Misc queries) | |||
Finding Info from Column A and Removing it from Column B | Excel Discussion (Misc queries) | |||
Based on a condition in one column, search for a year in another column, and display data from another column in the same row look | Excel Discussion (Misc queries) | |||
Sum column information based on multiple criteria | Excel Worksheet Functions | |||
increment a column based on information in another column | New Users to Excel |