List row and column headers from 2-D array based on cell contents

I have a spreadsheet that lists over 100 college courses in a row across the top and about 400 hundred learning objectives in a column down the left side. Each course has been examined to determine which learning objectives are addressed in the course. If a course addresses a learning objective, an X is placed in the cell where the course column and learning objective row intersect, as shown in the example below. Most of the approximately 40,000 cells are empty, but about 1,500 contain an X.

Matrix.JPG

I need to create a two-column list of all of the learning objective to course number matches on a separate spreadsheet. That is, for every X in the cells, I need the learning objective listed in column A and the matching course number listed in column B. This requires that information from the 2-D spreadsheet be consolidated into the two vertical columns as shown in this example:

List.JPG

I'm currently doing this manually, by filtering each column, one at a time, to show only the Xs and then copying and pasting the corresponding learning objective numbers into a second spreadsheet. Needless to say, this is very tedious, time consuming, and vulnerable to human error.

I'd appreciate any advice.

Hi there

You have your sample workbook? Try to upload it at the forum, to avoid that we re-type again your data. Click "Go Advanced" button and find paperclip button to attach the file.

Thanks

Alan,

Thanks so much for the VBA code and directions on its use. Your code worked perfectly and saved me a huge amount of time over the manual method I was using to get this work done. I really do appreciate your clever solution.

Baler5