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

See also questions close to this topic

• How to check excel file open by external process

  How to check excel file open by external process if open how to close it in c#. How to update file in open condition? For example, data.xlsx is my excel file. I have try this.

  Process[] excelProcess = Process.GetProcessesByName("excel");
  foreach (Process process in excelProcess)
  {
      if (process.MainWindowTitle.ToLower() == "data" || process.MainWindowTitle == "Microsoft Excel - data")
      {
          if (!process.HasExited)
          {
              process.Kill();
          }
       }
  }  
  
• Have Product URLs but need Excel Script to pull Product Values

  I had an excel script query.

  So each month we export event tracking which happened on product-specific urls. Then we manually have to go through a list of 1300 URLs. We look at the product URL, go to the page, copy the value and paste it next to the product URL (Event on Product page = Value).

  Is there a script which can look at the product URL, go to the page and pull the value of the product on that cell next to the product URL? Any help welcome!

  Thank you!
• I am looking for the value using the fields that are available, need the help in vlookup formula

  Below is the data, , i am unable to use the formula which is mentioned in the question

  I have the data till "name" column, i need to populate the values in "result" column...."value" and "name" columns have common data, and "id" column in unique, I need to apply the vlook up formula, excel screenshot:

  
• reference workbooks() with a workbook that has spaces in between its file name

  I work with multiple workbooks open at the same time to track articles that I write. Both workbooks has respective macros and user forms that edit its data, one of which has a modeless userform. How can I make sure that the modeless userform do changes on the right workbook. I've tried referencing, Workbooks("Published Articles Tracker v3.0.xlsm").Sheets(Sheet2).Activate, but it always return an error. I would like to keep referencing my workbook using workbooks() method but I think the problem is the format of my filename, but if possible I'd like to keep it this way. Any thoughts?
• Outlook VBA code Help needed, How to create a folder based on domain and in that folder create an folder based on sender name

  So i am guessing this is easy for some of you.

  I want to create a macro/rule/code that creates a folder in outlook based on the senders Domain, after that i want it to create a folder based on the senders name in the Senders domain folder, and then move the mail to that folder.

  I get alot of emails from alot of diffrent companies and alot of diffrent people within thoose companies, so if anyone can helpme with this, that would be VERY good.

  So i am thinking in terms of folder layout something like this: Inbox\@senders domain\@Senders name\Email.msg
• vba code to cycle through listbox values, copy to cell and print?

  I need a code that will copy the first value in my ListBox2 to cell C8 in sheet called "Gym Weekly Template" (which is Sheet1), print the sheet and then loop to the next value and repeat the same process, repeating until all the values in the ListBox2 has been copied and printed, in which I want the sheet to reset to how it appeared prior to the code running.

  Is anyone able to help with that?

  Thank you!

  Jackson

  I currently have written the following code for selecting the first value

  Dim i As Long
      For i = ListBox1.ListCount - 1 To 0 Step -1
          ListBox2.ListIndex = I
      Next i
  
• Sending specific sheet in Workbook to Specified email stored in a sheet

  I have written a code for sending emails stored in sheet5 with an attachment, any one sheet of the workbook . When name of sheet matches with value in column A, it should select email from Column B and attach the sheet with same Name.

  For Each sh In ThisWorkbook.Worksheets
  
      Dim i As Integer
  
      While (Sheet5.Cells(1, i) <> vbNullString)
  
      If (sh.Name = Sheet5.Range("a" & i).Value) Then
          sh.Copy
          Set wb = ActiveWorkbook
  
          TempFileName = "Sheet " & sh.Name & " of " _
                       & ThisWorkbook.Name & " " & Format(Now, "dd-mmm-yy h-mm-ss")
  
          Set OutMail = OutApp.CreateItem(0)
  
          With wb
              .SaveAs TempFilePath & TempFileName & FileExtStr, FileFormat:=FileFormatNum
  
              On Error Resume Next
              With OutMail
                  .To = Sheet5.Range("b" & i).Value
                  .CC = Sheet5.Range("c" & i).Value
                  .BCC = ""
                  .Subject = "This is the Subject line"
                  .Body = "Hi there"
                  .Attachments.Add wb.FullName
                  .Display
              End With
              On Error GoTo 0
  
              .Close savechanges:=False
          End With
  
          Set OutMail = Nothing
  
          Kill TempFilePath & TempFileName & FileExtStr
  End If
  
  Wend
  
  Next sh
  

  ' i am getting error at while loop "While (Sheet5.Cells(1, i) <> vbNullString)" and at If statement "If (sh.Name = Sheet5.Range("a" & i).Value) Then"

  Please guide
