data manipulation

[Manexcel]

I have a dataset of around 300 customer names. Each name is unique. Each name also has a unique number (can be 2 or 3 or 4 digits) e.g. customer1 51, customer2 9999, customer3 888, customer 4 7777 etc.


I have a series of meeting dates with each customer for 2018 e.g. 23-jan-18 and 27-Feb-18 and 27-Mar-18 etc. Could be 12 meetings per annum or 3 or 4 or 5 or any number in between

For each meeting there is a documented key meeting date (month number) e.g. 1 to 12. For this meeting I would like to have highlighted in BOLD.

An example dataset is below:

Customer name / customer no./ Key meet date / Meet date 1 / Meet date 2... / Meet date 12 etc
customer 1 / 51 / 1 / 01-jan-18 / 02-feb-18 / 03-mar-18 etc
customer 2 / 9999 / 3 / 02-jan-18 / 02-feb-18 / 04-mar-18 / 01-oct-18 / 01-dec-18
customer 3 / 888 / 2 / 03-jan-18 / 03-feb-18 / 01-mar-18
customer 4 / 7777 / 3 / 04-mar-18 / 01-sep-18

etc

My goal would be to have a monthly overview (1 month per sheet), by actual day of each month, for each of these meetings that occur on any given date / day. But only the numbers against each day in each month AND for each key meeting number to be in BOLD

An example of what my requested output is below:

For each month of the year...

Jan

1 51,
2 9999,
3 888,
4
31

Feb

1
2 51,9999,
3 888,
4
28

Mar

1 888
2
3 51,
4 9999, 7777,
31

etc.

Is it possible to create the above output via formula(s)?
I thank you in anticipation and for your consideration and time.

 

[Scott T]

Since you went with testing the month sheet make sure any non month sheet does not start with a month name. "Martina" starts with Mar so the code will think it is a month sheet.

I am unable to replicate your problem. When I run the code with conditional formatting to shade alternate lines it still works, but for the one column is cleared of formatting since the code does that.

What are formula you using in conditional formatting?

 

[Manexcel]

Scott thanks for the guidance and will ensure any non month sheet does not begin with a month sheet name.

The formatting I am using is =MOD(ROW(),2)=1 with fill colours.

I have performed some additional testing on my current issue.

You are correct the procedure works ok and I just lose one column D2:D32 on each sheet of formatting as this is cleared at the beginning of the procedure.

However, to overcome I thought I could comment out the line of code that "clears formats" (I thought wrong!) as this is when customer numbers were being given wrong days and being highlighted in BOLD incorrectly.

I trust the above will assist in clarification.


​

 

[Scott T]

Try changing the line to. Rather then clearing all formatting this will change the bold status of the cell.

w.Range("D2.D32").Font.Bold = False 'set column in monthly sheets to unbold

 

[Manexcel]

Scott, this has worked just great. Thank you so much.

As you are aware the customer numbers are being put into cell D2 on the monthly sheets and depending on the number of customers for any given day the number of columns they span increases.

Is there a way of maximising the number of columns from D2 to say M2 before the customer numbers are wrapped?

I have tried merging cells in a monthly sheet from D2 to say M2 but the procedure gives an error message of;

"run time error 1004" - we cant do that to a merged cell.

line w.range ("d2.d32").clearcontents

Any way around?

Thanks again.​

 

[Scott T]

If you have wrap text on then the column width determines when the line is wrapped.

Merged cells cause a lot of problems and should be avoided as much as possible.

 

[Manexcel]

Scott, fully understand and am going with your recommendation.

I hope you don't mind asking another question...

As a further enhancement to the worksheets I wanted to count the number of customer numbers per day that are in bold. I have been given the following code;

Function BoldCount(Rng As Range) As Long
Dim x As Long, Txt As String
Txt = Rng.Characters.Text
For x = 1 To Len(Txt)
If Not Rng.Characters(x, 1).Font.Bold Then Mid(Txt, x) = " "
Next
BoldCount = 1 + UBound(Split(Application.Trim(Txt)))
End Function

Via VBA code option to use
CountOfBoldWords = BoldCount(Range("D2"))

To call this from within a worksheet formula, you would do this...

=BoldCount(D2)


I used the =BoldCount(D2) option in cell P2 and it worked ok and I got the sum of customer numbers in BOLD which is exatly what I wanted
However, when your procedure runs it clears and deletes the data in cell D2:D32.
At this point I get an error #value ! in P2 until I “reset” the =BoldCount(D2) function and all is ok again.

Is there an option around this so I don’t have to reset the =BoldCount(D2) function on 18 sheets.

Many thanks again for your time

 

[Scott T]

Find the two Application.ScreenUpdating lines and add the Application.Calculation after each of them

Application.ScreenUpdating = False
Application.Calculation = xlCalculateManual 




[COLOR=#008000]' this is at the end of the code[/COLOR]
Application.ScreenUpdating = True  
Application.Calculation = xlAutomatic

 

[Manexcel]

This has resolved my issue.

Scott I wish to thank you for all your time and efforts in resolving my challenges. It is very much appreciated. Great job.

 

[Scott T]

Glad to help.