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

See also questions close to this topic

• How to Delete COMPLETELY Blank Rows VBA

  In my macro, I want to delete completely blank Rows. Not Rows that has something in one column because that is still valuable information.

  I have looked all over the internet but if someone could come up with a function that deletes ENTIRELY blank rows and not just rows with something missing in a few columns that would be awesome.

  (Information is only on Columns A through N and roughly 7 thousand rows). I haven't developed any code for this because I am really stumped.
• Summing csv columns

  I have a csv file with a couple columns and about 100k rows. One of the columns is a date, and I was wondering what the easiest way was to count the number of rows that have a certain date for all the possible dates and make a new csv file with just the date and the number of rows that have that date in the specific column. Any language or method is fine!

  Thanks

  Example of what data looks like now
• How do I import Subscript and Superscript from an excel file to mySQL Database?

  We have a lot of chemistry MCQs in an excel file with lot of chemistry formulas and equations. When we tried to import these MCQs into mySQL based database all subscripts used in formulas lost and taken as normal character e.g, H2SO4, CO2, CH3 etc and makes our formulas and elements name nonsense. Is there any solution by which mySQL can import subscript and superscript as it is from excel? Or Is there any solution so we can force excel to consider subscript and superscript as inserted symbols because mySQL can pick symbols as it is?
• .Find method not searching in order

  Can anyone explain why the string in D2 is being built out of order in the first loop?

  This is only happening for the first search value, Dom. The rest of the strings are being built in the order in which they appear (see Column B). I treid adding SearchDirection:= xlNext but the output remained the same with or without that bit of code.

  In the photo, Column A:B are the raw data and Column C:D are the output from macro.

  The cell in question is D2. It should show USD/EUR/GBP instead of EUR/GBP/USD

  

  Dim ws As Worksheet: Set ws = ThisWorkbook.Sheets("Sheet1")
  Dim FoundName As Range, SearchRange As Range, Names As Range, Name As Range
  Dim MyString As String, i As Long
  
  ws.Range("A1:A" & ws.Range("A" & ws.Rows.Count).End(xlUp).Row).AdvancedFilter Action:=xlFilterCopy, CopyToRange:=ws.Range("C1"), Unique:=True
  
  Set SearchRange = ws.Range("A2:A" & ws.Range("A" & ws.Rows.Count).End(xlUp).Row)
  Set Names = ws.Range("C2:C" & ws.Range("C" & ws.Rows.Count).End(xlUp).Row)
  
  For Each Name In Names
      Set FoundName = SearchRange.Find(Name, SearchDirection:=xlNext)
          For i = 1 To Application.WorksheetFunction.CountIf(SearchRange, Name)
              MyString = MyString & FoundName.Offset(, 1) & "/"
              Set FoundName = SearchRange.FindNext(FoundName)
          Next i
      Name.Offset(, 1) = Left(MyString, Len(MyString) - 1)
      MyString = ""
  Next Name
  
• Translating working Python API script into VBA -

  I've been scouring the interwebs for days now looking for the solution to translating my API script into VBA to allow it to operate directly within an Excel file. I have managed to replicate the signature key that is produced in my python script. However I'm still hitting invalid credential errors as I believe i'm structuring the headers and parameters in the correct way.

  HEADER_A,HEADER_B and HEADER_C are placeholders for the headers required as outlined in the documentation. The parameter "job_id" is also required.

  Working python script:

  signature = generateSignature(sigtag,key)
  headers = {"HEADER_A": signature,
             "HEADER_B": timestamp,
             "HEADER_C": employee_id}
  apirequest = requests.get(GEOPAL_BASE_URL + apicall,
                            headers=headers,
                            params={"job_id": job_id})
  apifeedback = apirequest.json() 
  

  I've been piecing together approaches to the problem, however i cannot find an example where someone has had to use both parameters and headers like I had to use with the requests library above.

  Here is my progress so far in VBA:

  Private Sub cmdgetjobinfo()
      Dim URL As String, timestamp As String, empID As String, key As String, method As String, signature As String, hash As String, params As String
      Dim objHTTP As Object: Set objHTTP = CreateObject("WinHttp.WinHttpRequest.5.1")
      Dim ws As Worksheet: Set ws = Worksheets("API")        
      URL = "https://app.addressofwebsite.com/"
      uri = "api/jobs/get"
      timestamp = Format(Now(), "ddd, DD MMM YYYY HH:MM:SS") & " +0000"
      empID = "715"
      method = "get"
      key = "abckey"
  
      hash = ComputeHMACSHA256(key, method & uri & empID & timestamp)
      signature = EncodeBase64(hash)
      params = ("job_id=testid")
  
      objHTTP.Open "GET", URL & uri, False
      objHTTP.setRequestHeader "Content-Type", "application/json", "Accept", "application/json"
      objHTTP.setRequestHeader "Authorization", "HEADER_A", signature,"HEADER_B", timestamp,"HEADER_C", empID
      objHTTP.send params
      strResult = objHTTP.responseText
      Worksheets("API").Range("A10:A10") = strResult
  End Sub
  

  Current output from script:

  {"status":false,"tag":"api\/jobs","error_code":0,"error_message":"Invalid Credentials"}
  

  I know the issue exists around the 'objHTTP.send' and 'objHTTP.setRequestHeader' lines. I've tried them all on separate requestheader lines, without the 'authorisation' tag and lots of other random things. Is anyone able to point out where I'm failing here?

  Many many thanks for your time!
