Populate Date Based On Non-Modal Count?

[xlmaniac]

Dear All,
I do have a sample data set across A1:E4.
The data represents the NO Of BINs in which the SKU is kept at the DC.
I would like to figure out the dates on which the no of BINs is more than the Modal no of BINs across a given period.
The formulae put in F2=COUNTIF(B2:E2,"<>"&MODE(B2:E2)) & so on.
Could somebody help me out with the formulae across G2:G4 which can populate the desired result as shown below?

[TABLE="width: 616"]
<tbody>[TR]
[TD]SKU[/TD]
[TD]01-Oct[/TD]
[TD]02-Oct[/TD]
[TD]03-Oct[/TD]
[TD]04-Oct[/TD]
[TD]Non Modal Frequency[/TD]
[TD]Desired Result[/TD]
[/TR]
[TR]
[TD]A[/TD]
[TD]4[/TD]
[TD]3[/TD]
[TD]4[/TD]
[TD]4[/TD]
[TD]1[/TD]
[TD]02-Oct[/TD]
[/TR]
[TR]
[TD]B[/TD]
[TD]8[/TD]
[TD]8[/TD]
[TD]9[/TD]
[TD]10[/TD]
[TD]2[/TD]
[TD]03.10.2017/04.10.2017[/TD]
[/TR]
[TR]
[TD]C[/TD]
[TD]12[/TD]
[TD]15[/TD]
[TD]12[/TD]
[TD]12[/TD]
[TD]1[/TD]
[TD]02-Oct[/TD]
[/TR]
</tbody>[/TABLE]

 

[Peter_SSs]

Re: How To Populate Date Based On Non-Modal Count?

The following line is highlighted while clicking the Debug button:-

ModeVal = Application.Mode(Application.Index(a, i, 0))

Sounds like all those values under the date headings are Text values not Numeric values. Is that correct?

Would it be okay if we changed them to numeric values?

If so, try this version

Sub NonModalDates_v2()
  Dim a As Variant
  Dim i As Long, j As Long, ModeVal As Long, cols As Long
  Dim s As String
  
  With Range("A1", Range("A" & Rows.Count).End(xlUp)).Resize(, 121)
    .NumberFormat = "General"
    .Value = .Value
    a = .Value
  End With
  cols = UBound(a, 2)
  For i = 2 To UBound(a)
   ModeVal = Application.Mode(Application.Index(a, i, 0))
   s = ""
   For j = 2 To cols
     If a(i, j) <> ModeVal And a(i, j) <> "" Then
       s = s & "/" & Format(a(1, j), "d-mmm")
     End If
   Next j
   a(i, 1) = Mid(s, 2)
  Next i
  With Range("A1").Offset(, cols + 2).Resize(UBound(a))
    .Value = Application.Index(a, 0, 1)
    .Cells(1).Value = "Non-Modal Date(s)"
    .Columns.AutoFit
  End With
End Sub

 

[xlmaniac]

Re: How To Populate Date Based On Non-Modal Count?

Sounds like all those values under the date headings are Text values not Numeric values. Is that correct?

Would it be okay if we changed them to numeric values?

If so, try this version

Sub NonModalDates_v2()
  Dim a As Variant
  Dim i As Long, j As Long, ModeVal As Long, cols As Long
  Dim s As String
  
  With Range("A1", Range("A" & Rows.Count).End(xlUp)).Resize(, 121)
    .NumberFormat = "General"
    .Value = .Value
    a = .Value
  End With
  cols = UBound(a, 2)
  For i = 2 To UBound(a)
   ModeVal = Application.Mode(Application.Index(a, i, 0))
   s = ""
   For j = 2 To cols
     If a(i, j) <> ModeVal And a(i, j) <> "" Then
       s = s & "/" & Format(a(1, j), "d-mmm")
     End If
   Next j
   a(i, 1) = Mid(s, 2)
  Next i
  With Range("A1").Offset(, cols + 2).Resize(UBound(a))
    .Value = Application.Index(a, 0, 1)
    .Cells(1).Value = "Non-Modal Date(s)"
    .Columns.AutoFit
  End With
End Sub

Dear Sir,
Thanks for the revised codes.
My data under the date header was in numeric value only.
With this revised code also I am getting the same error like before.
Could you pls check once more?
Thanks for all the support & your effort.
Regards

 

[Peter_SSs]

