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

See also questions close to this topic

• Drag down auto fill for a custom made function

  I have the following function:

  Function myFunc() As String
  
  Dim i As Long, lTotal As Long
  For i = 3 To 10
  
      If Range("H3").Value = Cells(i, 2).Value And Range("I3").Value < _
      Cells(i, 4).Value And _
      Range("I3").Value >= Cells(i, 3).Value Then
  
      lTotal = Cells(i, 5).Value + lTotal
  
      End If
  
      myFunc = lTotal
  
  Next i
  
  End Function
  

  I want this function to be autofill when I drag it down the column. How can I do that?
• Excel VBA delete email after sending

  might be you are able to help me with VBA code.

  I got a code that send as PDF part of excel sheet. Problem is that email is used by many people and sometimes text is confidential. Is there an option to delete email (sent items and deleted items) after email is sent?

  Using office 2000

  Here is my existing code.

  Sub SendDDocs()
    Dim IsCreated As Boolean
    Dim PdfFile As String, Title As String
    Dim OutlApp As Object
    Dim rng As Range
    Set rng = Range("A1:J103")
  
    Title = Range("o1")
  
  
    Title = Range("o1").Value & " Confidetial"
    PdfFile = CreateObject("WScript.Shell").SpecialFolders("Desktop") & "\" & Title & ".pdf"
  
    With ActiveSheet
      .ExportAsFixedFormat Type:=xlTypePDF, Filename:=PdfFile, Quality:=xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas:=False, OpenAfterPublish:=False
    End With
  
    On Error Resume Next
    Set OutlApp = GetObject(, "Outlook.Application")
    If Err Then
      Set OutlApp = CreateObject("Outlook.Application")
      IsCreated = True
    End If
    OutlApp.Visible = True
    On Error GoTo 0
  
    With OutlApp.CreateItem(0)
  
      .Subject = Title
      .To = "email@email.com"
      .CC = "email@email.com"
      .Body = "" & vbLf & vbLf _
            & "a" & vbLf & vbLf _
            & "" & vbLf _
            & Application.UserName & vbLf & vbLf
      .Attachments.Add PdfFile
  
      Application.Visible = True
      .Display
    End With
  
  Kill PdfFile
  
    If IsCreated Then OutlApp.Quit
  
    Set OutlApp = Nothing
  
  End Sub
  
• Replacing multiple variables in a text substring in excel

  I am using Excel for Mac 2016.

  In column B there are 1.000+ product names, alphanumeric. One part of the product name can be the name of a color.

  I now want to separate the color name from the product name and move the color name to column C, replacing it in column B with "" (empty).

  In column A I have listed all color names that can appear in the product name.

  My problem is to have the list of color names in A to look for as a substring in B. I tried arrays and ranges with SEARCH and FIND but only the first color is matched. I have tried =FIND({26,29},B2) and =FIND(A1:A10;B2)

  Hope to find help here.

  Sample data:

  column a:
  black
  yellow
  red
  
  column b:
  first product red
  second product black 2018
  third product 2017
• Printing all words from the word document using VBI macro

  I want to create a loop that will print all words from the active word document.

  My current solution is:

  Sub findWord()
      For Each aWord In ActiveDocument.Words
      Debug.Print (aWord.Text)
      Next aWord
      End Sub
  

  Unfortunatly when I run this code it prints random words from the document in random order.

  What am I doing wrong?
• VBA Application.Workbooks.Open(file_name.xls) opens already opened workbook instead of file_name passed

  I am trying to open a workbook using Application.Workbooks.Open by passing the file name along with path as an argument. If any other excel is opened it is activating opened excel instead of opening excel with the given file name

  My Code :

  Dim rsWB As Workbook
  Dim fname As String
  
  fname = "C:\Users\test\my development\ouput\new_output.xlsx"
  Set rsWB = Application.Workbooks.Open(fname)
  
  rsWB.Activate
  MsgBox (rsWB.Name)  
  

  Case 1. If no other excel file is open. rsWB.Name = new_output.xlsx

  Case 2. If output.xlsx is already open. rsWB.Name = output.xlsx