• How to programatically define FieldInfo parameter on Workbooks.OpentText in Excel VBA?

  The Workbooks.OpenText method takes a parameter called FieldInfo to determine the format types in each column. For example the following code will open a text file and give Columns 1 and 2 a Text format, and Column 3 a General format.

  Workbooks.OpenText fileName:="C:\somefile.txt", _
      FieldInfo:=Array(Array(1, 2), Array(2, 2), Array(3, 1))
  

  Let's say at runtime I determine that my text file has 4 columns and columns 2 and 4 should be text. How can I pass that in programmatically?
• Excel VBA - Error when running vba code to execute autofill column macro

  Background: 1.My workbook has 3 sheets (names of the sheets: 1.Key Contacts & RACI, 3.Deliverables, 5.Meetings). 2.Each sheet has an ID column that when a new row is added it will select the select the first ID in the column and then autofill the rest of the cells in that column within the Excel Table.

  3. Each sheet has VBA code that will run the macro when a target formula based cell changes value.

  Problem: When adding a new row in the tables in Deliverables and the Meetings sheets, the code and macros run perfectly but when I add rows to the table in the Key Contacts RACI sheet I get an error (Run-time error 1004 Autofill method of Range class failed).

  Here's the problem, for some reason instead of the Marco running for the Key Contacts sheet, it somehow tries to run the macro for the Deliverables sheet or at least that's where the debug take me to...code shown here.

  Private Sub Worksheet_Calculate()
  
  'This code will auto run given macro when the formula results in the cell changes
  
  Private Sub Worksheet_Calculate()
  
  'This code will auto run given macro when the formula results in the cell changes
  
  Static OldVal As Variant
  If Range("C3").Value <> OldVal Then
      OldVal = Range("C3").Value
          Call Macro2 'this updates the IDs on the Key Contacts and RACI tab
  
  Application.ErrorCheckingOptions.BackgroundChecking = False
  
      End If
  
  
  End Sub
  
  Sub Macro2()
  '
  ' Macro2 Macro
  '
  
  '
      Range("B5").Select
      Selection.AutoFill Destination:=Range("Table7[ID]")
      Dim sourceCol As Integer, rowCount As Integer, currentRow As Integer
      Dim currentRowValue As String
  
      sourceCol = 4   'column d has a value of 4
      rowCount = Cells(Rows.Count, sourceCol).End(xlUp).Row
  
      'for every row, find the first blank cell and select it
      For currentRow = 5 To rowCount
          currentRowValue = Cells(currentRow, sourceCol).Value
          If IsEmpty(currentRowValue) Or currentRowValue = "" Then
              Cells(currentRow, sourceCol).Select
          End If
      Next
  End Sub
  

  Note that the macro it should be running is Macro2 and the table it should update is Table9

  I'm so lost on this one.

  Images of code and macroImages of code and macro
• Postgres make spaces with ''::text statement

  Does anyone know why Postgresql 9.3.17 do this with concat() function ?

  If I do :

  SELECT concat(tab.a::text, ' '::text, tab.b::text) AS field FROM schem.tab

  I'll have "a b" with 3 spaces (one in the ' ' and two added by Postgres)

  If I do:

  SELECT concat(concat(tab.a::text, ' '::text), tab.b::text) AS field FROM schem.tab

  I'll have "a b" with my only space
• C# linq-to-sql. Concat with order

  I have 2 linq-to-sql queries in C#, from Table1 (id, itemid, date) and Table2 (id, itemid, date) and I am using concat between them but the dates are not ordered. How I can order the result date-wise?
• Concatenate with new observations- DataFrame

  I'm trying to concatenate with new observations. I got the answer that I think it's right but still get the system came back to me saying "ValueError Can only compare identically-labeled DataFrame objects" Can anyone tell me why there's value error while I think I got the right result?

  Here is the question:

  Assume the data frame Employee is as below:

        Department      Title  Year Education Sex
  Name                                           
  Bob           IT    analyst     1  Bachelor   M
  Sam        Trade  associate     3       PHD   M
  Peter         HR         VP     8    Master   M
  Jake          IT    analyst     2    Master   M
  

  and another data frame new_observations is:

           Department Education Sex      Title  Year
  Mary             IT             F         VP   9.0
  Amy               ?       PHD   F  associate   5.0
  Jennifer      Trade    Master   F  associate   NaN
  John             HR    Master   M    analyst   2.0
  Judy             HR  Bachelor   F    analyst   2.0
  

  Update Employee with these new observations.

  Here is my code:

      import pandas as pd
      Employee =pd.DataFrame({"Name":["Bob","Sam","Peter","Jake"],
                      "Education":["Bachelor","PHD","Master","Master"],
                     "Sex":["M","M","M","M"],
                      "Year":[1,3,8,2],
                     "Department":["IT","Trade","HR","IT"],
                "Title":["analyst", "associate", "VP", "analyst"]})
  
      Employee=Employee.set_index('Name')
  
      new_observations = pd.DataFrame({
                 "Name": ["Mary","Amy","Jennifer","John","Judy"],
                 "Department":["IT","?","Trade","HR","HR"],
                 "Education":["","PHD","Master","Master","Bachelor"],
                 "Sex":["F","F","F","M","F"],
                 "Title":["VP","associate","associate","analyst","analyst"],
                 "Year":[9.0,5.0,"NaN",2.0,2.0]},
                 columns= 
                 ["Name","Department","Education","Sex","Title","Year"])
  
      new_observations=new_observations.set_index('Name')
  
      Employee = Employee.append(new_observations,sort=False)
  

  Here is my result:

  code result

  I also tried

  Employee = pd.concat([Employee, new_observations], axis = 1, sort=False)