Checking a column entries for specific text and reference cells not containing that text

[Zsenialis]

Hello!

I have a really unique excel task I couldn't figure out and hope for help here.

I have a list of room types and their attributes (total area, furniture, etc). This sheet has a format that has to be kept, however to be able import the content to a different program I need a simplyfied, plain excel sheet. In order to achieve this I created a new sheet and referenced the room names there, and using Vlookup I also matched the related attributes. The problem is that there are several unnecessary rows I can't get rid of, such as "For new types add new row", etc and merged rows containing one room type have "0"s (obviously only the first cell has data the other merged cells are empty). I tried to use an IF and OR combination: if the unnecessary data is present in a cell then reference the next row, else reference current row. The problem is that with this function combination I can only cover a finite number of skipped rows and after 3 IFs I couldn't follow the function. Also there could be even 8-10 (or more) merged rows that have to be skipped. Is there a way to create this function to be indefinite (basically to tell the program to skip the unnecessary row until it finds one with relevant data) or a solution with a different approach?

If something is not clear about the description, please let me know and I describe it further!

Thanks in advance!

 

[bschwartz]

Can you post some example data?

 

[Zsenialis]

Sure!

As you can see, with the simple referencing the excess empty cells got referenced as well + the 2 unnecessary row. I tried a function using OR (to check for the three unnecessary cells) and multiple IF to reference the next valid cell if true and reference current cell if false. This is quite complicated (at least for me, from a certain point) but could work if there were finite number of rows to check, but a row can be inserted any time when a new furniture type is required. Some formula or different workaround would be good to automate the check and wouldn't depend on the number of rows.

 

[bschwartz]

I think it would be very beneficial if you would set the data up a little more like this:

Excel 2010
	A	B	C	D	E
1	Room #	Room Name	Room total area (m2)	?	Furniture requirement
2	1.01	Dressing Room	40	20	Locker
3	1.01	Dressing Room	40	4	Bench
4	1.01	Dressing Room	40	4	Shower
5	1.01	Dressing Room	40	2	Toilet
6	1.01	Dressing Room	40	4	Urinal
7	1.02	Coach Room	16	2	Locker
8	1.02	Coach Room	16	1	Table
9	1.02	Coach Room	16	1	Bench
10	1.02	Coach Room	16	2	Chair
Sheet1

Then your best bet will probably be a pivot table. If not, then at least your data will be setup in a way that makes it simpler to extract specific records.

 

[Zsenialis]

The problem is that I'm not allowed to format the 1st sheet. I tried to filter out the unnecessary rows in the 2nd sheet, but unfortunately the program doesn't recognize that the rows are hidden. It could work that I select the sorted content, copy and paste it somewhere, the content would be as needed, but this has to be fool-proof and if it can be automated (sheet 2 generates the desired plain table without any user action, after modifing the original sheet) it would be just awesome.

 

[bschwartz]

First of all, why do you say those rows are extra? Don't you want to return all of the attributes from those rows?

 

[Zsenialis]

No, those are the part of the original sheet, but I don't need them for this comparision right now. I will need them later, but that check is gonna be manual.

 

[bschwartz]

No, those are the part of the original sheet, but I don't need them for this comparision right now. I will need them later, but that check is gonna be manual.

Ok, I understood this to mean you wanted to return the Attributes as well:

In order to achieve this I created a new sheet and referenced the room names there, and using Vlookup I also matched the related attributes.

Sounds like you need an Index/Small function. I'll be on later, and can take a look at it.

 

[Zsenialis]

Ok, I understood this to mean you wanted to return the Attributes as well

The last two columns are not needed right now. This table is prepared to compare the Room number, Room name and Total area in this table with the information generated from a 3D model. So I still need to return the above mentioned attributes, but those are not relevant in regards with the empty cells generated by the merged cells.

 

[bschwartz]

I built this on the same worksheet since you haven't provided the name of your first worksheet. If you start this off in column G of the first worksheet to test it, you should then be able to move it onto the second worksheet, and the references will update. Enter the formula in G2, and fill through I2, and then down as needed:

Excel 2010
	A	B	C	D	E	F	G	H	I
1	Room #	Room Name	Room total area (m2)	?	Furniture requirement		Room #	Room Name	Room total area (m2)
2	1.01	Dressing Room	40	20	Locker		1.01	Dressing Room	40
3				4	Bench		1.02	Coach Room	16
4				4	Shower
5				2	Toilet
6				4	Urinal
7	1.02	Coach Room	16	2	Locker
8				1	Table
9				1	Bench
10				2	Chair
11		Add new row for additional rooms
12		Insert requirements from scope documentation
Room Types

Cell Formulas
Range	Formula
G2	=IF(ROWS(G$2:G2)<=COUNT($A$2:$A$12),INDEX(A$2:A$12,AGGREGATE(15,6,ROW($A$2:$A$12)-ROW($A$2)+1/--($A$2:$A$12<>""),ROWS(G$2:G2))),"")