• Combining strings from multiple cells

    From noodnutt@gmail.com@21:1/5 to All on Mon Feb 17 20:02:03 2020
    Hi Team

    I get asked some interesting questions by people thinking just because I know a sprinkling of VBA, I can do god-like things... lol.

    So! I was asked the following:

    Assumed ranges:

    A = Names
    B = PO

    Lets assume the following:
    NAME: PO:
    ABC123 P/L 1234567
    ABC123 P/L 2345678
    ABC123 P/L 3456789

    They would like:
    NAME: PO:
    ABC123 P/L 1234567, 2345678, 3456789

    I have hesitation in admitting this is beyond my scope of knowledge, so hoping someone out there can assist please.

    As always, many thanks for your time and efforts.
    Cheers
    Mark.

    --- SoupGate-Win32 v1.05
    * Origin: fsxNet Usenet Gateway (21:1/5)
  • From noodnutt@gmail.com@21:1/5 to All on Mon Feb 17 21:58:45 2020
    Apologies All

    I just realised, I forgot to mention if I can get this in VBA please.

    I was thinking of something along the lines of:

    Dim sWs As Worksheet: Set sWs = ThisWorkbook.Sheets("Import")
    Dim tWs As Worksheet: Set tWs = ThisWorkbook.Sheets("Conv 1")
    Dim myRng As Range: Set myRng = tWs.Range("A2:A20")
    Dim c As Range
    Dim lRow As Long

    lRow = sWs.Range("A" & sWs.Rows.Count).End(xlUp).Row

    Dim rngNames As Range: Set rngNames = sWs.Range("Q2:Q" & lRow)
    Dim rngSlots As Range: Set rngSlot = sWs.Range("A2:A" & lRow)

    For Each c In myRng
    If Not c = "" Then
    If rngNames = c.Value Then
    If rngNames.Counter > 1 Then
    With c
    .Offset(, 1).Value = Join(Transpose(rngSlot, ","))
    End With
    End If
    End If
    End If
    Next c

    Thnx again.

    --- SoupGate-Win32 v1.05
    * Origin: fsxNet Usenet Gateway (21:1/5)
  • From noodnutt@gmail.com@21:1/5 to All on Mon Feb 17 22:04:21 2020
    And this line should be "NO Hesitation":

    I have hesitation in admitting this is beyond my scope of knowledge, so hoping someone out there can assist please.

    --- SoupGate-Win32 v1.05
    * Origin: fsxNet Usenet Gateway (21:1/5)
  • From Claus Busch@21:1/5 to All on Tue Feb 18 11:08:41 2020
    Hi Mark,

    Am Mon, 17 Feb 2020 20:02:03 -0800 (PST) schrieb noodnutt@gmail.com:

    I get asked some interesting questions by people thinking just because I know a sprinkling of VBA, I can do god-like things... lol.

    So! I was asked the following:

    Assumed ranges:

    A = Names
    B = PO

    Lets assume the following:
    NAME: PO:
    ABC123 P/L 1234567
    ABC123 P/L 2345678
    ABC123 P/L 3456789

    They would like:
    NAME: PO:
    ABC123 P/L 1234567, 2345678, 3456789

    I don't understand your layout correctly.
    Following code works if the names are in column A and the PO in column Q
    of sheets Import and sheets Import is sorted by name:

    Sub Con()
    Dim sWs As Worksheet, tWs As Worksheet
    Dim LRowI As Long, LRowC As Long
    Dim fMatch As Long
    Dim myCnt As Integer
    Dim varPO As Variant
    Dim rngC As Range

    Set sWs = Worksheets("Import"): Set tWs = Worksheets("Conv")
    LRowI = sWs.Cells(Rows.Count, "A").End(xlUp).Row

    sWs.Range("A1:A" & LRowI).AdvancedFilter Action:=xlFilterCopy, _
    CopyToRange:=tWs.Range("A1"), Unique:=True
    LRowC = tWs.Cells(Rows.Count, "A").End(xlUp).Row

    For Each rngC In tWs.Range("A2:A" & LRowC)
    fMatch = Application.Match(rngC, sWs.Range("A1:A" & LRowI), 0)
    myCnt = Application.CountIf(sWs.Range("A:A"), rngC)
    If myCnt > 1 Then
    varPO = Application.Transpose(sWs.Cells(fMatch, "Q").Resize(myCnt))
    rngC.Offset(, 1) = Join(varPO, ", ")
    Else
    rngC.Offset(, 1) = sWs.Cells(fMatch, "Q")
    End If
    Next
    End Sub


    Regards
    Claus B.
    --
    Windows10
    Office 2016

    --- SoupGate-Win32 v1.05
    * Origin: fsxNet Usenet Gateway (21:1/5)
  • From noodnutt@gmail.com@21:1/5 to All on Tue Feb 18 16:02:59 2020
    Hi Claus

    As always, thank you so much for your input.

    I think I was remiss in the way I explained the issue. My Bad. Apologies for not being clear (which I should have been).

    Sheet("Import")
    Customer Name: = Col Q - CountA()
    Ship/PO No: = Col A - Join(Transpose(","))

    When I ran your code, I got a all the shipment No's in Column A (Ascending) & the Company Name in Column B (Not Concatenated).

    Outcome should be.
    Sheet("Conv 1")
    Customer Name: = Col A
    Ship/PO No: = Col B

    I tried changing:
    sWs.Range("A1:A" & LRowI).AdvancedFilter Action:=xlFilterCopy, CopyToRange:=tWs.Range("A1"), Unique:=True

    To
    sWs.Range("Q1:Q" & LRowI).AdvancedFilter Action:=xlFilterCopy, CopyToRange:=tWs.Range("A1"), Unique:=True

    But! it thru an error:
    (The Extract Range has a missing or invalid Field Name)

    Many thanks Claus.

    Cheers
    Mark.

    --- SoupGate-Win32 v1.05
    * Origin: fsxNet Usenet Gateway (21:1/5)
  • From noodnutt@gmail.com@21:1/5 to All on Tue Feb 18 17:57:37 2020
    Hi Claus

    I worked it out.

    Initially, I was using a helper sheet(Conv) to see if the code did what I needed, once I removed the .AdvanceFilter line and referenced the .Match to the main data sheet, it worked perfectly.

    Thank you.
    Mark.

    --- SoupGate-Win32 v1.05
    * Origin: fsxNet Usenet Gateway (21:1/5)