Last occurence of a random element in cell in column

Hello everyone,

My name is Maico and I need some help with my thesis and I cannot seem to make it work.

I am working on a problem with cells and columns.

It can be that I have a column with concatenated cells as follows:
4
4-8
4-8-3
2-4-8-3
1-4-8-3-5
-
-
-
9
9-6
7-9-6
-

The column next to the above one gives certain numbers for capacity, for example
100
200
300
400
500
-
-
50
150
250
-

I want to give an number as input and receive back from the formula the complete cell next to the one that corresponds with the latest occurence of the input in the cell.
This means that, if the input is 6, my output would be the value of the cell next to 7-9-6, the one with the capacity, since this is the last occurence of a 6. If my input is 4, I want my output to be the value of the cell next to the one with 1-4-8-3-5.
The formula should work for any number as input, since it is coming from a generated column and is random of length. Also, the number of empty rows (with -) differs, sometimes 1, sometimes 2 but it can also be 100.

This means that the column can be
4
6
5
but the column is most likely really long, maybe like 1000, or more

I want my output then to be
500
250
500

because these are the corresponding contents in the column next to the one with
1-4-8-3-5
7-9-6
1-4-8-3-5

becuaes these are the latest occurences of the numbers 4, 6 and 5.
I have been trying to use the index function and more things but I just cannot seem to make it work.
All the help is appreciated

Cheers,
Maico

In addition:

Sorry that I didn't post my file, it is really a mess.
I think it is quite understandable from this.
Any feedback would also be welcome

Hello JeteMC,

Sorry for the slow response but it worked very well as I was asking, so thank you very much for that.

Unfortunately, I found something that I cannot fix, so I hope you, or someone else, can also help me out with that.
When you download the file, and you change the concatenated column from
4
4-8
4-8-3
2-4-8-3
1-4-8-3-5
-
-
-
9
9-6
7-9-6
-

to

4
4-8
4-8-3
2-4-8-3
1-4-8-13-5
-
-
-
9
9-6
7-9-6
-

The difference is in the number 3, which I changed in the last column once to the number 13.
If I search, in this file, then for the last occurence of the number 3, it will give me the number corresponding to the cell in which there is 13, instead of the one with 3 alone.
So the problem is that I need to find the corresponding value of something in between two dashes (-), instead of only finding the number 3, because it does not do it correct.
I need to be able to do this for one digit, two digit and three digit numbers. So sometimes with 1, sometimes with 2, and sometimes with 3, random, so it needs to work in all cases.

Next to that, I discovered something else that I can not seem to be able to fix..

The problem is that I have the concatenated cell like this
4-87-9-1-43
In this cell, and in any random cell, I need to extract the very first number with a formula, and in another formula the very last number.
So in this case, one formula should give me the number 4 and the other should give me 43.
Also this should work with one, two and three digit number that can be random at both the beginning and end.
So this basically means everything before the first dash (-) and everything after the last dash (-)

It seems that I am not advanced in Excel, so that is why I am requesting your help..

All help is appreciated. I am so much stuck...

Cheers,
Maico

Hello,

I'm working now on the file and I noticed that it does not work yet for numbers like 10,20,30 and 40. Do you know how I can fix this myself?

Also, in my previous post I was asking if it was possible to always extract the first full number before the first dash (-) and the last full number after the last dash (-). I also can't find the answer to that still. I believe that it is not in the file.
Please correct me if I am wrong.

All help is appreciated!
Cheers,
Maico

I added a screenshot where I am searching for 3 and I get the one from 30

Originally Posted by Maicoooooo

Hello,

I'm working now on the file and I noticed that it does not work yet for numbers like 10,20,30 and 40. Do you know how I can fix this myself?

Also, in my previous post I was asking if it was possible to always extract the first full number before the first dash (-) and the last full number after the last dash (-). I also can't find the answer to that still. I believe that it is not in the file.
Please correct me if I am wrong.

All help is appreciated!
Cheers,
Maico

Goodmorning again,

Thanks for the help, it seems to work out all fine. I appreciate it very much

In the meantime, I have been trying to make some progress in my thesis but I stumbled upon some new problems.

Since someone asked me to always send a file with the question, I believe that is what I have added now.



In the sheet, you can find the problem as described before, with concatenated cells corresponding to routes.

What I would now like to build automatically, is something I have now just typed in the two tables from E1 to G5, and fro0 I1 to J4.

For table 1, I want to make an automatic table which numbers the final routes. This means that the table should only include routes that are final, and not any intermediate routes, since they are not finished yet
They can be numbered like I did now for example, like 1, 2,3 and so on. The problem, again, is that there can be for example 60 routes in total and it needs to build it automatically with only the routes that are final.

For table 2, I would like to see which values are not included in the final routes yet. In this case, it is about numbers 2 and 15. What I can think of for example is to add a number in cell K1 which in here is 15, corresponding to the total number of clients.
In table 2 then, the numbers from 1 up to 15 should be displayed, if and only if they are not used in table 1, column F.


I would appreciate the help, because you guys are helping me out so much!

Cheers,
Maico