• Deleting row elements except column1 and then shift cells up for the rest

  Is there any way to delete all the row elements except the row element in column A and then shift cells up only for the deleted row using VBA coding. Right now I manually select a single row except the row element in column A and then delete using ctrl+- and select shift cells up.

  I have also tried this

  Worksheets("sheet1").Rows(i).Delete Shift:=xlShiftUp
  

  but it deletes the whole row.
• Automate copy paste Excel ranges dynamically from different sheets

  I have tables in different sheets of excel and requirement is to copy second last row of table to last row. Can someone pls help me how can we dynamically find table size so that code wont be required to change evrytime when table size changes? There are many tables and we want to automate this task using vba code and macro button.

  I have written a code but that is range specific and everytime I need to update the range and process become cumbersome.

  Sub history_copy()
  
  Set obj = ActiveWorkbook.Sheets("sheet A")
  obj.Range("E12:N12").Value = obj.Range("E11:N11").Value
  obj.Range("E34:N34").Value = obj.Range("E33:N33").Value
  obj.Range("E46:N46").Value = obj.Range("E45:N45").Value
  obj.Range("E57:N57").Value = obj.Range("E56:N56").Value
  
  Set obj1 = ActiveWorkbook.Sheets("sheet b ")
  obj1.Range("G21:O21").Value = obj1.Range("G20:O20").Value
  
  Set obj2 = ActiveWorkbook.Sheets("sheet c")
  obj2.Range("D4:M4").Value = obj2.Range("D3:M3").Value
  obj2.Range("D14:M14").Value = obj2.Range("D13:M13").Value
  obj2.Range("D23:M23").Value = obj2.Range("D22:M22").Value
  obj2.Range("D33:M33").Value = obj2.Range("D32:M32").Value
  obj2.Range("D43:M43").Value = obj2.Range("D42:M42").Value
  obj2.Range("D52:M52").Value = obj2.Range("D51:M51").Value
  obj2.Range("D61:M61").Value = obj2.Range("D60:M60").Value
  obj2.Range("D70:M70").Value = obj2.Range("D69:M69").Value
  obj2.Range("D79:M79").Value = obj2.Range("D78:M78").Value
  obj2.Range("D88:M88").Value = obj2.Range("D87:M87").Value
  obj2.Range("D97:M97").Value = obj2.Range("D96:M96").Value
  obj2.Range("D106:M106").Value = obj2.Range("D105:M105").Value
  obj2.Range("D114:M114").Value = obj2.Range("D113:M113").Value
  
  Set obj3 = ActiveWorkbook.Sheets("sheet d")
  obj3.Range("D4:L4").Value = obj3.Range("D3:L3").Value
  
  
  
  Set obj5 = ActiveWorkbook.Sheets("sheet f")
  obj5.Range("D4:L4").Value = obj5.Range("D3:L3").Value
  
  Set obj6 = ActiveWorkbook.Sheets("sheet e")
  obj6.Range("C4:C12").Value = obj6.Range("B4:B12").Value
  obj6.Range("H4:H8").Value = obj6.Range("G4:G8").Value
  obj6.Range("N4:N11").Value = obj6.Range("M4:M11").Value
  obj6.Range("S4:S10").Value = obj6.Range("R4:R10").Value
  obj6.Range("X4:X18").Value = obj6.Range("W4:W18").Value
  obj6.Range("AD4:AD9").Value = obj6.Range("AC4:AC9").Value
  obj6.Range("AI4:AI13").Value = obj6.Range("AH4:AH13").Value
  obj6.Range("AO4:AO6").Value = obj6.Range("AN4:AN6").Value
  obj6.Range("AT4:AT11").Value = obj6.Range("AS4:AS11").Value
  obj6.Range("AY4:AY20").Value = obj6.Range("AX4:AX20").Value
  obj6.Range("BD4:BD10").Value = obj6.Range("BC4:BC10").Value
  obj6.Range("BI4:BI21").Value = obj6.Range("BH4:BH21").Value
  
  Set obj7 = ActiveWorkbook.Sheets("sheet i")
  obj7.Range("C4:C12").Value = obj7.Range("B4:B12").Value
  obj7.Range("H4:H8").Value = obj7.Range("G4:G8").Value
  obj7.Range("N4:N11").Value = obj7.Range("M4:M11").Value
  obj7.Range("S4:S10").Value = obj7.Range("R4:R10").Value
  obj7.Range("X4:X18").Value = obj7.Range("W4:W18").Value
  obj7.Range("AD4:AD9").Value = obj7.Range("AC4:AC9").Value
  obj7.Range("AI4:AI13").Value = obj7.Range("AH4:AH13").Value
  obj7.Range("AO4:AO6").Value = obj7.Range("AN4:AN6").Value
  obj7.Range("AT4:AT11").Value = obj7.Range("AS4:AS11").Value
  obj7.Range("AY4:AY20").Value = obj7.Range("AX4:AX20").Value
  obj7.Range("BD4:BD10").Value = obj7.Range("BC4:BC10").Value
  obj7.Range("BI4:BI21").Value = obj7.Range("BH4:BH21").Value
  
  
  End Sub
  
