maxif WITHOUT array

A

aborg88

New Member
Joined
Jul 6, 2011
Messages
31
Hello ohh wise ones,

Simple question for some, but causing me difficulty,

I am trying to get the max date for a certain user without using an array formula,

i've got one sheet with userid's and dates

[TABLE="width: 500"]
<tbody>[TR]
[TD]Userlogin[/TD]
[TD]Date[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD][TABLE="width: 68"]
<tbody>[TR]
[TD="class: xl63, width: 68, align: right"]4/21/2014[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD][TABLE="width: 68"]
<tbody>[TR]
[TD="class: xl63, width: 68, align: right"]4/21/2014[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD][TABLE="width: 68"]
<tbody>[TR]
[TD="class: xl63, width: 68, align: right"]4/22/2014[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD][TABLE="width: 68"]
<tbody>[TR]
[TD="class: xl63, width: 68, align: right"]4/21/2014[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]

I would like a formula which will give me the max date for a user WITHOUT using an array formula,

I userstand that =max(criteria=criteriarange, value) will give me the correct answer, however,

1. firstly an array does not re populate automatically
2. i am getting an error from excel saying it has ran out of resources
3. it causes performance issues on the workbook.

Any ideas? :)

Plsss
 
Sort by date Sort by votes
Example:


Excel 2010
ABCD
1UserloginDateUserloginMax Date
2121/04/2014121/04/2014
3121/04/2014222/04/2014
4222/04/2014
5221/04/2014
Sheet1
Cell Formulas
RangeFormula
D2=MAX(INDEX((A$2:A$5=C2)*(B$2:B$5),))
 
