before the last

[eran3185]

hi
i have a very big data base (almost 20,000 lines).

i need a function that give me column g and column f.
for example : f2=f3=f4=sheffild , g2=g3=g4=01/03/2007 etc ...
:dunno

[unmarkedhelicopter]

Eh !

[Bob Phillips]

F2: =INDEX(B:B,MATCH(A2,A:A,0))
G2: =INDEX(C:C,MATCH(A2,A:A,0))

and copy down

[eran3185]

:hi:

[eran3185]

hi again
in the second car for example ' the last date is 10/10/2007 and the last city is coventry.
i need that in the f column will be "leeds" and in the g column will be "9/10/2007" - these is the date before the last date .
:banghead:

[Bob Phillips]

I think you need to explain the rules for deriving these values, as I clearly do not understand, and your example shows what my formula produces.

[eran3185]

i have almost 20,000 lines in my file.
it's look like the att file.
i want to see in column g not the last date (like u can see in column e) but the date before .
for example :in lines 5-8 (the second car) - the last date is 10/10/2007 and the last city is coventry.
i need that in g column will be "9/10/2007" (10/10/2007=last date , the latest date , and 9/10/2007 is the date before the last) and f column will be "leeds" .
for example in lines 18-21 (car number : 44-456-22) the last date is 1/8/2007 , and the date before is 5/5/2007 , and the city is Sheffield.

[unmarkedhelicopter]

I think you need to explain the rules for deriving these values, as I clearly do not understand, and your example shows what my formula produces.Like I said "Eh !".

[Aussiebear]

Is there a way of offsetting the matched value to find the previous city?

[eran3185]

no , i want to find the date and then the to match the city

[Bob Phillips]

F2: =IF(COUNTIF($A$2:$A$21,A2)=1,B2,INDEX($B$2:$B$21,MATCH(1,($C$2:$C$21=LARGE( IF($A$2:$A$21=A2,$C$2:$C$21),2))*($A$2:$A$21=A2),0)))

G2: =IF(COUNTIF($A$2:$A$21,A2)=1,C2,INDEX($C$2:$C$21,MATCH(1,($C$2:$C$21=LARGE( IF($A$2:$A$21=A2,$C$2:$C$21),2))*($A$2:$A$21=A2),0)))

both are array formulae, they should be committed with Ctrl-Shift-Enter, not just Enter. Excel will automatically enclose the formula in braces (curly brackets), do not try to do this manually. When editing the formula, it must again be array-entered.

Also, the 21 refers to the last line of data, adjust it to some value taht will always be in range.

[eran3185]

thenk's again
it's ok !
:yes

[Zack Barresse]

You can mark your thread as Solved by going to Thread Tools | Mark Thread Solved | Perform Action. :)

[Aussiebear]

F2: =IF(COUNTIF($A$2:$A$21,A2)=1,B2,INDEX($B$2:$B$21,MATCH(1,($C$2:$C$21=LARGE( IF($A$2:$A$21=A2,$C$2:$C$21),2))*($A$2:$A$21=A2),0)))



Hello Bob, can you help me to understand the intent of this formulae please?

As I currently understand this:

If(Count the value of cell A2 within the range A2:A21) equals 1 then F2 equals value of cell B2 else Index range B2:B21 and Match "1"in the range A2:A21 providing it equals the 2nd largest value in the range C2:C21.... I don't I'm even close there in that last bit.

[Bob Phillips]

I'll give it a go Ted, but I think you get the general idea.

The idea is to get the last but one item for a group of car numbers, in date terms.


LARGE(IF($A$2:$A$21=A2,$C$2:$C$21),2)

gets the second from last date for all items in the car number group for this row


($C$2:$C$21=LARGE(IF($A$2:$A$21=A2,$C$2:$C$21),2))

then checks all dates against that date


($A$2:$A$21=A2)

checks the car numbers against the car number for this row, which means that this part


($C$2:$C$21=LARGE(IF($A$2:$A$21=A2,$C$2:$C$21),2))*($A$2:$A$21=A2)

checks for both conditions (there should only be one), so we get that row index using


MATCH(1,($C$2:$C$21=LARGE( IF($A$2:$A$21=A2,$C$2:$C$21),2))*($A$2:$A$21=A2),0)

which we pass to the INDEX function to get the city name.

The COUNTIF just checks if there is a singleton entry for this car number, and returns that city name directly if so, because the rest of the formula fails on a singleton.

[Aussiebear]

Thanks Bob. It all makes sense now, that you have logically explained it to me.

[Bob Phillips]

I think the key element here Ted is the matching on two conditions trick, comparing the value 1 against the array of results from ANDing (*) those conditions.

The LARGe is a useful trick, and the COUNTIF takes care of singletons, but the heart is the two-condition MATCH.