Posts by djced

    Dear experts

    Please ignore earlier post...

    I have a unique problem. I searched for the solution in the existing posts and unable to find one.


    I have to import a text file from an application into Excel sheet. In that

    1. I have to break to new row if a string of text comes ["{\"

    2. After that the text to new column on the string of text \",\"

    3. The data has to be arranged under common headings

    cinotype_namecase_noreg_yearreg_nopetparty_nameresparty_namefil_yearfil_no


    Sample file attached


    Please help


    Jay

    Files

    • Sample.txt

      (1.42 kB, downloaded 69 times, last: )

    Dear experts

    I have a unique problem. I searched for the solution in the existing posts and unable to find one.


    I have to import a text file from an application into Excel sheet. In that I have to break to new row if a string of text comes ["{\"


    After that the text to new column on the string of text \",\"


    Please help


    Jay


    Sample text

    ["{\"cino\":\"TNED010000502012\",\"type_name\":\"OS\",\"case_no\":\"231701002372013\",\"reg_year\":2013,\"reg_no\":100237,\"petparty_name\":\"P.Kandasamy\",\"resparty_name\":\"P.Shanmugam\",\"fil_year\":\"0\",\"fil_no\":\"0\",\"establishment_name\":\"Principal District Court, Erode\",\"establishment_code\":\"TNED01\",\"state_code\":\"10\",\"district_code\":\"24\",\"state_name\":\"Tamil Nadu\",\"district_name\":\"Erode\",\"date_next_list\":\"2020-04-23\",\"date_of_decision\":null,\"date_last_list\":\"2020-03-19\",\"updated\":true,\"court_no_desg_name\":\"Principal District Judge\"}","{\"cino\":\"TNED010002822017\",\"type_name\":\"AS\",\"case_no\":\"230201000732017\",\"reg_year\":2017,\"reg_no\":100073,\"petparty_name\":\"Thirumayammal and 3 others\",\"resparty_name\":\"R.Thirumoorthy\",\"fil_year\":\"0\",\"fil_no\":\"0\",\"establishment_name\":\"Principal District Court, Erode\",\"establishment_code\":\"TNED01\",\"state_code\":\"10\",\"district_code\":\"24\",\"state_name\":\"Tamil Nadu\",\"district_name\":\"Erode\",\"date_next_list\":\"2018-04-24\",\"date_of_decision\":\"2018-04-24\",\"date_last_list\":\"2018-04-24\",\"updated\":true,\"court_no_desg_name\":\"Principal District Judge\"}",

    Dear XL gurus
    I face few problems in sample excel Attached


    [INDENT]1.the Filter works only if all the B1, B2, B3 are fully entered
    2.How to make even a blank Cell in B1, B2, B3 HAS To MEAN ALL possiblity
    3.Number (B2) starts in '0' gives error. how to declare it as text?
    4.Even If the B2 Cell contains part of the "Number" needs to be displayed (eg: 154 may result in 01540, 23154, 15456)
    5.Why the dynamic Range doesn’t cover Whole Database?
    6.A report of the result not copied in full to Report Page[/INDENT]

    Match And Copy


    dear Kenneth Hobson
    Ur code helped me a lot !
    I modified that a bit to suit my needs.


    THANKS !


    still I need some improvements
    1. I had to redefine the Ranges as both of them vary daily
    2. If I need to get the result that matches right(5) from check sheet to right(5) from base sheet. (now right 5 in check sheet gets the match from anywhere from the cell) eg: jksdf45250 gets match from kg45250xy
    the delay in replying here is due to my try over ur solution. But I am not getting the right result
    thank u sir in advance

    Re: Match And Copy


    thanks Mr.Kenneth Hobson
    here is my examble xls
    thanks to the code i got here. I am able to make last 5 digits be matched
    but
    1. i cant make the range flexible...
    2. It doest only match last 5 but also anywhere
    2. result is only restricted to 'C' coloumn not continues in 'D','E'... and so on (even if more matchs are there)

    Files

    we have two workbooks which do change daily...


    one with 'data' sheet containing 40,000+ rows of data in coloumn 'B'
    another with 'cust' sheet with 10,000+ rows of data in coloumn 'C'


    as a part of daily work we have to compare last part of the each cell in Cust sheet with data sheet
    1. last 6 digits are compared and the results copied to 'cust' sheet from 'D' column and so on adjacent to the corresponding data.
    2. last 5 digits are compared and the results copied to 'cust' sheet from end of the aforesaid result (1)
    3. last 4 digits are compared and the results copied to 'cust' sheet from end of the aforesaid result (2)
    4. any duplicate within the row to be deleted from left to right


    as the data are enormous the work can be completed only 10 - 15 % manually


    masters here can help us out to get the result at accelerated pace through VBA


    thanks in advance :exclamat:
    kavi