0

So I have a table full of data which looks like this:

          Subject A   Subject B   Subject C   Subject D   Count
Person A     F           F                       F          3
Person B     F           F           F                      3
Person C                             F           F          2
Count        2           2           2           2

And for each person, I need to print out a note for them, which looks something like this.

Person A
Subject A
Subject B
Subject D

Person B
Subject A
Subject B
Subject C

Person C
Subject C
Subject D

So, it should be vertical instead of horizontal. There are about 930 students and 17 subjects. Also, there are 4 columns for each student's class, id, name, and number.

Is it possible to do it with just Excel functions or does VBA must be implemented?

I can also write so Visual Basic application, is it possible to use Visual Basic to read in the data and generate a Excel file as result?

If excelmfunction is to be used, please tell me what function to use. If VBA is to be used, please tell me the guide or function to follow. If VB is to be used, please tell me the used APIs.

EDIT: Since I need a bunch of little notes, how about Mail Merge with Word? Is it doable?

2
  • I don't think this can be done with just worksheet functions but it could be done with VBA. Another way that will rearrange your data almost the way you want is to unpivot your data Jan 24, 2013 at 7:14
  • @MikeFitzpatrick Ya, I see what you mean, unpivot the data seems to simply the process. Thanks for the suggestion.
    – Shane Hsu
    Jan 24, 2013 at 7:25

1 Answer 1

1

I was bored, so I made a quick example to show you how to start.

  • Download & open the example
  • press Alt+F8 & start the macro
  • look at sheet 2

I didn't use advanced methods intentional.
Each code line is commented so others can adapt the code.

Sub makenotes()
Sheets(2).ResetAllPageBreaks    'if there are some old page breaks, we delete them first

Dim subjects()  'create an empty array
subjects() = Array(5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15, 16, 17, 18, 19)   'which columns are subjects?

For irow = 2 To 34      'how many students (rows) should I loop through?
For icolumn = 1 To 19       'and per line: how many subjects & personal (columns) should I loop through?

ivalue = Sheets(1).Cells(irow, icolumn)     'save our cell value. we need it multiple times
if Not ivalue = vbNullString Then       'first we look if the cell has a value to print
    icount = icount + 1         'oh, we found something! lets increase our line counter (start is 0+1)

    On Error Resume Next    'the following match method will produce an error, if nothing will be found. We have to handle it.
    subjectcolumn = Application.Match(icolumn, subjects, 0)   'are we in a subject's column? not? ok give me the error
    On Error GoTo 0    'this turns off our error handle and everything is like before

    If IsError(subjectcolumn) Then       ' if we are not in a subject's column ...
        Sheets(2).Cells(icount, 1) = Sheets(1).Cells(irow, icolumn)  'then just write the normal cell value
    Else
        Sheets(2).Cells(icount, 1) = Sheets(1).Cells(1, icolumn)    'else write the header of that column
    End If
End If

Next icolumn      'we are finished with this column. Go back and start with the next column
Sheets(2).Rows(icount + 1).PageBreak = xlPageBreakManual     'ok, we are finished with that student. Lets insert a page break

Next irow  'and go back and start with the next student
Sheets(2).Columns(1).HorizontalAlignment = xlLeft   'add more formating commands. record them with the macro recorder
End Sub   

enter image description here
Click for larger screenshot

1
  • Wow, you are helpful! And all my god, that's a lot less code. I will show mine later.
    – Shane Hsu
    Jan 24, 2013 at 20:25

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .