How to Create a Macro to Edit a Variable Amount of Information

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hi,

I have an Excel table full of information for a certain number of
individuals. I've created a macro to reorganize, transpose, and arrange the
data that works beautifully for information for two individuals. However,
the number of individuals whose information is in the table will vary, so I
need to be able to set the macro to do these tasks regardless of the amount
of information. I imagine that I need to be able to set it to copy and paste
all the rows for one name, then all the rows for the second name, so on,
until there is a blank cell (indicating that all of the information has been
manipulated and the macro can stop).

Any ideas about how to do this would be great, as well as resources for more
information. I've looked around but have found much specific to this issue.

I'm using Excel 2003 on Windows XP.

Thanks,

Matt
 
Give us an example of the data and the results.

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)
 
Sure.

The information is student tests scores from an exam on an online testing
software. The results are downloaded from the software into excel.

The raw data looks something like this:
FIRST NAME QUESTION ID SCORE
Matthew 1 0
Matthew 2 1
Matthew 3 1
John 1 1
John 2 1
John 3 0

In reality, the number of students varies and the number of questions is
closer to 100 (While the number of students varies, there is always the same
number of questions for each student). I want to take this raw data and make
it look like this:

1 2 3
Matthew 0 1 1
John 1 1 0

So, the first column is the student's name and each subsequent column is the
score for the question, beginning with question 1 and going to question 100.

This easy enough to do by transposing the data and doing some basic editing.
Automating it with a macro is also easy using record. However, I want the
macro to perform these functions automatically regardless of the number of
students.

I think a place to start might be writing some code to tell the macro to
count the number of rows until it gets to a blank, thus specifying ranges and
establishing patterns to count the data.

Hope this is clear. I'm glad to try to clarify further.

Matt
 
Back
Top