Upvote 0
hi thanks for reply! however... the userId's are actually varchar's not numbers, the solution doesnt work :(
 
Upvote 0
Sheet 1

Column A
[TABLE="width: 117"]
<colgroup><col></colgroup><tbody>[TR]
[TD]User Login[/TD]
[/TR]
[TR]
[TD]max220479[/TD]
[/TR]
[TR]
[TD]juksaa[/TD]
[/TR]
[TR]
[TD]kingwalth[/TD]
[/TR]
[TR]
[TD]romeo82[/TD]
[/TR]
[TR]
[TD]anette40[/TD]
[/TR]
[TR]
[TD]Golfer007[/TD]
[/TR]
[TR]
[TD]Klevakiller[/TD]
[/TR]
[TR]
[TD]max220479[/TD]
[/TR]
[TR]
[TD]coennie65[/TD]
[/TR]
[TR]
[TD]sam75[/TD]
[/TR]
[TR]
[TD]colinden[/TD]
[/TR]
[TR]
[TD]vesvaa[/TD]
[/TR]
[TR]
[TD]juksaa[/TD]
[/TR]
[TR]
[TD]colinden[/TD]
[/TR]
[TR]
[TD]kamilek89[/TD]
[/TR]
[TR]
[TD]Klevakiller[/TD]
[/TR]
[TR]
[TD]filklater[/TD]
[/TR]
[TR]
[TD]mariomyran[/TD]
[/TR]
[TR]
[TD]Kredo[/TD]
[/TR]
[TR]
[TD]max220479

Sheet 1
Column A
[TABLE="width: 64"]
<colgroup><col width="64" style="width:48pt"> </colgroup><tbody>[TR]
[TD="width: 64"]Max date[/TD]
[/TR]
[TR]
[TD="class: xl65, align: center"]=MAX(INDEX((Sheet2!$A:$A=A2)*(Sheet2!$B:$B),))[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]

Sheet 2
Column A
[TABLE="width: 64"]
<colgroup><col width="64" style="width:48pt"> </colgroup><tbody>[TR]
[TD="width: 64"]User Login[/TD]
[/TR]
[TR]
[TD]kamilek89[/TD]
[/TR]
[TR]
[TD]max220479[/TD]
[/TR]
[TR]
[TD]max220479[/TD]
[/TR]
[TR]
[TD]juksaa[/TD]
[/TR]
[TR]
[TD]kingwalth[/TD]
[/TR]
[TR]
[TD]romeo82[/TD]
[/TR]
[TR]
[TD]anette40[/TD]
[/TR]
[TR]
[TD]Golfer007[/TD]
[/TR]
[TR]
[TD]Klevakiller[/TD]
[/TR]
[TR]
[TD]max220479[/TD]
[/TR]
[TR]
[TD]coennie65[/TD]
[/TR]
[TR]
[TD]sam75[/TD]
[/TR]
[TR]
[TD]colinden[/TD]
[/TR]
[TR]
[TD]vesvaa[/TD]
[/TR]
[TR]
[TD]juksaa[/TD]
[/TR]
[TR]
[TD]colinden[/TD]
[/TR]
[TR]
[TD]kamilek89[/TD]
[/TR]
[TR]
[TD]Klevakiller[/TD]
[/TR]
[TR]
[TD]filklater[/TD]
[/TR]
[TR]
[TD]mariomyran[/TD]
[/TR]
[TR]
[TD]Kredo[/TD]
[/TR]
[TR]
[TD]max220479[/TD]
[/TR]
[TR]
[TD]max220479[/TD]
[/TR]
[TR]
[TD]Mr_Bean[/TD]
[/TR]
[TR]
[TD]Lavik1994[/TD]
[/TR]
[TR]
[TD]DoctorYo[/TD]
[/TR]
[TR]
[TD]snudden86[/TD]
[/TR]
[TR]
[TD]twizt[/TD]
[/TR]
[TR]
[TD]Alora[/TD]
[/TR]
[TR]
[TD]seafun[/TD]
[/TR]
[TR]
[TD]max220479[/TD]
[/TR]
[TR]
[TD]drphil[/TD]
[/TR]
[TR]
[TD]snill

Sheet 2
Column B
[TABLE="width: 112"]
<colgroup><col></colgroup><tbody>[TR]
[TD]Withdrawal Date[/TD]
[/TR]
[TR]
[TD="align: right"]4/21/2014[/TD]
[/TR]
[TR]
[TD="align: right"]4/21/2014[/TD]
[/TR]
[TR]
[TD="align: right"]4/21/2014[/TD]
[/TR]
[TR]
[TD="align: right"]4/21/2014[/TD]
[/TR]
[TR]
[TD="align: right"]4/21/2014[/TD]
[/TR]
[TR]
[TD="align: right"]4/21/2014[/TD]
[/TR]
[TR]
[TD="align: right"]4/21/2014[/TD]
[/TR]
[TR]
[TD="align: right"]4/21/2014[/TD]
[/TR]
[TR]
[TD="align: right"]4/21/2014[/TD]
[/TR]
[TR]
[TD="align: right"]4/21/2014[/TD]
[/TR]
[TR]
[TD="align: right"]4/10/2014[/TD]
[/TR]
[TR]
[TD="align: right"]4/21/2014[/TD]
[/TR]
[TR]
[TD="align: right"]4/21/2014[/TD]
[/TR]
[TR]
[TD="align: right"]4/21/2014[/TD]
[/TR]
[TR]
[TD="align: right"]4/21/2014[/TD]
[/TR]
[TR]
[TD="align: right"]4/21/2014[/TD]
[/TR]
[TR]
[TD="align: right"]4/21/2014[/TD]
[/TR]
[TR]
[TD="align: right"]4/21/2014[/TD]
[/TR]
[TR]
[TD="align: right"]4/21/2014[/TD]
[/TR]
[TR]
[TD="align: right"]4/21/2014[/TD]
[/TR]
[TR]
[TD="align: right"]4/21/2014[/TD]
[/TR]
[TR]
[TD="align: right"]4/21/2014[/TD]
[/TR]
[TR]
[TD="align: right"]4/21/2014[/TD]
[/TR]
[TR]
[TD="align: right"]4/21/2014[/TD]
[/TR]
[TR]
[TD="align: right"]4/21/2014[/TD]
[/TR]
[TR]
[TD="align: right"]4/21/2014[/TD]
[/TR]
[TR]
[TD="align: right"]4/21/2014[/TD]
[/TR]
[TR]
[TD="align: right"]4/21/2014[/TD]
[/TR]
[TR]
[TD="align: right"]4/21/2014[/TD]
[/TR]
[TR]
[TD="align: right"]4/21/2014[/TD]
[/TR]
[TR]
[TD="align: right"]4/21/2014[/TD]
[/TR]
[TR]
[TD="align: right"]4/21/2014[/TD]
[/TR]
[TR]
[TD="align: right"]4/21/2014[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]
 
Upvote 0
aborg88 said:
how do i post an excel file?
Click to expand...
You can't attach an actual file but my signature block below has suggestions for how to post small screen shots like Andrew & I have.

You could get that error from Andre'w formula if your dates are text and maybe have some spaces before them. Is that possible?
Does this variation of Andrew's formula do any better?

Excel Workbook
ABCD
1UserloginDateUserloginMax Date
2abc21/4/14abc21/04/14
3abc21/4/14def22/04/14
4def22/4/14
5def21/4/14
Max Date
 
Upvote 0
Unfortunately i still get VALUE ... My dates are numbers... Sorry to be a pain :(

[TABLE="width: 500"]
<tbody>[TR]
[TD]User ID[/TD]
[TD]Date[/TD]
[/TR]
[TR]
[TD]abc[/TD]
[TD][TABLE="width: 64"]
<tbody>[TR]
[TD="width: 64"]21/4/2013[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
[TR]
[TD]abc[/TD]
[TD][TABLE="width: 64"]
<tbody>[TR]
[TD="width: 64"]22/4/2013[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
[TR]
[TD]def[/TD]
[TD]
21/4/2013

<tbody>
</tbody>
[/TD]
[/TR]
[TR]
[TD]def[/TD]
[TD][TABLE="width: 64"]
<tbody>[TR]
[TD="width: 64"]21/4/2013[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]

[TABLE="width: 500"]
<tbody>[TR]
[TD]User ID[/TD]
[TD]Date[/TD]
[/TR]
[TR]
[TD]abc[/TD]
[TD]MAX(INDEX((A$2:A$5=D2)*TRIM((B$2:B$5)),))[/TD]
[/TR]
</tbody>[/TABLE]
 
Last edited:
Upvote 0
You must log in or register to reply here.

Similar threads

E
  • Solved
formula issues in tables with LED, MINIFS, MAXIFS functions
Replies
2
Views
140
ED38
E
D
  • Question Question
help matching date and using column related to date with copying row data to new new sheet
Replies
1
Views
137
Sam_D_Ben
Sam_D_Ben
M
  • Solved
Filter Function: Return single row while looking at entire array?
Replies
3
Views
314
Cubist
Cubist
M
  • Solved
Need help with returning selectable array
Replies
4
Views
155
mrblister
M
R
  • Question Question
VBA to automate calculation
Replies
1
Views
270
HighAndWilder
H

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