• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 219
  • Last Modified:

Match Formula?

Hi,

I have a row of data in column B, there may be data anywhere from B1-B10,000. Can i return to M1, M2, M3 etc any results.

The data in ColB will always be "Double Div"

So if theres an instance return it, i can guarantee this wont happen more than 50 times so i could drag down till then.

Ive attached the file

Thanks
Seamus

CP-Report.zip
0
Seamus2626
Asked:
Seamus2626
  • 4
  • 2
  • 2
2 Solutions
 
StephenJRCommented:
Seamus - do you mean col A? Col B is empty. An instance of what - not clear what you are looking for?
0
 
Rory ArchibaldCommented:
What exactly do you want returned?
0
 
StephenJRCommented:
OK, I think I see now. You want B37, B195 returned in M1, M2 etc?
0
[Webinar] Kill tickets & tabs using PowerShell

Are you tired of cycling through the same browser tabs everyday to close the same repetitive tickets? In this webinar JumpCloud will show how you can leverage RESTful APIs to build your own PowerShell modules to kill tickets & tabs using the PowerShell command Invoke-RestMethod.

 
Rory ArchibaldCommented:
My best guess - in M7:
=IF(COUNTIF(INDEX(B:B,C7):INDEX(B:B,IF(C8="",65536,C8-1)),"Double Div")>0,"Double Div","")
and copy down.
0
 
Seamus2626Author Commented:
Sorry guys, i have to shoot, i will return first thing Mon Morning.

Thanks for the help

Seamus
0
 
StephenJRCommented:
Perhaps this in M1 and down. An array formula so Ctrl+Shift+Enter:

=IF(ROWS(M$6:M6)<=COUNTIF($B$1:$B$1500,"Double Div"),"B" & SMALL(IF($B$1:$B$1500="Double Div",ROW($M$1:$M$1500)-ROW($M$1)+1),ROWS(M$6:M6)),"")
0
 
StephenJRCommented:
Should be, in M6 and down.
0
 
Seamus2626Author Commented:
Thanks lads, both worked perfectly!
0

Featured Post

Get your problem seen by more experts

Be seen. Boost your question’s priority for more expert views and faster solutions

  • 4
  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now