Arranging values from Series of Arrays in Descending Order by Formula - Not Macro

Hi All,

Basic example of the following attached.

I have a worksheet that assembles data from a report exported from another database. I want to create a formula that takes this assembled data and formats it into something that resembles an accounting journal.

On the attached:
- Cells B3:G10 represent the assembled data
- Cells B15:E27 represent the ideal solution I am looking for. Ideal because it omits and zero values from the journal
- Cells B15:E30 represent a satisfactory solution. Satisfactory because it assembles all data (including zero values) but I will still have to manually omit any zero values from the journal by deleting myself

The requirements:
- The journal should group primarily by type of expense (expenses are represented by column in the 'assembled data' section) and within this, by department (Dept 1 being listed first, Dept 2 second and so on).
- The solution should detect that there are no more than 3 departments in the assembled data. Therefore it will list "Expense Type 1 - Dept. 1" on the first row, "Expense Type 1 - Dept. 2" on the second row, "Expense Type 1 - Dept. 3" on the third row. The solution will then detect that there is no Dept 4 and will proceed by listing "Expense Type 2 - Dept 1" on the fourth row and so on. Please note that the number of departments can increase or decrease month to month so the solution must have the flexibility to list "Expense Type 1 - Dept. 4" should Dept 4 ever be added.

FYI - I would like a formula solution to this as oppose Macro. I don't know anything about Macros and I would like to be in a position to maintain myself should anything go wrong.

Your sample and description give us the general idea of what you need, but to clarify a couple of things?

Yous sample shows Fuel as the first expense, then the remaining expenses sorted in ascending order. (both in the array, and in the result). Should the results be sorted in this way if the array differs, or in the order of the array?

How big is the real array likely to be?

Will future data be added to the existing array, will it replace the existing array, or will you use a new array in a new sheet?

FYI - with a task like this, a 'Macro' will be easier to maintain that a formula

This one needed some serious thought.

Ideal solution.

B16 =IF(B15="","",IFERROR(INDEX($C$3:$G$3,IFERROR(MATCH(B15,$C$3:$G$3,0),1)+(COUNTIF(INDEX($C$5:$G$7,0,IFERROR(MATCH(B15,$C$3:$G$3,0),1)),">0")=COUNTIF(B$15:B15,INDEX($C$3:$G$3,IFERROR(MATCH(B15,$C$3:$G$3,0),1))))),""))

C16 =IF(B16="","",INDEX($B$5:$B$7,MATCH(TRUE,IF(INDEX($C$5:$G$7,0,MATCH(B16,$C$3:$G$3,0))>0,($B$5:$B$7>IF(B16=B15,C15,0))),0)))

D16 =IF(B16="","",INDEX($C$5:$G$7,MATCH(C16,$B$5:$B$7,0),MATCH(B16,$C$3:$G$3,0)))

E16 =IF(B16="","",INDEX($C$4:$G$4,MATCH(B16,$C$3:$G$3,0)))

The formula in C16 needs to be Array confirmed with Shift Ctrl Enter.

Not sure how well it will work with larger data volumes.

Dear Jason,

Attached is the real sheet, with personal/commercially sensitive information removed and replaced with generics to protect people/company. I wanted to avoid uploading this because (if I were reading this and trying to help) I fear it would complicate things as there is a web of formulas.

Tabs:
- 'Master Data' is the sheet containing the instructions for allocation and arrangement of data. No need to look at this.
- 'Drop Sheet' is the exported report from the external database. The report is exported from the ext. database in Excel format then 'dropped' (copy and pasted) here
- 'AssembledInformation' takes the information as per the 'DropSheet' and organises it according to the instructions contained in the 'MasterData' tab
- 'Journal' is the sheet which will eventually contain the 'AssembledInformation' data in Journal format.

With reference to the above, I want to take the data from the 'AssembledInformation' (which is spread over columns B to tab and arrange the data BU) and arrange in descending order on the 'Journal' tab.

