Making individualized word lists from a SS

[Guest]

I am a teacher tracking missed spelling words by using an Excel SS table with
student names across the top and the spelling words along the side. Each cell
within the table is either blank or contains a number designating the total
times the student has missed each word.

How can I generate personalized lists of missed words for each
student and indicate the number of times each word was incorrectly spelled?

My first thought was to somehow use Access to manipulate the file info, but
a person in that help group said it would take several days work of work to
get a "normalized" datebase out of the Excel file.

Is there a way to do a lookup to see if the cell contains data and if yes,
put the word and number in the cell into a list that also contains other
words for the same name?

Thanks in advance for any suggestions.

 

[Guest]

See if this macro will convert your data to the kind of list you need. It
assumes you have have a table that looks like this, with the words going down
the side & student names across the top

Joe Nancy Don
Dairy 2 1
Water 1 3
Thimble 1 2

Once you installed it, select your whole table, including names & words,
then run the macro. It'll add a new sheet to your workbook that converts the
table above into this

Dairy Joe 2
Dairy Don 1
Water Joe 1
Water Nancy 3
Thimble Nancy 1
Thimble Don 2


For information on installing the code see
Getting Started with Macros and User Defined Functions

http://www.mvps.org/dmcritchie/excel/getstarted.ht
--------------------------------------------------------------------------------------

Sub SpellingTable()
Dim rng As Range
Dim rngNames As Range
Dim rngWords As Range
Dim wsTgt As Worksheet
Dim intNames As Integer
Dim intWords As Integer
Dim x As Integer
Dim y As Integer
Dim iCount As Integer

Set rng = Selection

Set wsTgt = Worksheets.Add
wsTgt.Name = "SpellingLists"

Set rngNames = rng.Offset(0, 1).Resize(1, rng.Columns.Count - 1)
Set rngWords = rng.Offset(1, 0).Resize(rng.Rows.Count - 1, 1)
intNames = rngNames.Columns.Count
intWords = rngWords.Rows.Count
iCount = 1
Set rng = rng.Offset(1, 1).Resize(rng.Rows.Count - 1, rng.Columns.Count - 1)
With rng
For x = 1 To intWords
For y = 1 To intNames
If .Cells(x, y).Value <> "" Then
With wsTgt
.Cells(iCount, 1) = rngWords.Offset(x - 1, 0).Resize(1, 1)
.Cells(iCount, 2) = rngNames.Offset(0, y - 1).Resize(1, 1)
.Cells(iCount, 3) = rng.Cells(x, y).Value
iCount = iCount + 1
End With
End If
Next y
Next x
End With

End Sub

 

[pau1a]

Hi,

Step1
select all cells containing data, include the pupil names and words
Insert
Name
Define
Call this information range "data" (without speechmarks) or other chosen name

Step2
Go through and insert zeros in any cells where the child has not made an
error with a particular word so you don't have any empty cells in your data
range

Step3
Go onto a new sheet in your workbook, this will be using the tabs in the
bottom left.
In the cell A1 type the name of the first child you are creating a list for
In cell A2 type in the following formula
=IF(HLOOKUP($A$1,Data,2)<>0,Sheet1!A2, " ")
The hLookup function looks for the child's name in the top row of the data,
the reference to sheet1 assumes that your data is saved on sheet1, if it
isn't then you will need to alter this.
If the child has got this word wrong once then the word will appear in the
spreadsheet, otherwise there will be a blank row


You can then leave the formulae as they are and type the second name in cell
A1 to produce your second list.

I hope this is helpful, I'm aware there may be a way around entering the
zeros but I've had a quick experiment and couldn't find it myself - it will
be to do with <>0 - which means not equal to zero, you'd instead need a
criteria to check if the cell is empty or not.

Hope this helps.