Posts by cpereznj

That did it Mumps. Thank you so much for your help on this project.

Hi Mumps, I'm attaching the file as requested. The file only has one module with your latest code to merge comments if they have the same date. I highlighted the events with the same date in the Events sheet. I also made a note in the 2018 sheet to show that the dates in the Date columns are based on formulas. The formulas are there to make it easier to fill the calendar based on the year.
Once again, thank you so much for your help on this project.

Your latest code works if the dates in the "Day" columns are manually entered. For example 01/01/2018. However, if I use formulas to calculate the dates then the code does not work. If each cell in the "Day" columns have formulas the code does not work.

Hi Mumps, I see the problem. Please use the latest file I uploaded on this post. It should have two modules. The first module contains your code with the cases. The second module contains the code to merge events with the same date. This code does not work if the calendar dates in the 2018, 2019 and 2020 are based on formulas. Hopefully, you can replicate the issue on your end.
Thank you

That is great Mumps. Worked like a charm. Thank you so much. I noticed that you used the LookIn:=xlFormulas string. What if the 2018 and other year worksheets use formulas to fill the calendar days? See attached file. I'm using the formulas to make it easy to auto create the calendar with just entering the first of the year in 2018 A1 and so forth. Your code above does not work if I use the formulas.

Thank you again for all the help with this request.

Thank you Mumps. Unfortunately, the updated code did not work. Only one event appears in the comment box. What can I do to merge events with the same date?

Thank you Mumps for the updated code. That did the trick. I have one last question. Just realized that some events may have the same date. If that is the case, is there a way to append or concatenate both events in the same comment?

Thank you Mumps for getting a solution so quickly.. I tried the code and noticed the comments are added to row 3 only in the 2018, 2019 and 2020 sheets. Is there a way the comments can actually be placed in the Day cell corresponding to the Event date? The calendar is setup in a vertical way. I'm attaching my test file in case you need it.

Yes, the events are sequenced based on their dates, oldest to newest. All the events in the "Events" sheet should be added as a comment on the other sheets.

Hi there, I have a file used to track quantities based on dates. The file has three sheets: "2018", "2019" and "Events". Need the events in the "Events" sheet to auto populate comments in the corresponding "2018" or "2019" sheets based on the dates. For example:

"Events" A3 - Event 1 should be placed in "2018" A3. This is because the date in "2018" B3 matches the date in "Events" B3.
"Events" A4 - Event 2 should be placed in "2018" D3. This is because the date in "2018" E3 matches the date in "Events" B4.
"Events" A5 - Event 3 should be placed in "2018" G3. This is because the date in "2018" H3 matches the date in "Events" B5 and so forth.

Thank you in advance for your help.

Re: Concatenate name and add "."

Thank ypou NBVC. That did the trick.

Hi there,

I was able to put the following formula together from searching online:

=CONCATENATE(LEFT(TRIM(RIGHT(B30,LEN(B30)-IF(ISERROR(FIND(" ",B30,
FIND(" ",B30,FIND(",",B30,1)+2))),LEN(B30),
FIND(" ",B30,FIND(" ",B30,FIND(",",B30,1)+2))-1))),1)&".")

I have a list of names in the format: last name, first name and middle initial (with and without a ".")

I'm trying to split the names into separate columns and add the "." to the middle initial. However if the name does not have a middle initial I still get the ".". How can I make the formula add the "." only when there is a middle initial or name?
For example, if I have the following names:

[TABLE="width: 704"]

[tr]

[td]

Name

[/td]

[td]

Last

[/td]

[td]

First

[/td]

[td]

Middle

[/td]

[/tr]

[tr]

[td]

Smith, John J.

[/td]

[td]

Smith

[/td]

[td]

John

[/td]

[td]

J.

[/td]

[/tr]

[tr]

[td]

Smith, John James

[/td]

[td]

Smith

[/td]

[td]

John

[/td]

[td]

J.

[/td]

[/tr]

[tr]

[td]

Smith, John

[/td]

[td]

Smith

[/td]

[td]

John

[/td]

[td][/td]

[/tr]

[/TABLE]

Thank you in advance for your help