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

I can't seem to get it to work. It's probably me since I'm fairly new to macros. I've attached the spreadsheet I've been working on with some sample data if you have a minute to take a look. the tab "Orders by Status" is where I need the macro to be applied. as you can see, the "quoted", "awarded", and "delivered" columns each show the PO numbers with that status but only based on the cell position of the master sheet labeled "sales log". I need the PO numbers to display at the top of the list, almost as if I applied a filter and excluded the blanks. To add to that, I need to apply a formula in the "days since quoted" and "$" columns to display the relevant data for that PO. you will see the formulas I've entered take care of all of this except for the fact that there are blank cells i between PO numbers in each column. Worse comes to worse I will just filter each column but I figured there would be some way to make it work.

Thanks again!

Got the file!
So you want to disperse the data on 'Sales Log' to 'Orders by Status'
as follows:

Quoted:
PO#, Days since quoted, Total Amount
..where days since quoted will be Today-QuoteDate

Awarded:
PO#, Days since quoted, Total Amount
..where days since awarded will be Today-AwardDate

Delivered:
PO#, Total Amount

The following macro outputs to Sheets("Orders by Status") across 10
cols, with 1 blank col between the 3 sets of data. (Note that it sets
the headings and adjusts RowHeight of the headings row)

Sub SummarizeMasterList_v2()
Dim vDataIn, vDataOut(), n&, lRowC1&, lRowC2&, lRowC3&
Dim wksSource As Worksheet, wksTarget As Worksheet

Const lQDate& = 3 'col conating Quote Date
Const lADate& = 5 'col containing Award Date
Const lAmnt& = 6 'col containg Total Sales Amount

With ThisWorkbook
Set wksSource = .Sheets("Sales Log")
Set wksTarget = .Sheets("Orders by Status")
End With 'ThisWorkbook

With wksSource
vDataIn = .Range(.Cells(1), .Cells(1).End(xlDown)).Resize(, lAmnt)
End With 'wksSource
ReDim vDataOut(LBound(vDataIn) To UBound(vDataIn), 1 To 10)

'Initialize the first row with 'Status' headings
vDataOut(1, 1) = "Quoted": lRowC1 = 1
vDataOut(1, 2) = "Days Since Quoted"
vDataOut(1, 3) = "Total Amount"

vDataOut(1, 5) = "Awarded": lRowC2 = 1
vDataOut(1, 6) = "Days Since Quoted"
vDataOut(1, 7) = "Total Amount"

vDataOut(1, 9) = "Delivered": lRowC3 = 1
vDataOut(1, 10) = "Total Amount"

'Set the next available row in each column
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) 'PO#
vDataOut(lRowC1, 2) = Date - vDataIn(n, lQDate)
vDataOut(lRowC1, 3) = vDataIn(n, lAmnt)

Case vDataOut(1, 5)
lRowC2 = lRowC2 + 1
vDataOut(lRowC2, 5) = vDataIn(n, 1) 'PO#
vDataOut(lRowC2, 6) = Date - vDataIn(n, lADate)
vDataOut(lRowC2, 7) = vDataIn(n, lAmnt)

Case vDataOut(1, 9)
lRowC3 = lRowC3 + 1
vDataOut(lRowC3, 9) = vDataIn(n, 1) 'PO#
vDataOut(lRowC3, 10) = vDataIn(n, lAmnt)
End Select 'Case vDataIn(n, 2)
Next 'n

'Dump the data into the target sheet
With wksTarget.Cells(1)
.RowHeight = 24 '//edit to suit
.Resize(UBound(vDataOut), UBound(vDataOut, 2)) = vDataOut
End With

'Cleanup
Set wksSource = Nothing: Set wksTarget = Nothing
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

Success! Thank you, Garry.

Success! Thank you, Garry

You are most welcome!

--
Garry

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