General discussion

  • Creator
    Topic
  • #2083023

    CONCATENATE ROWS – EXCEL

    Locked

    by old and tired.. ·

    We receive RFQ’s via email in excel 5.0 format. Each wksheet (42 separate wksheets per wkbook) has approximately 2500 rows of info organized as
    COL A = PART NUMBER (one cell)
    COL B = DESCRIPTION (from one to 7 cells in the column)

    I need to make a VB macro which would allow me to concatenate all the cells in COL B into one cell, while referencing the individual part number in COL A.

    The following Macro works some…but not OK
    Sub ConcatColumns ()
    Do While ActiveCell <> “” ‘Loops until the active cell is blank.
    ‘The “&” must have a space on both sides or it will be treated
    ‘as a variable type of long integer.
    ActiveCell.Offsett(0, 1).FormulaR1C1 = _
    ActiveCell.Offsett(0, -1) & ” ” & ActiveCell.Offsett(0, 0)
    ActiveCell.Offsett(1, 0).Select
    Loop
    End Sub

    What happens is that I cannot
    a)
    get all the cells in COL B (for Description) relating to any ONE PART Number (in COL A) to be specifically concatenated into COL C,in one cell.
    b)
    ex

All Comments

  • Author
    Replies
    • #3900572

      CONCATENATE ROWS – EXCEL

      by ionfreeman ·

      In reply to CONCATENATE ROWS – EXCEL

      Function loogy(wks As Excel.Worksheet)
      Dim cl As Excel.Range, row As Excel.Range, sList
      ‘insert a new column
      wks.Columns(2).Insert
      For Each row In wks.UsedRange.Rows
      sList = vbNullString
      ‘collect all the values after the new column into a list
      For iCell = 3 To row.Cells.Count
      Set cl = row.Cells(iCell)
      If “” = cl.Value Then Exit For
      sList = sList + CStr(cl.Value) + Chr(59)
      Next iCell
      ‘assign the list to the new column
      row.Cells(2) = sList
      Next row
      End Function

    • #3900442

      CONCATENATE ROWS – EXCEL

      by duong beo ·

      In reply to CONCATENATE ROWS – EXCEL

      My friend sometime has to do such a hard and boring work like what you are experiencing. She complaint to me and all the time I had to create macros for her that’s why I really sympathy for you. I have tested this piece of code and it ran well.
      I’mafraid my answer exceed 1000 characters limit and you cannot load it. If so, feel free to contact me at Ngo_The_Doung@mail.ryoyu.co.jp
      Good luck,
      Duong beo

      Sub Macro1()
      ‘ Macro1 Macro
      ‘ Keyboard Shortcut: Ctrl+Shift+T
      Dim MyRow, MyResultRow As Integer
      Dim MyPartColumn, MyDescColumn, MyResultColumn As String
      Dim MyTempString As String
      Dim MyResult As String
      Dim i, j As Integer

      MyRow = 1
      MyResultRow = MyRow
      MyPartColumn = “A”
      MyDescColumn = “B”
      MyResultColumn = “C”
      MyPartValue= Worksheets(“Sheet1”).Range(MyPartColumn + LTrim(Str(MyRow))).Value

      While MyPartValue <> “”
      MyTempString = Worksheets(“Sheet1″).Range(MyPartColumn + LTrim(Str(MyRow))).Value
      MyResult = ” ”
      While Worksheets(“Sheet1”).Range(MyPartColumn + LTrim(Str(MyRo

    • #3739855

      CONCATENATE ROWS – EXCEL

      by old and tired.. ·

      In reply to CONCATENATE ROWS – EXCEL

      This question was auto closed due to inactivity

Viewing 2 reply threads