Excel vba concatenate (join cell values) based on other cell values

See also questions close to this topic

• Write formula to an entire range, or just loop through cells with an IF statement. Which is faster?

  I'm using a macro to re-write formulas to a range in case a cell gets unintentionally modified by a user. Would looping through each cell and fixing errors be faster then rewriting the entire range? Thoughts?

  I have tried protecting sheets and certain ranges, but still occasionally have issues.

  Currently using:

  Sheet2.Range("TableSh[Produced]").Formula = "=SUMIF(TableP[Run],[@Run],TableP[Good PCS])"
  

  Would this ↓ be faster?

  For Each R In Range("TableSh[Produced]")
  
        If Not R.Formula = "=SUMIF(TableP[Run],[@Run],TableP[Good PCS])" Then
             R.Formula = "=SUMIF(TableP[Run],[@Run],TableP[Good PCS])"
        End If
  Next
  
• Is there a faster way to copy-paste dynamic ranges in VBA?

  I am trying to merge daily report to a single main document. Before, I was using Excel functions to do this. But the file size became enormous. So after all I thought, I may use VBA to eliminate huge amount of functions on the worksheets and reduce file size and calculation time as well.

  Basically I have main line numbers as database on Sheet1 (main document). From Sheet2 I need to take daily amounts of works done for the line numbers listed on Sheet1.

  For example, in Sheet1, cell "M3" represents the first line number (Line-01). In cell "X3", there will be work amount (10 joints) which comes from Sheet2 Range "O:O". This will repeat at random times according to daily performance. So I would have datas from range "O3" to "O1000" (even more).

  Sub DailyProgress()
      Dim i As Long
  
      Sheets(1).Activate
  
      Range("X3:X" & Range("M" & Rows.Count).End(xlUp).row).Formula = _
      "=IFERROR(vlookup(M3,Sheet2!D:O,12,0),Text(,))"
  
      i = 3
      'Because my first cell is on row 3
      Do Until IsEmpty(Cells(i, 12))        
          Cells(i, 24).Copy
          Cells(i, 24).PasteSpecial xlPasteValues
  
          i = i + 1
      Loop
  End Sub
  

  I wrote the code to make vlookup, copy and paste as value. But this block is not smooth enough. By the way, I need to copy datas to cells "Y3" and "Z3" from Sheet2. So It will take much more time next time.
• Fail to recall a Excel macro after auto open from Outlook macro

  I wrote a macro from Outlook to open Excel.

  Below the code:

  Public Sub OpenMasterPM()
      Dim xExcelFile As String
      Dim xExcelApp As Excel.Application
      Dim xWb As Excel.Workbook
      Dim xWs As Excel.Worksheet
      Dim xExcelRange As Excel.Range
      xExcelFile = "C:\Users\andrea.vighetti\Documents\Banca5\Evolutive\Master PM_prova.xlsm"
      Set xExcelApp = CreateObject("Excel.Application")
      Set xWb = xExcelApp.Workbooks.Open(xExcelFile)
      Set xWs = xWb.Sheets(1)
      xWs.Activate
      xExcelApp.Visible = True     
  End Sub
  

  After that, Excel is open and I have an Auto_Open macro on Excel that I want start when that page is open but this doesn't work.

  It seems that auto open macro wrote on Excel doesn't start when Excel is open starting from the macro of Outlook, below the vba code on Excel:

  Sub Auto_open()
  
      Sheets("Evolutive TFS").Select
      ActiveWorkbook.RefreshAll
      Sheets("Gantt").Select
      ActiveSheet.ListObjects("Gantt").Range.AutoFilter Field:=3, Criteria1:="=Implementation", Operator:=xlOr, Criteria2:="=Test"
  
  End Sub
  
• MS Access Multiple value from a FORM into table as a Single Line

  To avoid Multiple Value fields, I need some help to configure the "multi-select" option of a FORM within MS Access to a SINGLE line/field.

  Example:

  

  And in the Table it should come out like one single line/field

  Example

  

  Is this even possible? if Show can someone help me?
• Effecient way to transfer excel survey to word?

  Every month my company sends out a survey with 20-50 questions with 100-150 respondents. I'm looking for the best way to transfer those varying cells to word. I thought the best way currently would be to set every used cell as a one giant table and paste the table to word. However, the table is always too large and falls off the page. I've used a document with tags in the past and then told excel to copy and paste to the specific tags but I am unsure how to do that with this many variables. Does anyone have a clear place I could start to tackle this problem?
• Add multiple columns with same value to pivot table

  I have the following base table:

  I want to create a pivot table, that uses the columns bias_1 to bias_n as rows to achieve a similar result like this:

  However, when I add multiple of the desired source table, the result looks like this:

  Can anyone help? The solution has to be done with pivot, so the results can be double-clicked and excel redirects the user to the exact source rows, that have the bias in one of the bias_n columns.
