Is this possible in Excel??

ntbok

New Member
Joined
Nov 3, 2018
Messages
1
Hello all,

I have an Excel problem that I can't figure out. I think it involves a few steps, and actually I'm not sure if Excel is able to do it... hopefully someone on here can help me, or at least tell me if it's possible!

I have a sheet with data, below is a smaller version:

Hole IDDepth fromDepth toAuAs
2018PJAC00104175
2018PJAC00148510
2018PJAC0018121220
2018PJAC001121666350
2018PJAC001162096100
2018PJAC002042050
2018PJAC00248375
2018PJAC002812810
2018PJAC00212161215
2018PJAC0021620125
2018PJAC00304040
2018PJAC00348050
2018PJAC003812510
2018PJAC003121610
2018PJAC0031620875
2018PJAC0032024550
2018PJAC0032428650
2018PJAC00328321640
2018PJAC004041380
2018PJAC0044822750
2018PJAC00481214510
2018PJAC005046310
2018PJAC00548120
2018PJAC005812110
2018PJAC0051216250
2018PJAC005162062165
2018PJAC005202421180
2018PJAC00524284750
2018PJAC0052832640
2018PJAC0053236560
2018PJAC0053640910

<tbody>
</tbody>


I need to find the Max Au value in each hole - for example the max Au for 2018PJAC001 will be 663. I then need to find the Depth From value that corresponds to the Max Au value - in this case the answer is 12.

Things to note:
- The real sheet has over 4000 rows of data, and there are 331 holes, so there could be values in the Au column that are the same (I don't think a MATCH formula will work....)
-I've used a pivot table to find the max value in each hole, but can't return the corresponding Depth From value that the answer came from...

This is the first step of my problem. I will post the next part if this part can be solved!
Fingers crossed it's possible :confused::confused:

Thanks :)

Ntbok
 

Excel Facts

Quick Sum
Select a range of cells. The total appears in bottom right of Excel screen. Right-click total to add Max, Min, Count, Average.
Hi,

With the table you posted in A1:E32 (with headers in row 1) and your choice of Hole ID (e.g. 2018PJAC001) in H2, this formula in I2:

=LOOKUP(1,0/FREQUENCY(0,1/(1+(A$2:A$32=H2)*D$2:D$32)),B$2:B$32)

Copy down to give similar results for other Hole IDs in H3, H4, etc.

Note that, if two or more Au entries share the maximum value for a given Hole ID, the Depth from corresponding to that occurring first in the table will be the preferred return.

Regards
 
Upvote 0
Hi

Pivot solution:
Hole ID and Au in rows area.
Au and Depth from in values area.
Change function for Au to Max.
Select a cell in the Au column in the pivot table.
Right click -> Top 10 ...
Top - 1 - Items - Max of Au

Arbeitsblatt mit dem Namen 'Tabelle2'
ABCD
1Hole IDAuMax. of AuSum of Depth from
22018PJAC00166366312
32018PJAC00220200
42018PJAC003878716
52018PJAC0042272274
62018PJAC00563630

<colgroup><col style="width: 28ptpx"><col width="81,75pt"><col width="30pt"><col width="63pt"><col width="117pt"></colgroup><tbody>
</tbody>
Verwendete Systemkomponenten: [Windows (32-bit) NT 6.01] MS Excel 2016
Diese Tabelle wurde mit Tab2Html (v2.6.0) erstellt. ©Gerd alias Bamberg

<tbody>
</tbody>


Formula solution:
Arbeitsblatt mit dem Namen 'Tabelle1'
HIJ
1Hole IDAuDepth from
22018PJAC00166312
32018PJAC002200
42018PJAC0038716
52018PJAC0042274
62018PJAC005630

<colgroup><col style="width: 28ptpx"><col width="64,5pt"><col width="60,75pt"><col width="60,75pt"></colgroup><tbody>
</tbody>

