Removing characters from different cells based on contents in a separate cell

[Nightowel23]

Hello all, hope someone can help with this.

How can I remove the characters (initials) "BD, NK, PS" that appear in the light blue cell at the bottom from where they are in the white cells above. The order in which they may appear in the light blue cell can be random and the initials can vary at different times but will always match initials somewhere in the white cells above. The positions of the initials in the white cells is always fixed.

I have tried to nest a few search and replace functions but have had no luck.

Any ideas? Thank you

[Gregb11]

I'm not sure I follow. If you can upload the results you are expecting, that might help. Are you looking to duplicate what's there with the initials removed in the white cells?

[Nightowel23]

Thanks for your response.

See attached 'example 2 xlsx', I have given 3 examples of what I am looking to achieve based on a static reference table.

The text in red is the text I want to hide/delete when it is present in the input cell at the bottom.

Any ideas?

[Glenn Kennedy]

Please check if you have TEXTJOIN in your Excel version. Some www sites claim it is... others say it isn't...

[AliGW]

Microsoft say not:

TEXTJOIN function
Excel for Microsoft 365 Excel for Microsoft 365 for Mac Excel for the web Excel 2019 Excel 2019 for Mac

[Glenn Kennedy]

I am aware of that. Other sites state otherwise.

[AliGW]

I've not come across anyone here yet who has Excel 2016 for whom TEXTJOIN exists. It'll be a first!

[Nightowel23]

I have managed to add the text join function to the workbook using VB code in a workbook module. Will that do?

The function is coming up when I type it in the cells.

Please Login or Register  to view this content.

[AliGW]

You need to use code tags around code - I have done it for you today.

The TEXTJOIN function you've added via a VB module is a UDF (user-defined function). The latest versions of Excel fave a new formula function called TEXTJOIN, which is what Glenn was asking about.

[kvsrinivasamurthy]

Here is the code for UDF "GetData"

Please Login or Register  to view this content.

In H2 then copied to other cells in that table.

=GetData(C5,$H$21)

Similarly for 2nd and 3rd table

[Nightowel23]

Thanks,

I am aware of what Glenn was asking, having added the TEXTJOIN function via the VB module and now tested it, its seem to be doing the same function as that in the latest version of excel.

Glen must have a solution in mind based on the TEXTJOIN function?

[AliGW]

We shall have to wait and see, but I think he was hoping that your version contained the new TEXTJOIN function.

[kvsrinivasamurthy]

PL see post #10 . No textjoin function is required. It is UDF based solution.

[JohnTopley]

Check out the code (both sets) on Column D and "delimeters in R21 (ps,Nka)

You need to cater for mis-typing (NKa vs Nka)

Check GetData on column D with $H$21 (D9)

[Nightowel23]

Thank you for your solution, it has almost done what I need.

Can you edit it slightly such that

1) when 2 initials are similar for example "NK" and "NKa" and if "NK" is the input box it should just remove "NK" and not affect "NKa"

2) Can anything be done to remove the commas, see attached excel.

thank you

[JohnTopley]

Please Login or Register  to view this content.

[Glenn Kennedy]

if you're OK with VBA.. go with JT's solution. I am having fun, as Ecel is telling me that "JB" is not an exact match with "JB" ... there are days...

[JohnTopley]

1) when 2 initials are similar for example "NK" and "NKa" and if "NK" is the input box it should just remove "NK" and not affect "NKa"

How do you cater for mis-typing between the column and input box?

and DM vs Dm when both should be either DM or dm

Very Error prone!

[JohnTopley]

If you are not concerned about case mis-typing then remove the UCase from my VBA. I added it because I assumed (after testing) it was a typo - shows one should not assume too much (or nothing at all) !

[Nightowel23]

Thanks, mostly there.

Ive removed the Ucase from the VB code. Just need to maintain a space after the comma in the white boxes. see the sheet attached

Mistyping is catered as the input in the blue cells will be automated based on a pre-defined table. The blue box will auto populate with those specific initials that are defined once, so they wont actually be typed in manually.

[JohnTopley]

