Summary

[harmonix]

Hello Experts,

Another newbie here. I know this maybe very easy but I'd like to see the sample code for this summary for a table of data. For the sample data I attached, I'd like to automatically create a summary which the output should look like the one on the Summary Sheet. If I run the procedure, It will create the dates I specified for the summary as well as the data found in it.

Sample data.xlsx

*My example is a bit lame as I cant think of any data.

Thank you so much for your guidance.

[mdmackillop]

Try this

[harmonix]

Thank you so much md. I know this maybe too much but id like to create a vba code for this. Can you help? I've tried to create it but am having a hard time due to inexperience. Appreciate anything that could get me rolling with this. Thanks

[mdmackillop]

[vba]Sub Summary()
Dim wsSrce As Worksheet, wsSumm As Worksheet

Dim n As Range, Nms As Range, Dts As Range
Dim r As Range

Set wsSrce = Sheets("Source")
Set wsSumm = Sheets("Summary")

With wsSrce
Set n = .Cells(Rows.Count, 1).End(xlUp)
Set Nms = Range(n, n.End(xlUp))
Set Dts = Range(.Cells(2, 2), .Cells(2, Columns.Count).End(xlToLeft))
End With

With wsSumm
.Cells(1, 1) = "Late"
.Cells(3, 1) = "Names"
.Cells(2, 2) = "Date"
Set r = .Cells(4, 1).Resize(Nms.Count)
r.Value = Nms.Value
For Each cel In Dts
If IsDate(cel) Then
i = i + 1
.Cells(3, 1).Offset(, i) = cel.Value
End If
Next
Set r = r.Offset(, 1).Resize(, i)

r.FormulaR1C1 = _
"=IF(OFFSET(Source!R1C1,MATCH(RC9,Source!C1,0)-1,MATCH(R3C,Source!R2,0)-1)=""Yes"",""Yes"","""")"

CondFormat r
Exit Sub
End With
End Sub

Sub CondFormat(r As Range)

r.FormatConditions.Add Type:=xlExpression, Formula1:="=B4=""Yes"""
With r.FormatConditions(1).Interior
.PatternColorIndex = xlAutomatic
.ColorIndex = 3
.TintAndShade = 0
End With
r.FormatConditions(1).StopIfTrue = True

r.FormatConditions.Add Type:=xlExpression, Formula1:="=B4="""""
With r.FormatConditions(2).Interior
.PatternColorIndex = xlAutomatic
.ColorIndex = 4
.TintAndShade = 0
End With
r.FormatConditions(2).StopIfTrue = False
End Sub[/vba]

[harmonix]

Again thank you md. You're such a great help to this community.