Pull data from 3 cells using formula

I need a lookup formula that will look up the company name from another tab, and pull in the job title, job ID #, and date information to be represented as text in a single cell next to each company.

Here is a video depicting what I am trying to accomplish http://screencast.com/t/8TfsvAAUG

Here is the spreadsheet with an example in the Companies tab in cell C5 https://docs.google.com/spreadsheets/d/17LeToNWtIC5uqo8QnXsDHYof7cisAakKct2ZBtK6QfU/edit#gid=1254353415

Assistance is greatly appreciated.
frugalmuleAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

rspahitzCommented:
This certainly won't be trivial with formulas, but can be done.  Since you are looking for a collection of things that yo'd like to merge, a simpler solution will be some VBA/macros.

But first, using formulas, you'd use something like a LOOKUP to locate the information then grab an adjacent column and return it.  since you want multiple columns, you'd have to perform this several times then concatenate the pieces.  Also, the LOOKUP function is more complicated than the VLOOKUP but VLOOKUP requires that the search field (RobHTech) be in the leftmost column of your search.  After this, you'd need to repeat the process to search for the next match, which would have to determine (1) did it find a match? If not then done; if so then (2) search again but below the position where the first one was found.

In light of the complexity of this, let's explore a VBA solution.

Designing some simple pseudo code you'd have this:
1. Search for each occurrence of the company on sheet 1, column B by looking in every row of sheet 2, column H
2. For each row in sheet 2 with a match, gather the contents of columns D, F and S and merge them into a "sentence" with any previous entries.
3. When all rows are searched, place the results in sheet 1, column C

I assume you want to repeat the above for each row in sheet 1, starting at row 2.

Does that make sense? Do you want o go in the macro direction? (You'll need to resave the spreadsheet as an xlsm file.)
Saqib Husain, SyedEngineerCommented:
If you have upto two instances of a particular company on apptracker then you can use this formula

=index(Apptracker!D:D,small(ArrayFormula(if(Apptracker!H:H=B2,row(H:H))),1))&" #"&index(Apptracker!F:F,small(ArrayFormula(if(Apptracker!H:H=B2,row(H:H))),1))&" on "&TEXT(index(Apptracker!S:S,small(ArrayFormula(if(Apptracker!H:H=B2,row(H:H))),1)),"ddd m/d/yyyy")&iferror(", "&index(Apptracker!D:D,small(ArrayFormula(if(Apptracker!H:H=B2,row(H:H))),2))&" #"&index(Apptracker!F:F,small(ArrayFormula(if(Apptracker!H:H=B2,row(H:H))),2))&" on "&TEXT(index(Apptracker!S:S,small(ArrayFormula(if(Apptracker!H:H=B2,row(H:H))),2)),"ddd m/d/yyyy"),"")


If you have more then copy the part of the formula starting with    &iferror...
up to the end of the formula and append this to the end of the formula and then change the three instances of ,2) to ,3) and so on. I have not tested upto what length of formula can be taken by GS
frugalmuleAuthor Commented:
It has to be in Google drive for sharing, so if it could be done as a Google script instead of for an xlsm file, then it would work.
Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook now!

