Excel help

bficky

So I have idea of what I want to do in Excel, and I’m pretty sure it can be done, but my Google skills are coming up empty.

In one column, going down, I have a long list of client names (all unique). Then, in adjacent columns, I have corresponding data for each of those names: start date in one column, end date in another, payment method, due date met Y/N, location, etc. Maybe 10 columns in all, with an increasing number of rows when new data comes in.

What I’d like is on the left side of the screen is a bunch of pull down lists where I can narrow down the data to only show clients from 2009, or only show clients of a certain location where we finished the work in 2010 but missed the due date, etc. On the right side of the screen is the original data filtered down that meet those parameters on the left.

My first thought was to use a filter, but I don’t like how it’s doing it. I’m in Office 2007, and the filter mechanic is cumbersome. I click on the column header, then I individually click on all the parameters I don’t want to see, then I hit ok. The data that doesn’t match the parameters just gets hidden (not removed), and when the whole row is hidden, some of the left side parameter input part gets hidden too. I know I can move the left hand side above the data, but I’d rather it work another way. Plus I’d need a way to tie the pull down choices to the column headers.

I’m completely OK with my main data being in one tab and the parameter pull downs and matching data on another. I’m just not sure how to make it so that based on my parameters, a list of matching data is gathered from the other tab and shown on my main tab, and it’s completely dynamic, so that I can go add to the data tab and the parameter tab is auto updated.

Should I be looking deeper into filters, or is this now a list, or a table, or a pivottable? Do I need to get macros involved (I hope not).

TLDR: How do I create/view a dynamic filtered version of original data in a new location?

Orogogus

I'd say this is a job for a pivottable.

wonderpug

Yeah, definitely dive into learning pivot tables. You're going to love 'em.

Mostly you can poke and prod and experiment your way through them, but the biggest thing that's not immediately apparent is that if your original data changes on the source table you must hit refresh on the pivot table for things to update there as well.

Bobble

Pivot tables are awesome. One bit of advice I'd throw out for them is to go under Pivot table options (just right click in the table), Display, and enable the Classic Pivot table layout. It may be a little more intuitive if you can drag the fields where you want them.

bficky

Thanks for the responses. I've looked into pivottables, and I don't think I'm understanding them very well. Here's my fake data:

and here's what a quick and dirty pivottable got me:

and here's what I'm looking for:

I realized a good way to explain what I'm looking for. I'm basically looking for a iTunes smart playlist for my data. Is this possible in Excel?



Here's my fake data in text format:

Client Contract Date Completion date Method Zone Cost
AAA 3/2/2009 6/24/2009 check 2 2.5
BBB 5/7/2009 7/1/2009 check 2 2.1
CCC 5/14/2009 8/22/2009 check 3 3
DDD 6/1/2009 CC 1 2.5
EEE 7/2/2009 11/17/2009 check 2 3.2
FFF 8/29/2009 2/2/2010 check 2 2.9
GGG 8/31/2009 11/29/2009 CC 4 2.7
HHH 9/15/2009 3/14/2010 CC 3 1.9
III 11/1/2009 3/20/2010 check 1 3.4
JJJ 2/21/2010 CC 2 2.5
KKK 4/2/2010 6/15/2010 CC 1 2.8
LLL 5/12/2010 CC 3 3.4
MMM 6/5/2010 check 2 3.8
NNN 6/10/2010 11/1/2010 check 1 2.7
OOO 8/9/2010 CC 4 3.5
PPP 8/11/2010 check 3 2.4

Deebaser

I don't know if that's possible with stock excel. You can get close to that with advanced filters using a criteria range, but you'd have to transpose the left box and place it outside the filter range.

jclast

You'd need (as far as my Excel knowledge goes) a macro to do that. Menu on page 1, all data on page 2, and filtered data on page 3. Make your picks on page 1, hit the button, and then let VBA sift through your complete data on page 2 and spit out the filtered set on page 3.

SlickShughes

I've done similar for a single criteria using MATCH, OFFSET, and ROW, but I don't know if it would work on multiple conditions as you've described. I suppose you could iterate the solution - Sheet1 has your original data, Sheet2 after Contrat Year filter applied, Sheet3 after Completion Year filter applied, etc. - but it would be cumbersome. I'll try to puzzle it out if I have time later today.

Also I don't know how sortable the results would be.

Dr. Frenchenstein

Do you have access? this would be a snap using that.

you are definitely going to need some VB scripting here in a macro to get this done in excel. are you at all familiar with that?

Orogogus

I still think a pivottable would handle this adequately.

Going off the quick and dirty screenshot, the first thing to do is create the contract year and completion year fields in the original table; you can use Excel's =YEAR() function for that, although you'll probably also have to reformat it so it doesn't turn into 7/1/1905 or whatever.

