Creatings lists from Non-Consecutive cells

I have a problem I keep coming up against – how to transform information in non-consecutive cells into a shortened list of information in consecutive cells in a different worksheet

I have uploaded a workbook with a couple of examples of the issue in four spreadsheets two base information sheets – one is a weekly record (Weekly Summary) of resources deployed in the week and the other is a Structural Opening Sheet and two result worksheets – Daily Resource and LIntol Schedule

I want the title (resource) of the column with Information marked by an “x” in the "Weekly Resource Summary" worksheet to appear in a list in the "Daily Summary" sheet as per the typed sheet – but obviously done by formula automatically when you put an “X” in the Weekly Sheet.

Similarly, I want the information in column E of the "Structural Openings" worksheet to appear as shown in column C in the "Lintol Schedule" worksheet

I am not even sure if Excel can do this - but if it can I really like to know how!

Cheers!

It can be done, but the formulas are not realy practical (columns F:H in the daily summary).

I've added an extra table to the bottom of the week summary showing a more practical format, which could be rotated if that would be better.

Lintol schedule is easy enough, I've added some formulas to that and applied borders so that you can see where, they are in order shortest to longest rather than list order, but that can be changed easily enough if needed.

Jason

That's brilliant! Thanks for your help! I have been toiling with the first one for ages trying to resolve it without success - so this is great!

I will study the formulas you have given me to understand where I have been going wrong - but I think not really understanding the use of ISERROR is part of the problem.

One thing I would ask is whether the problem in the first two sheets would be easier if I omitted the dates in the second ("Daily Resource") spreadsheet?

I ask this as I realised after I posted that I don't need the second "Daily resource", sheet to have dates it in (though the answer you have given is great for another problem I have been struggling with).

Basically the "Weekly Resource Summary" sheet is an input sheet for about 200 columns of data - the spreadsheet I posted here is just a sample of the data input sheet. But the "Daily Resource" will be a sheet for only one day - so I will only be taking data from the "Weekly Summary Resource" sheet for only one date.

Also, would the formula be any different if I wanted to make the "Weekly Resource Sheet" into a "Yearly Resource Sheet" such that I had a line for each Sunday that would be empty of data most of the time as although we often work Saturdays, we rarely work Sundays - but it is possible we may do at times?


Once again, Thanks! so much for your help!

Looking at the revised requirement for the daily sheet from weekly / yearly, I'm going to suggest a slightly different approach.

While the 2 methods that I've demonstrated in the sample file would both work with more data, the formulas are going to have a lot more data to process, so a more efficent method might be beneficial.

Start by adding a new, blank sheet to the sample file.

In B2, enter any of the dates from the weekly summary.
In B3, enter

Formula:

Please Login or Register  to view this content.

(this formula finds which row in the table contains the date entered).
In B4, enter

Formula:

Please Login or Register  to view this content.

(this counts the x's in the row identified by the first formula).
In C2, enter and fill down

Formula:

Please Login or Register  to view this content.

By splitting the formula down into a couple of helper cells, you can make the main part shorter and easier to follow, as well as being more efficient.
In this way the formula in B3 and B3 only need to be calculated once each, if they included in the main formula instead of individual cells, then for 200 rows of formula, the first one would be calculated 400 times, the second 200.
Also, we don't need the IFERROR trap this time, because we have the count of x's in B4, we can compare the count of rows instead and stop when both are the same. This means that the rest of the formula doesn't even have to be calculated.

Give this a go, see if it does what you need or if it is something that you can work with.
For the date entry into B2, try setting it up using a data validation dropdown list, using the dates in the weekly summary as the source.
Also, you don't need 2 cells for date and weekday, you can simply format the date as dddd dd/mm/yy without affecting the formulas.

Hey Jason.b75

Thanks very much for your response again! Apologies for the tardy response, but I have been laid up with a stomach bug.

Again you have preformed what appears, to a lowly Excel pawn-surf such as myself, like magic! You are truly an Excel Wizard-King! I never imaged that the mathematical formulas would be of use to me – I usually just use the lookup and logical functions. But clearly I was misunderstand how Excel manipulates data.

Ok, I spent a full evening trying to understand your last formula 😊 but I did manage to understand enough to put it into the spreadsheet. I split the columns and helper cells into sections, so the most it is looking up is about 30 columns and it is fine- and it does put what I want it to put in the cells I want the answers to appear in the way I want them to appear – which is excellent!

I said above that it is the ISERROR function I didn’t understand – but I meant to say AGGREGATE – is there anywhere you can recommend that gives a good explanation of this function?

I will come back when I have had a look at your new formula in a bit more detail.

I doubt that you will find a definitive explanation of the aggregate function, it is effectively 19 functions in 1, with a further 8 options for each of things to include or exclude.
The key part to look at is how the data that is passed to the aggregate function is manipulated beforehand.

(COLUMN($C$2:$K$2)-COLUMN($C$2)+1) this part produces an array of all of the column numbers in the first range, the subtraction and addition are used to adjust those column numbers so that the first is always 1, in the range used in the formula {3,4,5,6,7,8,9,10,11}-3+1 = {1,2,3,4,5,6,7,8,9}

(INDEX('Week Resource Summary'!$C$5:$K$11,$B$3,0)="x") this part produces a logical array of TRUE for "x" or FALSE for no "x".

The division of the column numbers from the first array by the logical results of the second (TRUE evaluates to 1, FALSE evaluates to 0) will return the column number where there is an "x" or DIV/0 where there is no "x".

The aggregate funciton uses the options for the 'SMALL' function and ignore errors, so as you drag down, only the valid column numbers will be returned to index in ascending order, using ROWS(C$2:C2) as a counter.

Hope that makes some sense.

Jason

After testing different parts of the formula separately, and by running the "evaluate formula" function when I tried to transpose the formula and got it wrong at first, I had sussed a lot of the formula out - the one thing I didn't understand was the division part - but now that you have explained that, it makes perfect sense to me - in essence, you cant divide by zero, so the false (or zero) returns an error - so a "true" returns only correct results (using the number 1 instead of true) - makes perfect sense once it is explained!

Oh, and the second formula works too! I am now using the two formulas for other things!

Thanks for all your help on this Jason!