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

 

[Fluff]

Hi & welcome to MrExcel.
Are you trying to find the type in B2 in column L & then concatenate the values in col M with the Unit in A2?

 

[Jennifer Meyer]

Fluff,
Yes, I think. That sounds so easy. Maybe I am over complicating it. I thought I would need to search and index somehow because of there are so many different Types (Column B) and each Type has different Door #'s. There are 192 units so I want to be able to copy a formula and drag it down/across.

I have done the attached formula for other types of projects like this. However my architect provided the data in a different format. Usually I each Unit has the same door #'s (Example: Entry-1, Bedroom-2, Closet-3, etc.) There is no similarities here.

Jennifer

 

[Fluff]

Ok, how about

+Fluff New.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L	M
1
2
3	A1000	IL B2	A1000-b2-1	A1000-b2-2	A1000-b2-3	A1000-b2-4	A1000-b2-5	A1000-b2-6				IL A1	a1-1
4	A2000	IL B1	A2000-b1-1	A2000-b1-2	A2000-b1-3	A2000-b1-4	A2000-b1-5	A2000-b1-6	A2000-b1-7			IL A1	a1-2
5												IL A1	a1-3
6												IL A1	a1-4
7												IL A1	a1-5
8												IL A1	a1-6
9												IL B1	b1-1
10												IL B1	b1-2
11												IL B1	b1-3
12												IL B1	b1-4
13												IL B1	b1-5
14												IL B1	b1-6
15												IL B1	b1-7
16												IL B2	b2-1
17												IL B2	b2-2
18												IL B2	b2-3
19												IL B2	b2-4
20												IL B2	b2-5
21												IL B2	b2-6
Sheet2

Cell Formulas
Range	Formula
C3:J4	C3	=IFERROR($A3&"-"&INDEX($M$3:$M$50,AGGREGATE(15,6,(ROW($M$3:$M$50)-ROW($M$3)+1)/($L$3:$L$50=$B3),COLUMNS($C$3:C$3))),"")

 

[Jennifer Meyer]

WOW! That is way over my knowledge. I have never used AGGREGATE.

My actual spreadsheet has a few more columns so am trying to get it to work in my actual spreadsheet. I'll let you know how it goes.
If you have any pointers as to how I can change it up to have Column L be column AB and Column M be AD, that would be great.

A few of my unit types actually have 19 doors, not just the six in the example I provided.

Thanks again!
Jennifer

 

[Fluff]

If you have any pointers as to how I can change it up to have Column L be column AB and Column M be AD

Just change the L to AB & the M to AD

 

[Jennifer Meyer]

Just change the L to AB & the M to AD

Just change the L to AB & the M to AD

I tried that and it is not working.

Does the AGGREGATE info need to be changed? I do not know how that part of the formula works.

Jennifer

 

[Fluff]

How about
=IFERROR($A3&"-"&INDEX($AD$3:$AD$50,AGGREGATE(15,6,(ROW($AD$3:$AD$50)-ROW($AD$3)+1)/($AB$3:$AB$50=$B3),COLUMNS($C$3:C$3))),"")

 

[Jennifer Meyer]

I got it! Darn spaces! There was a space after my column B data. I have gotten it to work in my entire workbook.

Thank you so much,
Jennifer

 

[Fluff]

Glad it's sorted & thanks for the feedback