Search and find

  • Thread starter Thread starter ak
  • Start date Start date
A

ak

What I'm trying to do...
Compare the type of bldg and location and bring
back "Dist" for a location that is a "Rec" type bldg with
the same location info as a "Dist" bldg

3 columns

Column A Column B Column C
Bldg # Type of bldg Location
1298 Dist 1234 50th St.
3087 Rec 3489 49th St.
4059 Rec 1234 50th St.
3999 Dist 3489 49th St.

For example Bldg 3087 in Column D would contain 3999,
since 3087 is "Rec" and 3999 is "Dist" and the addresses
are the same.

What do you guys think?

Thanks in advance for any help

ak
 
It's not clear to me, if the same address can exist
more than twice in column C. I'm also not
sure, if "Rec" has to come before "Dist" for the
same location (should Building 4059 contain
1298 in column D?)

I have assumed, that the max instances of an address
is 2, and that order of "Rec" and "Dist" doesn't matter.

Under those circumstances this array formula should work.
In D2 enter:

=IF(B2="Rec",INDEX($A$2:$A$100, MAX(IF(($B$2:$B$100="Dist")*
($C$2:$C$100=C2),ROW($C$2:$C$100)-ROW($C$2)+1,-1))),"")

The formula must be entered with <Shift><Ctrl><Enter> also if
edited later. If done correctly, Excel will display the formula
in the formula bar enclosed in curly brackets { }. Don't enter
these brackets yourself.

Drag D2 down with the fill handle (the little square in the
lower right corner of the cell.

All D-cells adjacent to a "Rec" cell, which hasn't got a matching
"Dist"-cell, will contain #VALUE!


--
Best Regards
Leo Heuser
MVP Excel

Followup to newsgroup only please.
 
Back
Top