Re: How To Populate Date Based On Non-Modal Count?

Do you already have the mode calculated on the worksheet for each row?
- If so, what column?

Can you confirm that all the values under the dates are whole numbers?
Are they the result of formulas or just straight numerical values?

 

[xlmaniac]

Re: How To Populate Date Based On Non-Modal Count?

Do you already have the mode calculated on the worksheet for each row?
- If so, what column?

Can you confirm that all the values under the dates are whole numbers?
Are they the result of formulas or just straight numerical values?

Dear Sir,
The mode calculation was not done before.
Now I have put the formula in Column DR(DR2=MODE(B2:DQ2) & so on).
All the values under the dates are whole numbers & just numeric values and not derivative of any formula.
Pls help.
Regards

 

[Peter_SSs]

Re: How To Populate Date Based On Non-Modal Count?

Now I have put the formula in Column DR(DR2=MODE(B2:DQ2) & so on).
All the values under the dates are whole numbers & just numeric values and not derivative of any formula.

OK, thanks. Give this version a try

Sub NonModalDates_v3()
  Dim a As Variant
  Dim i As Long, j As Long, ModeVal As Long, cols As Long
  Dim s As String
  
  a = Range("B1:B" & Range("A" & Rows.Count).End(xlUp).Row).Resize(, 121).Value
  cols = UBound(a, 2)
  For i = 2 To UBound(a)
   ModeVal = a(i, cols)
   s = ""
   For j = 1 To cols - 1
     If a(i, j) <> ModeVal And a(i, j) <> "" Then s = s & "/" & Format(a(1, j), "d-mmm")
   Next j
   a(i, 1) = Mid(s, 2)
  Next i
  With Range("A1").Offset(, cols + 2).Resize(UBound(a))
    .Value = Application.Index(a, 0, 1)
    .Cells(1).Value = "Non-Modal Date(s)"
    .Columns.AutoFit
  End With
End Sub

 

[xlmaniac]

Re: How To Populate Date Based On Non-Modal Count?

OK, thanks. Give this version a try

Sub NonModalDates_v3()
  Dim a As Variant
  Dim i As Long, j As Long, ModeVal As Long, cols As Long
  Dim s As String
  
  a = Range("B1:B" & Range("A" & Rows.Count).End(xlUp).Row).Resize(, 121).Value
  cols = UBound(a, 2)
  For i = 2 To UBound(a)
   ModeVal = a(i, cols)
   s = ""
   For j = 1 To cols - 1
     If a(i, j) <> ModeVal And a(i, j) <> "" Then s = s & "/" & Format(a(1, j), "d-mmm")
   Next j
   a(i, 1) = Mid(s, 2)
  Next i
  With Range("A1").Offset(, cols + 2).Resize(UBound(a))
    .Value = Application.Index(a, 0, 1)
    .Cells(1).Value = "Non-Modal Date(s)"
    .Columns.AutoFit
  End With
End Sub

Dear Sir,
Thanks a lot for providing me with the new codes.
I have tried using this code but it is also throwing Run Time Error(Type Mismatch)
While de-bugging,the following code is getting highlighted
Could you pls help in fixing this?

.Value = Application.Index(a, 0, 1)

 

[Peter_SSs]

Re: How To Populate Date Based On Non-Modal Count?

It is working for me, so clearly there must be something different about the file you are working with and the one I am working with. Are you able to upload a copy of the workbook (with any sensitive data removed or disguised) to a file-share site like Dropbox and provide a link here so that I can look at an actual file that is not working?

 

[xlmaniac]

Re: How To Populate Date Based On Non-Modal Count?

It is working for me, so clearly there must be something different about the file you are working with and the one I am working with. Are you able to upload a copy of the workbook (with any sensitive data removed or disguised) to a file-share site like Dropbox and provide a link here so that I can look at an actual file that is not working?

Dear Sir,
I created a new workbook and copied the codes and tried populating the result.
Now it is churning out correct result.
I think there was some mistake from my part in copying & pasting the code.
My heartfelt gratitude to you for sparing your valuable time and providing me with the solution.
Feel honored to interact & learn Excel from Gurus like you.
Proud to be a part of this forum.
Regards

 

[Peter_SSs]

Re: How To Populate Date Based On Non-Modal Count?

I'm glad you were able to resolve the problem and that the code is now doing what you want it to. Thanks for letting us know.
I agree that this is a good forum to belong to.