CODEWITHSUNDEEP
excelvba
coder
coder

Reputation: 23

how to get a min and max value that is equal to a specific cell value?

I tried a formula to equal the ranges to a cell value. But the output is not as I expected. I think this should be in a for each loop not in a formula.

Does anyone have any ideas?

Here is the data:

Col:A     Col:B

1001       abc
1002       abc
1003       abc
1004       abc
1005       abc
1006       xyz
1007       xyz
1008       xyz
1009       xyz
1010       xyz

Here is the formula that I tried:

"=CONCATENATE(MIN(IF(B2:B250=B2,A2:A250)),"-",MAX(IF(B2:B250=B2,A2:A250)))"

and the output is: 


Col: A         Col:B
1001-1010       abc
1002-1010       abc
1003-1010       abc
1004-1010       abc
1005-1010       abc
1006-1010       xyz
1007-1010       xyz
1008-1010       xyz
1009-1010       xyz
1010-1010       xyz

The output that i want to achieve is:

Col: A     Column b
1001-1005   abc
1006-1010   xyz

Upvotes: 1

Views: 191

Answers (3)

G.M
G.M

Reputation: 365

I created a Sub for you that might help you. Its maybe not perfect but with a little bit of tinkering it should work for what you want to achieve.

Please accept the answer and vote for it if it helps you.

Option Explicit

Public Sub Grouping()

Dim LastRow As Integer
Dim StartRow As Integer
Dim CurrentValue As String
Dim StartValue As String

Dim i As Integer
Dim k As Integer   
Dim Outputstring As String

Dim LengthCounter As Integer

'2 -> insert Number of column B
LastRow = Worksheets("Sheet 1").Cells(Rows.Count, 2).End(xlUp).Row

StartValue = Worksheets("Sheet 1").Cells(1, 2).Value
StartRow = 0
LengthCounter = 0
k = 0

For i = 1 To LastRow
CurrentValue = Worksheets("Sheet 1").Cells(i, 2).Value

    If CurrentValue = StartValue Then
        LengthCounter = LengthCounter + 1
    Else
        Outputstring = Worksheets("Sheet 1").Cells(StartRow, 1).Value & " - " & Worksheets("Sheet 1").Cells(i - 1, 1).Value & "  " & Worksheets("Sheet 1").Cells(i - 1, 2).Value
        Worksheets("Sheet 1").Cells(k, 3).Value = Outputstring
        k= k +1
        StartRow = i
        StartValue = Worksheets("Sheet 1").Cells(i, 2).Value
    End If
Next i

End Sub

Upvotes: 0

JvdV
JvdV

Reputation: 75840

In case column D is free to use (choose whichever column really if you want to) you could try:

Input:

enter image description here

Code:

Sub Test2()

Dim arr As Variant, x As Long, mx As Long, mn As Long, col As String
With ThisWorkbook.Sheets("Sheet1") 'Change accordingly
    lr = .Cells(.Rows.Count, "A").End(xlUp).Row
    .Range("B1:B" & lr).AdvancedFilter Action:=xlFilterCopy, CopyToRange:=.Range("D1"), Unique:=True
    arr = .Range("D2:D" & .Cells(.Rows.Count, "D").End(xlUp).Row).Value
    For x = LBound(arr) To UBound(arr)
        mn = .Evaluate("=MAX(IF(B2:B" & lr & "=""" & arr(x, 1) & """,A2:A" & lr & "))")
        mx = .Evaluate("=MIN(IF(B2:B" & lr & "=""" & arr(x, 1) & """,A2:A" & lr & "))")
        col = Split(.Cells(1, x).Address, "$")(1)
        Debug.Print "Column " & col & " = " & mn & "-" & mx & " " & arr(x, 1)
    Next x
    .Range("D1:D" & .Cells(.Rows.Count, "D").End(xlUp).Row).Clear
End With
End Sub

Output:

enter image description here

On a very large dataset, I would suggest to use a Dictionary instead maybe (array formulas are not fast). But for a rather small dataset, I guess this is a fast way.

Swap the line: 

Debug.Print "Column " & col & " = " & mn & "-" & mx & " " & arr(x, 1)

For:

.Cells(x + 1, 3).Value = "Column " & col & " = " & mn & "-" & mx & " " & arr(x, 1)

But you might want to play around with where and how you want to output your results.

Upvotes: 1

mits
mits

Reputation: 926

For this method to work you need to add headers in the line above data.

Put this formula in a cell:

= "Column a = " & DMIN(A1:B11;A1;B1:B2) & " - " DMAX(A1:B11;A1;B1:B2)

syntax: DMIN( "the_whole_range_of_data_including_headers";" cell_with_header_in_column_with_search_values";" cell_with_header_and_cell_beneath_it_in_column_with_match_values")

Upvotes: 0

Related Questions