Please Login or Register  to view this content.

Add to change "," to ", "

[Nightowel23]

thanks!

Last thing ive noticed is when the input cell is blank,

1) it does not copy the whole initial accurately from the reference table, it seems to only copy the first letter if the initials are 2 characters or then the first two characters if the initials is 3 characters.
2) when there are several initials that are separated by commas its only copies the letters with no commas and then the very last letter is also missing.

[JohnTopley]

Why would you call function if input is blank?

Amended code ......

Please Login or Register  to view this content.

[JohnTopley]

Corrected code

Please Login or Register  to view this content.

[Nightowel23]

Thank you for your help,

This has been so effective, I was wondering if I could have your input on this final bit, I do have a excel formula which nearly achieved what I need to do but I think with your help a VBA code will be more robust.

1. The current code for the 'remove it' is adding 2 spaces in-front of the comma instead of the 1 space which is in the original static reference table. I cannot see where this error is coming from.

2. Can you help with VBA code to help populate the 3 blue leave boxes under the specific dates with the initials, dates and leave type from the table on sheet2.
Accordingly, I think the 'remove it' VB code may need to be changed as when I reference to a cell range in the formula it returns an error.

Many thanks for your help, I work in a hospital ward and this will save us a lot of time which we can dedicate to other tasks!

If its too much to ask, please just help resolve issue 1. Thanks again,

[JohnTopley]

For 2. I suspect there can be more than one entry for each box e.g 2 people on annual leave in the same week. If so, the ouput will be "messy" if the data is concatenated into one cell. I'll take a look at how this might be best handled.

[kvsrinivasamurthy]

Here is the new simple code for UDF seems to be working ok.

Please Login or Register  to view this content.

[Nightowel23]

Here is the new simple code for UDF seems to be working ok.

Please Login or Register  to view this content.

Thank you for your solution, it is working beautifully.

Are you able to also help with the excel example 5 doc, need VBA code to help populate the 3 blue leave boxes with initials based on matching the dates and leave type from the table on sheet2.

[Nightowel23]

How can I get the formula to look at values in a cell range. I am getting an error when I expand the formula reference cell to a reference range. thanks

[kvsrinivasamurthy]

Post #28. Do you need VBA code or Formula will do.

Post #29. Pl Upload file showing the problem.

[Nightowel23]

See up attached excel

Post #28. Ok formula will do, I couldn't get the formula 100%, can you help?.

Post #29. File uploaded.

[kvsrinivasamurthy]

Name of the leaves are not matching in both the sheets.

[Nightowel23]

see now, have changed it

[kvsrinivasamurthy]

Pl upload the expected results in all cells including where leave data is to be entered..
Because you want avoid all the entries in range C21:L21 for each cell in C7:L20.
In formula
=GetData(C7,$O$22:$P$24)

In what way C7 has relation to $O$22:$P$24, Where leave is to be entered.

[Nightowel23]

1.

Please Login or Register  to view this content.

when reference to cell range $O$21:$P$23 where the different leave types will be it returns VALUE! error message?

2. Need to put the initials from the table in sheet 2 into the leave box, (Annual leave, Study leave, Other leave in sheet 1) with matching dates.

Does this help explain?

[Nightowel23]

sorry error on previous excel

see this one with correctly referenced cells in formula

[kvsrinivasamurthy]

As you are referring to number of cells in second part the code is revised for UDF.
Formula is also given for Rows 22 to 24
Formula in O22 then copied across

Please Login or Register  to view this content.

Revised code

Please Login or Register  to view this content.

[Nightowel23]

thank you so much, nearly there.

If you look at example 7d, formula only returns one initial "Nka" need to return like "NKa, YJL" etc

see attached please.

[JohnTopley]

Added code to put the initials in lines 21-23

Please Login or Register  to view this content.

This code is triggered by code in the Sheet1

Please Login or Register  to view this content.

What needs to be confirmed is the format of ALL your data in your "live" [production] workbook


There is a named range "Initials" which is used to search through your leave table. NOTE: chane defintion to

=OFFSET(Sheet2!$E$6,,,COUNTA(Sheet2!$E$6:$E$100),1)

Click on line 21 to 23 in output table to trigger event.

[Glenn Kennedy]

Chaps...

For interest only. How to waste a wet sunday afternoon in Belfast....

https://www.excelforum.com/excel-for...ml#post5487526

I gave up on this thread a long time ago... it moved in a VBA direction that I don't do. however, it threw up an interesting (no... really bl@@dy annoying) issue for me. My effort NEARLY works... but not quite. And I don't know why.

[Nightowel23]

Works solid!

But I dont understand where to put the

Please Login or Register  to view this content.

When I place it where "Initials" are defined it throws up a debug error.

Please Login or Register  to view this content.

Sorry im trying to learn VB! thanks for your help

[Nightowel23]

I can see that the initials in the table on sheet 2 are only read up to line 11, beyond that it does not seem to work, how do I implement the above code to extend the range for this? thanks

[JohnTopley]

Formulas==>Name Manager==>Initials==> (Edit) Refers to: =OFFSET(Sheet2!$E$6,,,COUNTA(Sheet2!$E$6:$E$100),1)

[Nightowel23]

ahh i see what you mean. Thank you so much its does the trick! It works so well.

Any recommendations for where to learn VB like this for excel spread sheets?

Thank you

[JohnTopley]

I haven't looked recently but there is plenty of help on--line plus numerous books,

e.g www.wiseowl.co.uk

[JohnTopley]

If you have a satisfactory solution, ......

If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED.

Also, you may not be aware that you can thank those who have helped you by clicking the small star icon located in the lower left corner of the post in which the help was given. By doing so you can add to the reputation(s) of those who helped.

[Nightowel23]

I am trying to upscale your solution using the following edited code with the following excel but I cant seem to get it to click.

Could you kindly see where I've gone wrong.

Thank you

Please Login or Register  to view this content.

Please Login or Register  to view this content.

[kvsrinivasamurthy]

Code for UDF "GetInitials" is here. Macro code also available. Pl see file.

Please Login or Register  to view this content.

[JohnTopley]

Subroutine updated vs Function from #48: you choose!

Please Login or Register  to view this content.

[Nightowel23]

Thank you for your reply and solutions.

I am trying to upscale the solution. See attached excel.

After the 4th set of table the leave is not populated correctly in the box. I have expanded the range in the code.

Please Login or Register  to view this content.

And selected the correct cells in the formula, not sure why its not picking the correct dates.

Could you take a look? Thanks

[Nightowel23]

Actually I think the above is working now. ​

Is it possible to incorporate an additional function this time that looks across the Morning name row and compares this against the leave table on sheet 2 and removes the surname from this row. if the person is on leave .

Please have a look , this will be the last thing before I upscale.

Thank you.

[kvsrinivasamurthy]

Is it possible to incorporate an additional function this time that looks across the Morning name row and compares this against the leave table on sheet 2 and removes the surname from this row. if the person is on leave .

I did not follow what you want. Can you explain in detail with example.

[Nightowel23]

I did not follow what you want. Can you explain in detail with example.

I have added a surname row (morning name) to the tables on sheet 1 and these surnames are also in the leave table on a c column on sheet 2.

Similar to what happens with the initials, these surnames need to be removed from the rows in tables on sheet 1 if they appear and are within the date range on the leave table in sheet 2.

Need a similar function like the getintials but this time getsurname.

Does this make sense? Thanks

[JohnTopley]

How do you want this code "triggered"?

Options:

1) Go through ALL "Morning name" lines and remove names (I assumes names are surnames) for all matching leave days

If you do not have too many weekly blocks of data then each "Morning Names" could be a named range (not the only solution) and the trigger is any entry into any day of the selected "Morning names". 52 weeks = 52 named ranges!


2) Click on "Morning names" on a line and trigger code for that single line: or an entry in a line triggers the code.

[kvsrinivasamurthy]

For 5-4-21 G206 shows Kaz/Krem/David. But in morning name in Sheet2 only Klender is on leave. Wha tis required in G206 to O206.

[Nightowel23]

See example 8c excel --> row 92, 121, 150, 179, and 208 for what is required as example for the morning names.

Yes John, 1) can it just be triggered base on clicking on the table for sheet 1? Or just entry into the leave table on sheet 2 to trigger it.

There will only be up to 6 weeks at a time on sheet1.

See example 8c

Thanks

[JohnTopley]

Another question: are we only concerned with dates in the "base" table i.e "Static Table for reference" ?

If we are to look at all of the 6 other tables, we need to "identity" the date rows in each table: they are either fixed as per your latest sheet with each table of consistent length oor we add a "prefix" e.g "Date" in column F to search for the next block. OR as I proposed earlier, we give each table a name (Named range).

Hope this makes sense!

[kvsrinivasamurthy]

Code revised for UDF "GetInitials".
Change the range in the formula and copy to Morning Name row also.

[Nightowel23]

Another question: are we only concerned with dates in the "base" table i.e "Static Table for reference" ?

If we are to look at all of the 6 other tables, we need to "identity" the date rows in each table: they are either fixed as per your latest sheet with each table of consistent length oor we add a "prefix" e.g "Date" in column F to search for the next block. OR as I proposed earlier, we give each table a name (Named range).

Hope this makes sense!

John - the morning name row may move up or down a few cells in the final version, is there a way to select with a function, similar to what we are currently doing with =getinitials(cell range), thanks

[Nightowel23]

Code revised for UDF "GetInitials".
Change the range in the formula and copy to Morning Name row also.

'

Thanks for the update,

The requirement is wrong, please look at example 8d excel.

The morning name is static based on the top static table for reference.

Only the names should be removed from the morning name row on sheet1 if the name is in name column and matches the date range from the start and end leave dates on the sheet2 table. All the other names should remain in the morning name row.

See row 90, 119, 148, 177 and 206 with example of final outcome.

[JohnTopley]

Please Login or Register  to view this content.

I have used named ranges for table "Dates" and "Morning Names" which will adjust if rows are added/removed

Test it by calling macro from button on Sheet1 above Static table.

[Nightowel23]

Brilliant thank you,

This way is fine, but then also need a second button in case the leave dates are changed to update everything --> the button needs to reset all the morning names back to the reference table and then re-check leave dates in sheet 2 in case any dates have been changed and then remove names again.

Do you understand the problem? thank you

[JohnTopley]

So to be certain: Button 2 will set all the "Morning Names" in each of the 6 tables to the values in the "Reference Table". You will then run Button 1?

Was there any reason why the tables could not be placed across columns so we have 6 weeks of contiguous dates across row 2 (for example).

I know the tables can vary in depth, but if the variation is not too great, the overall table depth could be set to the max.Even allowing for the reference table this only 60 columns.

NOTE: I changed the "Leave Table" to start in row 2 (headings in row 1).

Just better data formatting to do it this way.

[Nightowel23]

Can the code not be triggered when selecting the sheet1 tab so that all the "Morning Names" in each of the 6 tables are reset to the values in the "Reference Table" and the names are removed as per button1 automatically, just in case someone forgets to press the button before issuing the rota. If that is not possible then okay make a second button to reset the names back to reference and will have to remember to press button 1 to remove the names if there has been any change to the dates in the leave table.

The tables are ordered in that way as another table will go on the right side of them.

There were a few empty rows at the top of the leave table as something else was going to be put there and then the pane frozen so that it could be seen when scrolling down the leave table, will it stop the code from working if i re-add the blank rows again?

thanks

[Nightowel23]

Also, I have a formula that will change the dates based on the month selected, so the morning name row needs to be a bit dynamic in the sense it needs to reset, look at the date and leave table to make sure its all in sync.

[JohnTopley]

Please Login or Register  to view this content.

Also, I have a formula that will change the dates based on the month selected, so the morning name row needs to be a bit dynamic in the sense it needs to reset, look at the date and leave table to make sure its all in sync.

And the logic is? There is no obvious link between names assigned in "Mornimg Names" and the "Leave" table.

