Eliminating empty cells over multiple columns

[QSGuy]

Hey

I am using the following formula to eliminate empty cells and give me the entry in a cell in a particular column of an array for a single column:

=IFERROR(INDEX($A:$A&" "&$B:$B,AGGREGATE(15,6,ROW($C$2:$C$761)/($I$2:$I$761=T$3),ROWS(T4:T$4))),"")

What I would like to be able to do is check multiple columns and list the results in a single column

I have attached a workbook that references children with the same name in three classes and the pets they own - so in my example we have four kids in 3 classes with the same name - Michael, Hannah, James and Sarah listed in columns across the results columns - a list in the rows of the pets they own woith a "pet tag" for each pet in the first column and between the two an array for the three classes with the data in it - ie the names of the child that owns a particular pet

What I want to do is create a list of the pets owned by each name in all classes - my formula only works in one class...I have tried to manipulate it various ways to see if I can come up with a solution to no avail...

[Glenn Kennedy]

In I7, copied across and down:

=IFERROR(INDEX($B:$B,MOD(AGGREGATE(15,6,(COLUMN($E$5:$G$5)*1000000)+ROW($E$7:$G$18)/($E$7:$G$18=I$5),ROWS(I$7:I7)),1000)),"")

[Richard Buttrey]

Hi,

This is clearly a much simplified example and probably doesn't represent the size of your actual data. We often find that when we give an answer to what is a trivial non representative example when the solution is used with the real world data it doesn't work because of factors like string length and other stuff which is important but hasn't been mentioned.

Therefore please upload your real workbook (or at least a cut down copy), and manually add the results you expect to see. Clearly identify which is original data and which are the results and in a note clearly explain how you have arrived at your results.

That said the difficulty you find is the result of a less than perfect database. Were you to collect all your data in a normalised regular two dimensional range then you'd simplify your solution considerably, and a Pivot Table would give you all you want without needing to resort to formulae.

So if you're prepared to rearrange your data I suggest that would be a more efficient method.
Create a table with columns for

Name
Class
Pet Tag
Pet - if there is a one to one relationship between Pet Tag & Pet in a separate table then a VLOOKUP could be used for this field.

[QSGuy]

Glen

Thanks for your response - the formula is what I was looking for! Thank you!

[QSGuy]

Glen

Yes, this is a greatly simplified example. The sheet where these formula are going is part of a workbook with many pages - it is a pricing document for construction works: the format is set by industry standard - what is called a "Bill of Quantities" so I cant really change that: the tag is a page and letter reference for all the items to be priced on a particular page. The "Bills" come out in Excel and I have devised the sheet such that all I have to do is copy the client copy Bill into the "tag" and Pet" columns - the tag being the page and item reference and the "pet" being the individual item to be priced.

Quite often one item might have multiple trades associated with it - so a wall has a plasterboard partition, painting, skirting, etc etc etc. I will type in the different subcontractors required (children's names in this example) for each item in the "class" columns - which will be collected in the results column and create the list of items to be priced by all the different subcontractors.

There is usually a principle trade associated with each item - so for knocking through a new door opening in an existing external building wall, the demolition sub will be the primary trade - to knock out the hole. Up till now I have just written demo in my single column and the item has been collected in the list of items to be priced by the demolition subcontractor. But the item will also require a bricklayer to make good the reveals created and the plasterer and painter to make good the internal wall and the joiner to put in the door. So I want the collection (the results column in my example) to include the item for knocking through the doorway in the bricklayer, plasterer and painter's collection as well as the demo sub. Then, when I send out the enquiries, I don't have to do separate lists for all the secondary subcontractors - the items are already in their collection using the formula I was look for.

These collections will then be used by multiple other sheets to create different sets of information: drawing lists, spec lists, the actual pricing schedules for each trade and the individual pricing items as well as a vlookup search through various other lists to bring the finished prices back to this page in the pricing columns. each item to be priced has its own worksheet and there is a collection sheet for adding profit etc. All of which are referenced on or to the original client's "Bill" page.

Basically, I am trying to automate the whole process - so that all I have to do is copy paste the clients "Bill of Quantities" drawing schedules and specification schedule into the sheet I am seeking the formula for here - and that will form the basis for all the other sheets I use - creating lists automatically through multiple other sheets once I have typed the trade of the different subs in the "class" columns in this example. I have about 200 results columns beside these for all the different subcontractors I might use (the results column in this example) where the collections are created - another worksheet excludes empty columns (ie columns with no data harvested with the formula I was looking for) and creates a list of subs just for this project from the list of @ 200 - with the architect's drawing schedule copied in as I do for the "Bills" and the same on another sheet with the "Specfications" - there are similar sheets for Structural Engineer's drawings and specs and Mechanical and Electrical Engineers' drawings and specs: all created automatically coming from the client's "Bill" sheet. That information is then tabulated in another sheet which brings Bills, Drawings and Specs together in a formal enquiry for prices. I have another sheet where I store all the information for the project such as contractual items and site specifics - and another sheets that acts as a database of all my subcontractors contact details: so spending about an hour copying the bills, drawing schedules and specification schedules into a number of sheets in this book, and then adding the subcontractor trades into this sheet and spending about half an hour filling into the form I use as the data sheet for all the contract and site details, I have all my enquiries ready to go - and all I have to do is type a three letter trade code (which is used throughout the workbook) and a four letter subcontractor code (to bring the contact info from the database) into the formal enquiry sheet, PDF-print the formal enquiry into an "enquiries" folder and type the next three and four letter codes in for the next enquiry - and keep doing that till I have all my enquiries. When I ahve printed them all I send them out via zipped folder email with all the drawings and specs attached.

That process would have taken three or four people a week when I first started work 20 years ago. Now I will be able to do it in a day. It used to take me three of four days without the formula I was looking for here as I had to print separate enquiry sheets for all the secondary subcontractors.

When the subcontractors prices come back I have tender comparison sheets (again using my codes) for the different trades, all created from the collections I am creating here I put a 1 in the top of the column for the subcontractor whose prices i intend using and those rates automatically go into my summary cost sheet in the right place via lookup on the summary page.

Once I have copied the client sheets and created the collections that I was seeking the multiple-columns formula for here, the only things I have to type in again for the whole project are the subcontract prices into the comparison pages and the material costs and labour for the few items that are not to be done by subcontractors, along with what are called "Prelims": site management costs, hoardings, cabins etc - and yep, you guessed it, I have a pre-filled schedule for that and I only have to type in some of those costs that are not time related: wayer, gas electric connections and the like - because my site data sheet with all the contract data in has the number of weeks in it and that automatically populates my prelims worksheet with the majority of items.

The problem I have found in the past is that with Tables any links just come up as the table/sheet name and, in such a complicated workbook, that just makes life very difficult: if something falls down you need to know which formula in which of the muliply-linked pages has fallen down crashing the whole thing. So generally, I try and avoid tables.