Concatenate multiple values from table dynamically with specific delimiters

[Ralleberg]

Hi Forum,

I have a staffing plan where different values are entered depending on whether it is holiday, illness or other absence.
I want to do a data collection based on date and their shifts with a specific formatting as well as in one cell (concatenate?).
The formatting is due to a sub-system which must be passed on to the ERP system, therefore this is a requirement.

I have created an example where at the bottom I have shown the results I want to achieve through either VBA or directly as functions.

Is there a kind soul who has an idea of how to achieve this result so I can change the date at the top and it will dynamically change to the values entered in the above chart.


Cheers, Rasmus

[nankw83]

Hello Ralleberg & welcome to the forum,

Try the below macro based on your sample file & let me know how it goes

Please Login or Register  to view this content.

[Ralleberg]

Hello Ralleberg & welcome to the forum,

Try the below macro based on your sample file & let me know how it goes

First of all thank you nankW83! The macro did the job I initial wanted to happened.
But my calendar is placed in another sheet than my example shows, so is there a way to implement the URL to the sheet in the macro as well?

And another question:

I have multiple sheets (one per week), which have 5 dates where I want the macro to print out the results as concatenated text to a cell with a matching date in the first row. Could this be implemented easily or?

[karmapala]

Hi Ralleberg,

I can change the date at the top and it will dynamically change to the values entered in the above chart.

I'm sorry as I don't completely understand your situation and what you want to achieve.
Anyway, my guess is that you want to enter a date value and get the result based on the date value you enter.
If my guess is correct, this is my way to do the code :

Please Login or Register  to view this content.

2020-05-28_01-25-58.gif

The condition for the macro to run :
the data source table must always in the same format just like in your sample workbook.
There are two type of absence, which is in your sample workbook is vacation and sickness.
(The code need to be modified if for example there is another type of absence, accident for example).

The structure of the active sheet (where you want to get the result) must be the same like in your sample workbook, which is :
1 --- D
1 --- A
1 --- N
2 --- D
2 --- A
2 --- N

And again if the type of absence for example are three types,
the code need to be modified because the active sheet becomes like this :
1 --- D
1 --- A
1 --- N
2 --- D
2 --- A
2 --- N
3 --- D
3 --- A
3 --- N

That's if I'm not mistaken to get what you mean

[nankw83]

@Ralleberg

Are all the sheets in the same workbook or a different file to the one where you'll run your macro from & have the results at ?

[Ralleberg]

I have multiple sheets. I've created a more detailed example than before, which hopefully gives the picture of what im searching for.

The "Staff overall schedule 2020" workbook:

• Is placed somewhere else than the other file. So I want to enter a path for linking the two files together.

The "Weekly staff planning" workbook:

• New sheets are created for each week, and the 5 dates on top is changed to match the week.
• I want to have a button on each page to search for a match on the dates, and enter the values from the other workbook in the corresponding cells as shown in the example.

[nankw83]

Two questions

1- Will you have any other type other than 1, 2 & 3 (vacation, sickness, other absence)
2- In the file where you want to run the macro, your data will always start in row # 8 ? And will go down by 9 rows (3 rows per each absence type)

[nankw83]

One more question, in your latest file provided, "MasterPlan2020" layout is matching to your original file correct? So your data starts from row #5 unlike your sample file where the data starts from row #4

[Ralleberg]

Okay let me try to answer:

1: Yes there will. But I would like to have the opportunity in the macro to add up some of the types, so that in the absence multiple criteria would be applied to the cell.
2: In the original one it would be more than 8, but the overall structure of the weekly plan workbook is correct.
3: Yea that's correct. It's because that the MasterPlan of our department and the first rows are used for other information.

Hope this could help you? If you could make comments along in the macro where I should/could change values in order to fit the data in the original files, that would be just perfect.
I have basic understanding of VBA and would to some extend get the idea of how it works.

Thank you nankw83 for helping me out. This have been on my wishlist for a long time!

[nankw83]

Okay try the below macro, if it works I will add some comments & some tips on how to move forward on your own

Please Login or Register  to view this content.

[Ralleberg]

Okay try the below macro, if it works I will add some comments & some tips on how to move forward on your own

Wow, how could you make that soo fast? Just tested the macro and it seems to work like a charm - thank you!

One last question though:

For the absence cells there could be more than just the "1" definition that should be taken into account. Could you somehow make it look through a list of absence-definitions that would add up the initials in those cells?

[nankw83]

I love excel, I practice a lot & I'm still learning :D …

I didn't get your question, do you mean the type of 1,2,3 (vacation, sickness, other absence) ?

[Ralleberg]

You're cool, I want to learn that too.. :D

So I have various of types. Those 1,2,3 where just simplified examples.

One more try. In the weekly plan, the macro should look for more than the type-1 in the overall schedule. Multiple types should be included as an absence in the weekly workbook. Lets say we have type 1,2,3,4 and 5. Where only type 3 is due to sickness and should be entered in the sickness cell. All other types should be included as an absence.

[nankw83]

If my understanding is correct, in the sample you've provided only 3 types of absences (vacation, sickness, other absence) but in your actual data you have more. We can make small amendment to the macro to cater for that taking into consideration the order in both the original file & the weekly update is the same. i.e. First 3 rows are for vacation [N,D,A], 2nd 3 rows for sickness [N,D,A] … etc. If I misunderstood, I need another sample file

[Ralleberg]

You're right. I have the opportunity to copy a small amount of the original files to use, and i could attach them to this thread tomorrow, if that's okay? With that you would have the full overview, and the correct structure of both files.

[nankw83]

Sure, it's 11:25 PM here in Kuwait & I need to sleep too. Looking forward to see your file tomorrow to finalize & close this thread

[Ralleberg]

Sure, it's 11:25 PM here in Kuwait & I need to sleep too. Looking forward to see your file tomorrow to finalize & close this thread

Yup, the same here in Denmark. I’m looking forward to the result too. Already you have made some great solutions and I’m thrilled that you are willing to help me that much.

[Ralleberg]

So nankw83. I've got some new files which are almost identical with the original ones - at least the columns and cells are matching in order to get the right vba. Hope you're still up for the challenge today (Y).


Please be aware that there are multiple absence types that all should be presented as an absence in my sheet (green marked) and the blue "sick" should be presented as before separately.
Also be aware that not all of the names on the schedule have shifts. Those should not be included in my sheet at all, those are for another purpose. So only operators that have Day, Afternoon or Night should be taken into account, hope this could be made dynamically as well.


Hope this is useful.

[nankw83]

I don't know why didn't you include these files from the beginning Anyway, check the below macro & let me know how it goes. I put some comments to give you some guidance in case you need to make any changes in the future

Please Login or Register  to view this content.

[Ralleberg]

Hi Nankw83, you're right o don't know either... I hoped that I could do the vba tweaking all by myself, but realized afterwards that this is beyond my abilities.

I did some minor changes in the VBA above and I just got the result as intended!

My last request is, that if the overall schedule is already open when the macro is activated, it shouldn't close the sheet?

[nankw83]

Glad things are finally getting sorted out

Checking if the file is open is a bit tricky (as file could be opened through a different excel instance … etc.) so I used a UDF taken from HERE & embedded it in your code. Just give the below code a run & ensure to change the first 3 constants are matching to your files

Please Login or Register  to view this content.

[Ralleberg]

Glad things are finally getting sorted out

Checking if the file is open is a bit tricky (as file could be opened through a different excel instance … etc.) so I used a UDF taken from HERE & embedded it in your code. Just give the below code a run & ensure to change the first 3 constants are matching to your files

Oh I see..
I have changed the 3 constants on top, and when i run the macro i get an error stating: "Path/File access error". I'm sure I've entered the correct path and filename?

[nankw83]

Try to remove the FilePath from the function below

From this: IsFileOpen(FilePath & FileName)
To this : IsFileOpen(FileName)

[Ralleberg]

@nankw83 just forget it. I commented the

Please Login or Register  to view this content.

line, so now it just opens the schedule sheet, that seems to be just fine. I'm thinking the other way is going to be more complex than I thought.

But thank you very much, for helping me out! I had the first usage today, and it work just perfectly.

I'll mark the thread as closed in a moment.

[nankw83]

Glad to help

If this takes care of your requirements, please mark this thread as ‘SOLVED’ from the Thread Tools menu at the top of your first post. Also, you could add reputations to those who have helped by clicking the star under the username of the members