Why are "Wright/Romeo" in 02/03/2021 "morning Names" but not in 03/03/2021? And Klender in 03/03/2021 but not on holiday until 05/04/2021 ?

And yes, it will impact the VBA if the "Leave Table" is moved down. In my view, it is much better to start tables in row 1 and in particular refrence tables. It not, it requres added logic to dtermine the "Offset" from Row 1.

[Nightowel23]

Where do I put that code?

The team leader is different every morning and is fixed for each day.

thank you

[Nightowel23]

Figured it out,

theres a debug error on this line when the macro is run

Please Login or Register  to view this content.

[JohnTopley]

In my possted code I missed the "S" of SUB: code goes in Module 1

Sub Reset_Morning_Names()

And need Named range called "Ref_Mnames"

[Nightowel23]

yes, already did that,

debug error with

Please Login or Register  to view this content.

[Nightowel23]

compile error

[JohnTopley]

See attached

[Nightowel23]

Thanks a million thats working.

Is there anyway you could add 6 empty rows to the top of the table on sheet 2 as there is some crucial information that will go there.
I tried to do this with the code but im still a novice,

Thank you.

[JohnTopley]

If you look on Sheet3 you will see a table as your require and in Module 2 code I used to test it. The key code is assigment variable srow - which determines start row of "leave" tableand the "With" code relaces the eqivalent code im module 1. Check named ranges for the "Leave" table -(2 versions) : your little challenge is to modify the code.

If you cannot manage I will send you a version in the morning

[Nightowel23]

Gave it a good 2 hours. Cant crack it. Youll have to show me tomorrow.

[JohnTopley]

Early starter I see!

Can you let me know re this what is required this

Also, I have a formula that will change the dates based on the month selected,

Is it simply assigning data based on the reference week? If so, the only date needed is date of the first Monday of the 6 weeks: all other dates can be inserted using simple formulae.

I would rather address this before sending an updated file wich onnly has the minor changes

Please Login or Register  to view this content.

[Nightowel23]

Early starter I see!

Can you let me know re this what is required this

Is it simply assigning data based on the reference week? If so, the only date needed is date of the first Monday of the 6 weeks: all other dates can be inserted using simple formulae.

I would rather address this before sending an updated file wich onnly has the minor changes

Please Login or Register  to view this content.

Yes, essentially the first Monday of the 6 weeks period will be calculated using

=DATE(E3,VLOOKUP(Rota!E4,Formula!E2:F13,2,0),1)

The subsequent dates will be calculated from this with a simple addition across the date rows.

The user will be able to change the month via a drop down list in a cell so that the dates are automatically updated on all the 6 tables.

Thanks for the code. Will try it this evening!

Unless you can send a updated document. Thanks

[kvsrinivasamurthy]

Pl see file. I have applied UDF's only to Table1,Table2 and Table3.
GetInitials Code changed To take the range from Column D to I.
New UDF GetMorningNames code enclosed. Range is Column D to I.

Code For UDF GetMorningNames

Please Login or Register  to view this content.

[Nightowel23]

Please Login or Register  to view this content.

I have added a column to the leave table, the getinitials only works when the initials are in column E. How can I change the code to getinitials when initials are in column D?

Thanks

[kvsrinivasamurthy]

Pl upload file.

[jindon]

Nightowel23,
I haven't real all through this thread, but does attached help?

Please Login or Register  to view this content.

[Nightowel23]

Trying to get the cells to display a certain text when the cell is empty based the initials and annual leave dates.

See attached excel for an example of the current problem.

Thank you.

[kvsrinivasamurthy]

You added a column C in between. That is why it was not working. I have changed the code. Now Ok .
Pl see file.

[Nightowel23]

Please see the pink box as well. Need your help with the formula.

Please Login or Register  to view this content.

[kvsrinivasamurthy]

Pl refer the sheet name and cell name and what is required there.

[Nightowel23]

See orange box on sheet 1 for example of requirement, thank you

[kvsrinivasamurthy]

Code changed.
Same UDF gives required output.
Pl see file.