rspahitzCommented:
See if this works for you (you may need to change some of the constants.
Option Explicit

Private Const MAIN_SHEET As String = "AppTracker"
Private Const COMPANY_SHEET As String = "Companies"

Sub LoadInfo()
    Dim iRow As Integer
    Dim strCompany As String
    Dim strApplText As String
    Dim iCompanyRowCntr As Integer
    Dim iCompanyLastRow As Integer
    Const MAIN_COMPANY_COLUMN As Integer = 2
    Const MAIN_APPLICATION_COLUMN As Integer = 3
    Const COMPANY_COMPANY_COLUMN As Integer = 8 ' "H"
    Const COMPANY_JOB_COLUMN As Integer = 4 ' "D"
    Const COMPANY_CODE_COLUMN As Integer = 6 ' "F"
    Const COMPANY_DATE_COLUMN As Integer = 19 '"S"
    
    iCompanyLastRow = GetLastCompanyRow()
    iRow = 5 ' maybe turn this into a Do-loop starting from row 2 until strCompany=""
    strApplText = ""
    strCompany = Sheets(MAIN_SHEET).Cells(iRow, MAIN_COMPANY_COLUMN).Value
    ' if strCompany="" then exit do
    
    With Sheets(COMPANY_SHEET)
        For iCompanyRowCntr = 2 To iCompanyLastRow
            If .Cells(iCompanyRowCntr, COMPANY_COMPANY_COLUMN).Value = strCompany Then
                strApplText = strApplText & ", " _
                    & .Cells(iCompanyRowCntr, COMPANY_JOB_COLUMN).Value _
                    & " #" & .Cells(iCompanyRowCntr, COMPANY_CODE_COLUMN).Value _
                    & " on " & .Cells(iCompanyRowCntr, COMPANY_DATE_COLUMN).Value
            End If
        Next
    End With
    Sheets(MAIN_SHEET).Cells(iRow, MAIN_APPLICATION_COLUMN).Value = Mid(strApplText, 3) ' remove leading comma
    ' maybe Loop back to the next row after incrementing row
End Sub

Private Function GetLastCompanyRow() As Integer
    Dim strCurrentTab As String
    
    strCurrentTab = ActiveSheet.Name
    Sheets(COMPANY_SHEET).Select
    Selection.SpecialCells(xlCellTypeLastCell).Select
    
    GetLastCompanyRow = Selection.row
    Sheets(strCurrentTab).Select
End Function

Open in new window


Do you know how to add this? Alt+F11 to get you to the VB area, then add a module, then insert the above code and run the LoadInfo method
rspahitzCommented:
sorry, just saw your message about google docs...haven't tried that so not sure how to translate this.
Saqib Husain, SyedEngineerCommented:
Did you try the formula I gave you?
frugalmuleAuthor Commented:
Saquib, thank you
please provide examples for multiple applications and not just two.
Saqib Husain, SyedEngineerCommented:
What is the maximum number of repetitions do you expect?
ProfessorJimJamMicrosoft Excel ExpertCommented:
frugamule,

i have saved the formula in your worksheet.  here is the simple solution

=transpose(FILTER(Apptracker!D:D&","&"#"&Apptracker!F:F&","&"on "&TEXT(Apptracker!S:S,"MM/DD/YYYY"),Apptracker!H:H=B2,NOT(ISBLANK(Apptracker!D:D))))

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Koen Van WielinkBusiness Intelligence SpecialistCommented:
In case you're interested in a solution using apps script, I wrote one and added it to your sheet. The script adds a new menu item to the sheet when it's opened called "get data". There's a menu item under that called "Refresh". Selecting that will start the script that appends all the job application details to the companies sheet.
For each company in the "Companies" sheet it will run through the column H on the apptracker sheet. For each matching name, details from columns D, F and S are added to a string variable. When the end of the sheet has been reached, the resulting string is added to the "Companies" sheet for the respective company.

Full script here:
function onOpen() {
   // On opening the sheet, add a menu
  var ui = SpreadsheetApp.getUi();
  ui.createMenu('Get Data')
      .addItem('Refresh', 'getJobInfo')
      .addToUi();  
}

function getJobInfo() {
  // Refreshes the data on the Companies tab with the latest job application info.
  
  var spreadsheet = SpreadsheetApp.getActive();
  
  // set variables for the source and destination sheets and ranges  
  var dataSheet = spreadsheet.getSheetByName('Apptracker');
  var dataRange = dataSheet.getRange(2,4,dataSheet.getMaxRows(),16);
  var destinationSheet = spreadsheet.getSheetByName('Companies');
  var destinationRange = destinationSheet.getRange(2, 2, destinationSheet.getMaxRows(), 2);

  // get list of companies
  var companies = destinationSheet.getRange(2, 2, destinationSheet.getMaxRows());
  
  // set string variables for final result
  var resultString = ''
  var delimiter = ''
  
  // For each company in the list, loop through data sheet to check for job applications
  for (var row = 1; row < companies.getLastRow() - 1; row++) {
    var company = companies.getCell(row, 1).getValue();
    
    // Check if the selected cell has a value. If so, check for job applications.
    if (company.toString().length > 0) {
      
       // if the company in the application list row matches the company being checked, append values to string
       // and update the delimiter variable with a comma and space
      for (var i = 1; i < dataRange.getLastRow() - 1; i++) {
        var currComp = dataRange.getCell(i, 5).getValue();
        if (company == currComp) {
          resultString = resultString.concat(delimiter, dataRange.getCell(i,1).getValue(), ' #', dataRange.getCell(i,3).getValue(), ' on ', dataRange.getCell(i,16).getValue());
          delimiter = ', ';
        
        }
      
      }
      
    }
    
    // set the final string result as the value of the correct destination cell and clear string variables for next round
    destinationSheet.getRange(2, 2, destinationSheet.getMaxRows(), 2).getCell(row,2).setValue(resultString);
    resultString = '';
    delimiter = '';
    
  }
   
}

Open in new window


I removed most of the empty lines in your sheet as I didn't want to hard-code the number of lines to be checked by the script. As a result, if you have a lot of empty lines in your sheet, they will all be checked. Better to remove most of the empty lines as this will speed up the execution.

Let me know if you have any questions regarding this solution.

PS. It's the first apps script I've written from scratch, so if any other coders out there find inefficiencies or mistakes, feel free to point them out. having said that, I think the script is pretty efficient.
Koen Van WielinkBusiness Intelligence SpecialistCommented:
Oh, when you execute the script you might get a question asking for permissions. This is standard for Apps Script and it's safe to accept that prompt.
frugalmuleAuthor Commented:
Koen and ProfessorJimJam,

Thank you!

The script from Koen works well as you can see here http://screencast.com/t/ihLCmIbD but is incredibly SLOW and in fact, exceeds execution time as shown with this error http://screencast.com/t/wHN6Dws8.  

This slightly adapted function from ProfessorJimJam here appears to do well also, but puts each application in a new column as opposed to a single cell.  Also, after further review, it would be good if each application could appear on separate lines vertically, but still within the cell.
=transpose(FILTER(Apptracker!D:D&", "&"#"&Apptracker!F:F&", "&"on "&TEXT(Apptracker!S:S,"ddd MM/DD/YYYY"),Apptracker!H:H=B2,NOT(ISBLANK(Apptracker!D:D))))

Open in new window

Koen Van WielinkBusiness Intelligence SpecialistCommented:
Hi frugalmule,

Just curious, how many records do you have on each sheet? Wouldn't mind trying to tweak the script to increase performance. It's a good learning exercise for me :D.
frugalmuleAuthor Commented:
I don't know.  The formula though is a better option.  Please see new question.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Spreadsheets

From novice to tech pro — start learning today.