Only list values - NOT blanks

[KimberleyBob]

Hi there,

I'm hoping someone out there can help me with this, as I'm out of ideas now!

I am working on a rota. Unfortunately we have 3 staff teams and multiple part-timers all on a 3 (or 8 week) rolling rota, so this is proving somewhat complicated. I've reached the last hurdle (I hope), which is presentation.

Currently the rota outputs a name or a "" for each member of staff that could potentially be on shift that day, as we have over 100 staff, this means a lot of ""s. I would like a formula which looks at each day, and lists the populated cells only. Due to the poor speed of our systems, macros are not an ideal option.

All help on this will be most gratefully received!

Kim

[Cutter]

Can you post a sample file with personal data removed/replaced?

[KimberleyBob]

Absolutely, I'll pop this on first thing tomorrow (I'm working this out on a sample database with fake info anyway!)

Thanks

[KimberleyBob]

Please see the attachment below. (I've had to remove Feb-Dec data to reduce its size for uploading)

Automated Rota Draft2.xls

Thanks in advance for your help

[Cutter]

Here's my suggestion:

Instead of using resource intensive formulas in your current areas on sheets Early and Late, have other areas directly below those that display the data as you want it.

In cell C18 of Early sheet place this formula (and drag across to I18):
=IF(C13>0,VLOOKUP("?*",C4:C12,1,0),"")

In cell C19 of Early sheet place this formula (and drag across to I19):
=IF(C$13>ROWS(C$18:C18),VLOOKUP("?*",INDEX(C$4:C$12,MATCH(C18,C$4:C$12,0)+1):C$12,1,0),"")

Now select C19:I19 and drag down to row 26

That will display your scheduled staff from top to bottom with no "" results between.

In cell C13 you had:
=COUNTA(C4:C12)-COUNTIF(C4:C12,"")
this can be simplified as:
=COUNTIF(C4:C12,"?*")
and drag across to I13

Now select C13:I26 and copy - then select cell C13 on sheet Late and paste

Let me know how you make out with this or if you prefer something else.

[KimberleyBob]

Thank you, Cutter, this worked a treat, and I understood the formulae! I've struggled with Match and VLookup, and didn't really know that Rows existed, so thank you so so much.

[Cutter]

You're welcome. Please don't forget to mark your thread as SOLVED (click Forum Rules @ top of page to see instructions in rule #9).

[Cutter]

And thank you for the 'star tap'.

[controlfreak]

Hi Cutter,

I've a similar problem to the one you just solved here
http://www.excelforum.com/excel-gene...s-formula.html
Any chance you could take a look at it for me?!!!

[Cutter]

@controlfreak

I have posted a suggestion in your thread.