PDA

View Full Version : before the last



eran3185
11-09-2007, 04:47 AM
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
11-09-2007, 05:16 AM
Eh !

Bob Phillips
11-09-2007, 05:20 AM
F2: =INDEX(B:B,MATCH(A2,A:A,0))
G2: =INDEX(C:C,MATCH(A2,A:A,0))

and copy down

eran3185
11-09-2007, 05:34 AM
:hi:

eran3185
11-09-2007, 10:38 AM
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
11-09-2007, 10:46 AM
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
11-09-2007, 02:06 PM
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
11-09-2007, 02:19 PM
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
11-09-2007, 03:20 PM
Is there a way of offsetting the matched value to find the previous city?

eran3185
11-09-2007, 03:42 PM
no , i want to find the date and then the to match the city

Bob Phillips
11-09-2007, 04:13 PM
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
11-10-2007, 08:07 AM
thenk's again
it's ok !
:yes

Zack Barresse
11-10-2007, 11:37 AM
You can mark your thread as Solved by going to Thread Tools | Mark Thread Solved | Perform Action. :)

Aussiebear
11-10-2007, 04:03 PM
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
11-10-2007, 04:52 PM
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
11-10-2007, 08:23 PM
Thanks Bob. It all makes sense now, that you have logically explained it to me.

Bob Phillips
11-11-2007, 03:27 AM
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.