The following macro assumes you want to put the summary below the list.
Sub SummarizeMasterList()
Dim vDataIn, vDataOut(), n&, lRowC1&, lRowC2&, lRowC3&
vDataIn = Cells(1).CurrentRegion
ReDim vDataOut(LBound(vDataIn) To UBound(vDataIn), 1 To 3)
'Initialize the first row with 'Status' headings
vDataOut(1, 1) = "Quoted": c1 = 1
vDataOut(1, 2) = "Awarded": c2 = 1
vDataOut(1, 3) = "Delivered": c3 = 1
'Set the next available row in each column, and put the PO# there.
For n = 2 To UBound(vDataIn) '//ignores list headings
Select Case vDataIn(n, 2)
Case vDataOut(1, 1)
lRowC1 = lRowC1 + 1: vDataOut(lRowC1, 1) = vDataIn(n, 1)
Case vDataOut(1, 2)
lRowC2 = lRowC2 + 1: vDataOut(lRowC2, 2) = vDataIn(n, 1)
Case vDataOut(1, 3)
lRowC3 = lRowC3 + 1: vDataOut(lRowC3, 3) = vDataIn(n, 1)
End Select 'Case vDataIn(n, 2)
Next 'n
With Cells(UBound(vDataIn) + 2, 1)
.RowHeight = 24 '//edit to suit
.Resize(UBound(vDataOut), UBound(vDataOut, 2)) = vDataOut
End With
End Sub
--
Garry
Free usenet access at
http://www.eternal-september.org
Classic
VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.
vb.general.discussion