Hi All,

I am trying to create 3 different lists to summarize data from a master list. The master list looks like this:

A B
PO Number Status
10 Delivered
11 Awarded
12 Quoted
13 Delivered
14 Quoted

I would like the three separate lists to summarize the above data like so:

Quoted Awarded Delivered
12 11 10
14 13

So IF Column B has a status of "Quoted", then list all PO Numbers in Column A with that status...and so on.

I also need the data to update so If I change the status of one PO Number from "Quoted" to "Awarded", that PO would file in the appropriate column.

Thanks for your help on this!

Very easy to do with a macro! Where do you want the separate lists to
go?

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion

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

Oops! Missed some variables that got renamed...

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": lRowC1 = 1
vDataOut(1, 2) = "Awarded": lRowC2 = 1
vDataOut(1, 3) = "Delivered": lRowC3 = 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

Thanks Garry!

You're welcome!
Does it do what you want?

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion