List without blank and if condition

mba_110

New Member
Joined
Nov 28, 2012
Messages
43
Hi everyone,

I got some difficulty in sorting out the below formula, it is a complex one with array.

Code:
=IFERROR(INDEX(Payroll!$C$9:$C$95,SMALL(IF(ISTEXT(Payroll!$C$9:$C$95),ROW(Payroll!$C$8:$C$94),""),ROW(Payroll!C9))),"")

formula return with no value (The value i am calling is text)

Now what i am trying to do?

I am trying to make list without blanks its for my payroll sheet and i am trying to extract the full list without blank and with a condition that if salary amount cell which is Payroll!AB9 is not null then only that employees name should appear in list otherwise list serial should skip his name and go to next, same for all,

in above i manage to get only the code that make list without blank which is also not giving the value and in addition to that i want the add if(Payroll!AB9<>"",Payroll!C9,"") but first above code should work properly.

Any help here will be appreciated.

Thanks.
 

Excel Facts

Who is Mr Spreadsheet?
Author John Walkenbach was Mr Spreadsheet until his retirement in June 2019.

James006

Well-known Member
Joined
Apr 4, 2009
Messages
3,680
Hello,

To make your life easier, you should create a named range for your Payroll!C9:C95 area ... such as rng ... and have Array Formula :

Code:
=IF(ROWS($2:2)<=COUNTA(rng),INDEX(rng,SMALL(IF(rng<>"",ROW(rng)-MIN(ROW(rng))+1),ROWS($2:2))),"")

Hope this will help
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
73,213
Office Version
  1. 365
Platform
  1. Windows
Another option, this is a normal formula, rather than an array formula
=IFERROR(INDEX(Payroll!$C$9:$C$95,AGGREGATE(15,6,ROW(Payroll!$C$9:$C$95)-ROW(Payroll!$C$9)+1/(Payroll!$C$9:$C$95<>""),ROWS($1:1))),"")
 
Last edited:

mba_110

New Member
Joined
Nov 28, 2012
Messages
43
Thanks fluff your formula is working perfect, however as i said i want to add the if(Payroll!AB9<>"",Payroll!C9,"") if employee's salary is zero on payroll!AB9 starting from first row then his name should not appear in list and go to next name i try to amend your formula like below but its not showing correct result, still showing the blank cells if payroll!AB9 is zero or null.

Code:
=IFERROR(IF(Payroll!AB9<>"",INDEX(Payroll!$C$9:$C$95,AGGREGATE(15,6,ROW(Payroll!$C$9:$C$95)-ROW(Payroll!$C$9)+1/(Payroll!$C$9:$C$95<>""),ROWS($1:1))),""),"")


Where i am making mistake ?
 
Last edited:

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
73,213
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

How about
=IFERROR(INDEX(Payroll!$C$9:$C$95,AGGREGATE(15,6,ROW(Payroll!$C$9:$C$95)-ROW(Payroll!$C$9)+1/((Payroll!$C$9:$C$95<>"")*(Payroll!$AB$9:$AB$95<>0)),ROWS($1:1))),"")
 

mba_110

New Member
Joined
Nov 28, 2012
Messages
43
That is great again....thumps up genius

Now i am taking it to final level, i got some employees to whom i am not paying directly and i have another list in summary showing there names as in payroll sheet and i want to exclude them from this list as well whether there is an amount on payroll!AB9 or not.

the rage for excluding employee is in sheet Summary!B24:B30

I hope this can be possible.
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
73,213
Office Version
  1. 365
Platform
  1. Windows
Whilst it's almost certainly possible, that's beyond my knowledge.
Hopefully one of the Formula folk step in & help.
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,164,017
Messages
5,834,962
Members
430,330
Latest member
drAli77

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top