• How to use Concat in a where clause in MYSQL?

  I am trying to collect all the names with an extension at the end of a string in MYSQL.

  Table1

  ID  | Name 
   0    Bob 1
   1    Bob 2
   2    Joe 1
   3    Joe 2
   4    Bob
   5    Joe
  

  The query I'm running is similar to this one:

  SELECT ID, name
  FROM Fname
  WHERE name = CONCAT(name,' ','1');`
  

  Where I want to collect all the Names that end it a space and 1.
• pandas aggregating frames by largest common column denominator and filling missing values

  I have been struggling with this issue for a bit and even though there are some workarounds i would assume, I would love to know if there is an elegant way to achieve this result:

  import pandas as pd
  import numpy as np
  
  data = np.array([
              [1,10],
              [2,12],
              [4,13],
              [5,14],
              [8,15]])
  
  df1 = pd.DataFrame(data=data, index=range(0,5), columns=['x','a'])
  
  data = np.array([
              [2,100,101],
              [3,120,122],
              [4,130,132],
              [7,140,142],
              [9,150,151],
              [12,160,152]])
  
  df2 = pd.DataFrame(data=data, index=range(0,6), columns=['x','b','c'])
  

  Now I would like to have a data frame that concatenate those 2 and fill the missing values with the previous value or the first value otherwise. Both data frames can have differnet sizes, what we are interested in here is the unique column x.

  That would be my desired output frame df_result. x is the aggregated unique "x" between the 2 frames

      x   a   b   c
  0   1   10  100 101
  1   2   12  100 101
  2   3   12  120 122
  3   4   13  130 132
  4   5   14  130 132
  5   7   14  140 142
  6   8   15  140 142
  7   9   15  150 151
  8   12  15  160 152 
  

  Any help or hint would be much appreciated, thank you very much
• Keras concatenate LSTM model with non-LSTM model

  I have two data. One is time series and the other contains features such as Sex, education, etc. and I want to concatenate output of LSTM model and a dense model. However, I got an error message (look at the end).

  This is what the data looks like:

  

  

  And this is the code:

  # PAY_data net
  input1 = Input(shape=(6,1))
  pay = LSTM(10)(input1)
  pay = Dense(10, activation='relu')(pay)
  
  # DEMO_data net
  input2 = Input(shape=(5,1))
  demo = Dense(10, activation='relu')(input2)
  demo = Dense(10, activation='relu')(demo)
  
  merge = concatenate([pay, demo])
  
  hidden1 = Dense(10, activation='relu')(merge)
  
  output = Dense(1, activation='sigmoid')(merge)
  model = Model(inputs=[input1, input2], outputs=output)
  
  print(model.summary())
  
  model.compile(loss='binary_crossentropy', optimizer='adam', metrics=    ['accuracy'])
  
  model.fit([PAY_data, DEMO_data], y,nb_epoch=20, batch_size=50, verbose=2, validation_split=0.2)
  

  and this is the error I get: