Complex setting for looking up and listing based on 5 variables

[Fno]

Dear Excel masterminds.

I have been working on making an invoice database for a company within the family, as they still work with software that 15+ years old. I have pretty much made the database in the following manner:
Sheet1: Contains a list of invoices and their services/items. It is meant to be fully information lookup style so that everything can be extracted from this "database".
Sheet2: Contains a printable format for the invoices. The person working with the document can select the invoice number by a drop down list and then the respective invoice will be listed.
Sheet3: Contains lists of customer adresses and such.

It all turned out nicely when I thought that the company only sold a single service and then a set of items, meaning that I could just make an array of INDEX, MATCH and SMALL functions (along with a bunch of IF statements) to first list all the services and then the items (2 variables). However, I just imported last years invoices to test the document and now it turns out that the services in the company can be categorized in 4 groups and then items. I am trying to work around a way to cleanly list the services on the printable format based on an ordered manner.

To strip everything down to the basics, this is what I have:
- Services are categorized into 4 groups and then there are items as a last group, meaning I have 5 variables. I will call these variables X, Y, Z, V and I.
- In Sheet2 I have cells that count the number of services/items per invoice. The cells count the following: G12 counts X, G13 counts Y, G14 counts Z, G15 counts V and G16 counts I.
- In Sheet2 I have cells B19-B33 (aka 15 cells) that are meant for services and items to be listed in order.

This is what I need:
- Based on the counted values of the variables, I need them to be listed in B19-B33 in the order of X > Y > Z > V > I.
- If there are multiple instances of a service, say 2 instances of X, then I need both instances before proceeding down the ordered list.
- To make an example: If I have 2X, 1Y and 2I, I will need the list to be X#1, X#2, Y#1, I#1, I#2. If I have 1Y, 1V and 3I, I will need the list to be Y#1, V#1, I#1, I#2, I#3.

Anyone think/know if this is possible?

Regards,

[JohnTopley]

Please post a sample Excel file.

[Fno]

I have attached a sample from my document with altered entries.

Do note that the formulas I have been using for the lookup function are somewhat messy - I have worked my way through this and went along with whatever worked. I think I need to completely rewrite the formulas for the added variables to work. Also, a bunch of the information is in Danish.
The variables may be found in the sheet called "Lister" (where I keep my lists). The variables I have been working on so far are under the column "VaretypeID" which are ordered by numbers 1-5.

[Fno]

I was thinking about working around my problem by adding a column to the listing B19-B33 which gives the identity of the listing in question, so say that I have X, X and Z, it will give X1, X2 and Z1 in the given column.
Or in two columns for which one identifies the type (X X and Z) and the other the instances (1, 2 and 1) as.

I was then thinking of using an index function that matches the identity and then uses the instance number as the k-value in the SMALL function.
Is there any way to set the k-value in the SMALL function to be defined by another cell value?

[Fno]

I found a way to work-around my problem. Albeit it is not an elegant solution, it is yet a solution...

I have attached the working document for reference, but I will try and go through each step below, should it actually end up helping someone else or if someone comes up with a cleaner solution. Mine is really messy.... Some errors may be in the formulas as I am translating from DK to ENG while writing this.

- - -

I started by defining a binary matrix that counts the instances of the invoice in question per service. The matrix gives "A" as an output per service instance of the first category, "B" as an output per service instance of the second category and so on. For each missing instance it gives an "N".

I then numerized the instances by counting each category for each instance. The results are copied to another matrix, or table rather, which basicly just changes the outputs from "A" to "A1", "A2", "A3" and so on for the instances. For every instance not counted I just substituted with a "T".

I then made a list of the above in range AA2:AA36. In the column to the left of this one, column Z, I added the following formula: IF(AA2="T","",COUNT.IF($AA$2:$AA$36,"<="&AA2)) <- this was then copied down to row 36. This column will sort the list alphabetically.
I then made a new list in column AB with numbers 1-36.
Finally I sorted the list by adding a new column, column AC, with the following formula: IFERROR(VLOOKUP(AB2,Z$2:AA$36,2,FALSE);"") and copying down to row 36. This will give a sorted list, sorting after the desired category, starting from A and ending with E.

In column AD and AE I then added the LEFT and RIGHT formula to extract the category and the instance number of the sorted list.

Now comes a new remark that is based on inquiry from the company. If the category is A-D, it will require that a text is added for that service and below that text a space. If the category is E (items), then it doesn't matter and these will be listed right after another.

This does limit the invoice, as having 4 services will fill up 12 lines out of the 15 available. I worked around the problem by adding a new calculating column in AI:
The first cell (AI1) counts the number of services per invoice.
The rows below then gives a number based on AI1 - if there is 1 service it will give 1,1,1,2,3,4,5,6.... and if there are 3 services it will give 1,1,1,2,2,2,3,3,3,4,5,6... (see the document for the code).
In column AG I then added an index of the category based on the list value given in AI2-AI16, however, with IF statements checking for category A-D. If such category is found, it will a "TEXT" line in the range and a "SPACE" line before proceeding with the index. I did the same in column AH, but instead of category it will index for instance number.

This may seem tremendously tedious, but it does the job.
Back in the printing friendly sheet I can now perform an array formula that first checks for the SPACE and TEXT conditions, and after that lists the services/items of interest based on the sorted list I have made in column AG/AH by using a set of INDEX/SMALL functions.

I can go into deeper detail if wanted, but I won't write a novel if for no use :)