Probably SUMPRODUCT (across sheets) but confused due to complexity...

Thanks for such a quick reply. I saw that you converted from multiple entries per cell (the serials) to single entries. One, how did you do that? Two, The chart containing the grades and serials has many other fields, like report author, title, and text. If I did what you suggest, I think I'd have duplicates of those other cells. I'll have to think of the implications of that. Maybe I'm creating an intermediary sheet to manipulate, in which case, it would work.

At any rate, I hadn't thought of your solution. I'm not near the live sheet-- snow day. In the meantime, if you could tell me how you got the result in the modified sheet, I'd appreciate it.

As an aside, it's annoying to have more than one value in a cell, but the way the data comes off of the run it's unavoidable without disturbing the other cells. Thanks again for your help. Cheers!

Originally Posted by jimbosi

I saw that you converted from multiple entries per cell (the serials) to single entries. One, how did you do that?

I use a macro:

Sub SplitOutValues()
Dim lngR As Long
Dim V As Variant
Dim i As Integer

For lngR = Cells(Rows.Count, "A").End(xlUp).Row To 3 Step -1
V = Split(Cells(lngR, "A").Value, ",")
If UBound(V) <> 0 Then
Cells(lngR, "A").Resize(1, 3).Copy
Cells(lngR, "A").Offset(1).Resize(UBound(V), 3).Insert shift:=xlDown
For i = 0 To UBound(V)
Cells(lngR, "A").Offset(i).Value = Trim(V(i))
Next i
End If
Next lngR
End Sub

Two, The chart containing the grades and serials has many other fields, like report author, title, and text....if you could tell me how you got the result in the modified sheet.

I used a Pivot Table, and you just select the fields of interest - you can have as many extra columns of data as you like, and they are ignored until you use them.

As an aside, it's annoying to have more than one value in a cell, but the way the data comes off of the run it's unavoidable without disturbing the other cells. Thanks again for your help. Cheers!

The macro was written to act on column A to C of the active sheet - if you have more information that needs to be replicated when the cells are split, change the 3 in the Resize method to the number of columns needed.