• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Formula Challenge 009 -The Difference between the Latest and Earliest instance

S

sameer bhide

New Member
[pre]
Code: 
Client	Amt
IBM	49
TYCO	46
E&Y	82
ORACLE	88
TVS	24
BHEL	79
ABB	29
ORACLE	89
CGL	95
AREVA	49
ABB	63
E&Y	58
IBM	61
AREVA	81
ORACLE	13
ORACLE	100
TYCO	89
CGL	22
SIEMENS	10
ACES	21
AREVA	29
TVS	78
ACES	86
SIEMENS	45
TVS	16
BHEL	69
BHEL	43
IBM     56
ABB	56
[/pre]

Column A contains Clients in Random order

Column B Contains Amounts in Random order


Cell D1 contains the name of one Client Eg Oracle

Cell E1 should have a formula that finds out difference in amount between last instance of the searched Client first instance

In our case Last instance of Oracle = 100

First Instance of Oracle = 88

So Difference = 12

For IBM = 56-49 =7


Rules

No Names, Helper Rows and Columns, VBA, No Modification of Data in any manner

My Formula has 62 Characters including the =


Sam
 
@Sandeep : If I put back range references in to your formula it becomes

=ABS(LOOKUP(9^9,SEARCH(D1,A1:A30),B1:B30)-VLOOKUP(D1,A1:B30,2,0)) it becomes 65 Character

@Dan : If I replace whole column references with data ranges

=INDEX(B1:B30,MAX((A1:A30=D1)*ROW(A1:A30)))-VLOOKUP(D1,A1:B30,2,0)

it becomes 66 Characters


My 62 Character formula is without whole column references and names
 
Hi,

Here are some formulas that seem to work:

[pre]
Code: 
Len	Formula
51	=ABS(VLOOKUP(D1,A:B,2,)-LOOKUP(9^9,IF(A:A=D1,B:B)))
54	=ABS(VLOOKUP(D1,A:B,2,)-LOOKUP(1,N(A2:A30=D1),B2:B30))
56	=ABS(VLOOKUP(D1,A:B,2,)-LOOKUP(9.9E+307,IF(A:A=D1,B:B)))
64	=ABS(SUM(N(OFFSET(B1,MATCH({1,2},1/(A:A=D1),{0,1})-1,))*{1,-1}))
64	=ABS(SUM(N(INDIRECT("B"&MATCH({1,2},1/(A:A=D1),{0,1})))*{1,-1}))
64	=ABS(SUM(N(OFFSET(B1,MATCH({1,1},N(A2:A30=D1),{0,1}),))*{1,-1}))
65	=ABS(SUM(N(OFFSET(B1,MATCH({1,2},1/(A:A=D1),{0,1})-1,0))*{1,-1}))
67	=ABS(MMULT(N(OFFSET(B1,MATCH({1,2},1/(A:A=D1),{0,1})-1,0)),{1;-1}))
68	=ABS(SUM(LOOKUP(MATCH({1,2},1/(A:A=D1),{0,1}),ROW(A:A),B:B)*{1,-1}))
71	=ABS(SUM(N(INDIRECT(ADDRESS(MATCH({1,2},1/(A:A=D1),{0,1}),2)))*{1,-1}))
72	=SUM(SMALL(N(OFFSET(B1,MATCH({1,2},1/(A:A=D1),{0,1})-1,)),{2,1})*{1,-1})
73	=SUM(SMALL(N(OFFSET(B1,MATCH({1,2},1/(A:A=D1),{0,1})-1,0)),{2,1})*{1,-1})
74	=ABS(INDEX(B:B,MATCH(1,IF(A:A=D1,1),))-INDEX(B:B,MATCH(2,1/IF(A:A=D1,1))))
75	=ABS(SUM(N(INDIRECT("R"&MATCH({1,2},1/(A:A=D1),{0,1})&"C2",FALSE))*{1,-1}))
86	=ABS(INDEX(B2:B30,MATCH(1,IF(A2:A30=D1,1),))-INDEX(B2:B30,MATCH(2,1/IF(A2:A30=D1,1))))
90	=ABS(SUM(N(OFFSET(B1,CHOOSE({1,2}, MATCH(1,N(A:A=D1),),MATCH(2,1/(A:A=D1)))-1,0))*{1,-1}))
100	=ABS(SUM(N(OFFSET(B1,SMALL(IF(A2:A30=D1,ROW(A2:A30)),CHOOSE({1,2},1,COUNTIF(A:A,D1)))-1,0))*{1,-1}))
112	=ABS(SUM(ROUND(MOD(SMALL(IF(A2:A30=D1,ROW(A2:A30)+B2:B30%%),CHOOSE({1,2},1,COUNTIF(A:A,D1))),1)*10^4,0)*{1,-1}))
[/pre]