Then drag the column headings around until they're in the right order.

Next, double click on each column heading and turn off the subtotals (set it to "none").

The column and data fields are going to be empty, which is a bit of an eyesore. If you need sums or counts you can drag one of the fields (cost, probably) into the data section and turn the subtotal back on.

jclast

Hm. I don't seem to be able to attach a file. Here is the macro code that I wrote. PM me an email address if you want to see the complete file. I have three sheets. First is "MENU", second is "Data", and third is "Filtered". The first page 4 date fields, a method field (simple constrain to make a pull-down), and a zone field (another simple constrain to make a pull-down). Both pull-down have a "no filter" option. If the date is left blank no filter is applied there. There's one button on that page that kicks off the macro. It's only named Button2 because I didn't like the first button for some reason that I can't remember. There is only 1 on the sheet. Oh, and the "Filtered" page can still be sorted after it's been filtered. And the data that I used is the sample data you posted earlier.

Sub Button2_Click()

' grab variables
Sheets("MENU").Select
contract_date_start = ActiveSheet.Range("B3").Value
contract_date_stop = ActiveSheet.Range("B4").Value
complete_date_start = ActiveSheet.Range("B5").Value
complete_date_stop = ActiveSheet.Range("B6").Value
method = ActiveSheet.Range("B7").Value
zone = ActiveSheet.Range("B8").Value

' copy full data to filtered data
Sheets("Filtered").Select
ActiveSheet.Columns("A:F").Select
Selection.Clear
Sheets("Data").Select
Dim i As Integer
i = 2
While (ActiveSheet.Range("A" & i).Value <> "")
    i = i + 1
Wend
Dim i_max As Integer
i_max = i - 1
ActiveSheet.Range("A1:F" & i_max).Select
Selection.Copy
Sheets("Filtered").Select
ActiveSheet.Range("A1:F" & i_max).Select
ActiveSheet.Paste

' delete rows that occur before contract_date_start
If (contract_date_start <> "") Then
    For i = 2 To i_max
        If (ActiveSheet.Range("B" & i).Value = "") Then Exit For
        Debug.Print ("contract_date_start = " & contract_date_start & " | B" & i & " = " & ActiveSheet.Range("B" & i))
        If (ActiveSheet.Range("B" & i).Value < contract_date_start) Then
            ActiveSheet.Rows(i).Delete Shift:=xlUp
            i = i - 1
        End If
    Next i
End If

' delete rows that occur after contract_date_stop
If (contract_date_stop <> "") Then
    For i = 2 To i_max
        If (ActiveSheet.Range("B" & i).Value = "") Then Exit For
        Debug.Print ("contract_date_stop = " & contract_date_stop & " | B" & i & " = " & ActiveSheet.Range("B" & i))
        If (ActiveSheet.Range("B" & i).Value > contract_date_stop) Then
            ActiveSheet.Rows(i).Delete Shift:=xlUp
            i = i - 1
        End If
    Next i
End If

' delete rows that occur before complete_date_start
If (complete_date_start <> "") Then
    For i = 2 To i_max
        If (ActiveSheet.Range("C" & i).Value = "") Then Exit For
        If (ActiveSheet.Range("C" & i).Value < complete_date_start) Then
            ActiveSheet.Rows(i).Delete Shift:=xlUp
            i = i - 1
        End If
    Next i
End If

' delete rows that occur after complete_date_stop
If (complete_date_stop <> "") Then
    For i = 2 To i_max
        If (ActiveSheet.Range("C" & i).Value = "") Then Exit For
        If (ActiveSheet.Range("C" & i).Value > complete_date_stop) Then
            ActiveSheet.Rows(i).Delete Shift:=xlUp
            i = i - 1
        End If
    Next i
End If

' delete rows that do not meet the prescribed method
If (method <> "no filter") Then
    For i = 2 To i_max
        If (ActiveSheet.Range("D" & i).Value = "") Then Exit For
        If (ActiveSheet.Range("D" & i).Value <> method) Then
            ActiveSheet.Rows(i).Delete Shift:=xlUp
            i = i - 1
        End If
    Next i
End If

' delete rows that do not meet the prescribed zone
If (zone <> "no filter") Then
    For i = 2 To i_max
        If (ActiveSheet.Range("E" & i).Value = "") Then Exit For
        If (ActiveSheet.Range("E" & i).Value <> zone) Then
            ActiveSheet.Rows(i).Delete Shift:=xlUp
            i = i - 1
        End If
    Next i
End If

End Sub

bficky

Wow, thanks. I'll check out that macro tonight and continue to mess around with the pivot tables. This is all for my mom's new hairbrained work from home idea. My parents are in town this weekend, so I'll ask her what all this data really is and how she wants to see it.

Spherick

Im going to suggest Access. Make that one table and have some quick and dirty SQL.