conditional format with formula

Hi there,

Please refer to the article to find your answer: https://www.extendoffice.com/documents/excel/4033-excel-exclude-zero-from-min.html

About the request - "highlight the value", what value do you want to highlight?

Amanda

Hi Dear Amanda
Tanks for reading my request and many thanks for your kindly reply's
I have a table with more than 5000 rows of data…
In this table each 7 to 11 rows have a single number that merged cells in column A!
Each parts with 1 row number containing data of several vendors about one item that each one has their own price and what I need is to have my date in each section to be sorted on lower price of any vendors on top of the row section😊
As you see, I have 2 requests:
1: sorting data in each part of my data in their section on lowest price,
2: highlight the lowest price in first row
I did solve my second request yesterday, but I didn't find any answer for my first one.
I will be thankful if you could solve my problem:)
Unfortunately i could not upload image of my table:(

here is Partial section picture of my table
https://drive.google.com/file/d/1MqdJo_Xa7nilbT8YMZiJIncNfApyuOd7/view?usp=sharing

Hi there,

Thanks for the picture, that helps! However, we don't understand the language in the picture.

So, could you plesae point out if each section you mentioned are the ranges:
P12:U12; P19:U19; ...
OR
P12:P18; P19:P25; ...

Hi and many thanks again for your attention dear Amanda.
First of all, the language i used in this table is FARCES.
In addition about your second question,
If I understand what you mean, I want to sort all of data in range D12:AH18 based on minimum value in cell Q12:Q18 that must come out in top of the row in section No.1 and so on the same in another sections such as No.2 to section No.712 and also I need after this sort when the minimum number of cell Q comes out on top of the row section, it shown by yellow color in background also the vendor name in cell "I" shown the same yellow background too.
I hope I have explained my purpose and request correctly.
Also, If necessary, tell me to post more pictures.
Thanks in advance for your reply.

Quoted from JavidP on Wednesday, 18 May 2022

I want to sort all of data in range D12:AH18 based on minimum value in cell Q12:Q18 that must come out in top of the row in section No.1 and so on the same in another sections such as No.2 to section No.712 and also I need after this sort when the minimum number of cell Q comes out on top of the row section, it shown by yellow color in background also the vendor name in cell "I" shown the same yellow background too.

So for section 1, you want to sort the row 12-18 based on the values in Q12:Q18 from smallest to largest;
for section 2, ....

And the minimum number of cell Q and vendor name in cell "I" they are in the same row. Did I understand it right?

Hi dear Amanda:)
Exactly right:)

Hi there,

We've come up with a VBA code to help you sort rows section by section, please see below:

Sub ExtendOffice_Sort()

Dim xExtendRg, xOfficeSRgC As Range

Dim xRg As Range

Dim xRangeAddress As String

Dim xNum, xCSNum, xCENum, xRSNum, xRSNum2, xRENum As Integer

Dim xRCount As Integer

Dim xBol, xBolWS As Boolean

Dim xStr1, xStr2 As String

Dim xWSh As Worksheet

Dim xSortColumn As Integer

On Error Resume Next

Set xExtendRg = Application.InputBox("Please select the range with data to be sorted:", "ExtendOffice - Kutools for Excel", , , , , , 8)

If xExtendRg Is Nothing Then Exit Sub

Set xOfficeSRgC = Application.InputBox("Please select the column with values to be sorted from smallest to largest:", "ExtendOffice - Kutools for Excel", , , , , , 8)

If xOfficeSRgC Is Nothing Then Exit Sub

xNum = Application.InputBox("Please enter a number of rows to be sorted in a section:", "ExtendOffice - Kutools for Excel", , , , , , 1)



Set xRg = xExtendRg



Set xWSh = xRg.Worksheet



xWSh.Activate

xSortColumn = xOfficeSRgC.Column

xRCount = xRg.Rows.Count

xCSNum = xRg.Column

xCENum = xCSNum + xRg.Columns.Count - 1

xRSNum = xRg.Row

xRENum = xRSNum + xRCount - 1

xRSNum2 = xRSNum

xRSNum = (xRSNum + xNum) - 1

xBol = True

xBolWS = Application.ScreenUpdating

Application.ScreenUpdating = False

Do While xBol

xStr1 = xWSh.Cells(xRSNum2, xCSNum).Address & ":" & xWSh.Cells(xRSNum, xCENum).Address

xStr2 = xWSh.Cells(xRSNum2, xSortColumn).Address & ":" & xWSh.Cells(xRSNum, xSortColumn).Address

'Debug.Print xStr1 & " - " & xStr2



    xWSh.Sort.SortFields.Clear

    xWSh.Sort.SortFields.Add Key:=Range(xStr2) _

        , SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal

    With xWSh.Sort

        .SetRange Range(xStr1)

        .Header = xlGuess

        .MatchCase = False

        .Orientation = xlTopToBottom

        .SortMethod = xlPinYin

        .Apply

    End With

    xWSh.Sort.SortFields.Clear



If (xRSNum + xNum) >= xRENum Then

    If xRSNum = xRENum Then

        xBol = False

    Else

        xRSNum2 = xRSNum + 1

        xRSNum = xRENum

    End If

Else

    xRSNum2 = xRSNum + 1

    xRSNum = (xRSNum + xNum)

End If

Loop



Application.ScreenUpdating = xBolWS



End Sub

And sorry that the code above does not highlight the minimum number of cell Q and vendor name in cell "I", since we've been busy recently working on our products.
Hope you understand.

Amanda

Hi again dear Amanda:)
Thank you.
I tested your code and of course it works well.
now i have to run it about 712 times on my table and select a piece of data each time
Hope to have receive more code to do my next request as soon as possible.
Thanks again and good luck.

Quoted from JavidP on Friday, 20 May 2022

now i have to run it about 712 times on my table and select a piece of data each time

You don't have to run it 712 times.

When a dialog pop up saying
"Please select the range with data to be sorted" > you just select all the data that need to be sorted, across section 1 to 712.
"Please select the column with values to be sorted from smallest to largest" > Select column Q in your case.
"Please enter a number of rows to be sorted in a section:" > Enter "7" in your case

OK. and what if some sections has more or less that 7 rows data?!

Quoted from JavidP on Tuesday, 17 May 2022

Hi Dear Amanda
Tanks for reading my request and many thanks for your kindly reply's
I have a table with more than 5000 rows of data…
In this table each 7 to 11 rows have a single number that merged cells in column A!
Each parts with 1 row number containing data of several vendors about one item that each one has their own price and what I need is to have my date in each section to be sorted on lower price of any vendors on top of the row section😊
As you see, I have 2 requests:
1: sorting data in each part of my data in their section on lowest price,
2: highlight the lowest price in first row
I did solve my second request yesterday, but I didn't find any answer for my first one.
I will be thankful if you could solve my problem:)
Unfortunately i could not upload image of my table:(

I thought all your section as the same number of rows
If not, then....