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?

Try this, copied across and down.

Book1
	A	B	C	D	E	F	G	H	I
1	SKU	1-Oct	2-Oct	3-Oct	4-Oct	Non Modal Frequency	Desired Result
2	A	4	3	4	4	1	2-Oct
3	B	8	8	9	10	2	3-Oct	4-Oct
4	C	12	15	12	12	1	2-Oct
Non modal Dates

Cell Formulas
Range	Formula
G2	=IFERROR(AGGREGATE(15,6,$B$1:$E$1/($B2:$E2<>MODE($B2:$E2)),COLUMNS($G2:G2)),"")

 

[Peter_SSs]

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

If you wanted the results in a single cell and you have Excel 2016 through Office 365 then try this. It is an array formula so should be entered without the {} but confirmed with Ctrl+Shift+Enter, not just Enter. If confirmed correctly, Excel will insert the {}. The formula can then be copied down.

Book1
	A	B	C	D	E	F	G
1	SKU	1-Oct	2-Oct	3-Oct	4-Oct	Non Modal Frequency	Desired Result
2	A	4	3	4	4	1	2-Oct
3	B	8	8	9	10	2	3-Oct/4-Oct
4	C	12	15	12	12	1	2-Oct
Non modal Dates (2)

Cell Formulas
Range	Formula
G2	{=TEXTJOIN("/",TRUE,IF(B2:E2<>MODE(B2:E2),TEXT(B$1:E$1,"d-mmm"),""))}
Press CTRL+SHIFT+ENTER to enter array formulas.

If you wanted this in a single cell and you don't have the TEXTJOIN function then
a) How many date columns do you really have?
b) Would a vba solution be acceptable?

 

[xlmaniac]

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

If you wanted the results in a single cell and you have Excel 2016 through Office 365 then try this. It is an array formula so should be entered without the {} but confirmed with Ctrl+Shift+Enter, not just Enter. If confirmed correctly, Excel will insert the {}. The formula can then be copied down.

	A	B	C	D	E	F	G
SKU
A
B
C

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]

[TD="align: right"]1-Oct[/TD]
[TD="align: right"]2-Oct[/TD]
[TD="align: right"]3-Oct[/TD]
[TD="align: right"]4-Oct[/TD]
[TD="align: right"]Non Modal Frequency[/TD]
[TD="align: right"]Desired Result[/TD]

[TD="align: center"]2[/TD]

[TD="align: right"]4[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]2-Oct[/TD]

[TD="align: center"]3[/TD]

[TD="align: right"]8[/TD]
[TD="align: right"]8[/TD]
[TD="align: right"]9[/TD]
[TD="align: right"]10[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]3-Oct/4-Oct[/TD]

[TD="align: center"]4[/TD]

[TD="align: right"]12[/TD]
[TD="align: right"]15[/TD]
[TD="align: right"]12[/TD]
[TD="align: right"]12[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]2-Oct[/TD]

</tbody>

Non modal Dates (2)​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Array Formulas[TABLE="width: 100%"]
<thead>[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]
[TH="width: 10px"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]G2[/TH]
[TD="align: left"]{=TEXTJOIN("/",TRUE,IF(B2:E2<>MODE(B2:E2),TEXT(B$1:E$1,"d-mmm"),""))}[/TD]
[/TR]
</tbody>[/TABLE]
Enter without the {} but confirm with Ctrl+Shift+Enter, not just Enter
If entered correctly, Excel will insert the {}[/TD]
[/TR]
</tbody>[/TABLE]




If you wanted this in a single cell and you don't have the TEXTJOIN function then
a) How many date columns do you really have?
b) Would a vba solution be acceptable?

Dear Sir,
Thanks a lot for the mesmerising solution.
I have Office 2010.
The columns will vary between 30(Min) to 120 Nos.
Pls provide a VBA Solution which can yield the result. It will be OK with me.
Regards

 

[Peter_SSs]

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

Confirming then that you want all the results in a single cell on each row, not separate cells like in post #2 ?

Also, with up to 120 columns, it would seem quite possible that there could be more than one mode. Is that possible and, if so, how should we deal with it?

For example, what should the result below be?

Book1
	A	B	C	D	E	F	G	H
1	SKU	1-Oct	2-Oct	3-Oct	4-Oct	5-Oct	6-Oct	7-Oct
2	A	4	3	3	6	5	4	2
Non modal Dates

 

[xlmaniac]

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

Confirming then that you want all the results in a single cell on each row, not separate cells like in post #2 ?

Also, with up to 120 columns, it would seem quite possible that there could be more than one mode. Is that possible and, if so, how should we deal with it?

For example, what should the result below be?

	A	B	C	D	E	F	G	H
SKU
A

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]

[TD="align: right"]1-Oct[/TD]
[TD="align: right"]2-Oct[/TD]
[TD="align: right"]3-Oct[/TD]
[TD="align: right"]4-Oct[/TD]
[TD="align: right"]5-Oct[/TD]
[TD="align: right"]6-Oct[/TD]
[TD="align: right"]7-Oct[/TD]

[TD="align: center"]2[/TD]

[TD="align: right"]4[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]6[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]2[/TD]

</tbody>

Non modal Dates​

Dear Sir,
Thanks a lot for your reply.
In a span of 4 months, there could be maximum 4 instances of deviation(against a particular SKU) since we have put a validation in system so that once 4th occurrences are made system will not allow to breach.
Due to this, there will be no effect on the modal number.
The code should be able to populate maximum 4 dates in a cell across 120 columns.
Pls help.
Regards

 

[Peter_SSs]

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

This assumes
- the date columns are B:DQ (120 columns) and puts the results in column DT.
- values to be checked against the mode are whole numbers like the sample data.

Test in a copy of your workbook.

Sub NonModaldates()
  Dim a As Variant
  Dim i As Long, j As Long, ModeVal As Long, cols As Long
  Dim s As String
  
  a = Range("A1", Range("A" & Rows.Count).End(xlUp)).Resize(, 121).Value
  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?

This assumes
- the date columns are B:DQ (120 columns) and puts the results in column DT.
- values to be checked against the mode are whole numbers like the sample data.

Test in a copy of your workbook.

Sub NonModaldates()
  Dim a As Variant
  Dim i As Long, j As Long, ModeVal As Long, cols As Long
  Dim s As String
  
  a = Range("A1", Range("A" & Rows.Count).End(xlUp)).Resize(, 121).Value
  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,
My sincere gratitude for helping me out with the VBA solution.
I have incorporated the code and have tried to run the same.
But it is throwing an error:-"Run Time Error 13" Type Mismatch.
Pls help in fixing the same.
Regards

 

[Peter_SSs]

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

But it is throwing an error:-"Run Time Error 13" Type Mismatch.

What line of code is causing the error (Click Debug)?

 

[xlmaniac]

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

Dear Sir,
My sincere gratitude for helping me out with the VBA solution.
I have incorporated the code and have tried to run the same.
But it is throwing an error:-"Run Time Error 13" Type Mismatch.
Pls help in fixing the same.
Regards

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

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

Regards