• Use active cell to retrieve data

  I have managed to get my VBA to use active cell to retrieve date and department number on my table. I am using this for advance filtering. I would like help in shortening up my code, the way I am going it would take quite a few lines. I have tried to add a message box that would say “No NCR” if cell valve = 0. I would also like message "Click on cell in table" if button is selected and the active cell is not in table range.

  Sub select Range()
  
  If ActiveCell.Address = "$B$7" Then
  Range("O6").Value = Range("B5").Value
  Range("Q6").Value = Range("A7").Value
  
  ElseIf ActiveCell.Address = "$B$8" Then
  Range("O6").Value = Range("B5").Value
  Range("Q6").Value = Range("A8").Value
  
  ElseIf ActiveCell.Address = "$B$9" Then
  Range("O6").Value = Range("B5").Value
  Range("Q6").Value = Range("A9").Value
  
  End If
  End Sub
  

  Table Data
• How to concatenate specific rows in python?

  I am looking to learn how to concatenate multiple columns in python. I have a dataset which looks like this:

  gene    match_type  drug                sources      pmids
  ABO     Definite    CHEMBL50267         DrugBank     17139284|17016423
  ABO     Definite    URIDINE_DIPHOSPHATE TdgClinicalTrial   17139284|17016423
  ABO     Definite    CHEMBL439009        DrugBank     12972418
  ABO     Definite    CHEMBL1232343       DrugBank       NA
  ABO     Definite    CHEMBL503075        DrugBank       NA   
  

  I am trying to bring this into one row (concatenating the drug column, the sources column and the pmids column) to look like:

  gene    match_type  drug                                                                         sources                                           pmids
  ABO     Definite    CHEMBL1232343 CHEMBL439009 CHEMBL50267 CHEMBL503075 URIDINE_DIPHOSPHATE NA  DrugBank TdgClinicalTrial DrugBank DrugBank DrugBank    0 12972418 17139284|17016423  17139284|17016423 NA NA
  

  I have looked into using if statements using pandas.concat and .iterrows to go through everything, but I have gotten a bit lost with this and I am not sure actually what functions I should have started with to achieve my goal. Any help in the right direction would be appreciated.

  This is what I've tried but it's got a lot wrong it if not everything:

  for index, row in data.iterrows():
      if[1,2]==[2,1]:
          pd.concat(['drug'],['interaction_types'],['sources'],['pmids'],)
      else: 
          print(row[:])
  
• Onclick should create link and open it as well

  I am new to JavaScript and html. I am trying to create a link using two input boxes and create a link so I can further fetch data from it's API. I tried every method but it is not happening. I am using this code.

  function openlink() {
    var a = document.getElementById("p1").value;
    var b = document.getElementById("p2").value;
  
    location.href = "http://" + a + b + "/webhdfs/v1/?user.name=hduser1&op=LISTSTATUS";
  }

  p1: <input type="text" id="p1" value="fvalue"> P2: <input type="text" id="p2" value="svalue">
  
  <p>Click On button.</p>
  
  p3: <input type="button" id="p3" onclick="function openlink()">

  Also, I am facing error of

  "unexpected end of input " at the end of P3 line

  can anyone help??
• Creating new variable based on specific rows of other two variables in a long formatted dataset

  I have a long dataset of emotional responses and I need to create a variable based on specific rows of two other variables, within subjects.

  The following data frame includes data for two participants ("person") presented with 2 pictures (P1, P2, P3), each with 3 repetitions (R1, R2, R3) which is the "phase" variable. The variable response includes two things the rating for each presentation ( scale -30 to 30) and the emotion experienced per picture.

  person <- c(1, 1, 1, 1, 1, 1, 1, 1, 2, 2, 2, 2, 2, 2, 2, 2)
  block <- c(4, 4, 4, 5, 5, 5, 8, 8, 4, 4, 4, 5, 5, 5, 8, 8)
  phase <- c("P1R1", "P1R2", "P1R3", "P2R1","P2R2","P2R3", "Post1", "Post2","P1R1", 
      "P1R2", "P1R3", "P2R1","P2R2","P2R3", "Post1", "Post2")
  response <- c(30, 30, 30, -30, -30, -30, "Happy", "Sad", 28, 27, 25, -23, -24,
      -22, "Excited", "Scared")
  df <- data.frame(person, block, phase, emotion, response)
  

  I need to create a new column that will be based on the block number and give me the emotion per picture.

  I would like the new column to be called “postsurvey” and expect it to be as following:

  postsurvey <-c ("Happy", "Happy", "Happy","Sad","Sad", "Sad", NA, NA, 
      "Excited", "Excited", "Excited", "Scared", "Scared", "Scared", NA, NA)
  df <- data.frame(person, block, phase, emotion, response, postsurvey)
  

  The code that I used is:

  df<-df  %>% group_by(person, block) %>%
  mutate(postsurvey=if(block==4){response[phase=="Post1"]}  
        else if (block==5){response[phase=="Post2"]} 
     else {print("NA")})
  

  I expect for each subject to receive for each block number the same response, but what I get is that the response is not grouped by the subjects, and is not repeated within the subject by a block number, as if there is a vector of emotions and a person gets emotions that are not his.

  *In my original data I have 4 pictures per subject with 10 repetitions, so the "else if" code repeated with more then two conditions.