• 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.

Plz help on excel formula

S

sukanta

New Member
I have two separate tables like below where unique ID may be duplicate. Now I want to set a formula in return Value column of Table 1 where the value will be Time A of Table 2 and the same should be the closest lesser value of Time A of Table 1


Table 1..........................................................Table 2

Time A / Time B / Unique ID / Return Value..........Unique ID / Time A / Time B
 
Hi Sukanta, please can you post a sample workbook....


if you go on to forums home, theirs a sticky post as "Posting a sample workbook"
 
hi,

unable to upload the workbook, can u plz help on the same. should i need to download and install the dropbox
 
Hi,


Please upload the workbook with the help of below link.


http://www.speedyshare.com/


Thanks,

Suresh Kumar S
 
Hi Sukanta,


After successful uploading, you receive two links: a download link and a deletion link. Please keep both and share the download link.


Thanks,

Suresh Kumar S
 
hi nipendra


if you download my workbook, I want to return the value in column D (vlookup with column C, return the value from column G but keep in mind that the value should be closest lower value of column A)
 
Hi sukanta,


I have gone through your workbook and I will try to find out the solution tomorrow. In the mean while our excel hero can help you.


Thanks,

Suresh Kumar S
 
Hi Sukanta,


In D3 write formula as


Code: 
=INDEX($G$2:$G$27,SMALL(IF($F$2:$F$27=C3,ROW($F$2:$F$27)-1),COUNTIF($C$3:C3,C3)))

Confirm formula by Ctrl + Shift + Enter.. Not Just Enter..


for more detail... Please look into Luke's Post..

http://chandoo.org/wp/2011/11/18/formula-forensics-003/


Regards,

Deb
 
Dear Debraj,


it works. but few cell its returning wrong value. if you go through my workbook, D19 is showing wrong value and D26 is returning #NUM!.


requesting you to plz check on the same.
 
Hi Sukanta..


Can you please download the file..

and confirm where exactly formula is not working..


https://dl.dropbox.com/u/78831150/Excel/Sukanta%20Help%20Formula.xlsm


Click any single cells in Column C or F for testing.. Enable Macro before proceed..


Regards,

Deb
 
Dear Debraj,


I have go through your workbook and the data for below cell no is not coming exactly.

Plz check the same.


Cell No.......Data comes...................Data Should Comes

D19...........2/1/2013 0:40................2/3/2013 21:33

D26...........#NUM!........................2/3/2013 11:28
 
Hi Sukanta..


Can you please download the file.. and enable Macro.


* Now click on D19.. and confirm which cell's are highlighting..

* Then match the corresponding cells with answer..


confirm why '2/3/2013 21:33' and why not.. '01/02/2013 0:40'


same for D26..


Regards,

Deb
 
Dear Debraj,


if a click on D19, cells are highlighting D19, D23,F8, F27.

now i want to return the value which is lesser closest to A19.


A19 = 2/4/2013 11:14:39 AM then closest lesser value from 'F8:F27' will be 2/3/2013 9:33:00 PM


similarly when i click D26, cells are highlighting D26,D17,D13,F16, F25.

now i want to return the value which is lesser closest to A26


A26 = 2/4/2013 3:20:43 PM then closest lesser value from 'F8:F27' will be 2/3/2013 11:28:00 AM
 
Hi Sukanta,


I am little bit confused..


but still I have tried as per my understanding..


Please check and if still you have some query, please elaborate little more..


In D3 write a Array Formula as ..


Code: 
=INDEX($G$3:$G$27,MATCH(MIN(ABS(A3-IF($F$3:$F$27=C3,$G$3:$G$27))),ABS(A3-IF($F$3:$F$27=C3,$G$3:$G$27)),0))


https://dl.dropbox.com/u/78831150/Excel/Sukanta%20Help%20Formula.xlsx


Regards,

Deb
 
dear Debraj,

I have downloaded your file. Go through the same and found some discrepancies.


I am trying to let you understand my quarries.


I want to find out the date and time (from column G) matching with column C but the value (Date and time) of Column G should be closet lesser value (date and time) of Column A.


Now your latest formula is working. Here previous issue of D19 and D26 is resolved but those values which are not available (in column G) are returning wrong value.


Let explain it from your sheet,


D5 is returning 1/31/2013 23:55 but if you see the respective lookup value of column C (in this case, 312-BUR-234) which is not available in column G. so it should returns N/A instead of 1/31/2013 23:55


Thanks for your great support.
 
Hi Sukanta,


Dhanyabaad.. for complete detail..


Please re-download the same file.. (last one)..


Jodi aagey puro information dite.. to prothomey hoye jeto.. :)


Formula used as..

Code: 
=INDEX($G$3:$G$27,MATCH(MIN(IF($F$3:$F$27=C3,ABS($G$3:$G$27-A3))),IF($F$3:$F$27=C3,ABS($G$3:$G$27-A3)),0))


Regards,

Deb


For the community: Thanks for complete detail.. Try to pass all information at one go..
 
Dear Debraj,

One more help is require on the same. Need to debug.


If Column D finds the lower value or not available (matching with Column C and column A) then formula works correctly but if he is not able to find lower value then its returning upper value.


I want only lower value, if he finds upper value then he should returns N/A.


One more help : in column E, I want the value of column H corresponding to Column D, what would be the formula.


Thanks
 
You must log in or register to reply here.
Back
Top