I am sure someone will optimize things further!


Cheers,

Sajan.
 
Sajan...Wow...


This was my formula with 62 characters array entered


=SUM(N(OFFSET(B1,MATCH({1,2},1/(A2:A30=D1),{-1,1}),))*{-1,1})


Do you know of a faster way of producing the array of {#DIV/0! and 1} that we get from 1/(A2:A30=D1).


If we can find a faster way of producing this array then we can use this method to perform a high low binary search and speedup the performance of SUMIF(s)/COUNTIF(s) on large data by doing range slicing
 
Hi Sam,

You will need to wrap the formula in ABS() so as to ensure that the correct results are produced even if the values are reversed.


(I do not know of any faster ways to get DIV/0 errors than what is suggested above.)


Hi Jeff,

With LOOKUP, the lookup_vector and result_vector can be independently specified, whereas VLOOKUP expects both to the supplied through a single parameter. (While you can construct a composite range using CHOOSE, LOOKUP provides that capability "natively.) This difference is readily apparent when searching for the last occurrence of a value.


For example, the following two formulas are equivalent:

=ABS(VLOOKUP(D1,A2:B30,2,)-VLOOKUP(1,CHOOSE({1,2}, 1/(A2:B30=D1), B2:B30),2,1))

=ABS(VLOOKUP(D1,A2:B30,2,)-LOOKUP(1,1/(A2:A30=D1),B2:B30))


Microsoft Excel Help also indicates that:



The array form of LOOKUP is very similar to the VLOOKUP function. The difference is that VLOOKUP searches in the first column, and LOOKUP searches according to the dimensions of array.


If array covers an area that is wider than it is tall (more columns than rows), LOOKUP searches for the value of lookup_value in the first row.


If an array is square or is taller than it is wide (more rows than columns), LOOKUP searches in the first column.


With the VLOOKUP function, you can index down or across, but LOOKUP always selects the last value in the row or column.




Cheers,

Sajan.
 
Hi,

I noticed an error in my first post. The second formula should be corrected as follows:

=ABS(VLOOKUP(D1,A:B,2,)-LOOKUP(1,1/(A:A=D1),B:B))

or

=ABS(VLOOKUP(D1,A2:B30,2,)-LOOKUP(1,1/(A2:A30=D1),B2:B30))


Cheers,

Sajan.
 
Hi Sajan

I just wanted the difference - Negative values are fine - so ABS is not required. Sorry if my initial post was not very clear about this.


Jeff,

The Lookup Function - Always performs a binary search and needs data to be sorted and behaves like the 1 option of match

The only difference between Lookup and MATCH (with 1 option) is Lookup need not be array entered when searching in an array returned by other functions


So for Example:

=INDEX(G$1:G$4,MATCH(9.9E+305,SEARCH(F$1:F$4,A1)))

and

=LOOKUP(9.9E+305,SEARCH(F$1:F$4,A1),G$1:$G$4)

produce the same result but LOOKUP is shorter and does not have to be array entered
 
Hi freinds

i want to compare 2 values usqing key field(TaskNo) and update in Sheet1. the taskno already there then update the add the hours.

example


(sheet 2) primary key value (taskno) 4575 avaliable in sheet 1. then add hours sheet1 hours values and sheet 2 same task no value(10+10) 20 in next colmn in sheet1.

A B C

taskno Hours

4575 10 20



If the task no not in sheet1 then add at the end of the task no in sheet 1 example sheet 2 (9568) in sheet 1




Sheet 1
--------


A B

1 TaskNo Hours

2 4575 10

3 8545 20

4 7854 5

5 9568 10 (new value from sheet2)


Sheet 2
--------

D E

1 TaskNo Hours

2 4575 20

3 8545 20

4 7854 5

5 9568 10



Can you guide me to do this using macros.

regards
ganesh
 
@Gane27
Hi!
Would you please start a new topic instead of writing on another user's one? It might be seen as hijacking. More indeed when it's such and old topic. If needed you could add a reference in your new one.
Perhaps you'd want to read the red sticky posts at this forums main page so as to know the guidelines that will lead to know how this community operates (introducing yourself, posting files, netiquette rules, and so on).
Regards!
PS: Please don't answer here at this thread.
 
Hi,
I developed an array formula:
=INDEX($B$1:$B$30,MAX(IF($A$2:$A$30=$D1,ROW($A$2:$A$30))))-INDEX($B$1:$B$30,MIN(IF($A$2:$A$30=$D1,ROW($A$2:$A$30))))

I know this is longer than many of formulas written above.
 
You must log in or register to reply here.
Back
Top