Sorting Results Based on Beams Name & Getting MAX/MIN Values

D

DEADRABBIT

New Member
Joined
Jan 4, 2019
Messages
6
I pulled the results from analysis software

Now I need to sort the results like that for every beam name like (GB58,GB59,GB60.....) at Column "B" I want to show only the maximum and minimum value at Column "D"

I want to delete all values between the max and min for every beam name (GB01,GB02,GB03,.....GB150)
And I want to keep just the first and the last values (max and min).

The formula that I can't make is "for every beam's name at column B show the max and min values from column D only and delete or hide the unneeded results in between"

Right now I am deleting manually the unneeded results between the max and min for every beam and it's very time consuming.

6PDq6fH


Will appreciate any feedback

Thanks & Best regards

Mo.
 

Excel Facts

Add Bullets to Range
Select range. Press Ctrl+1. On Number tab, choose Custom. Type Alt+7 then space then @ sign (using 7 on numeric keypad)
Sort by date Sort by votes
something like this?

[Table="width:, class:head"]
[tr=bgcolor:#FFFFFF][td=bgcolor:#70AD47]Column2[/td][td=bgcolor:#70AD47]Min[/td][td=bgcolor:#70AD47]Max[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E2EFDA]GB58[/td][td=bgcolor:#E2EFDA]
-6.3​
[/td][td=bgcolor:#E2EFDA]
1.3​
[/td][/tr]

[tr=bgcolor:#FFFFFF][td]GB59[/td][td]
10.6​
[/td][td]
18.1​
[/td][/tr]
[/table]
 
Upvote 0
sandy666 said:
something like this?

[TABLE="class: head"]
<tbody>[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] "]
[TD="bgcolor: #70AD47"][COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]Column2[/COLOR][/TD]
[TD="bgcolor: #70AD47"][COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]Min[/COLOR][/TD]
[TD="bgcolor: #70AD47"][COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]Max[/COLOR][/TD]
[/TR]
[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] "]
[TD="bgcolor: #E2EFDA"]GB58[/TD]
[TD="bgcolor: #E2EFDA"]
-6.3​
[/TD]
[TD="bgcolor: #E2EFDA"]
1.3​
[/TD]
[/TR]
[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] "]
[TD]GB59[/TD]
[TD]
10.6​
[/TD]
[TD]
18.1​
[/TD]
[/TR]
</tbody>[/TABLE]
Click to expand...

This Will be great.
 
Upvote 0
so you can use PowerQuery with M code:

Code: 
[SIZE=1]let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", type number}, {"Column2", type text}, {"Column3", type text}, {"Column4", type number}}),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"Column2"}, {{"Min", each List.Min([Column4]), type number}, {"Max", each List.Max([Column4]), type number}})
in
    #"Grouped Rows"[/SIZE]
 
Upvote 0
sandy666 said:
so you can use PowerQuery with M code:

Code: 
[SIZE=1]let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", type number}, {"Column2", type text}, {"Column3", type text}, {"Column4", type number}}),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"Column2"}, {{"Min", each List.Min([Column4]), type number}, {"Max", each List.Max([Column4]), type number}})
in
    #"Grouped Rows"[/SIZE]
Click to expand...

Thank you very much sandy.
I am newbie with this PowerQuery thing but I am downloading it right now and will watch some videos about how to use it.

Thanks and best regards,
Mo.
 
Upvote 0
in this case this is simple

group.jpg


btw. don't quote whole post just use reply

[Table="width:, class:head"]
[tr=bgcolor:#FFFFFF][td=bgcolor:#5B9BD5]Column1[/td][td=bgcolor:#5B9BD5]Column2[/td][td=bgcolor:#5B9BD5]Column3[/td][td=bgcolor:#5B9BD5]Column4[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#DDEBF7]
0.925​
[/td][td=bgcolor:#DDEBF7]GB58[/td][td=bgcolor:#DDEBF7]Envelope Ultimate Max[/td][td=bgcolor:#DDEBF7]
1.3​
[/td][/tr]

[tr=bgcolor:#FFFFFF][td]
0.925​
[/td][td]GB58[/td][td]Envelope Ultimate Max[/td][td]
0.8​
[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#DDEBF7]
0.925​
[/td][td=bgcolor:#DDEBF7]GB58[/td][td=bgcolor:#DDEBF7]Envelope Ultimate Min[/td][td=bgcolor:#DDEBF7]
0.7​
[/td][/tr]

[tr=bgcolor:#FFFFFF][td]
0.925​
[/td][td]GB58[/td][td]Envelope Ultimate Min[/td][td]
0.6​
[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#DDEBF7]
0.925​
[/td][td=bgcolor:#DDEBF7]GB58[/td][td=bgcolor:#DDEBF7]Envelope Ultimate Max[/td][td=bgcolor:#DDEBF7]
0.5​
[/td][/tr]

[tr=bgcolor:#FFFFFF][td]
0.925​
[/td][td]GB58[/td][td]Envelope Ultimate Max[/td][td]
0.4​
[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#DDEBF7]
0.925​
[/td][td=bgcolor:#DDEBF7]GB58[/td][td=bgcolor:#DDEBF7]Envelope Ultimate Max[/td][td=bgcolor:#DDEBF7]
0.2​
[/td][/tr]

[tr=bgcolor:#FFFFFF][td]
0.925​
[/td][td]GB58[/td][td]Envelope Ultimate Min[/td][td]
0.1​
[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#DDEBF7]
0.925​
[/td][td=bgcolor:#DDEBF7]GB58[/td][td=bgcolor:#DDEBF7]Envelope Ultimate Min[/td][td=bgcolor:#DDEBF7]
-0.1​
[/td][/tr]

[tr=bgcolor:#FFFFFF][td]
0.925​
[/td][td]GB58[/td][td]Envelope Ultimate Min[/td][td]
-0.2​
[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#DDEBF7]
0.925​
[/td][td=bgcolor:#DDEBF7]GB58[/td][td=bgcolor:#DDEBF7]Envelope Ultimate Min[/td][td=bgcolor:#DDEBF7]
-0.4​
[/td][/tr]

[tr=bgcolor:#FFFFFF][td]
0.925​
[/td][td]GB58[/td][td]Envelope Ultimate Max[/td][td]
-1.6​
[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#DDEBF7]
0.925​
[/td][td=bgcolor:#DDEBF7]GB58[/td][td=bgcolor:#DDEBF7]Envelope Ultimate Max[/td][td=bgcolor:#DDEBF7]
-1.7​
[/td][/tr]

[tr=bgcolor:#FFFFFF][td]
0.925​
[/td][td]GB58[/td][td]Envelope Ultimate Max[/td][td]
-1.9​
[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#DDEBF7]
0.925​
[/td][td=bgcolor:#DDEBF7]GB58[/td][td=bgcolor:#DDEBF7]Envelope Ultimate Min[/td][td=bgcolor:#DDEBF7]
-3.2​
[/td][/tr]

[tr=bgcolor:#FFFFFF][td]
0.925​
[/td][td]GB58[/td][td]Envelope Ultimate Min[/td][td]
-3.3​
[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#DDEBF7]
0.925​
[/td][td=bgcolor:#DDEBF7]GB58[/td][td=bgcolor:#DDEBF7]Envelope ultimate Max[/td][td=bgcolor:#DDEBF7]
-3.5​
[/td][/tr]

[tr=bgcolor:#FFFFFF][td]
0.925​
[/td][td]GB58[/td][td]Envelope ultimate Min[/td][td]
-3.5​
[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#DDEBF7]
0.925​
[/td][td=bgcolor:#DDEBF7]GB58[/td][td=bgcolor:#DDEBF7]Envelope ultimate Min[/td][td=bgcolor:#DDEBF7]
-6.3​
[/td][/tr]

[tr=bgcolor:#FFFFFF][td]
0.925​
[/td][td]GB59[/td][td]Envelope Ultimate Max[/td][td]
18.1​
[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#DDEBF7]
0.925​
[/td][td=bgcolor:#DDEBF7]GB59[/td][td=bgcolor:#DDEBF7]Envelope Ultimate Max[/td][td=bgcolor:#DDEBF7]
18.1​
[/td][/tr]

[tr=bgcolor:#FFFFFF][td]
0.925​
[/td][td]GB59[/td][td]Envelope Ultimate Max[/td][td]
18​
[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#DDEBF7]
0.925​
[/td][td=bgcolor:#DDEBF7]GB59[/td][td=bgcolor:#DDEBF7]Envelope Ultimate Max[/td][td=bgcolor:#DDEBF7]
18​
[/td][/tr]

[tr=bgcolor:#FFFFFF][td]
0.925​
[/td][td]GB59[/td][td]Envelope Ultimate Max[/td][td]
17.5​
[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#DDEBF7]
0.925​
[/td][td=bgcolor:#DDEBF7]GB59[/td][td=bgcolor:#DDEBF7]Envelope Ultimate Max[/td][td=bgcolor:#DDEBF7]
17.5​
[/td][/tr]

[tr=bgcolor:#FFFFFF][td]
0.925​
[/td][td]GB59[/td][td]Envelope Ultimate Max[/td][td]
17.2​
[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#DDEBF7]
0.925​
[/td][td=bgcolor:#DDEBF7]GB59[/td][td=bgcolor:#DDEBF7]Envelope Ultimate Max[/td][td=bgcolor:#DDEBF7]
17.2​
[/td][/tr]

[tr=bgcolor:#FFFFFF][td]
0.925​
[/td][td]GB59[/td][td]Envelope Ultimate Max[/td][td]
16.2​
[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#DDEBF7]
0.925​
[/td][td=bgcolor:#DDEBF7]GB59[/td][td=bgcolor:#DDEBF7]Envelope Ultimate Max[/td][td=bgcolor:#DDEBF7]
16.2​
[/td][/tr]

[tr=bgcolor:#FFFFFF][td]
0.925​
[/td][td]GB59[/td][td]Envelope Ultimate Max[/td][td]
15.7​
[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#DDEBF7]
0.925​
[/td][td=bgcolor:#DDEBF7]GB59[/td][td=bgcolor:#DDEBF7]Envelope Ultimate Max[/td][td=bgcolor:#DDEBF7]
15.7​
[/td][/tr]

[tr=bgcolor:#FFFFFF][td]
0.925​
[/td][td]GB59[/td][td]Envelope Ultimate Max[/td][td]
14.2​
[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#DDEBF7]
0.925​
[/td][td=bgcolor:#DDEBF7]GB59[/td][td=bgcolor:#DDEBF7]Envelope Ultimate Max[/td][td=bgcolor:#DDEBF7]
14.2​
[/td][/tr]

[tr=bgcolor:#FFFFFF][td]
0.925​
[/td][td]GB59[/td][td]Envelope Ultimate Max[/td][td]
13.5​
[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#DDEBF7]
0.925​
[/td][td=bgcolor:#DDEBF7]GB59[/td][td=bgcolor:#DDEBF7]Envelope ultimate Max[/td][td=bgcolor:#DDEBF7]
13.4​
[/td][/tr]

[tr=bgcolor:#FFFFFF][td]
0.925​
[/td][td]GB59[/td][td]Envelope Ultimate Max[/td][td]
11.4​
[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#DDEBF7]
0.925​
[/td][td=bgcolor:#DDEBF7]GB59[/td][td=bgcolor:#DDEBF7]Envelope ultimate Max[/td][td=bgcolor:#DDEBF7]
11.4​
[/td][/tr]

[tr=bgcolor:#FFFFFF][td]
0.925​
[/td][td]GB59[/td][td]Envelope Ultimate Min[/td][td]
10.7​
[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#DDEBF7]
0.925​
[/td][td=bgcolor:#DDEBF7]GB59[/td][td=bgcolor:#DDEBF7]Envelope Ultimate Min[/td][td=bgcolor:#DDEBF7]
10.7​
[/td][/tr]

[tr=bgcolor:#FFFFFF][td]
0.925​
[/td][td]GB59[/td][td]Envelope Ultimate Max[/td][td]
10.6​
[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#DDEBF7]
0.925​
[/td][td=bgcolor:#DDEBF7]GB59[/td][td=bgcolor:#DDEBF7]Envelope Ultimate Min[/td][td=bgcolor:#DDEBF7]
10.6​
[/td][/tr]
[/table]
 
Last edited:
Upvote 0
You must log in or register to reply here.

Similar threads

S
  • Question Question
Find min or max value in between two numbers
Replies
4
Views
436
Fluff
Fluff
B
  • Question Question
Validating if an alphanumeric value falls between defined min and max
Replies
8
Views
555
Micron
M
R
  • Question Question
How can I get the value of a specific column in a range.
Replies
4
Views
434
Scott Huish
Scott Huish
K
  • Solved
help with formatting a cell (for TIME, custom format) that retrieves its value from another cell...
Replies
2
Views
348
kbishop94
K
G
  • Question Question
Improve calculation speed for this worksheet with INDIRECT/FILTER functions
Replies
0
Views
341
GeneBF
G

Forum statistics

Threads
1,223,705
Messages
6,173,989
Members
452,541
Latest member
haasro02

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top