+ Reply to Thread
Results 1 to 27 of 27

Parsing data issues

  1. #1
    Registered User
    Join Date
    10-22-2015
    Location
    USA
    MS-Off Ver
    2013
    Posts
    22

    Parsing data issues

    Basic overview:
    I am parsing the column, if it is a date, I convert the date to a date corresponding with a Wednesday (same day, or next Wednesday). Then I get the value from row under the date. If the Wednesday date is the same as previous, I keep adding the number value. If it is different, I store the number value in a cell, reset the count, and repeat. For this data, I should be getting:
    8/26/2015 7
    9/2/2015 6
    9/9/2015 3

    But what I actually get is:
    8/26/2015 1
    9/2/2015 7
    9/9/2015 8

    WB is attached below. I know I need to set prevDate the first time, but I couldn't get it to work

    Any assistance to get it working and/or suggestion to make the code more efficient will be appreciated!

    Attachment 426518
    Attached Files Attached Files
    Last edited by ishq786; 10-26-2015 at 05:31 PM.

  2. #2
    Forum Guru xladept's Avatar
    Join Date
    04-14-2012
    Location
    Pasadena, California
    MS-Off Ver
    Excel 2003,2010
    Posts
    12,378

    Re: Parsing data issues

    Attach a sample workbook. Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are shown, mock them up manually if necessary.

    Remember to desensitize the data.

    Click on GO ADVANCED and use the paperclip icon to open the upload window.

    View Pic
    If I've helped you, please consider adding to my reputation - just click on the liitle star at the left.

    ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~(Pride has no aftertaste.)

    You can't do one thing. XLAdept

    ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~aka Orrin

  3. #3
    Registered User
    Join Date
    10-22-2015
    Location
    USA
    MS-Off Ver
    2013
    Posts
    22

    Re: Parsing data issues

    @xladept Thanks for the tip. I have updated the post.

  4. #4
    Registered User
    Join Date
    10-10-2015
    Location
    Hoboken, Antwerp, Belgium
    MS-Off Ver
    2010
    Posts
    93

    Re: Parsing data issues

    Hi,

    First of all, I don't see any worksheet name in your code, so try first to add the worksheet name before any Range you have, like this:
    Please Login or Register  to view this content.
    If you don't mention the worksheet you want to work on, VBA don't know on which worksheet you like to have the values.

    Second item: in this code you like to make a loop through the rows from row 35 'till where? I don't see any counting of rows, like this p.e.:
    Please Login or Register  to view this content.
    You can use this code too for knowing the last row with data in column A:
    Please Login or Register  to view this content.
    And at last, but not least, if you upload an example of your workbook here, it may be more easy to look at, and test, the entire code instead of the piece here.

    Update:
    Sorry, I was typing this while the Thread was updating, so I came second :D
    Last edited by Cheetahke; 10-22-2015 at 03:57 PM.
    Greetings,

    Cheetahke

  5. #5
    Registered User
    Join Date
    10-22-2015
    Location
    USA
    MS-Off Ver
    2013
    Posts
    22

    Re: Parsing data issues

    First of all, I don't see any worksheet name in your code, so try first to add the worksheet name before any Range you have, like this:
    That is because at the moment all the analysis data is being kept on the raw sheet. I will move it to another sheet later.

    Second item: in this code you like to make a loop through the rows from row 35 'till where? I don't see any counting of rows, like this p.e.:
    Below is how I declare the 'till where?' value and use it in For Loop.
    Please Login or Register  to view this content.
    it may be more easy to look at, and test, the entire code instead of the piece here.
    The workbook I uploaded is a working code. After I get the desired values, I will display them in a chart. So it is almost complete code.

  6. #6
    Forum Guru xladept's Avatar
    Join Date
    04-14-2012
    Location
    Pasadena, California
    MS-Off Ver
    Excel 2003,2010
    Posts
    12,378

    Re: Parsing data issues

    Hi Ishq,

    Please explain what the 7,6,3 signify

  7. #7
    Registered User
    Join Date
    10-22-2015
    Location
    USA
    MS-Off Ver
    2013
    Posts
    22

    Re: Parsing data issues

    Quote Originally Posted by xladept View Post
    Hi Ishq,

    Please explain what the 7,6,3 signify
    They are the numbers added for each week. So for example, values of dates 8/20 to 8/26 are summed together because the next Wednesday is 8/26 (I have it so the week ends on Wednesday) so all the values under those dates (1,2,1,1,1,1) will be summed together to 7, hence 8/26/2015 has 7 next to it. Similarly, 8/31 and 9/1 fall under week ending on 9/2 so 1+5 = 6. And lastly, 9/3's value is alone, so it has 3 next to week ending 9/9. Let me know if more clarification is needed.

  8. #8
    Registered User
    Join Date
    10-22-2015
    Location
    USA
    MS-Off Ver
    2013
    Posts
    22

    Re: Parsing data issues

    Anyone willing to assist? I am sure it is a small error in the code, but I can't seem to point it out.

  9. #9
    Forum Guru xladept's Avatar
    Join Date
    04-14-2012
    Location
    Pasadena, California
    MS-Off Ver
    Excel 2003,2010
    Posts
    12,378

    Re: Parsing data issues

    Hi Ishq,

    Try this:

    Please Login or Register  to view this content.

  10. #10
    Registered User
    Join Date
    10-22-2015
    Location
    USA
    MS-Off Ver
    2013
    Posts
    22

    Re: Parsing data issues

    Thanks for the response xladept. It returns 3,10,3 (instead of 7,6,3).

  11. #11
    Forum Guru xladept's Avatar
    Join Date
    04-14-2012
    Location
    Pasadena, California
    MS-Off Ver
    Excel 2003,2010
    Posts
    12,378

    Re: Parsing data issues

    Yeah, I've discovered that it's not recognizing some of the date fields - I may have it working soon

  12. #12
    Registered User
    Join Date
    10-22-2015
    Location
    USA
    MS-Off Ver
    2013
    Posts
    22

    Re: Parsing data issues

    Quote Originally Posted by xladept View Post
    Yeah, I've discovered that it's not recognizing some of the date fields - I may have it working soon
    Thank you for all the efforts, I look forward to it!

  13. #13
    Forum Guru xladept's Avatar
    Join Date
    04-14-2012
    Location
    Pasadena, California
    MS-Off Ver
    Excel 2003,2010
    Posts
    12,378

    Re: Parsing data issues

    But not, I'm afraid, today

  14. #14
    Registered User
    Join Date
    10-22-2015
    Location
    USA
    MS-Off Ver
    2013
    Posts
    22

    Re: Parsing data issues

    Quote Originally Posted by xladept View Post
    But not, I'm afraid, today
    I understand. At your earliest convenience would do (hopefully some time tomorrow )

  15. #15
    Forum Guru xladept's Avatar
    Join Date
    04-14-2012
    Location
    Pasadena, California
    MS-Off Ver
    Excel 2003,2010
    Posts
    12,378

    Re: Parsing data issues

    This works on this sample:

    Please Login or Register  to view this content.
    *But I don't trust it completely - any more samples?

  16. #16
    Registered User
    Join Date
    10-22-2015
    Location
    USA
    MS-Off Ver
    2013
    Posts
    22

    Re: Parsing data issues

    Quote Originally Posted by xladept View Post
    This works on this sample:

    *But I don't trust it completely - any more samples?
    Thanks for all the help!

    For the most part, it is working. I have attached a bigger data file named sampleWB2.

    Few observations:
    - It appears that it doesn't handle the last date case well. (Currently it is 8/13, but try replacing it with 8/12 - which is a Wednesday)
    - The new data is desensitized raw data exported from the database, and it seems that excel reads 2 extra lines beyond the data, so if you can take a look at that too
    Currently I did this:
    Please Login or Register  to view this content.
    - How hard would it be to do a minor change: if the first date is a Wednesday, store next Wednesday's date and sum up till next Wednesday too.
    For example, in the new data, 6/3 (the first date) is a Wednesday, but I still want it to sum till next Wednesday (6/10).

    P.S - I would like to know how you fixed the entire issue, so if you can provide a little summary and/or comment the code, that'll be great!
    Last edited by ishq786; 10-26-2015 at 07:02 PM.

  17. #17
    Forum Guru xladept's Avatar
    Join Date
    04-14-2012
    Location
    Pasadena, California
    MS-Off Ver
    Excel 2003,2010
    Posts
    12,378

    Re: Parsing data issues

    I think this may do it Hopefully, my commentary explains what the code doesn't say for itself

    Please Login or Register  to view this content.

  18. #18
    Registered User
    Join Date
    10-22-2015
    Location
    USA
    MS-Off Ver
    2013
    Posts
    22

    Re: Parsing data issues

    Getting close! lol
    If the last date is not a Wednesday (8/13 - 8/18), it shows the same date instead of showing next Wednesday's date (8/19). But if it IS a Wednesday (8/19), it doesn't show at all.

  19. #19
    Forum Guru xladept's Avatar
    Join Date
    04-14-2012
    Location
    Pasadena, California
    MS-Off Ver
    Excel 2003,2010
    Posts
    12,378

    Re: Parsing data issues

    Please Login or Register  to view this content.
    ?????????????

  20. #20
    Registered User
    Join Date
    10-22-2015
    Location
    USA
    MS-Off Ver
    2013
    Posts
    22

    Re: Parsing data issues

    After commenting out the last bit of code, when the last date falls on a Wednesday, it works. But for any other date, it does not round up to the next Wednesday's date.
    On Row 170 of excel, replace 8/13/2015 with any non-Wednesday date after the 12th. The result will be the same date printed (instead of rounded-up Wednesday date).

    My solution (ending code):
    Please Login or Register  to view this content.
    But is there something more efficient? Also, now that I am near the end, is there anything you'll suggest that'll make the code more efficient overall?

    Thanks for all the help!

    On a side note, I haven't been able to connect to the forum on a regular basis. I have attached the error/page I guess when trying to connect. Maybe you can pass it on to the right person.
    Attached Images Attached Images
    Last edited by ishq786; 10-26-2015 at 10:51 PM.

  21. #21
    Forum Guru xladept's Avatar
    Join Date
    04-14-2012
    Location
    Pasadena, California
    MS-Off Ver
    Excel 2003,2010
    Posts
    12,378

    Re: Parsing data issues

    Yeah - that Sucuri thing is bugging all of us - it's why I didn't answer you sooner.

    I'll see whether I can improve the code tomorrow - I think I understand what's going on now

  22. #22
    Registered User
    Join Date
    10-22-2015
    Location
    USA
    MS-Off Ver
    2013
    Posts
    22

    Re: Parsing data issues

    Quote Originally Posted by xladept View Post
    Yeah - that Sucuri thing is bugging all of us - it's why I didn't answer you sooner.

    I'll see whether I can improve the code tomorrow - I think I understand what's going on now
    Thank you!

  23. #23
    Registered User
    Join Date
    10-22-2015
    Location
    USA
    MS-Off Ver
    2013
    Posts
    22

    Re: Parsing data issues

    Sorry to be a pain and keep making changes, but if I change the end-of-week to be on Friday, the last date starts acting up again:
    Please Login or Register  to view this content.
    to
    Please Login or Register  to view this content.

  24. #24
    Forum Guru xladept's Avatar
    Join Date
    04-14-2012
    Location
    Pasadena, California
    MS-Off Ver
    Excel 2003,2010
    Posts
    12,378

    Re: Parsing data issues

    Another version: The red code allows you to run on any day of the week

    Please Login or Register  to view this content.

  25. #25
    Registered User
    Join Date
    10-22-2015
    Location
    USA
    MS-Off Ver
    2013
    Posts
    22

    Re: Parsing data issues

    Thanks! I will try it out and post back!
    Last edited by ishq786; 10-27-2015 at 05:54 PM.

  26. #26
    Registered User
    Join Date
    10-22-2015
    Location
    USA
    MS-Off Ver
    2013
    Posts
    22

    Re: Parsing data issues

    Thank you xladept. So far it is working as expected. I will have more people test it and hopefully there are no issues. Thank a ton for all your work!

  27. #27
    Forum Guru xladept's Avatar
    Join Date
    04-14-2012
    Location
    Pasadena, California
    MS-Off Ver
    Excel 2003,2010
    Posts
    12,378

    Re: Parsing data issues

    You're welcome!

+ 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. Data Validation List Issues/Sorting Issues
    By taylorbe in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 03-12-2014, 09:53 AM
  2. [SOLVED] Parsing data in a UDF
    By ARGK in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 12-12-2013, 05:16 PM
  3. Excel 2007 : Parsing data
    By vbagwell in forum Excel General
    Replies: 3
    Last Post: 10-21-2011, 02:13 PM
  4. Parsing out data after &
    By ntallman in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 03-23-2010, 11:10 AM
  5. Parsing data
    By Jlaz in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 02-12-2006, 01:45 AM
  6. Help With Parsing Data
    By Saxman in forum Excel General
    Replies: 5
    Last Post: 12-27-2005, 10:45 AM
  7. [SOLVED] help with data parsing
    By Param Dhillon in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 02-24-2005, 12:06 AM
  8. Help with data parsing
    By Param Dhillon in forum Excel General
    Replies: 2
    Last Post: 02-23-2005, 10:06 PM

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