Pulling Data from Multiple Cells into One Cell

[rockstarr319]

Scenario:

I have "Sheet A" with employee IDs 1,2,3, and 4 in coulmn A and coulmn B is labeled Experience. "Sheet B" has employee IDs 1,2,3 and 4 in coulmn A and coulmn B has a type of experience that employee has had. In Sheet B each employee appears on multiple rows because they have had many experiences.


Task:

I need to have all experiences for an employee (from sheet b) appear in one cell (in column b on sheet a). Each experience must be on a separate line within the cell. (the effect you usually get by pressing ALT+RETURN).


I'm open to using formulas if it's that easy. If the answer is a marco or VBA, please include as much info as possible, I'm newbie in those areas.


Thanks SOOOOOOO MUCH!!!!!


Examples of Expereinces:

Marine Operations Engineer - Fixed Facility, 2 year(s)

Project Engineer - Subsea Development, 2 year(s)

Estimating - Subsea Pipeline, 2 year(s)

 

[terrarium]

I think I can figure this out quick if you attach a sample. Google Docs is good for this. Sounds like you just need a vlookup that will concatenate strings.

 

[vijaySharma]

Rockstarr319,


Welcome to Chandorg Forums.


Try the below VBA code


1. Open your excel file (please make a duplicate copy just in case).

2. Hit ALT + F11 to start the VB Editor

3. On the VB Editor window click on Insert menu and then click on Module

4. Copy and Paste the below code into the module.

5. Press F5 to run the code and go back to the excel sheet to check the results.

Sub getExperience()

Dim strExp As String

Dim currEmpID As String


    Sheets("Sheet A").Select

Range("A2").Select


    Do While ActiveCell.Value <> ""

currEmpID = ActiveCell.Value


        Sheets("Sheet B").Select

Range("A1").Select

Do While ActiveCell.Value <> ""

If ActiveCell.Value = currEmpID Then

strExp = strExp & ActiveCell.Offset(0, 1).Value & vbCrLf

End If

ActiveCell.Offset(1, 0).Select

Loop

Sheets("Sheet A").Select

ActiveCell.Offset(0, 1).Value = strExp

ActiveCell.Offset(1, 0).Select

strExp = ""

Loop

Columns.AutoFit

End Sub

~VijaySharma