• Changing the shape of the specific dots in scatter plot excel based on values

  I need a scatter chart to adjust the color and shape of the markers based on different values. I found the code below and works great to change the color, is it possible to adjust this code or a new one to change the marker to have circles, triangles, squares, etc based on a different column value?

  Tnx!

  Sub ColorScatterPoints3()
      Dim cht As Chart
      Dim srs As Series
      Dim pt As Point
      Dim p As Long
      Dim Vals$, lTrim#, rTrim#
      Dim valRange As Range, cl As Range
      Dim myColor As Long
  
      Set cht = ActiveSheet.ChartObjects(1).Chart
      Set srs = cht.SeriesCollection(1)
  
     '## Get the series Y-Values range address:
      lTrim = InStrRev(srs.Formula, ",", InStrRev(srs.Formula, ",") - 1, vbBinaryCompare) + 1
      rTrim = InStrRev(srs.Formula, ",")
      Vals = Mid(srs.Formula, lTrim, rTrim - lTrim)
      Set valRange = Range(Vals)
  
      For p = 1 To srs.Points.Count
          Set pt = srs.Points(p)
          Set cl = valRange(p).Offset(0, 1) '## assume color is in the next column.
  
          With pt.Format.Fill
              .Visible = msoTrue
              '.Solid  'I commented this out, but you can un-comment and it should still work
              '## Assign Long color value based on the cell value
              '## Add additional cases as needed.
              Select Case LCase(cl)
                  Case "red"
                      myColor = RGB(255, 0, 0)
                  Case "blue"
                      myColor = RGB(0, 0, 255)
                  Case "green"
                      myColor = RGB(0, 255, 0)
                      Case "yellow"
                      myColor = RGB(255, 192, 50)
  
              End Select
  
              .ForeColor.RGB = myColor
  
          End With
      Next
  End Sub
  
• pandas concatenating dataframes and series

  I have a master dataframe sdb which I wanna go through in a for loop, obtain unique values from certain of its columns and concat these into a new output dataframe df. the index is gonna be different for each series once it has unique values only. see below where I got to:

  tbl = ''
  i = 1
  df = pd.DataFrame()
  
  for col in sdb.columns:
      tbl = 'e_{}'.format(i)
      uni = sdb[col].nunique()
      if uni <= 25:
          tbl = pd.DataFrame(sdb[col].drop_duplicates())
          tbl.reset_index(drop=True)
  
      df = pd.concat([df, tbl], axis=1)
  

  this code works until it gets to the last line and throws error: cannot concatenate object of type ""; only pd.Series, pd.DataFrame... this is not clear to me as when i do .info() on both tbl and df they are shown as dataframes. any thoughs much appreciated
• Can I join on a CONCAT with fields from both tables?

  I'm currently trying to join two tables using a unique identifier.

  SELECT CONCAT(OTable.c_s,'-',CTable.p_p_c,'-',CTable.d_p_c)
     OTable.carrier_delivery_sla,
     CTable.transit_days
  
  FROM *TABLE* AS OTable
  LEFT JOIN *TABLE* AS CTable
  ON OTable.CONCAT(OTable.c_s,'-',CTable.p_p_c,'-',CTable.d_p_c)
            = CTable.CONCAT(OTable.c_s,'-',CTable.p_p_c,'-',CTable.d_p_c)
  

  Any suggestions on how to make this work?
• How to concatenate sql, html and php with this code

  Basically it is an element wich show's an image from the path on database. But I just can't make it work.

                  <img class="img-circle profile_img" id="blah" src=" <?php $query2=mysqli_query($conexao,"select * FROM esc_usuarios_fotos WHERE img_usu_codigo = '" . $_SESSION['codigo'] . "'");
              while($row2=mysqli_fetch_array($query2)){
                  if ((!empty($row2['img_local'])) && (file_exists($row2['img_local']))) {
                      echo '<img class="image--cover" id="blah" src="'.$row2['img_local'].'" alt="Avatar" title="DEFINIDA" onerror="this.onerror=null;this.src=1.png;">';
                  } else {
                      echo 'Show a default image.'; // <img src="path_to_default_image" alt="Default_image"/>
                  }
                } ?>" alt="Avatar" title="DEFINIDA" >