In response to your questions:
- The results shall be sorted in order of the array i.e. if Column A is 'Chairs' and Column B is 'Fuel' one week and the next week they switch columns, it doesn't matter as they can appear in a different order in the journal. That is to say, I don't need 'Chairs' to be first on the journal list every time. Whatever appears in the first column can be first in the journal list.
- Real array attached (see tab 'AssembledInformation')
- Future data can both be added and replaced. Expense types (which are presented by column) will appear in month 1 (let's call it 'original data'). The next month (when the next monthly report is generated) additional expense types (columns) can appear alongside the original data. Likewise some original data may not appear on the next month's report but new expense types might, effectively taking the place of old expense types NB regards this point, I think I know where you're going and I'd draw your attention to the fact that in my real array, each expense/column is headed by a number, which acts as the constant (regardless of whether the expense in type in that column is the same as the orignal data). I would use these number references as oppose the expense type.


I have no problems if someone wants to implement Macros, but I would only ask that they explain to me how the specific Macro works. I haven't yet had the opportunity to explore this feature of Excel so would need a brief 101 on the subject.

Hi Stuart,

I've left the macro method alone while you have a look at the formula options that John and myself have provided.

I haven't looked at the new sample yet, but looking if the file size is anything to go by, I think that the formulas will take a lot of processing.

I'll have a look at the formulas to see if I can find a way to make them run more efficiently, but I don't think that there will be any way to eliminate the countif part of my first formula, which I think will be one of the bigger burdens on processing.

Revised formula for B16

=IF(B15="","",IFERROR(INDEX($C$3:$G$3,IFERROR(MATCH(B15,$C$3:$G$3,0),1)+(COUNTIF($C$5:INDEX($C$5:$G$7,0,IFERROR(MATCH(B15,$C$3:$G$3,0),1)),"<>0") < ROWS($B$16:B16))),""))

A little bit less processing effort needed with this one.

Hi Jason & John,

I've just had a crack at the formulas but with no success. The fault is mine as I hadn't replicated my real sheet accurately in the original upload. The mistake I had made was informing you that the array in cells B5:B7 were cost centre codes when in fact they are not.
This data is simply a reference.

The cost centre information is located to the right of each expense. Please see the revised example spreadsheet (or my real version) for an example of this.

As I said no success with those formulas and I imagine the above gives rise to some of the reasons why.

Based on the revised sample, (you were right about the real sheet, I tried to make sense of it- and failed!)

The revised layout, although closer to the real one, is far from formula friendly, but I did find a way.

Formulas assume no duplicate Dept / TB code in any one column of the assembled data (same code in multiple columns is ok).

B16 - Cost Centre.

PHP Code: 

=IF(B15="","",IFERROR(INDEX($C$3:$K$3,IFERROR(MATCH(B15,$C$3:$K$3,0),1)+((COUNTIF(INDEX($C$5:$K$7,0,IFERROR(MATCH(B15,$C$3:$K$3,0),1)),"<>0")=COUNTIF(B$15:B15,INDEX($C$3:$K$3,IFERROR(MATCH(B15,$C$3:$K$3,0),1))))*2)),"")) 


Note that the *2 multiplier assumes a continuous pattern of use 1 column, skip 1 column when searching for the cost centres.

C16- Dept / TB code (This one must be Array confirmed with Shift Ctrl Enter).

PHP Code: 

=IF(B16="","",INDEX($D$5:$L$7,INDEX($B$5:$B$7,MATCH(TRUE,IF(INDEX($C$5:$K$7,0,MATCH(B16,$C$3:$K$3,0))<>0,($B$5:$B$7>IF(B16=B15,MATCH(C15,INDEX($D$5:$L$7,0,MATCH(B16,$C$3:$K$3,0)),0),0))),0)),MATCH(B16,$C$3:$K$3,0))) 


D16 - Amount.

PHP Code: 

=IF(B16="","",INDEX($C$5:$K$7,MATCH(C16,INDEX($D$5:$L$7,0,MATCH(B16,$C$3:$K$3,0)),0),MATCH(B16,$C$3:$K$3,0))) 


Note that in the Dept / TB code formula and the Amount formula, some of the ranges have been offset by one column, this is done to align the TB code with the relevant cost centre, when changing the ranges to match other sheets, the format of the offsets must be preserved for the formula to function correctly.

E16 - Debit flag.

PHP Code: 

=IF(B16="","",INDEX($C$4:$K$4,MATCH(B16,$C$3:$K$3,0))) 


See if you can make sense of that, I need to book myself into Arkham Asylum for a few days after trying to get them working right.

Revised "Satisfactory" formula I16:L16

=IFERROR(INDEX($C$3:$K$3,,(INT((ROWS($1:1)-1)/COUNTA($B$5:$B$10)))*2+1),"")

=IFERROR(IF(N16<>"",INDEX($D$5:$L$10,MOD((ROWS($1:1)-1),COUNTA($B$5:$B$10))+1,((INT((ROWS($1:1)-1)/COUNTA($B$5:$B$10))*2+1))),""),"")

=IFERROR(IF(O16<>"",INDEX($C$5:$K$10,MOD((ROWS($1:1)-1),COUNTA($B$5:$B$10))+1,((INT((ROWS($1:1)-1)/COUNTA($B$5:$B$10))*2+1))),""),"")

=IFERROR(INDEX($C$4:$K$4,,(INT((ROWS($1:1)-1)/COUNTA($B$5:$B$10)))*2+1),"")

Hi Stuart

Quick revision to correct my formula, I forgot that unlike other formulas, COUNTIF doesn't see empty cells as 0 when you use <>0.

If there are no negative values in the range to be used then you just need to change "<>0" to ">0" which will look for positive values only, and correct the problem, otherwise this version will check positive and negative individually,

=IF(B15="","",IFERROR(INDEX($C$3:$K$3,IFERROR(MATCH(B15,$C$3:$K$3,0),1)+((SUM(COUNTIF(INDEX($C$5:$K$10,0,IFERROR(MATCH(B15,$C$3:$K$3,0),1)),{"<0",">0"}))=COUNTIF(B$15:B15,INDEX($C$3:$K$3,IFERROR(MATCH(B15,$C$3:$K$3,0),1))))*2)),""))

Rather than setting the formula to bigger ranges than needed, why not set the ranges that refer to the source table as Named Ranges, much easier to do a quick edit there than many times in a formula as and when things change.

Much more efficient for calculation than having lots of empty rows in the formulas as well.

I can see the logic in John's method and how he was thinking, but I'll leave it to him to explain how it works (possibly that is the part that needs adjusting to fix blanks being returned).

Glad my comment raised a chuckle or 2

See attached: highlighted in yellow.

The rows($1:1) is just a counter used to determine column or row in the INDEX formula.

Here's a copy of your last example using named ranges to eliminate the errors in my formula.

John has provided the same to show his formulas working, but just to add that the extra rows that were in your sample file (using John's method) were caused by the formula in B8. A formula that returns a blank with "" is still counted when using COUNTA.

In the file I've attached, I changed COUNTA to COUNT in John's formulas, but if the references in column B, which are used to define the number of rows, are not numeric, then COUNT will not see them.

** The named ranges behind my formulas use COUNT to find the last row, so they will also suffer the same fate.

In my formula you could replace COUNT/COUNTA with SUMPRODUCT which removes the problem of numeric vs text data

e.g.

=IFERROR(INDEX($C$5:$K$10,MOD((ROWS($1:1)-1),SUMPRODUCT((LEN($B$5:$B$10)>0)+0))+1,((INT((ROWS($1:1)-1)/COUNT($B$5:$B$10))*2+1))),"")

Unfortunately, you cannot use this in a named range (Jason's "Row_Count")

Originally Posted by JohnTopley

Unfortunately, you cannot use this in a named range (Jason's "Row_Count")

Any reason why not, John?

It works fine as far as I can see, the only difference being that the range needs to be qualified with the sheet name when used as a named range.

Although I would use =MATCH(2,1/LEN($B$5:$B$10)) as a preference (if there was a blank row in the middle of the data range, sumproduct would drop the last row). That said, my formula is not meant to cope with empty rows in the middle of the data range, so this method would highlight the fact that there is a problem by throwing out a sheet full of errors, but not be able to resolve them.

Ah ... I had any error but I didn't investigate further as I "assumed" it was SUMPRODUCT rather than not qualifying the range: another lesson for me!

And thank you for your assistance (to me) in attempting to get this resolved.