Return values from one column IF a certain value in another column
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! |
Return values from one column IF a certain value in another column
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 |
Return values from one column IF a certain value in another column
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 |
Return values from one column IF a certain value in another column
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!
Quote:
|
Return values from one column IF a certain value in another column
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 |
1 Attachment(s)
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! Quote:
|
Return values from one column IF a certain value in another column
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.
Quote:
|
Return values from one column IF a certain value in another column
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 |
All times are GMT +1. The time now is 11:04 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com