Recognizing street in which part, area, region city belongs ?

[tasic89]

I would like to do next.

City have streets, lets say 500 streets, unique names, phrases.

City also have "areas", its parts, its' like 10 parts, areas, regions. In 1st part there is like 80 streets, in 2nd part is like 40, in 3rd 100...

There are also some streets that are longer and than 1st fragment of that street up to number 100 is in 1st part of city area, and from number 101 is in 2nd part of city.

I can do that manually, but... IS there any chance for excel to do it (recognize) automatically ?

First sheet is a model where there are "rules" for recognizing which street is in which part, area, region of city it is.

problem.xlsx
	B	C	D
3	Streets	Street numbers	Region
4
5	San Fernando Road	1-100	HOLLYWOOD
6	San Fernando Road	101-200	SANTA MONICA
7	San Pedro Street		HOLLYWOOD
8	San Vicente Boulevard		HOLLYWOOD
9	San Vicente Boulevard		HOLLYWOOD
10	Santa Monica Boulevard		HOLLYWOOD
11	Santee Alley		HOLLYWOOD
12	Sawtelle Boulevard		HOLLYWOOD
13	Sepulveda Boulevard		HOLLYWOOD
14	Sunland Boulevard		WEST HOLLYWOOD
15	Sunset Boulevard		WEST HOLLYWOOD
16	Balboa Boulevard		WEST HOLLYWOOD
17	Beverly Boulevard		WEST HOLLYWOOD
18	Beverly Drive		WEST HOLLYWOOD
19	Beverly Glen Boulevard		WEST HOLLYWOOD
20	Broadway		SANTA MONICA
21	Broadway Tunnel		SANTA MONICA
22	Cahuenga Boulevard		SANTA MONICA
23	Centinela Avenue		SANTA MONICA
24	Central Avenue	1-150	SANTA MONICA
25	Central Avenue	151-300	WEST HOLLYWOOD
26	Century Boulevard		SANTA MONICA
27	Cesar Chavez Avenue		SANTA MONICA
28	Coldwater Canyon Avenue		PASADENA
29	Colorado Boulevard		PASADENA
30	Crenshaw Boulevard		PASADENA
streets region

This are models of sheet/table where I usually enter in which part of city they live. One of examples is with a street that "splits" in 2 regions, maybe it can even split in more regions, but...

problem.xlsx
	B	C	D	E	F	G	H	I	J	K	L
2
3
4		Name	Surname	Street	Area	City
5		Mike	Jonhs	San Pedro Street 10	HOLLYWOOD	LA
6		Peter	Strike	Beverly Drive 11	WEST HOLLYWOOD	LA
7		Maria	Alson	Central Avenue 3	SANTA MONICA	LA
8		Steven	Lamars	Central Avenue 202	WEST HOLLYWOOD	LA
9		Courtney	Olsen	Crenshaw Boulevard 40	PASADENA	LA
10
11
12
13		Name	Surname	Street	Column1	Area	City
14		Mike	Jonhs	San Pedro Street	10	HOLLYWOOD	LA
15		Peter	Strike	Beverly Drive	11	WEST HOLLYWOOD	LA
16		Maria	Alson	Central Avenue	3	SANTA MONICA	LA
17		Steven	Lamars	Central Avenue	202	WEST HOLLYWOOD	LA
18		Courtney	Olsen	Crenshaw Boulevard	40	PASADENA	LA
19
20
21
22
23
24
25
26
27
people

 

[kweaver]

I don't understand what you are looking for as a result and where?

 

[tasic89]

Well, sheet 2 with yellow color.

