Greenies08
New Member
- Joined
- Apr 4, 2011
- Messages
- 22
Hi there
I have some data that I want to pull out the 'max' and by 'who' within a defined month.
I've managed the max formula ok but struggling to build the month part into my index/match array.
Any help would be truly appreciated.
[TABLE="width: 409"]
<colgroup><col width="75" style="width: 56pt; mso-width-source: userset; mso-width-alt: 2742;"><colgroup><col width="189" style="width: 142pt; mso-width-source: userset; mso-width-alt: 6912;"><colgroup><col width="64" style="width: 48pt;" span="2"><colgroup><col width="153" style="width: 115pt; mso-width-source: userset; mso-width-alt: 5595;"><tbody>[TR]
[TD="width: 75, bgcolor: transparent"]03/01/2019[/TD]
[TD="width: 189, bgcolor: transparent"]A Smith[/TD]
[TD="width: 64, bgcolor: transparent, align: right"] 1400
[/TD]
[TD="width: 64, bgcolor: transparent"] 5005
[/TD]
[TD="width: 153, bgcolor: transparent"]Shrub[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]05/02/2019[/TD]
[TD="bgcolor: transparent"]C David[/TD]
[TD="bgcolor: transparent, align: right"]1400[/TD]
[TD="bgcolor: transparent"] 5005
[/TD]
[TD="bgcolor: transparent"]Shrub[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]08/01/2019[/TD]
[TD="bgcolor: transparent"]F Carol[/TD]
[TD="bgcolor: transparent, align: right"]1000
[/TD]
[TD="bgcolor: transparent"] 5000
[/TD]
[TD="bgcolor: transparent"]Tree[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]14/01/2019[/TD]
[TD="bgcolor: transparent"]G Williams[/TD]
[TD="bgcolor: transparent, align: right"]1400[/TD]
[TD="bgcolor: transparent"] 5000
[/TD]
[TD="bgcolor: transparent"]Tree[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]18/01/2019[/TD]
[TD="bgcolor: transparent"]B Smith[/TD]
[TD="bgcolor: transparent, align: right"]750[/TD]
[TD="bgcolor: transparent"] 5000
[/TD]
[TD="bgcolor: transparent"]Tree
[/TD]
[/TR]
</tbody>[/TABLE]
[TABLE="width: 674"]
<colgroup><col width="75" style="width: 56pt; mso-width-source: userset; mso-width-alt: 2742;"><colgroup><col width="189" style="width: 142pt; mso-width-source: userset; mso-width-alt: 6912;"><colgroup><col width="635" style="width: 476pt; mso-width-source: userset; mso-width-alt: 23222;"><tbody>[TR]
[TD="width: 75, bgcolor: transparent, align: right"]01/01/2019[/TD]
[TD="width: 189, bgcolor: transparent"][/TD]
[TD="width: 635, bgcolor: transparent"]Arrays[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]Max Shrub[/TD]
[TD="bgcolor: transparent"]1400[/TD]
[TD="bgcolor: transparent"]MAX(IF((MONTH($A$1:$A$5)=MONTH($A$7))*($D$1:$D$5=5005),$C$1:$C$5))[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]Who[/TD]
[TD="bgcolor: transparent"]A Smith[/TD]
[TD="bgcolor: transparent"]IF(B$8=0,"",INDEX($B$1:$B$5,MATCH(1,(5000=$D$1:$D$5)*(B$8=$C$1:$C$5),0)))[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]Max Tree[/TD]
[TD="bgcolor: transparent"]1400[/TD]
[TD="bgcolor: transparent"]MAX(IF((MONTH($A$1:$A$5)=MONTH($A$7))*($D$1:$D$5=5000),$C$1:$C$5))[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]Who[/TD]
[TD="bgcolor: transparent"]G Williams[/TD]
[TD="bgcolor: transparent"]IF(B$10=0,"",INDEX($B$1:$B$5,MATCH(1,(5000=$D$1:$D$5)*(B$10=$C$1:$C$5),0)))[/TD]
[/TR]
</tbody>[/TABLE]
[TABLE="width: 674"]
<colgroup><col width="75" style="width: 56pt; mso-width-source: userset; mso-width-alt: 2742;"><colgroup><col width="189" style="width: 142pt; mso-width-source: userset; mso-width-alt: 6912;"><colgroup><col width="635" style="width: 476pt; mso-width-source: userset; mso-width-alt: 23222;"><tbody>[TR]
[TD="width: 75, bgcolor: transparent, align: right"]01/02/2019[/TD]
[TD="width: 189, bgcolor: transparent"][/TD]
[TD="width: 635, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]Max Shrub[/TD]
[TD="bgcolor: transparent"]1400[/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]Who[/TD]
[TD="bgcolor: transparent"]A Smith[/TD]
[TD="bgcolor: transparent"]Should be C David?
[/TD]
[/TR]
</tbody>[/TABLE]
I have some data that I want to pull out the 'max' and by 'who' within a defined month.
I've managed the max formula ok but struggling to build the month part into my index/match array.
Any help would be truly appreciated.
[TABLE="width: 409"]
<colgroup><col width="75" style="width: 56pt; mso-width-source: userset; mso-width-alt: 2742;"><colgroup><col width="189" style="width: 142pt; mso-width-source: userset; mso-width-alt: 6912;"><colgroup><col width="64" style="width: 48pt;" span="2"><colgroup><col width="153" style="width: 115pt; mso-width-source: userset; mso-width-alt: 5595;"><tbody>[TR]
[TD="width: 75, bgcolor: transparent"]03/01/2019[/TD]
[TD="width: 189, bgcolor: transparent"]A Smith[/TD]
[TD="width: 64, bgcolor: transparent, align: right"] 1400
[/TD]
[TD="width: 64, bgcolor: transparent"] 5005
[/TD]
[TD="width: 153, bgcolor: transparent"]Shrub[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]05/02/2019[/TD]
[TD="bgcolor: transparent"]C David[/TD]
[TD="bgcolor: transparent, align: right"]1400[/TD]
[TD="bgcolor: transparent"] 5005
[/TD]
[TD="bgcolor: transparent"]Shrub[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]08/01/2019[/TD]
[TD="bgcolor: transparent"]F Carol[/TD]
[TD="bgcolor: transparent, align: right"]1000
[/TD]
[TD="bgcolor: transparent"] 5000
[/TD]
[TD="bgcolor: transparent"]Tree[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]14/01/2019[/TD]
[TD="bgcolor: transparent"]G Williams[/TD]
[TD="bgcolor: transparent, align: right"]1400[/TD]
[TD="bgcolor: transparent"] 5000
[/TD]
[TD="bgcolor: transparent"]Tree[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]18/01/2019[/TD]
[TD="bgcolor: transparent"]B Smith[/TD]
[TD="bgcolor: transparent, align: right"]750[/TD]
[TD="bgcolor: transparent"] 5000
[/TD]
[TD="bgcolor: transparent"]Tree
[/TD]
[/TR]
</tbody>[/TABLE]
[TABLE="width: 674"]
<colgroup><col width="75" style="width: 56pt; mso-width-source: userset; mso-width-alt: 2742;"><colgroup><col width="189" style="width: 142pt; mso-width-source: userset; mso-width-alt: 6912;"><colgroup><col width="635" style="width: 476pt; mso-width-source: userset; mso-width-alt: 23222;"><tbody>[TR]
[TD="width: 75, bgcolor: transparent, align: right"]01/01/2019[/TD]
[TD="width: 189, bgcolor: transparent"][/TD]
[TD="width: 635, bgcolor: transparent"]Arrays[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]Max Shrub[/TD]
[TD="bgcolor: transparent"]1400[/TD]
[TD="bgcolor: transparent"]MAX(IF((MONTH($A$1:$A$5)=MONTH($A$7))*($D$1:$D$5=5005),$C$1:$C$5))[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]Who[/TD]
[TD="bgcolor: transparent"]A Smith[/TD]
[TD="bgcolor: transparent"]IF(B$8=0,"",INDEX($B$1:$B$5,MATCH(1,(5000=$D$1:$D$5)*(B$8=$C$1:$C$5),0)))[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]Max Tree[/TD]
[TD="bgcolor: transparent"]1400[/TD]
[TD="bgcolor: transparent"]MAX(IF((MONTH($A$1:$A$5)=MONTH($A$7))*($D$1:$D$5=5000),$C$1:$C$5))[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]Who[/TD]
[TD="bgcolor: transparent"]G Williams[/TD]
[TD="bgcolor: transparent"]IF(B$10=0,"",INDEX($B$1:$B$5,MATCH(1,(5000=$D$1:$D$5)*(B$10=$C$1:$C$5),0)))[/TD]
[/TR]
</tbody>[/TABLE]
[TABLE="width: 674"]
<colgroup><col width="75" style="width: 56pt; mso-width-source: userset; mso-width-alt: 2742;"><colgroup><col width="189" style="width: 142pt; mso-width-source: userset; mso-width-alt: 6912;"><colgroup><col width="635" style="width: 476pt; mso-width-source: userset; mso-width-alt: 23222;"><tbody>[TR]
[TD="width: 75, bgcolor: transparent, align: right"]01/02/2019[/TD]
[TD="width: 189, bgcolor: transparent"][/TD]
[TD="width: 635, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]Max Shrub[/TD]
[TD="bgcolor: transparent"]1400[/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]Who[/TD]
[TD="bgcolor: transparent"]A Smith[/TD]
[TD="bgcolor: transparent"]Should be C David?
[/TD]
[/TR]
</tbody>[/TABLE]