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 ...
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 ...
Eh !
2+2=9 ... (My Arithmetic Is Mental)
F2: =INDEX(B:B,MATCH(A2,A:A,0))
G2: =INDEX(C:C,MATCH(A2,A:A,0))
and copy down
____________________________________________
Nihil simul inventum est et perfectum
Abusus non tollit usum
Last night I dreamed of a small consolation enjoyed only by the blind: Nobody knows the trouble I've not seen!
James Thurber
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 .
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.
____________________________________________
Nihil simul inventum est et perfectum
Abusus non tollit usum
Last night I dreamed of a small consolation enjoyed only by the blind: Nobody knows the trouble I've not seen!
James Thurber
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.
Like I said "Eh !".Originally Posted by xld
2+2=9 ... (My Arithmetic Is Mental)
Is there a way of offsetting the matched value to find the previous city?
Remember To Do the Following....
Use [Code].... [/Code] tags when posting code to the thread.
Mark your thread as Solved if satisfied by using the Thread Tools options.
If posting the same issue to another forum please show the link
no , i want to find the date and then the to match the city
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.
____________________________________________
Nihil simul inventum est et perfectum
Abusus non tollit usum
Last night I dreamed of a small consolation enjoyed only by the blind: Nobody knows the trouble I've not seen!
James Thurber
thenk's again
it's ok !
You can mark your thread as Solved by going to Thread Tools | Mark Thread Solved | Perform Action.
Regards, Zack Barresse
Check out the KB! :|: BOARD TAGS: WHAT ARE THEY AND HOW DO I USE THEM
What is a Microsoft MVP? | Free Microsoft Courses | My Book on Excel Tables
Hello Bob, can you help me to understand the intent of this formulae please?Originally Posted by xld
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.
Remember To Do the Following....
Use [Code].... [/Code] tags when posting code to the thread.
Mark your thread as Solved if satisfied by using the Thread Tools options.
If posting the same issue to another forum please show the link
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.
gets the second from last date for all items in the car number group for this rowLARGE(IF($A$2:$A$21=A2,$C$2:$C$21),2)
then checks all dates against that date($C$2:$C$21=LARGE(IF($A$2:$A$21=A2,$C$2:$C$21),2))
checks the car numbers against the car number for this row, which means that this part($A$2:$A$21=A2)
checks for both conditions (there should only be one), so we get that row index using($C$2:$C$21=LARGE(IF($A$2:$A$21=A2,$C$2:$C$21),2))*($A$2:$A$21=A2)
which we pass to the INDEX function to get the city name.MATCH(1,($C$2:$C$21=LARGE( IF($A$2:$A$21=A2,$C$2:$C$21),2))*($A$2:$A$21=A2),0)
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.
Last edited by Bob Phillips; 11-11-2007 at 03:23 AM.
____________________________________________
Nihil simul inventum est et perfectum
Abusus non tollit usum
Last night I dreamed of a small consolation enjoyed only by the blind: Nobody knows the trouble I've not seen!
James Thurber
Thanks Bob. It all makes sense now, that you have logically explained it to me.
Remember To Do the Following....
Use [Code].... [/Code] tags when posting code to the thread.
Mark your thread as Solved if satisfied by using the Thread Tools options.
If posting the same issue to another forum please show the link
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.
____________________________________________
Nihil simul inventum est et perfectum
Abusus non tollit usum
Last night I dreamed of a small consolation enjoyed only by the blind: Nobody knows the trouble I've not seen!
James Thurber