Complex Formula

[julieavg]

Hello Everyone,
I am hoping someone may be able to help me out. I have a list of building addresses and unique building numbers generated from our system. I have another list of opportunities generated from the same system that contain two building numbers for each opportunity. Each opportunity has its own unique opportunity number. I need to lookup the building number from list one and receive a return of all opportunity numbers that contain that building number. I am at a loss and everything I try does not work. Any help or guidance is greatly appreciated. Thank you in advance.

 

[oldbrewer]

address	buildnum					opportunity	buildnum1	buildnum2	helper
addr1	8					opp1	9	6	1	####	opp1
addr2	6					opp2	1	3
addr3	4					opp3	3	8
addr4	9					opp4	2	7
addr5	1					opp5	5	1
addr6	3					opp6	7	2
addr7	2					opp7	8	3
addr8	5					opp8	6	3
addr9	7					opp9	4	5
						opp10	3	4
						opp11	2	9	1		opp11
						opp12	5	9	1		opp12
						opp13	7	3
						opp14	8	2
						opp15	9	1	1		opp15
											opp1opp11opp12opp15
			choose address 4		buildnjum
			addr4			9	******
			####'	=IF(OR(H2=$G$22,I2=$G$22),1,"")	this puts a 1 by all rows containing buliding 9
			******	=OFFSET($A$1,MATCH(D22,$A$2:$A$10,0),1)
			now you need to list the opportunity numbers
			formula used for this
			=IF(J2=1,G2,"")
			finally concatenate col L
			formula in l18 is
			=(L2&L3&L4&L5&L6&L7&L8&L9&L10)&(L11&L12&L13&L14&L15&L16)

<colgroup><col span="6"><col><col span="2"><col span="6"></colgroup><tbody>
</tbody>

 

[julieavg]

I am sorry, but I cannot seem to get this. Can you provide the column header? Are G & H your building numbers within the opportunities? Thank you.

 

[julieavg]

Maybe some additional information may help. I have two tabs in my file. One contains 2 Columns, Building Number (A) and Building Address (B). There are about 250 Building Numbers. Then I have a tab with thousands of Opportunities with 3 columns. One Column is the opportunity number , Building A and Building B. On tab one, I need to get a return of any opportunity numbers that contain the building number. Sometimes buildings are left blank and in this case, I do not need to account for them. Is this something that is possible. I have tried using a range as well with my initial tab of buildings. I have to believe this is possible. Thank you anyone who can point me in the correct direction.

 

[oldbrewer]

address

buildnum

opportunity

buildnum1

buildnum2

helper

row1

addr1

8

opp1

9

6

1

####

opp1

row2

addr2

6

opp2

1

3

row3

addr3

4

opp3

3

8

row4

addr4

9

opp4

2

7

row5

addr5

1

opp5

5

1

row6

addr6

3

opp6

7

2

row7

addr7

2

opp7

8

3

row8

addr8

5

opp8

6

3

row9

addr9

7

opp9

4

5

row10

opp10

3

4

row11

opp11

2

9

1

opp11

row12

col a

col b

opp12

5

9

1

opp12

row13

opp13

7

3

row14

opp14

8

2

row15

opp15

9

1

1

opp15

row16

row17

col d

col g

col h

col I

col j

col k

opp1opp11opp12opp15

row18

row19

row20

choose address 4

buildnjum

row21

addr4

9

******

row22

row23

####'

=IF(OR(H2=$G$22,I2=$G$22),1,"")

this puts a 1 by all rows containing buliding 9

******

=OFFSET($A$1,MATCH(D22,$A$2:$A$10,0),1)

now you need to list the opportunity numbers

formula used for this

=IF(J2=1,G2,"")

finally concatenate col L

formula in l18 is

=(L2&L3&L4&L5&L6&L7&L8&L9&L10)&(L11&L12&L13&L14&L15&L16)

THIS IS THE GENERAL APPROACH ONCE YOU AGREE IT DOES WHAT YOU WANT IT CAN BE MODIFIED TO ACCOMMODATE

THE FACT THAT YOU HAVE 2 TABS

YOU CHOOSE AN ADDRESS IN d22 AND THE BUILDING NUMMBER IS FOUND IN G22

THEN ALL OPS WITH 9 IN THEM ARE LABELLED WITH A 1 AND WHERE THERE IS A 1 THE OPP NUMBER APPEARS

FINALLY ALL OP NUMBERS ARE CONCATENATED

<colgroup><col span="6"><col><col span="2"><col span="8"></colgroup><tbody>
</tbody>

 

[julieavg]

address

buildnum

opportunity

buildnum1

buildnum2

helper

row1

addr1

8

opp1

9

6

1

####

opp1

row2

addr2

6

opp2

1

3

row3

addr3

4

opp3

3

8

row4

addr4

9

opp4

2

7

row5

addr5

1

opp5

5

1

row6

addr6

3

opp6

7

2

row7

addr7

2

opp7

8

3

row8

addr8

5

opp8

6

3

row9

addr9

7

opp9

4

5

row10

opp10

3

4

row11

opp11

2

9

1

opp11

row12

col a

col b

opp12

5

9

1

opp12

row13

opp13

7

3

row14

opp14

8

2

row15

opp15

9

1

1

opp15

row16

row17

col d

col g

col h

col I

col j

col k

opp1opp11opp12opp15

row18

row19

row20

choose address 4

buildnjum

row21

addr4

9

******

row22

row23

####'

=IF(OR(H2=$G$22,I2=$G$22),1,"")

this puts a 1 by all rows containing buliding 9

******

=OFFSET($A$1,MATCH(D22,$A$2:$A$10,0),1)

now you need to list the opportunity numbers

formula used for this

=IF(J2=1,G2,"")

finally concatenate col L

formula in l18 is

=(L2&L3&L4&L5&L6&L7&L8&L9&L10)&(L11&L12&L13&L14&L15&L16)

THIS IS THE GENERAL APPROACH ONCE YOU AGREE IT DOES WHAT YOU WANT IT CAN BE MODIFIED TO ACCOMMODATE

THE FACT THAT YOU HAVE 2 TABS

YOU CHOOSE AN ADDRESS IN d22 AND THE BUILDING NUMMBER IS FOUND IN G22

THEN ALL OPS WITH 9 IN THEM ARE LABELLED WITH A 1 AND WHERE THERE IS A 1 THE OPP NUMBER APPEARS

FINALLY ALL OP NUMBERS ARE CONCATENATED

<tbody>
</tbody>

This is great. Thank you so much. Very much appreciated.