Retrieve row/column headers from Excel matrix into Word mailmerge

Question

I have a spreadsheet (to manage entries in a competition) which is basically a matrix of Xs or blanks, depending on whether the person (row header) has entered a given class (column header).

I want to retrieve a set of value pairs of person/class for each cell that has an X, to use in a mailmerge to print out competition entry slips. My VBA skills are rather elementary, and I haven't found a way to do this without creating an enormous and complicated table.

For example, fred has entered class 1 and 2, and mary has entered classes 3 and 4. I want to get a table with 4 records like    fred,1; fred,2; mary,3; mary,4;   that I can use in a mailmerge.

I can't help thinking this should be simple if only I knew how! Either Office 2003 or 2007. All clues gratefully received!

Answer 1

If you have more than 25 classes, you will need to use this version:

Dim myrange As Range

Dim numrows As Long, numcols As Long

Dim arrData As Variant

Dim i As Long, j As Long, k As Long

Worksheets(1).Activate

Set myrange = Worksheets(1).Range("A1", Range("A1").End(xlDown))

numrows = myrange.Rows.Count

Set myrange = Worksheets(1).Range("A1", Range("A1").End(xlToRight))

numcols = myrange.Columns.Count

arrData = Worksheets(1).Range(Cells(1, 1), Cells(numrows, numcols))

Worksheets(2).Range("A1").Value = "Entrant"

Worksheets(2).Range("B1").Value = "Class"

k = 1

For i = 2 To UBound(arrData, 1)

For j = 2 To UBound(arrData, 2)

If arrData(i, j) <> "" Then

k = k + 1

Worksheets(2).Cells(k, 1).Value = arrData(i, 1)

Worksheets(2).Cells(k, 2).Value = j - 1

End If

Next j

Next i

Worksheets(2).Activate

It can also be used if there are less than 25 classes

--

Hope this helps.

Please reply to the newsgroup unless you wish to avail yourself of my

services on a paid consulting basis.

Doug Robbins - Word MVP, Posted via the NNTP Bridge

"loiswakeman" wrote in message news:2a66747c-9642-4e19-88bb-582976bc0b6d...

I have a spreadsheet (to manage entries in a competition) which is basically

a matrix of Xs or blanks, depending on whether the person (row header) has

entered a given class (column header).

I want to retrieve a set of value pairs of person/class for each cell that

has an X, to use in a mailmerge to print out competition entry slips. My VBA

skills are rather elementary, and I haven't found a way to do this without

creating an enormous and complicated table.

For example, fred has entered class 1 and 2, and mary has entered classes 3

and 4. I want to get a table with 4 records like fred,1; fred,2; mary,3;

mary,4; that I can use in a mailmerge.

I can't help thinking this should be simple if only I knew how! Either

Office 2003 or 2007. All clues gratefully received!

---

Doug Robbins - Word MVP

Answer 2

If your data in on Sheet 1 of the workbook in the following format

entrant Class1 Class2 Class3 Class4

Fred x xMary x x

running a macro (in Excel) with the following code

Dim myrange As Range

Dim numrows As Long, numcols As Long

Dim rngend As String

Dim arrData As Variant

Dim i As Long, j As Long, k As Long

Worksheets(1).Activate

Set myrange = Worksheets(1).Range("A1", Range("A1").End(xlDown))

numrows = myrange.Rows.Count

Set myrange = Worksheets(1).Range("A1", Range("A1").End(xlToRight))

numcols = myrange.Columns.Count

rngend = Chr(64 + numcols) & numrows

arrData = Worksheets(1).Range("A1:" & rngend)

Worksheets(2).Range("A1").Value = "Entrant"

Worksheets(2).Range("B1").Value = "Class"

k = 1

For i = 2 To UBound(arrData, 1)

For j = 2 To UBound(arrData, 2)

If arrData(i, j) <> "" Then

k = k + 1

Worksheets(2).Range("A" & k).Value = arrData(i, 1)

Worksheets(2).Range("B" & k).Value = j - 1

End If

Next j

Next i

Worksheets(2).Activate

will populate Sheet 2 of the workbook with:

Entrant Class

Fred 1

Fred 2

Mary 3

Mary 4

--

Hope this helps.

Please reply to the newsgroup unless you wish to avail yourself of my

services on a paid consulting basis.

Doug Robbins - Word MVP, Posted via the NNTP Bridge

"loiswakeman" wrote in message news:2a66747c-9642-4e19-88bb-582976bc0b6d...

I have a spreadsheet (to manage entries in a competition) which is basically

a matrix of Xs or blanks, depending on whether the person (row header) has

entered a given class (column header).

I want to retrieve a set of value pairs of person/class for each cell that

has an X, to use in a mailmerge to print out competition entry slips. My VBA

skills are rather elementary, and I haven't found a way to do this without

creating an enormous and complicated table.

For example, fred has entered class 1 and 2, and mary has entered classes 3

and 4. I want to get a table with 4 records like fred,1; fred,2; mary,3;

mary,4; that I can use in a mailmerge.

I can't help thinking this should be simple if only I knew how! Either

Office 2003 or 2007. All clues gratefully received!

---

Doug Robbins - Word MVP

Answer 3

What does your data layout actually look like in Excel, please show a sample.

---

If this answer solves your problem, please check Mark as Answered. If this answer helps, please click the Vote as Helpful button. Cheers, Shane Devenshire

Answer 4

Do you already have this sheet, or are you in the design stage?

If you are in the design stage, you might be better off 

 a. storing the data in the format you want for the merge - like this, I think:

Name Class

fred 1

fred 2

mary 3

mary 4

then use that list as

 a. the data source for your merge

 b. the data source for an Excel Pivot table in another worksheet in the workbook, showing the class numbers across the top, the names down the side, using max(class) as the value to display, and formatting the numbers as "X".

You wouldn't be able to "work backward", i.e. you would not be able to type an X into the table, and see a new record appear in the data source, but once you had it set up, I do not think it would be too hard just to identify the right place to add a record in the data source table, and refresh the pivot table.

This is not trivial if you haven't worked with Pivot Tables before. It isn't something I have done much of either, but it's probably how I would do it. I would suggest that you have a look at Pivot Tables in general - if you need some step-by-step instructions, I can attempt that.

If you already have lots of data in your sheet, I think it would need some VBA to extract it - I can also try to provide that also.

You might find it useful to post your message and this suggestion in an Excel group, because they can probably help you do this with far more speed and expertise than I can.