There I was entering manually where people live, in which area (something I need, but I don't have in documents I am getting).

Yellow are where I want.

I want that excel somehow recognize automatically that " Central Avenue 202 " is in "WEST HOLLYWOOD ", that "Crenshaw Boulevard 40 " is in "PASADENA " ...

1st sheet is list of streets in certain region

 

[kweaver]

I created 2 help columns (fill these down):

Book6
	A	B	C	D	E
5	San Fernando Road	1-100	HOLLYWOOD	1	100
6	San Fernando Road	101-200	SANTA MONICA	101	200
Streets

Cell Formulas
Range	Formula
D5:D6	D5	=IF(ISBLANK(B5),"",1*LEFT(B5,FIND("-",B5)-1))
E5:E6	E5	=IF(ISBLANK(B5),"",1*(RIGHT(B5,LEN(B5)-FIND("-",B5))))

Then,

Book6
	A	B	C	D	E	F	G
1	Name	Surname	Street	Column1	Area	City
2		Mike	Jonhs	San Pedro Street	10	HOLLYWOOD	LA
3		Peter	Strike	Beverly Drive	11	WEST HOLLYWOOD	LA
4		Maria	Alson	Central Avenue	3	SANTA MONICA	LA
5		Steven	Lamars	Central Avenue	202	WEST HOLLYWOOD	LA
6		Courtney	Olsen	Crenshaw Boulevard	40	PASADENA	LA
Report

Cell Formulas
Range	Formula
F2:F6	F2	=IF(0=VLOOKUP(D2,Streets!$A$5:$C$30,2,FALSE),VLOOKUP(D2,Streets!$A$5:$C$30,3,FALSE),INDEX(Streets!$C$5:$C$30,MAX((ROW($1:$26)*(E2>=Streets!$D$5:$D$30)*(D2=Streets!$A$5:$A$30)))))

Might not be the most elegant or easiest, but was my first shot at it.

 

[tasic89]

Hi

I made your formula work, and your whole two sheets work.

I can make changes to my current streets in "Streets" sheet , and it will work.

My problem now is - I want to add new streets, with new street numbers rules (like Balboa Street 1-50, Balboa Street 51-100, Balboa Street 101-300, or just new street without numbers)

What should I do ? I tried changing manually your formula from

=IF(0=VLOOKUP(D4,Streets!$A$5:$C$30,2,FALSE),VLOOKUP(D4,Streets!$A$5:$C$30,3,FALSE),INDEX(Streets!$C$5:$C$30,MAX((ROW($1:$26)*(E4>=Streets!$D$5:$D$30)*(D4=Streets!$A$5:$A$30)))))

to

=IF(0=VLOOKUP(D4,Streets!$A$5:$C$35,2,FALSE),VLOOKUP(D4,Streets!$A$5:$C$35,3,FALSE),INDEX(Streets!$C$5:$C$35,MAX((ROW($1:$26)*(E4>=Streets!$D$5:$D$35)*(D4=Streets!$A$5:$A$35)))))

- Because I added 5 new streets, I thought I just need to change C/D30 to C/D35

But I got errors, and e.g. "Central Avenue" which was "splitted" before, now I am getting other results...

What should I do if I want to change streets or add new streets ?

 

[kweaver]

You also have to change the ROW instruction from $1 to $26 TO $1:$31 because you added 5 rows.

 

[tasic89]

Fantastic, it works !

 

[tasic89]

Hmmm .... Now I have some issue that I wasn't aware of with streets... They have ODD and EVEN numbers.

So... Central Avenue can be up to 1-31 in "Santa Monica", while Central Avenue 2-26 is in "Santa Monica" but 28-52 is "West Hollywood" ... So, 28 can't be used like in formulas like above... Is there any option to do something about those streets that split in some parts with ODD and EVEN numbers, even if I have to do them in other table ? A lot of them don't split, but those that does, I could maybe put them in my first table as regular , but "central avenue" is "santa monica, west hollywood" and than in other table i have central avenue 1, 2, 3, 4,.... 26,27,29,31 is in "Santa Monica" while "28, 30, 32,33...." is in "West Hollywood" ...

I just don't know how

OVO RADIIIII. FINAL COUNTDOWN.xlsx
	A	B	C	D
1	Street	Street No	ODD/EVEN	Region
2	Central Avenue	1	FALSE	SANTA MONICA
3	Central Avenue	2	TRUE	SANTA MONICA
4	Central Avenue	3	FALSE	SANTA MONICA
5	Central Avenue	4	TRUE	SANTA MONICA
6	Central Avenue	5	FALSE	SANTA MONICA
7	Central Avenue	6	TRUE	SANTA MONICA
8	Central Avenue	7	FALSE	SANTA MONICA
9	Central Avenue	8	TRUE	SANTA MONICA
10	Central Avenue	9	FALSE	SANTA MONICA
11	Central Avenue	10	TRUE	SANTA MONICA
12	Central Avenue	11	FALSE	SANTA MONICA
13	Central Avenue	12	TRUE	SANTA MONICA
14	Central Avenue	13	FALSE	SANTA MONICA
15	Central Avenue	14	TRUE	SANTA MONICA
16	Central Avenue	15	FALSE	SANTA MONICA
17	Central Avenue	16	TRUE	SANTA MONICA
18	Central Avenue	17	FALSE	SANTA MONICA
19	Central Avenue	18	TRUE	SANTA MONICA
20	Central Avenue	19	FALSE	SANTA MONICA
21	Central Avenue	20	TRUE	SANTA MONICA
22	Central Avenue	21	FALSE	SANTA MONICA
23	Central Avenue	22	TRUE	SANTA MONICA
24	Central Avenue	23	FALSE	SANTA MONICA
25	Central Avenue	24	TRUE	SANTA MONICA
26	Central Avenue	25	FALSE	SANTA MONICA
27	Central Avenue	26	TRUE	SANTA MONICA
28	Central Avenue	27	FALSE	SANTA MONICA
29	Central Avenue	28	TRUE	WEST HOLLYWOOD
30	Central Avenue	29	FALSE	SANTA MONICA
31	Central Avenue	30	TRUE	WEST HOLLYWOOD
32	Central Avenue	31	FALSE	SANTA MONICA
33	Central Avenue	32	TRUE	WEST HOLLYWOOD
34	Central Avenue	33	FALSE	WEST HOLLYWOOD
35	Central Avenue	34	TRUE	WEST HOLLYWOOD
36	Central Avenue	35	FALSE	WEST HOLLYWOOD
37	Central Avenue	36	TRUE	WEST HOLLYWOOD
38	Central Avenue	37	FALSE	WEST HOLLYWOOD
39	Central Avenue	38	TRUE	WEST HOLLYWOOD
40	Central Avenue	39	FALSE	WEST HOLLYWOOD
41	Central Avenue	40	TRUE	WEST HOLLYWOOD
42	Central Avenue	41	FALSE	WEST HOLLYWOOD
43	Central Avenue	42	TRUE	WEST HOLLYWOOD
44	Central Avenue	43	FALSE	WEST HOLLYWOOD
45	Central Avenue	44	TRUE	WEST HOLLYWOOD
46	Central Avenue	45	FALSE	WEST HOLLYWOOD
47	Central Avenue	46	TRUE	WEST HOLLYWOOD
48	Central Avenue	47	FALSE	WEST HOLLYWOOD
49	Central Avenue	48	TRUE	WEST HOLLYWOOD
50	Central Avenue	49	FALSE	WEST HOLLYWOOD
51	Central Avenue	50	TRUE	WEST HOLLYWOOD
52	Central Avenue	51	FALSE	WEST HOLLYWOOD
53	Central Avenue	52	TRUE	WEST HOLLYWOOD
54	Central Avenue	53	FALSE	PASADENA
55	Central Avenue	54	TRUE	WEST HOLLYWOOD
56	Central Avenue	55	FALSE	PASADENA
57	Central Avenue	56	TRUE	WEST HOLLYWOOD
58	Central Avenue	57	FALSE	PASADENA
59	Central Avenue	58	TRUE	WEST HOLLYWOOD
60	Central Avenue	59	FALSE	PASADENA
61	Central Avenue	60	TRUE	PASADENA
62	Central Avenue	61	FALSE	PASADENA
63	Central Avenue	62	TRUE	PASADENA
64	Central Avenue	63	FALSE	PASADENA
65	Central Avenue	64	TRUE	PASADENA
66	Central Avenue	65	FALSE	PASADENA
67	Central Avenue	66	TRUE	PASADENA
68	Central Avenue	67	FALSE	PASADENA
69	Central Avenue	68	TRUE	PASADENA
70	Central Avenue	69	FALSE	PASADENA
71	Central Avenue	70	TRUE	PASADENA
72	Central Avenue	71	FALSE	PASADENA
73	Central Avenue	72	TRUE	PASADENA
74	Central Avenue	73	FALSE	PASADENA
75	Central Avenue	74	TRUE	PASADENA
76	Central Avenue	75	FALSE	PASADENA
77	Central Avenue	76	TRUE	PASADENA
78	Central Avenue	77	FALSE	PASADENA
Sheet1

Cell Formulas
Range	Formula
C2:C78	C2	=ISEVEN(B2)

 

[tasic89]

I have found a solution to my problem via your solution ??

I just added streets above in "Streets" sheet, literally one by one so specific street number is "connected" to specific region. I have a lot of rows, not elegant, but if it works, than its perfect.

And instead of formulas in D and E columns, i just added "=B" two times, because ... it works :D

If you have any tip how to make it more elegant and easier for my problem, please share your opinion.