Skip to main content
Support is Offline
Today is our off day. We are taking some rest and will come back stronger tomorrow
Official support hours
Monday To Friday
From 09:00 To 17:30
  Monday, 16 May 2022
  12 Replies
  7.5K Visits
0
Votes
Undo
Hi anyone
I need to use min function except zero in additional format and highlight the value, but I can not able:(
Please help me to solve my problem.
Thanks.
1 year ago
·
#2720
1
Votes
Undo
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
1 year ago
·
#2721
0
Votes
Undo
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:(
1 year ago
·
#2731
0
Votes
Undo
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; ...
1 year ago
·
#2732
0
Votes
Undo
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.
1 year ago
·
#2733
0
Votes
Undo

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?
1 year ago
·
#2734
0
Votes
Undo
Hi dear Amanda:)
Exactly right:):)
1 year ago
·
#2741
0
Votes
Undo
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
1 year ago
·
#2743
0
Votes
Undo
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 :D
Hope to have receive more code to do my next request as soon as possible.
Thanks again and good luck.
1 year ago
·
#2744
0
Votes
Undo

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

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 :)
1 year ago
·
#2745
0
Votes
Undo
OK. and what if some sections has more or less that 7 rows data?!;) ;)
1 year ago
·
#2746
0
Votes
Undo
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 :o
If not, then....
  • Page :
  • 1
There are no replies made for this post yet.