Report List with No Blank Cells (Excel 97)

Topic

New Reply

Sorry if this has been answered already….How long ’til I can search again????

I have a list of employees. In that list only certain ones meet a specific criteria. I have already identified against my list of 30, a COUNTIF function to return a 1 if the criteria has been met. On a different sheet, I want to list only the ones that meet this criteria. I don’t want blank spaces in between each one that returns TRUE. How can I create this list? See attachment for expected results…

thanks in advance
christine

Reply | Quote

Replies

Use an advanced filter (Data -filter -advanced filter)
1) put a heading on your 2 columns (eg A5 = “A”, B5 = “B”
2) create a criterion using col A heading in another location. eg Cell G5 = “A”. Underneath this (Cell G6) put a zero (0) since you want to extract the ones that a = zero
3) Create the out put range put the column headings you want (eg in I5 put “B” to output column B.

Now we will extract the cells in Col B that have “0” in col A:
Select a cell in col A or b in the data range
select data-filter – advanced filter from the menu (you whole range will be outlined)
Pick “copy to another location
Criteria range = G5:G6
Output range = I5
Check “unique records only” if you want no duplicates

You can also use the filter in place or even autofilter to filter in place and then copy and paste the range elsewhere
Steve

Reply | Quote

Thanks for the help. I do have another question though. The source of my data is a query to a database with a work date parameter that is setup to refresh on open. Will this advanced filter automatically adjust at each update?

Reply | Quote

When you do the adv filter after the query it should reset to the “CurrentRegion” of the database, so it will expand and contract as data is added/removed

Steve

Reply | Quote

So I just gave it a shot. Properties set to update on file open. Changed the criteria to return different data. The advanced filter did not update. I had to manually refresh the filter. So, is the best I can do a macro button (or something) that will enable the report viewer to see updated data from the filter?

Reply | Quote

could you not just build another query using your new criteria for the second list?

Peter

Reply | Quote

Hey, hey now…..we can’t do things the smart way! Come on, if it’s not something that would go around in circles and waste time, why do it??????

So I’m trying to get a subtract join setup and that won’t work either! I just can’t win on this thing!!!!!

Reply | Quote

I misunderstood your question. I thought you were asking if the DATALIST would be updated (the SOURCE list). The output list (as you discovered) is “static”. It is a set routine to “filter and copy and paste” that excel runs. It must be rerun (though a macro could do it easily).

You could do it with an ARRAY formula to make it dynamic but you would have to adjust the size to be large enough to cover the “database” max rows. It could get sluggish if it gets too big:
Enter this formula in a cell in row 6 (eg I6)and confirm with control-shift- enter:

=IF(ISERROR(SMALL(IF($A$6:$A$37=0,ROW($A$6:$A$37),””),(ROW($A$6:$A$37)-5))),””,INDEX($B$6:$B$37,(SMALL(IF($A$6:$A$37=0,ROW($A$6:$A$37),””),(ROW($A$6:$A$37)-5)))-5))
[note the 5s in the formula are because you started in row 6]

Then hit and HIGHLIGHT I6:I37 and again confirm with ctrl-shift-enter.

This list is LIVE. Change the 0s and 1s and your list will update! You can Expand the list by moving A37:B37 to another row, then repeating the and expanding the range from above. You will NOT be able to insert rows due to the ARRAY in col I.

Steve

Reply | Quote

Thanks for the clarification. I just wrote a quick macro and added to a button on the report. The user can manage to click ONE more thing, I think! But I will keep your array formula handy.

thanks again
christine

Reply | Quote