Close up empty rows after IF formula returns 'true' results

Thank you Ron & TMS for such quick responses.

Ron attached is a very mini version of what I am doing.

TMS I'm really pressed for time (deadline for this was the 16th of last month) and will have to look at filtering when I get the chance.

Regards,
Jon.

Oops, I just realized on the 'Room Item Summary' worksheet I forget to put the formulas to show the total items per room for rooms 2 to 6. No big thing as I have room 1 showing the totals of items per room.

Hi Ron,

Sorry, it's been so long since I used this site I forget to include such critical info - I'm using Excel 13 off a Windows 7 OS.

To Trevor (a.k.a. TMS) - My apologies for calling you TMS, I only just noticed your name since my earlier reply to Ron and yourself.

Regards,
Jon.

Not one to let the sand settle under my feet... while waiting around for someone to offer a solution, I ‘played’ around with the array
I mentioned in my last post that was written by Benishiryo to solve another person's problem
(this array: =IFERROR(INDEX($B$2:$B$100,SMALL(IF($A$2:$A$100="Yes",ROW($A$2:$A$100)),ROWS(C$2:C2))-ROW($A$2)+1),"").

As a result I was able to solve my own problem by experimentation. I'm not sure what every element in the array is there for,
I just know that I got it to do exactly what I needed. Essentially I had to add a few columns to satisfy the requirements of
Benishiryo's array.

I'm sure an Excel Expert reading this, and looking at the attached excel file of my solution, could possibly have achieved the same
result much neater and without the extra steps/columns I have had to put in to make it work but, then again on the other hand, such
an expert might never have offered a solution.

For those who are looking for such a solution, it is for your benefit that I have peppered my workbook solution with comments in a
hope that you find them helpful. On the summary worksheet the key array is used twice - once to give a closed up list of the number
of items and in the next column to give a closed up list of these items. I found I didn't really need to understand exactly what
every array element was there for, I just had to apply a little logic to get it to return what I wanted. Please enjoy.

Hi Trevor,

You'll see I found my own solution by adapting another solution... but, to answer your suggestion about autofiltering, I haven't used it too much when using excel and even if I was very familiar with its use not everybody who will need to use this workbook are. The object was to set up a system whereby anybody in the team could go to the matrix worksheet and from the 'wishlist' of items available select what they would like in the room they will be working in (of the new complex we are building) merely by putting a number in the right cell. Because the wishlist is well over 400 items as of this moment, the idea of the summary worksheet was so they could see what others before them had selected and/or easily see what was missing that they would like.

Thanks for responding... if you have time I'd be interested to hear from you regarding the solution I came to.

BTW I'd like to mark this thread as solved but can't see how.

Regards,
Jon.

Dear Trevor,

Thanks for letting me know how to mark it as solved - and I guess, as you didn't comment on the solution I came up with, it's about as good as I could get(?).

The person due thanks is Benishiryo but, I got his formula array from another thread so, I'll go to that thread, leave him a thank you comment and when he replies tick his star... that way he might also find his way back to my use of his formula.

Regards,
Jon.