web intelligence - fx to show only specified values

[CompeteWithPete]

so my current formula is as follows:

 

=if(column>=column; "yes"; "no")

 

what condition do i need to make the report delete all the rows with NO value?

[Deft]

I could definitely do this in VBA with Excel. I had to Google Web Intelligence to find out what it is.

[CompeteWithPete]

its basically the same as excel.

its an application for report creation.

it uses the exact same concepts as excel tho.

 

that being said any suggestions?

[Deft]

Ok - I will give you some tips if you were doing this in Excel, as it is my favourite topic. Firstly there is no built in function that would delete rows - as generally a function will return a value rather than take an action.

One simple option in Excel (without deleting) is usually just to filter the dataset and therefore filter on the column that has that attribute - and only show "yes" rows. If you do want to delete manually, actually showing the "no" rows and manually deleting them as a block is pretty quick.

Otherwise you are looking at writing VBA code to do proper automated deleting. The slow way is to run a standard loop and delete the rows one by one (starting at the bottom and working up - as each time you delete a row, the ones underneath "move up" so your loop will get screwed up if you try working from the top. The quick way is to basically automate the "manual" method above - filter and then delete visible rows. The code for the loop is easy in VBA, the code for the filtering is also pretty easy but I'd need to look it up. Neither is probably that helpful but I'll put the loop code here for fun as it's so easy I can literally type it straight out.

Sub DeleteRows()

    Dim LastRow As Long, FirstRow As Long, i As Long
    
    'Assuming you have a tabular layout and your first entry is on row 2
    FirstRow = 2
    'Automatically figure out the last used row in Column A
    'There are more robust ways of doing this but hey ho.....
    LastRow = Range("A" & Rows.Count).End(xlUp)
    
    'Loop starting at end and working up
    For i = LastRow To FirstRow Step -1
        If Range("A" & i).Value = "No" Then Rows(i).Delete
    Next i

End Sub

[Frost]

Won't COUNTIF work?

[CompeteWithPete]

thx for the info! it gives me an idea of what the syntax should look like. i love excel too. whoda thunk a sheet with blocks and formulas could do anything and everything

 

countif would only count how many yes/no there are. i need to nest my current condition with =if"". i just need to play with it a bit. its driving me crazy tho

[Deft]

No problem. I am king of Excel. If you ever need tips on general Excel stuff, VBA or Pivot Table type things then give me a shout.

[CompeteWithPete]

i found out because the column i created to populate yes/no was not a query from the initial universe i needed to create a variable with my condition in order to specify population.