Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Finding amounts that meet specific criteria
In one tab of my spreadsheet, I have data setup as follows:
Dec 1 Dec 2 Dec 3 Dec 4 Dec 5 Ariz 1.00 2.00 2.00 1.00 0.00 Ala 5.00 0.00 3.00 2.00 4.00 Calif 3.00 0.00 2.00 5.00 6.00 Colo 5.00 2.00 0.00 3.00 5.00 In a seperate tab of the worksheet, I'd like to come up with a formula that will be able to list out any of the amounts that are greater than 0. For example, I'd like the second tab to list the following: Ariz Dec 1 1.00 Ariz Dec 2 2.00 Ariz Dec 3 2.00 Ariz Dec 4 1.00 Calif Dec 1 3.00 Calif Dec 3 2.00 In short, I'm looking for the second tab to list all the amounts individually, and omit any days that are zero...and also give me the ability to pick which states I want to see this data for. Thank you! |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Finding amounts that meet specific criteria
Public Sub ProcessData()
Const STATE_NAMES As String = "Ariz,Calif" Const TEST_COLUMN As String = "A" '<=== change to suit Dim i As Long, j As Long Dim LastRow As Long Dim LastCol As Long Dim NextRow As Long Dim sh As Worksheet Dim states As Variant Dim cell As Range Set sh = Worksheets("Sheet2") states = Split(STATE_NAMES, ",") With ActiveSheet LastRow = .Cells(.Rows.Count, TEST_COLUMN).End(xlUp).Row LastCol = .Cells(1, .Columns.Count).End(xlToLeft).Column For i = 2 To LastRow If Not IsError(Application.Match(.Cells(i, "A").Value, states, 0)) Then For j = 2 To LastCol If .Cells(i, j).Value 0 Then NextRow = NextRow + 1 sh.Cells(NextRow, "A").Value = .Cells(i, TEST_COLUMN).Value sh.Cells(NextRow, "B").Value = .Cells(1, j).Value sh.Cells(NextRow, "C").Value = .Cells(i, j).Value End If Next j End If Next i End With End Sub -- __________________________________ HTH Bob "Brian" wrote in message ... In one tab of my spreadsheet, I have data setup as follows: Dec 1 Dec 2 Dec 3 Dec 4 Dec 5 Ariz 1.00 2.00 2.00 1.00 0.00 Ala 5.00 0.00 3.00 2.00 4.00 Calif 3.00 0.00 2.00 5.00 6.00 Colo 5.00 2.00 0.00 3.00 5.00 In a seperate tab of the worksheet, I'd like to come up with a formula that will be able to list out any of the amounts that are greater than 0. For example, I'd like the second tab to list the following: Ariz Dec 1 1.00 Ariz Dec 2 2.00 Ariz Dec 3 2.00 Ariz Dec 4 1.00 Calif Dec 1 3.00 Calif Dec 3 2.00 In short, I'm looking for the second tab to list all the amounts individually, and omit any days that are zero...and also give me the ability to pick which states I want to see this data for. Thank you! |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Finding amounts that meet specific criteria
Bob,
Thanks for your response... Unforutnately I'm not familiar with what you have listed below. I don't know what do with this information. I'm guessing this is some sort of visual-basic code? "Bob Phillips" wrote: Public Sub ProcessData() Const STATE_NAMES As String = "Ariz,Calif" Const TEST_COLUMN As String = "A" '<=== change to suit Dim i As Long, j As Long Dim LastRow As Long Dim LastCol As Long Dim NextRow As Long Dim sh As Worksheet Dim states As Variant Dim cell As Range Set sh = Worksheets("Sheet2") states = Split(STATE_NAMES, ",") With ActiveSheet LastRow = .Cells(.Rows.Count, TEST_COLUMN).End(xlUp).Row LastCol = .Cells(1, .Columns.Count).End(xlToLeft).Column For i = 2 To LastRow If Not IsError(Application.Match(.Cells(i, "A").Value, states, 0)) Then For j = 2 To LastCol If .Cells(i, j).Value 0 Then NextRow = NextRow + 1 sh.Cells(NextRow, "A").Value = .Cells(i, TEST_COLUMN).Value sh.Cells(NextRow, "B").Value = .Cells(1, j).Value sh.Cells(NextRow, "C").Value = .Cells(i, j).Value End If Next j End If Next i End With End Sub -- __________________________________ HTH Bob "Brian" wrote in message ... In one tab of my spreadsheet, I have data setup as follows: Dec 1 Dec 2 Dec 3 Dec 4 Dec 5 Ariz 1.00 2.00 2.00 1.00 0.00 Ala 5.00 0.00 3.00 2.00 4.00 Calif 3.00 0.00 2.00 5.00 6.00 Colo 5.00 2.00 0.00 3.00 5.00 In a seperate tab of the worksheet, I'd like to come up with a formula that will be able to list out any of the amounts that are greater than 0. For example, I'd like the second tab to list the following: Ariz Dec 1 1.00 Ariz Dec 2 2.00 Ariz Dec 3 2.00 Ariz Dec 4 1.00 Calif Dec 1 3.00 Calif Dec 3 2.00 In short, I'm looking for the second tab to list all the amounts individually, and omit any days that are zero...and also give me the ability to pick which states I want to see this data for. Thank you! |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Finding amounts that meet specific criteria
Excel 2007
Different data, but method applies. No formulas or code used. http://www.mediafire.com/file/mwyy2m...12_01_08a.xlsx |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
EXCEL - Meet 2 criteria, then find next case of third criteria | Excel Worksheet Functions | |||
Finding amounts that sum to a number | Excel Discussion (Misc queries) | |||
Counting characters in multiple rows when rows meet specific criteria | Excel Worksheet Functions | |||
Finding count of unique number within a specific criteria? | Excel Worksheet Functions | |||
Formula that only adds numbers that meet specific criteria | Excel Discussion (Misc queries) |