Create excel table using formulas

Your file doesn't have anything in columns I:N.
Is there a reason why you don't want to use the built-in Sort function?
1. Select your whole range (A2:D25 in your sample file);
2. On the Data tab, click Sort to open up the Sort dialogue box;
3. Because you want to sort by two variables, click Add Level;
4. Against Sort by, select Arbeitsort from the drop-down list;
5. Against Then by, select Date;
6. Click OK.

Okay, I've got a solution for you to dynamically sort the data. It uses helper columns, which you can hide later.

Helper column 1.
This assigns numbers to each Arbeitsort, with lowest number (0) for the first alphabetically. For example, 'Papito' in B6 is assigned the number 6 because there are six entries in column B which are before 'Papito' alphabetically (3 x Bruppbacher and 3 x Endreinigung). The formula (in F3, dragged down) is:

Formula:

=COUNTIF($B$3:$B$25,"<"&$B3)

Note the quotation marks around the < and the & sign.

Helper column 2
This does the same as above, for each Date which has the same Arbeitsort. For example, row 6 gets the value of 2 because there are four dates against 'Papito', 2 of which are less than the one in A6 (A6 is 15.01.2018, the two 'lower' dates are 02.01.2018 and 04.01.2018 in A8 and A14). The formula (in G3, dragged down) is:

Formula:

=COUNTIFS($B$3:$B$25,$B3,$A$3:$A$25,"<"&$A3)

Note that your dates are currently stored as text, but even if you convert them to proper dates, this formula will still work.

Helper column 3
This uses a newly-inserted column H and simply adds the two previous helpers. This is the formula (in H3, dragged down):

Formula:

=F3+G3

Of course, you could skip the two previous columns and just combine them in this one, using this formula:

Formula:

=COUNTIF($B$3:$B$25,"<"&$B3)+COUNTIFS($B$3:$B$25,$B3,$A$3:$A$25,"<"&$A3)

Ideally, this column would contain only unique numbers, but if you have two rows with the same Date and Arbeitsort then you will get non-unique numbers (for example, both rows 16 and 17 have 17.01.2018 and Schaub).

Helper column 4
To fix the possibility of non-unique numbers in the previous column, we now Rank those numbers. Again, this is a new column - column I.
If we used one the Rank functions on its own, we would still get non-unique numbers. For example, H13 and H22 both contain the number 4, so would both be ranked the same. We want unique increasing numbers, so we combine Rank with a CountIf of each number. Here's the formula (in I3, dragged down):

Formula:

=RANK.EQ(H3,H$3:H$25,1)+COUNTIF(H$3:H3,H3)-1

Output Data
Each row has now been assigned a unique number, from 1 for the first alphabetical Arbeitsort & Date combination, increasing (up to 23, with the sample data you gave). We can now refer to these numbers in an Index-Match to sort the original data. The new Target Data columns are: K (Date), L (Arbeitsort), M (Start) and N (End). Put the formula below in K3 and drag across to N3, then down as far as needed:

Formula:

=INDEX(A$3:A$25,MATCH(ROWS(K$3:K3),$I$3:$I$25,0))

What the formula does is this:
• Rows provides a number, increasing from 1;
• Match then looks for this number in the list of numbers in column I (the Ranked helper column) and returns the row that match is in;
• Index then returns the data found in that row of the appropriate column (col A in the formula above, but this changes to B, C, D as you drag it across).

When there is no data to find, you will get #N/A errors. You can remove these by using IfError:

Formula:

=IFERROR(INDEX(A$3:A$25,MATCH(ROWS(K$3:K3),$I$3:$I$25,0)),"no data")

Replace "no data" with any error message you want, or with just "" for a blank.

Note
For all the formulae above, you should ensure that the range is amended to show the actual range you need. For example, if you think you may have up to 1000 entries, then replace each $25 with $1000 (only the $25 - not every 25 on its own).

Translation
I note that Arbeitsort is German. If you need the formulae in German too, here they are:

Formula:

=ZÄHLENWENN($B$3:$B$25;"<"&$B3)

=ZÄHLENWENNS($B$3:$B$25;$B3;$A$3:$A$25;"<"&$A3)

=F3+G3

=ZÄHLENWENN($B$3:$B$25;"<"&$B3)+ZÄHLENWENNS($B$3:$B$25;$B3;$A$3:$A$25;"<"&$A3)

=RANG.GLEICH(H3;H$3:H$25;1)+ZÄHLENWENN(H$3:H3;H3)-1

=INDEX(A$3:A$25;VERGLEICH(ZEILEN(K$3:K3);$I$3:$I$25;0))

=WENNFEHLER(INDEX(A$3:A$25;VERGLEICH(ZEILEN(K$3:K3);$I$3:$I$25;0));"no data")

The translation was done using this website: https://en.excel-translator.de/translator/



I hope that does what you need. I've attached a file showing it working with your sample data.

You're welcome, thanks for the feedback and for marking the thread as Solved.