Sort by more creteria

[KJ_2016]

Dear all

i close a post and was not able to reopen it , so sorry that i wrote it again !

Dear all

after some test i found a issue !

I have a big table .
Site Dept Date SCORE



Goal
Get a resullt for each site Dept and last enter date and the score behind





Site Dept Date Score Helper (hide) Site Dept Date Score
Spain Sales 04.05.2016 3,1 Spain_Sales Spain Sales 01.07.2016 1,2
Germany Infra 01.08.2016 2 Germany_Infra Germany Infra 01.08.2016 2
Netherland Sales 01.01.2016 1,3 Netherland_Sales Netherland Sales 01.01.2016 1,3
MA Tech 03.03.2016 4,5 MA_Tech MA Tech 08.07.2016 1,2
TR Tech 01.01.2016 1 TR_Tech TR Tech 01.01.2016 1
Spain Tech 15.08.2016 2,1 Spain_Tech Spain Tech 01.07.2016 1,2
Spain Sales 01.07.2016 1,2
MA Tech 08.07.2016 1,2


You see that result for Spain tech must 2.1 not 1.2

[Glenn Kennedy]

Will you please attach a sample Excel workbook? Please don't attach a picture of one.

1. Make sure that your sample data are REPRESENTATIVE of your real data. The use of unrepresentative data is very frustrating and can lead to long delays in reaching a solution.

2. Make sure that your desired solution is also shown (mock up the results manually).

3. Make sure that all confidential information is removed first!!

4. Try to avoid using merged cells. They cause lots of problems!

Unfortunately the attachment icon doesn't work at the moment. So, to attach an Excel file you have to do the following: Just before posting, scroll down to Go Advanced and then scroll down to Manage Attachments. Now follow the instructions at the top of that screen.

[KJ_2016]

Sure attach file

[Glenn Kennedy]

Use this in M2....

=IFERROR(LOOKUP(2,1/($A$2:$A$9=$J2)*($B$2:$B$9=$K2)*($C$2:$C$9=L2),D$2:D$9),"")

[KJ_2016]

Thanks for answer !

I just ty it , but without success !

I get now no result !

[shukla.ankur281190]

May Be

try

M2

Please Login or Register  to view this content.

[KJ_2016]

Hello

thanks for your INFO

But i fact a other issue !

When add new value in column A to D
The result was not right ! It put the enter date in the result fields

Goal was - to get the only the result base on the latest date for it

[Glenn Kennedy]

The date in C15 is not a date. It needs / not . to be recognised as a date. Also , unless you need dates in the 19th century... don't use them for test purposes in Excel. For Excel, time began on 0th January 1900....

I have set up 4 named ranges (CTRL-F3 to view them) to expand the ranges automatically to match your data. This stops Excel looking down 1000 rows, if it is not needed.

Formulae using the named ranges now incorporated ....

[KJ_2016]

Many thanks !

That help a lot - one additional question!

Was it possible to have the result in a fix sort line. When we enter a new line it clalculate and the sequence change the row.

It will be great to get the result without changeing the sequence

regards

[Glenn Kennedy]

I don't understand. Please explain again and (even better) amend my sheet to show the problem...