• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Problem with Concatenateif function

RobSA

Member
Hi Folks,

I am having a problem with the concatenateif function.

I have highlighted the problem in red fill in the DTS worksheet.

The idea is that upon obtaining a value in a column the number reflected in column b of the LOG worksheet must be listed.

The problem is that the number of time the value is found is correct however the concatenate function only does some of the work, that is only list some of the numbers and in one instance does not list the number.

Your help here would be appreciated
 

Attachments

  • RH - 7 4 1 1 DTS IN Log 31082015.xlsm
    675.1 KB · Views: 0
Thank you for your reply - new file is uploaded
 

Attachments

  • Concatenate.xlsm
    675.3 KB · Views: 1
The formula in the Concatenateif function is
If CriteriaRange.Cells(i).Value >= lowerval And CriteriaRange.Cells(i).Value < higherval Then

Cell H14 shows the value of 017 which is the only cell from the Log sheet that matches the criteria >= 191 and <200

What should it be if this is wrong?
 
20 isn't included as the formula in the Macro is less than the Higher values
The formula in the Concatenateif function is
If CriteriaRange.Cells(i).Value >= lowerval And CriteriaRange.Cells(i).Value < higherval Then

If you want to include the upper limit change the formula to :
If CriteriaRange.Cells(i).Value >= lowerval And CriteriaRange.Cells(i).Value <= higherval Then
 
Hi Hui,

The correction has worked well for the upper table, however the lower table now has duplications.

Can you assist?
 
Hi:

Your code is very simple it will loop through DTS Chart tab and pick up the days between column (column C is your lower limit and column D is your upper limit) and check for a value in the LOG tab column that false between your lower and upper limit. Your original code included your lower limit but excluded your upper. But when you included<=in your logical statement it considered your upper limit as well. As per my understanding your code is doing what it is suppose to do. The lower table maybe giving duplicates because in your code you are not asking to remove any duplicates, you may have to remove duplicates from your base data if you want to get results without a duplicate.

Thanks
 
Back
Top