Index/Search - Combine Cells

[Jennifer Meyer]

Hello,
I have a list of Unit numbers and each Unit Number has a "type", each type had different doors/door numbers. I need to combine the Unit number with the Unit Type and the Door #.

I have a approximately 12 different Unit Types
Examples: IL A1, IL A1.1, IL A1.2 etc.
Each Type has doors but all doors are not labeled the same in each type.
Example: Door 4 in Type IL A1 may be a Door 6 in Type IL A1.2

I am looking for a formula to search for the Type and return the correct door #'s like the row with
A1000-B2-1, A1000-B2-2, etc
This is the Unit # (A1000) Unit Type (B2) and Door # (1), (2).

Thank you
Jennifer

 

[Jennifer Meyer]

Fluff,
I have copied my worksheet to a new one and and updated the door types and units for another floor using the same formulas. I now have data showing up where it shouldn't.

Please look at the attached.

There should be no info in Column E - Column I as those Units do not have that many doors. Why are those cells filling with info from Columns C & D.
This makes me nervous for the huge one I did originally. I need to have a count of each type of door.

Jennifer

 

[Fluff]

That's because rows 11 to 16 in col Z are duplicates of the rows above.