Combine multiple rows into a single row with concatenation

Hi,

Would love a macro to convert multiple rows to a single row with some of these rows in the result needing to be concatenated... Found a very similar solved thread, but couldn't modify it to produce my desired result. (the link to this thread is: http://www.excelforum.com/excel-new-...02#post2833302).

I have attached the xlsx with test data. Sheet 1 has the original data, sheet 2 has the result that I want to achieve with a macro...(I have included instructions on how I achieve the result, but it requires way too many manual steps that I'm hoping you can provide a solution to)

StudentTimetable.xlsxStudentTimetable.xlsx

Thanks in advance.

Regards

Michael

MichaelWood,

For each student, for each day of the week Monday thru Friday, will there always be 7 rows of data?
HR
Period 1
Period 2
Period 3
Period 4
Period 5
Period 6


I have started on the macro coding, but, will not be able to finish it very soon, due to other obligations.

In the Quick Reply box, just put the word BUMP. Then, click on the Post Reply button, and someone else will assist you.

Hi Michael,

I've been wanting a problem like this to show the differences between Excel Pivot Tables and Access Pivot Tables. In Excel the answer is normally a number, so your problem, to show text in the values area is hard to do. In Access the Pivot Tables show Text. I'm hoping you have Microsoft Access as well as Excel. Then using Access I've imported your first sheet of the attached above. Doing a Pivot Table Query in Access, I used the student code for the report filter and simple drag and drop the other fields to rows and columns.

I believe the problem is to print out each students schedule with subject, teacher, room and period, etc. If you filter by a single student, their schedule is all displayed. Change the Pivot Table filter to the next student and the data changes. You should be using Access instead of Excel to do this problem.

I hope this helps.

MichaelWood,


I have been trying to learn what jindon can do. jindon is the Master.


Here is a very slight change/addition to his original code (in BOLD) that will add the Room's to worksheet "Result for 3 students".


Please TEST this FIRST in a COPY of your workbook (always make a backup copy before trying new code, you never know what you might lose).

1. Copy the below code, by highlighting the code and pressing the keys CTRL + C
2. Open your workbook
3. Press the keys ALT + F11 to open the Visual Basic Editor
4. Press the keys ALT + I to activate the Insert menu
5. Press M to insert a Standard Module
6. Where the cursor is flashing, paste the code by pressing the keys CTRL + V
7. Press the keys ALT + Q to exit the Editor, and return to Excel
8. To run the macro from Excel, open the workbook, and press ALT + F8 to display the Run Macro Dialog. Double Click the macro's name to Run it.

Option Explicit
Sub test()
' jindon, 06/23/2012
' http://www.excelforum.com/excel-general/840158-combine-multiple-rows-into-a-single-row-with-concatenation.html
' stanleydgromjr, 06/23/2012
' Here is a very slight change/addition to his original code that will add the Room's to worksheet "Result for 3 students".
Dim a, b(), i As Long, ii As Long, n As Long
Dim AL As Object, txt As String, temp As String
Set AL = CreateObject("System.Collections.ArrayList")
a = Sheets("StudentTimetables").Range("a1").CurrentRegion.Value
For i = 2 To UBound(a, 1)
  txt = Join$(Array(a(i, 10), a(i, 11)), vbLf)
  If Not AL.Contains(txt) Then AL.Add txt
Next
ReDim b(1 To UBound(a, 1), 1 To AL.Count + 11)
n = 1
For i = 1 To 11
  b(n, i) = a(n, i)
Next
For i = 0 To AL.Count - 1
  b(n, i + 12) = AL(i)
Next
With CreateObject("Scripting.Dictionary")
  .CompareMode = 1
  For i = 2 To UBound(a, 1)
    txt = ""
    For ii = 1 To 6
      txt = txt & Chr(2) & a(i, ii)
    Next
    If Not .exists(txt) Then
      n = n + 1
      For ii = 1 To 11
        b(n, ii) = a(i, ii)
      Next
      .Item(txt) = n
    End If
    temp = Join$(Array(a(i, 10), a(i, 11)), vbLf)

    b(.Item(txt), AL.IndexOf(temp, 0) + 12) = Join$(Array(a(i, 7), a(i, 8), a(i, 9)), vbLf)

  Next
End With
With Sheets("Result For 3 students").Cells(1).Resize(n, UBound(b, 2))
  .CurrentRegion.ClearContents
  .Value = b
  .Rows(1).Font.Bold = True
End With
Set AL = Nothing
End Sub

Before you use the macro with Excel 2007 or newer, save your workbook, Save As, a macro enabled workbook with the file extension .xlsm


Then run the updated test macro.

Thank you sooo much MarvinP, jindon and stanleydgromjr for your efforts, it is greatly appreciated.

stanleydgromjr your code works perfectly for this data set, thank you.

Thanks stanleydgromjr you are brilliant.

MichaelWood,

Thanks stanleydgromjr.

You are very welcome. Glad I could help.

you are brilliant

No, the brilliant is jindon.