List Copy Paste Concatenate

[kingsolo]

Hello Experts,

I have a sheet that I need to figure out how to automate:
- I need to copy the Attendees Column (Could be upwards of 500) to Output A2
- Sort alphabetically
- If the level is below the level threshold listed, the person needs to report as NAME,LEVEL
- If there is anything in the percentage column, the person needs to report as NAME,PERCENTAGE
- If the level is below the level threshold AND there is something in the percentage column, the person needs to report as NAME,LEVEL,PERCENTAGE
- The next blank after the list of attendees should be 10 dashes (----------)
- Then copy all items (Maximum 10) from the Additional Info list to the first blank cell below the dashes

I have put an "Expected Outcome" tab to hopefully clear up any questions about how the report has to be submitted.

Thanks in advance.

https://1drv.ms/x/s!Aoa335Q8qKqRhiGPQKuNN3YFnKa0

 

[Fluff]

How about

Sub getList()
   Dim cl As Range
   Dim Lst As Object
   Set Lst = CreateObject("system.collections.arraylist")
   With Lst
      For Each cl In Range("A7", Range("A" & Rows.count).End(xlUp))
         If cl.Offset(, 1).Value < Range("B4").Value Then
            .Add Join(Application.Index(cl.Resize(, 3).Value, 1, 0), ",")
         Else
            .Add cl.Value & "," & cl.Offset(, 2).Value
         End If
      Next cl
      .Sort
      Sheets("Output").Range("a2").Resize(.count).Value = Application.Transpose(.toarray)
   End With
   With Sheets("Output").Range("A" & Rows.count).End(xlUp).Offset(1)
      .Value = String(10, "-")
      Range("E7", Range("E" & Rows.count).End(xlUp)).Copy .Offset(1)
   End With
         
End Sub

 

[kingsolo]

Thanks for the solution. Is there a way to remove the commas if there is no data that should be reported after the clients name? Currently, its showing "Yolanda," or "Clara,2," when it should return "Yolanda" or "Clara,2"

Thanks!

 

[Fluff]

How about

Sub getList()
   Dim Cl As Range
   Dim Lst As Object
   Dim x As String
   
   Set Lst = CreateObject("system.collections.arraylist")
   With Lst
      For Each Cl In Range("A7", Range("A" & Rows.count).End(xlUp))
         If Cl.Offset(, 1).Value < Range("B4").Value Then
            x = Join(Application.Index(Cl.Resize(, 3).Value, 1, 0), ",")
            If Right(x, 1) = "," Then x = Left(x, Len(x) - 1)
            .Add x
         Else
            If Cl.Offset(, 2) = "" Then
               .Add Cl.Value
            Else
               .Add Cl.Value & "," & Cl.Offset(, 2).Value
            End If
         End If
         
      Next Cl
      .Sort
      Sheets("Output").Range("a2").Resize(.count).Value = Application.Transpose(.toarray)
   End With
   With Sheets("Output").Range("A" & Rows.count).End(xlUp).Offset(1)
      .Value = String(10, "-")
      Range("E7", Range("E" & Rows.count).End(xlUp)).Copy .Offset(1)
   End With
         
End Sub

 

[kingsolo]

That got me as long as I put in a level. A lot of guys dont put the level in there, which means they are above the required level for this training cycle. Is what would the line be to say if it's blank skip that comma, but put a comma if there is a reportable percentage? Thank you so much!

 

[Fluff]

Fraid I don't understand, the code is giving the same output as your expected outcome.
What do you want changed?

 

[kingsolo]

So basically say the first person is Abel. The manager doesn't put a level since he knows Abel is above the current level required. It would need to output simply Abel not Abel, as it does now if you do not put a level, I guess because the code is taking the blank as 0 level. At the same time, it's possible to have a blank level but need a percentage. So then it would need to output Abel, 0.25. Hope that makes sense. Thank you again for your efforts, sorry for the delayed response, was sick past few days.

 

[Fluff]

That's what the code from post#4 does.
With your test file I get

Excel 2013 32 bit
	A
1	Subject
2	Abe
3	Ben,4,0.5
4	Clara,2
5	Deborah,4
6	Evan,6
7	Frank,0.75
8	Gabe
9	Hanah,1
10	Ingrid,4
11	Jacob,0.1
Output

 

[kingsolo]

Man... apologies. Just got out of the hospital. So check the link. I uploaded a Macro Enabled workbook with post #4's code. While it does work, when the first reports came in using the macro, I realized that sometimes the manager doesn't put a level since he knows Abe is above the current level required.

To illustrate I cleared the INPUT/LEVEL for Abe, Ben, & Clara. Ran the macro and what it produced is on the OUTPUT tab. On that tab you'll see that Abe has a hanging comma, as does Clara. Ben has commas where the Level would be. I assume the code is thinking 'BLANK'=0? So Im assuming it needs an ISBLANK check in there before dropping the comma on the level?

Hope that illustrated the issue. Again, thanks for the effort you put in, and sorry about the lengthy delay in response.

https://1drv.ms/x/s!Aoa335Q8qKqRhnVfGMlPVLvG1kQB

 

[Fluff]

Ok, try

Sub getList()
   Dim Cl As Range
   Dim Lst As Object
   Dim X As String
   
   Set Lst = CreateObject("system.collections.arraylist")
   With Lst
      For Each Cl In Range("A7", Range("A" & Rows.count).End(xlUp))
         If Cl.Offset(, 1).Value < Range("B4").Value Then
            X = Join(Application.Index(Cl.Resize(, 3).Value, 1, 0), ",")
            X = Replace(X, ",,", ",")
            If Right(X, 1) = "," Then X = Left(X, Len(X) - 1)
            .Add X
         Else
            If Cl.Offset(, 2) = "" Then
               .Add Cl.Value
            Else
               .Add Cl.Value & "," & Cl.Offset(, 2).Value
            End If
         End If
         
      Next Cl
      .Sort
      Sheets("Output").Range("a2").Resize(.count).Value = Application.Transpose(.toarray)
   End With
   With Sheets("Output").Range("A" & Rows.count).End(xlUp).Offset(1)
      .Value = String(10, "-")
      Range("E7", Range("E" & Rows.count).End(xlUp)).Copy .Offset(1)
   End With
         
End Sub