formula or macro help to show data based on name/days to service

[jw01]

hello all

ive been struggling to create a macro or a formula that will only show me the relating data based on two conditions i.e. company name and # of days > 6

i have attached a sample,

basically, in the worksheet "6+SLA" i want to show all hsbc and hbio account details (row 6 on SLA sheet matches, data sheet row 1 headers) whose days to complete is greater than 6 days (from the "data" worksheet)
-company name is in column AH on "data worksheet"
-# of days is in column AG on "data worksheet"

i would be very grateful.

[canapone]

Hi, i've added an extra helper column (AI) in order to filter >6 and <>"No". You could also modify AH.

All the formulas are array formulas (to be confirmed with control+shift+enter): please double-check if {} embrace all the formula.

=IF(ISERROR(INDEX(Data!$A$1:$A$1000,SMALL(IF(Data!$AI$1:$AI$1000=TRUE,ROW($A$1:$A$1000)),ROWS($A$1:$A1)))),"",INDEX(Data!A$1:A$1000,SMALL(IF(Data!$AI$1:$AI$1000=TRUE,ROW($A$1:$A$1000)),ROWS($A$1:$A1))))

Hope it helps

Regards

[jw01]

hello

thx u so much for your great help!!!

i really really appreciate it....i like the use of column AI which uses "no" and >6 which will then show the list.....awesome!

have a wonderful day
cheers

[pike]

If you are satisfied with the solution(s) provided, please mark your thread as Solved.

How to mark a thread Solved
Go to the first post
Click edit
Click Go Advanced
Just below the word Title you will see a dropdown with the word No prefix.
Change to Solved
Click Save

[jw01]

hey pike

i was wondering if u can help me one last time

i tried to modify the formula you provided into this:

{=IF(ISERROR(INDEX((INDEX(_Data,0,1)),SMALL(IF((INDEX(_Data,0,35))=TRUE,ROW($A$1:$A$1000)),ROWS($A$1:$A1)))),"",INDEX((INDEX(_Data,0,1)),SMALL(IF((INDEX(_Data,0,35))=TRUE,ROW($A$1:$A$1000)),ROWS($A$1:$A1))))}

however its giving me blanks *see attached*

the reason being...i uno how to adjust the range $A$1:$A$1000 to that of (INDEX(_Data,0,1)...

any suggestions?....the defined name "_Data" is entire table in worksheet "data"

ur thoughts? thx u so much!

[Haseeb Avarakkan]

See the attached. Hide Col_A & Row #5, if you want.