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

I have a report I need to run, and the constituent data is in two other sheets. Chart A contains a monthly report that references cited reports (A), date (B), and grade (C). In Chart B, it's a sheet that every department section issued (A) by serial number (B).

I need to write a report that counts instances of serials in A of Chart A, find and match them in serial (B) of Chart B, then look in (A) to see what department wrote them, then report them in Report C, column Number of Reports (B) and record the grade of each (C, D, and E). The same reports may get different grades from different graders. I need to count each instance as they appear in (A) of Chart A.

I want to capture the data from Chart A on a monthly basis, so Report C will be by month.

I hope what I'm asking even makes sense. I've enclosed a sample sheet of the data I want to run. I'm happy to answer clarifying questions.

Excel Forum is a stupendous resource for newbies like me. My sheets and charts have improved tremendously since I've found this board. The contributors are geniuses. This post is not cross-posted. Thanks in advance for your assistance.

No, not a SUMPRODUCT, but a pivot table. I have included a macro in the file to convert your column A of Chart A into usable values - you can add any new values (3 columns) below the bottom of the new table and the required formulas will copy down, and the pivot table will refresh, too.

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.

Thanks, Mr. Deitrick, for solving my problem. It's about 80% there for my purposes. However, for how I described it, it's 100% solved. I'll have to think about how to re-explain the remaining 20%. Of all the spreadsheets I've built out, this particular one is most vexing. There's a proprietary app that sort of does it already, but it spits out a highly customized (Excel) report that I can't manipulate. It's nearly impossible to put in columnar format (it has headers and wacky spacing and indents). If I could do that, it would be problem solved. I'll probably re-post when I get my thoughts together. Thanks again.