ZelleFormel
I2=MAXIFS(tbl_Data[Au],tbl_Data[Hole ID],H2)
J2=MAXIFS(tbl_Data[Depth from],tbl_Data[Hole ID],H2,tbl_Data[Au],I2)

<colgroup><col style="width: 40ptpx"><col></colgroup><tbody>
</tbody>
Verwendete Systemkomponenten: [Windows (32-bit) NT 6.01] MS Excel 2016
Diese Tabelle wurde mit Tab2Html (v2.6.0) erstellt. ©Gerd alias Bamberg

<tbody>
</tbody>
 
Upvote 0
another way

Hole IDDepth fromAu
2018PJAC001
12​
663​
2018PJAC002
0​
20​
2018PJAC003
16​
87​
2018PJAC004
4​
227​
2018PJAC005
0​
63​

With PowerQuery:

- Duplicate QueryTable
then
Code:
[SIZE=1]let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Grouped Rows" = Table.Group(Source, {"Hole ID"}, {{"Max", each List.Max([Au]), type number}})
in
    #"Grouped Rows"[/SIZE]

next merge these two tables

Code:
[SIZE=1]let
    Source = Table.NestedJoin(Table1,{"Au"},#"Table1 (2)",{"Max"},"Table1 (2)",JoinKind.RightOuter),
    #"Removed Columns" = Table.RemoveColumns(Source,{"Table1 (2)"}),
    #"Removed Other Columns" = Table.SelectColumns(#"Removed Columns",{"Hole ID", "Depth from", "Au"})
in
    #"Removed Other Columns"[/SIZE]
 
Upvote 0

Excel 2010
GH
2Hole IDAu
32018PJAC001663
42018PJAC00220
52018PJAC00387
62018PJAC004227
72018PJAC00563
8
1d
Cell Formulas
RangeFormula
H3=AGGREGATE(14,6,D$2:D$32/(A$2:A$32=G3),1)
 
Last edited:
Upvote 0
Another option


Excel 2013/2016
GH
22018PJAC00112
32018PJAC0020
42018PJAC00316
52018PJAC0044
62018PJAC0050
Sheet3
Cell Formulas
RangeFormula
G2{=INDEX(A$2:A$32,MATCH(0,COUNTIF(G$1:G1,A$2:A$32),0))}
H2{=INDEX(A$2:E$32,MATCH(MAX(IF(A$2:A$32=G2,D$2:D$32)),D$2:D$32,0),2)}
Press CTRL+SHIFT+ENTER to enter array formulas.
 
Upvote 0
If you are willing to consider a VBA macro...
Code:
[table="width: 500"]
[tr]
	[td]Sub MaxAUs()
  Dim R As Long, X As Long, Data As Variant, Dict1 As Object, Dict2 As Object
  Data = Range("A1").CurrentRegion
  Set Dict1 = CreateObject("Scripting.Dictionary")
  Set Dict2 = CreateObject("Scripting.Dictionary")
  For R = 2 To UBound(Data)
    If Data(R, 4) > Dict1.Item(Data(R, 1)) Then
      Dict1.Item(Data(R, 1)) = Data(R, 4)
      Dict2.Item(Data(R, 1)) = Data(R, 2)
    End If
  Next
  Range("H2").Resize(Dict1.Count) = Application.Transpose(Dict1.Keys)
  Range("I2").Resize(Dict1.Count) = Application.Transpose(Dict1.Items)
  Range("J2").Resize(Dict2.Count) = Application.Transpose(Dict2.Items)
End Sub[/td]
[/tr]
[/table]
 
Last edited:
Upvote 0
This is the most comprehensively answered question I've seen in a while.
 
Upvote 0
Well, I could imagine at least two more solutions: DAX measure in the pivot table and the new dynamic array function FILTER() - currently only available on the Insider Channel.
 
Upvote 0

Forum statistics

Threads
1,213,531
Messages
6,114,167
Members
448,554
Latest member
Gleisner2

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