+ Reply to Thread
Results 1 to 18 of 18

Compare JSON data and splitting into corrected codes, deleted codes and added codes

  1. #1
    Forum Contributor
    Join Date
    10-18-2016
    Location
    Chicago, USA
    MS-Off Ver
    Office 365
    Posts
    220

    Red face Compare JSON data and splitting into corrected codes, deleted codes and added codes

    Hi All,

    Hope you are doing well!..I am trying to extract the difference in the dcode entered by a coder and an auditor on a transaction (a transaction is represented by a combination of Billid and ctextid)..(the dcode is present in the info column which is JSON)...

    Going by the created date time for a transaction the coder enters the dcode at the earliest datetime for a transaction and the auditor enters at a later created time for the same transaction.

    There are only two rows for a single transaction -the earliest created datetime has the JSON (dcodes) entered by the coder and the later created datetime has the JSON(dcodes) entered by the auditor..Now I need to pick up the codername as the first user (earliest created datetime )in a transaction.Now I need to compare the earliest created JSON with the second created JSON( and pull out what dcodes were entered by the coder was corrected by the auditor ,what dcodes was deleted by the auditor,what dcodes was added by the auditor (in only this case the code entered by the auditor needs to be picked up as it is a newly entered code ,in the previous two cases the dcode by the coder needs to be picked up)..

    Also for determining the corrected codes (the matching can be done on description to check if the description of coder and auditor matches and in that sequence the codes entered by the coder that are changed by the auditor need to be taken )...Can you please help me with this..Attached the excel sheet with the input (input data) and output (expected data) tabs..

    Already posted link:
    https://www.excelforum.com/excel-for...ml#post5431624

    Thanks,
    Arun
    Last edited by chandramouliarun; 12-01-2020 at 05:52 AM.

  2. #2
    Banned User!
    Join Date
    02-06-2020
    Location
    Iowa City, IA, USA
    MS-Off Ver
    2016 - 365 / 2007
    Posts
    2,014

    Re: Compare JSON data and splitting into corrected codes, deleted codes and added codes

    so what data is what in the following block?
    Please Login or Register  to view this content.
    you talk about what has been deleted, entered by the first person, audited by the 2nd person, etc, etc....

    the data doesn't necessarily reflect that.

  3. #3
    Forum Contributor
    Join Date
    10-18-2016
    Location
    Chicago, USA
    MS-Off Ver
    Office 365
    Posts
    220

    Re: Compare JSON data and splitting into corrected codes, deleted codes and added codes

    @ Adam: A combination of (Billid and ctextid) is called a transaction ...Also two people enter the code dcode (a set of dcodes for the transaction)..by created time in ascending order the coder enters it and then the auditor enters it...We need to capture what extra dcode the auditor has added ,the dcode the auditor has deleted from the coder code for the same transaction and the dcode the auditor has actually changed (need to capture the code that was changed-coders dcode)..

  4. #4
    Banned User!
    Join Date
    02-06-2020
    Location
    Iowa City, IA, USA
    MS-Off Ver
    2016 - 365 / 2007
    Posts
    2,014

    Re: Compare JSON data and splitting into corrected codes, deleted codes and added codes

    none of that made any sense. sorry man. so do this, if you would. take this:
    Please Login or Register  to view this content.
    and tell me what data should be identified as all of these:

    - two people enter the code dcode
    - coder enters it and then the auditor enters it
    - extra dcode the auditor has added
    - the dcode the auditor has deleted from the coder code
    - the dcode the auditor has actually changed

  5. #5
    Forum Guru
    Join Date
    08-15-2004
    Location
    Tokyo, Japan
    MS-Off Ver
    2013 O.365
    Posts
    20,138

    Re: Compare JSON data and splitting into corrected codes, deleted codes and added codes

    chandramouliarun,

    This is how I interpret the problem and my result is slightly different from yours.
    Please Login or Register  to view this content.
    Last edited by jindon; 11-30-2020 at 12:58 PM. Reason: One line replaced.

  6. #6
    Forum Contributor
    Join Date
    10-18-2016
    Location
    Chicago, USA
    MS-Off Ver
    Office 365
    Posts
    220

    Re: Compare JSON data and splitting into corrected codes, deleted codes and added codes

    @Jindon: Can you please share your excel sheet with the output...

  7. #7
    Forum Guru
    Join Date
    08-15-2004
    Location
    Tokyo, Japan
    MS-Off Ver
    2013 O.365
    Posts
    20,138

    Re: Compare JSON data and splitting into corrected codes, deleted codes and added codes


  8. #8
    Forum Contributor
    Join Date
    10-18-2016
    Location
    Chicago, USA
    MS-Off Ver
    Office 365
    Posts
    220

    Re: Compare JSON data and splitting into corrected codes, deleted codes and added codes

    @ Jindon: This is Awesome!!!...This is almost what I wanted.. Just that For deleted codes need to have whatever dcode is not present in the 2nd JSon(the one with later created) that was there in the 1st json(earlier created), ...So for the current output for Billid 7789 and ctextid 60045 the deleted code should be Z003345,H678,U8923,Y98620 ... Can you please help me here!

    Thank you so much for your help!..

    Thanks,
    Arun
    Last edited by chandramouliarun; 12-01-2020 at 03:04 AM.

  9. #9
    Forum Guru
    Join Date
    08-15-2004
    Location
    Tokyo, Japan
    MS-Off Ver
    2013 O.365
    Posts
    20,138

    Re: Compare JSON data and splitting into corrected codes, deleted codes and added codes

    What is the definition of "correctedcode"?

    Try replace "Private Sub GetDetails" with following code and see how it goes.
    Please Login or Register  to view this content.
    Last edited by jindon; 12-01-2020 at 03:27 AM.

  10. #10
    Forum Contributor
    Join Date
    10-18-2016
    Location
    Chicago, USA
    MS-Off Ver
    Office 365
    Posts
    220

    Re: Compare JSON data and splitting into corrected codes, deleted codes and added codes

    @ Jindon: The corrected code is basically which codes have been overwritten (meaning replaced by other codes)...Now this can be included with the deleted codes.. The corrected codes can be done away with as the deleted codes and added codes gives the information on what codes changes have happened ...So with the latest code the output I am getting for Billid 7789 and ctextid 60045 for deleted code is U8923, Y98620 which is partially correct but the corrected codes also need to be included Z003345, H678...So the entire set of deleted codes should be Z003345,H678,U8923,Y98620 ..The corrected code column can be done away with or deleted..

    Really appreciate your help!..

    Thanks,
    Arun

  11. #11
    Forum Guru
    Join Date
    08-15-2004
    Location
    Tokyo, Japan
    MS-Off Ver
    2013 O.365
    Posts
    20,138

    Re: Compare JSON data and splitting into corrected codes, deleted codes and added codes

    Did you try the code above?

  12. #12
    Forum Contributor
    Join Date
    10-18-2016
    Location
    Chicago, USA
    MS-Off Ver
    Office 365
    Posts
    220

    Re: Compare JSON data and splitting into corrected codes, deleted codes and added codes

    @ Jindon ...I am getting the below as outputoutput.jpg
    Last edited by chandramouliarun; 12-01-2020 at 05:06 AM.

  13. #13
    Forum Guru
    Join Date
    08-15-2004
    Location
    Tokyo, Japan
    MS-Off Ver
    2013 O.365
    Posts
    20,138

    Re: Compare JSON data and splitting into corrected codes, deleted codes and added codes

    Seems working.

  14. #14
    Forum Contributor
    Join Date
    10-18-2016
    Location
    Chicago, USA
    MS-Off Ver
    Office 365
    Posts
    220

    Re: Compare JSON data and splitting into corrected codes, deleted codes and added codes

    @ Jindon: What I was referring to is for Bill ID 5678 and ctextid 99344 the corrected code 90774 should come under the deletedcode bucket...For Billid 7789 and ctextid 60045 the deleted code should have Z03345,H678,U8923,Y98620 and the correctedcode column can go away.. Basically whatever dcode is no there in the 2nd JSON can be put under deleted code..The corrected column is not needed...

  15. #15
    Forum Guru
    Join Date
    08-15-2004
    Location
    Tokyo, Japan
    MS-Off Ver
    2013 O.365
    Posts
    20,138

    Re: Compare JSON data and splitting into corrected codes, deleted codes and added codes

    Can you upload a workbook with the correct result?

  16. #16
    Forum Contributor
    Join Date
    10-18-2016
    Location
    Chicago, USA
    MS-Off Ver
    Office 365
    Posts
    220

    Re: Compare JSON data and splitting into corrected codes, deleted codes and added codes

    @ Jindon: I have uploaded the workbook with the correct result ..just the output tab changed...(at the beginning of this thread)

  17. #17
    Forum Guru
    Join Date
    08-15-2004
    Location
    Tokyo, Japan
    MS-Off Ver
    2013 O.365
    Posts
    20,138

    Re: Compare JSON data and splitting into corrected codes, deleted codes and added codes

    OK, now I see what you mean.
    Try replace "GetDetails" sub procedure with the below.
    Please Login or Register  to view this content.

  18. #18
    Forum Contributor
    Join Date
    10-18-2016
    Location
    Chicago, USA
    MS-Off Ver
    Office 365
    Posts
    220

    Re: Compare JSON data and splitting into corrected codes, deleted codes and added codes

    Thanks Jindon!..This really works!...

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. [SOLVED] Compare JSON data and splitting into corrected codes, deleted codes and added codes
    By chandramouliarun in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 11-29-2020, 12:06 PM
  2. Added codes to macro and some codes no longer work
    By alisoncleverly in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 02-14-2020, 01:26 PM
  3. [SOLVED] Convert Y Codes from one column to another based on Unique Codes
    By ashishmehra2010 in forum Excel General
    Replies: 2
    Last Post: 10-06-2015, 09:49 PM
  4. Replies: 7
    Last Post: 08-13-2015, 10:58 AM
  5. Replies: 1
    Last Post: 12-10-2013, 12:28 AM
  6. Replies: 0
    Last Post: 10-09-2013, 07:59 PM
  7. Combining vba codes makes the previous codes broken !
    By MDPLUS in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 03-26-2013, 10:00 AM

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1