+ Reply to Thread
Results 1 to 15 of 15

Macro to change data entered in a cell seperated by a comma, into a vertical list of data

  1. #1
    Registered User
    Join Date
    09-23-2011
    Location
    buford, ga
    MS-Off Ver
    Excel 2007
    Posts
    9

    Macro to change data entered in a cell seperated by a comma, into a vertical list of data

    i have data that is entered in the "data" sheet of a workbook, that i need transformed into the format on "sheet 1". Multiple invoices are listed in column E seperated by a column with their respective amounts listed in column F also seperated by a column. Inorder for me to input this data into my accounting software, i need each invoice with the ammount it is paying on its own row. Also, column K on "sheet 1" need to count the number of invoices entered, and if the total paid on invoices column added up for a custmer does not equal the amount of the check, i need all the amounts highlighted. Any suggestions on this will be appreciated, as it takes me roughly 6 hours to enter all that data manually. There is a range of the amount of checks that i need to input from 4 - 500, i just gave an example using 4.

    Thank you so much
    Attached Files Attached Files

  2. #2
    Forum Expert
    Join Date
    07-15-2012
    Location
    Leghorn, Italy
    MS-Off Ver
    Excel 2010
    Posts
    3,431

    Re: Macro to change data entered in a cell seperated by a comma, into a vertical list of d

    I did not understand all, but this is a code to split data seperated by a comma
    Please Login or Register  to view this content.
    If solved remember to mark Thread as solved

  3. #3
    Forum Expert mike7952's Avatar
    Join Date
    12-17-2011
    Location
    Florida
    MS-Off Ver
    Excel 2007, Excel 2016
    Posts
    3,517

    Re: Macro to change data entered in a cell seperated by a comma, into a vertical list of d

    I dont see some of the output fields in the data sheet so I hard coded them. Try this

    Please Login or Register  to view this content.
    Thanks,
    Mike

    If you are satisfied with the solution(s) provided, please mark your thread as Solved.
    Select Thread Tools-> Mark thread as Solved.

  4. #4
    Registered User
    Join Date
    09-23-2011
    Location
    buford, ga
    MS-Off Ver
    Excel 2007
    Posts
    9

    Re: Macro to change data entered in a cell seperated by a comma, into a vertical list of d

    Thank Mike, this is perfect. As far as the fields that you had to hardcode, i dont need the ENTERED? column. Also, i cant change the amount entered in column D on the data tab. What i wanted was a way to indicate if the amounts entered in column F of the data tab did not equal the amount entered in column d of the data tab - whatever way you can make that indication is fine with me even if you have to add a validation column.

    Thanks so much for your quick response - this is awesome!

  5. #5
    Forum Expert mike7952's Avatar
    Join Date
    12-17-2011
    Location
    Florida
    MS-Off Ver
    Excel 2007, Excel 2016
    Posts
    3,517

    Re: Macro to change data entered in a cell seperated by a comma, into a vertical list of d

    So with your example Customer A. On Sheet1 Cash Amount shows 292.42 and on the Data Sheet shows 561.54. Should column I on Sheet1 for Customer A show 561.54 an not 292.42. And would that need to be marked/flaged as a difference?

  6. #6
    Registered User
    Join Date
    09-23-2011
    Location
    buford, ga
    MS-Off Ver
    Excel 2007
    Posts
    9

    Re: Macro to change data entered in a cell seperated by a comma, into a vertical list of d

    yes you are 100% correct.

  7. #7
    Forum Expert mike7952's Avatar
    Join Date
    12-17-2011
    Location
    Florida
    MS-Off Ver
    Excel 2007, Excel 2016
    Posts
    3,517

    Re: Macro to change data entered in a cell seperated by a comma, into a vertical list of d

    Ok give this a shot

    Please Login or Register  to view this content.
    Last edited by mike7952; 09-13-2012 at 02:44 PM.

  8. #8
    Registered User
    Join Date
    09-23-2011
    Location
    buford, ga
    MS-Off Ver
    Excel 2007
    Posts
    9

    Re: Macro to change data entered in a cell seperated by a comma, into a vertical list of d

    the difference column is perfect. Can you remove the Entered ? column (i do not need it). And i noticed if there are 3 invoice numbers and 2 invoice amnts, the macro will error out. If it is easy for you to display a pop up box that shows where the error occured can you add it. If not, i understand. BUT this is perfect!! Thank you

  9. #9
    Forum Expert mike7952's Avatar
    Join Date
    12-17-2011
    Location
    Florida
    MS-Off Ver
    Excel 2007, Excel 2016
    Posts
    3,517

    Re: Macro to change data entered in a cell seperated by a comma, into a vertical list of d

    Yea no problem with the removing the column. I was assumming there would always be the same Amounts as Invoices. will there ever be More amounts then Invoices?

  10. #10
    Registered User
    Join Date
    09-23-2011
    Location
    buford, ga
    MS-Off Ver
    Excel 2007
    Posts
    9

    Re: Macro to change data entered in a cell seperated by a comma, into a vertical list of d

    those two fields are manually entered, so yes - there is the opportunity for error both ways

  11. #11
    Forum Expert mike7952's Avatar
    Join Date
    12-17-2011
    Location
    Florida
    MS-Off Ver
    Excel 2007, Excel 2016
    Posts
    3,517

    Re: Macro to change data entered in a cell seperated by a comma, into a vertical list of d

    So if they are different I can display a message box showing Company name or something, and the macro will stop and allow you to fix the issue? Then after you have fixed the issue you can retry running th macro? Is that what you mean?

  12. #12
    Registered User
    Join Date
    09-23-2011
    Location
    buford, ga
    MS-Off Ver
    Excel 2007
    Posts
    9

    Re: Macro to change data entered in a cell seperated by a comma, into a vertical list of d

    Yes, that is it

  13. #13
    Forum Expert mike7952's Avatar
    Join Date
    12-17-2011
    Location
    Florida
    MS-Off Ver
    Excel 2007, Excel 2016
    Posts
    3,517

    Re: Macro to change data entered in a cell seperated by a comma, into a vertical list of d

    Ok give this should do it.

    Please Login or Register  to view this content.

  14. #14
    Registered User
    Join Date
    09-23-2011
    Location
    buford, ga
    MS-Off Ver
    Excel 2007
    Posts
    9

    Re: Macro to change data entered in a cell seperated by a comma, into a vertical list of d

    Mike, you are offically a life saver!!! -- THIS IS PERFECT

  15. #15
    Forum Expert mike7952's Avatar
    Join Date
    12-17-2011
    Location
    Florida
    MS-Off Ver
    Excel 2007, Excel 2016
    Posts
    3,517

    Re: Macro to change data entered in a cell seperated by a comma, into a vertical list of d

    Your welcome.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

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