LinkBack URL
About LinkBacks
pick44 
I have data base with 4 columns of address numbers for each street name. Need to only have "one" street name with lowest to highest number range. See attached file...., I would have the answer of : Beverly Hill St 9500 - 9699
Thanks Rich ><>
I have data base with 4 columns of address numbers for each street name. Need to only have "one" street name with lowest to highest number range. See attached file...., I would have the answer of : Beverly Hill St 9500 - 9699
Thanks Rich ><>
in I3
=MIN(IF(A3=A$3:A$19,C$3:F$19))&" - "&=MAX(IF(A3=A$3:A$19,C$3:F$19))
Array formula, use Ctrl-Shift-Enter
and copy down the column
Regards
Special-K
Ensure you describe your problem clearly, I have little time available to solve these problems and do not appreciate numerous changes to them.
After I ran the formula, I found that some street numbers had "0" as the min. This happened when the mapping program I use did not have a beginning address and it places a "0". Can the formula be adapted to have a value "1" or higher?
I tried formula but did not work. I rewrote and it worked: See below
=MIN(IF(A3=$A$3:$A$19,$C$3:$F$19))&" - "&MAX(IF(A3=$A$3:$A$19,$C$3:$F$19))
I left out the &=MAX AND REPLACED IT WITH &MAX AND IF WORKED. Thanks
Try (untested)
=MIN(IF(AND(A3=$A$3:$A$19,$C$3:$F$19>0),$C$3:$F$19))&" - "&MAX(IF(AND(A3=$A$3:$A$19,$C$3:$F$19>0),$C$3:$F$19))
Not sure if this will work actually.
I interpreted a little differently.
In cell J3 array enter this formula and fill down until you get blanks.
Formula:
=IFERROR(INDEX($A$3:$A$19,SMALL(IF(FREQUENCY(MATCH($A$3:$A$19,$A$3:$A$19,0),ROW($A$3:$A$19)-
MIN(ROW($A$3:$A$19))+1),ROW($A$3:$A$19)-MIN(ROW($A$3:$A$19))+1),ROWS($3:3))),"")
And array entered in K3 and filled down.Formula:=IF(J3="","",MIN(IF((J3=$A$3:$A$19)*($C$3:$F$19>0),$C$3:$F$19))&"-"&
MAX(IF((J3=$A$3:$A$19)*($C$3:$F$19>0),$C$3:$F$19)))
Dave
If you using *Excel 2010 or higher
Here are regular formulas
For unique list
Enter in J3 and copy down
Formula:=IFERROR(INDEX(A$3:A$19,MATCH(0,INDEX(COUNTIF(J$2:J2,A$3:A$19),,),)),"")
then enter formula in L3 and copy down
Formula:=AGGREGATE(15,6,C$3:F$19/(A$3:A$19=J3),1)&" - "&AGGREGATE(14,6,C$3:F$19/(A$3:A$19=J3),1)
If you like my answer please click on * Add Reputation
Don't forget to mark threads as "Solved" if your problem has been resolved
"Nothing is so firmly believed as what we least know."
--Michel de Montaigne
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
Register To Reply
Bookmarks