to get last row of each day from excel sheet -


i need find last record of each day in excel if there multiple entries

*******intention eod balance row of each date********

like have data in excel 

date             cr_dr amount eod balance ---------------------- 7/9/2017 19:09  cr       10       10 7/10/2017 18:37 cr       25       35 7/10/2017 21:06 dr       10       25 7/11/2017 19:21 cr       15       40   7/15/2017 14:17 dr       20       20 7/17/2017 17:12 cr       100     120 7/18/2017 7:44  dr       30       90 7/18/2017 14:08 dr       50       40 7/18/2017 16:52 cr       120     160

for need data (get last row of each day)

7/9/2017 19:09  cr       10       10 7/10/2017 21:06 dr       10       25 7/11/2017 19:21 cr       15       40   7/15/2017 14:17 dr       20       20 7/17/2017 17:12 cr       100     120 7/18/2017 16:52 cr       120     160

adding output of formula applied

data post updated comments

solution 1

enter following formula in cell f2

=iferror(max(if(int($a$2:$a$10)=int(index($a$2:$a$10, match(0, frequency(if(exact(int($a$2:$a$10), transpose(int($f$1:f1))), match(row($a$2:$a$10), row($a$2:$a$10)), ""), match(row($a$2:$a$10), row($a$2:$a$10))), 0))),$a$2:$a$10,0)),"")

this array formula commit pressing ctrl+shift+enter. drag/copy down required.

then in cell g2 enter

=vlookup($f2,$a$2:$d$10,column(c1)-column($a$1),false)

drag formula across (to right) till cell i2 , down required.

see image reference.

enter image description here

solution 2

instead of using ugly looking long formula, here we'll use helper column.

in cell f2 enter following formula

=max(if(int($a$2:$a$10)=int(a2),$a$2:$a$10,0))

this array formula commit pressing ctrl+shift+enter. drag/copy down required.

then in cell g2 enter 

=iferror(index($f$2:$f$10,match(0,index(countif($g$1:g1,$f$2:$f$10),0,0),0)),"")

drag/copy down required.

finally, in cell h2 enter

=vlookup($g2,$a$2:$d$10,column(c1)-column($a$1),false)

drag formula across (to right) till cell j2 , down required.

see image reference.

enter image description here

edit : per comment.

instead of 

=vlookup($g2,$a$2:$d$10,column(c1)-column($a$1),false)

use below formula in cell h2

=index(b$2:b$10,max(if($a$2:$a$10=$g2,row($a$2:$a$10)-row(index($a$2:$a$10,1,1))+1)))

this array formula commit pressing ctrl+shift+enter. drag formula across (to right) till cell j2 , down required. drag/copy down required.

screen-shot :

enter image description here


Comments

Post a Comment

Popular posts from this blog

php - Vagrant up error - Uncaught Reflection Exception: Class DOMDocument does not exist -

i've set bento/ubuntu-16.04 vagrant box using php 5.6, , getting following error quite few times when try , run shell script: "uncaught reflection exception: class domdocument not exist". i've tried adding following top of script (not together), each no success: "sudo apt-get install php5.6-xml", "sudo apt-get install php5-xml", "sudo apt-get install php-dom" i've tried resetting server "sudo service apache2 restart". does have suggestions?
Read more

vue.js - Create hooks for automated testing -

Image
qa guys complained can't automate frontend testing. because our html looks same outside. lazy hack did this <template> <div :role="$options.name"> ... </div> </template> <script> export default { name: 'vmcomponentname' } </script> which takes name script , applies html. in browser get: <div role="vmcomponentname"> ... </div> line :role="$options.name" goes in every component. is there dry er solutions? please share. additional details i'll explain in details looking for. imagine have vmusercreate form creates user. test case can create user . to automatically test without role, have use following selector: .wrapper > .wrapper > .wrapper > .submit-button the test extremely brittle (will break lot). if use roles can use [role="vmusercreateform"] .submit-button selector. many folds less brittle. so basically, i'm ...
Read more

Add new key value to json node in java -

i have json file : { "id":1, "name":"abc", "addressdetails": { "city":"newyork" } } i wanted add 1 more key-value ("pincode" : "414141") node 'addressdetails'. i tried using : objectmapper mapper = new objectmapper(); jsonnode root = mapper.readtree(new file("d://test.json")); objectnode node = mapper.createobjectnode(); node.with("addressdetails").put("pincode", "414141"); but it's not getting added, there way can ? you can try you should modify file path objectmapper mapper = new objectmapper(); objectnode nodes = mapper.readvalue(new file("d:\\test.txt"), objectnode.class); nodes.with("addressdetails").put("pincode", "414141"); mapper.writer().writevalue(new file("d:\\test.txt"), nodes);
Read more