Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 217
  • 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
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
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

Vote for the Most Valuable Expert

It’s time to recognize experts that go above and beyond with helpful solutions and engagement on site. Choose from the top experts in the Hall of Fame or on the right rail of your favorite topic page. Look for the blue “Nominate” button on their profile to vote.

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