Return values from one column IF a certain value in another column

[sroeder]

Hi All,

I am trying to create 3 different lists to summarize data from a maste
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 lik
so:

Quoted Awarded Delivered
12 11 10
14 13

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

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

Thanks for your help on this

 

[GS]

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

 

[GS]

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

 

[GS]

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

 

[sroeder]

Thanks Garry!

;1610826']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.discussio

 

[GS]

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

 

[sroeder]

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

Thanks again!

;1610865']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.discussio

+-------------------------------------------------------------------
|Filename: Sales_Log.zip
|Download: http://www.excelbanter.com/attachment.php?attachmentid=835
+-------------------------------------------------------------------

 

[GS]

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

 

[sroeder]

Success! Thank you, Garry.

;1610904']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.discussio

+-------------------------------------------------------------------
+-------------------------------------------------------------------

 

[GS]

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