+ Reply to Thread
Results 1 to 18 of 18

How to edit then add together values in a CSV file

  1. #1
    Registered User
    Join Date
    12-26-2006
    Posts
    17

    How to edit then add together values in a CSV file

    Summary: This is an exported file from a paint colorant machine. I am interested in finding out how much colorant I use during a certain time period. This CSV file lists the ingredients used in columns named "Ingredient1, Ingredient2, etc.."

    Problem: I'm not sure how to add up these items, as there may be hundreds and the Ingredient name (Ax, B, D, I, etc) is not always in the same column. Also, I need to edit the amounts of the columns before they are added.

    For instance, the values are given in points, or fractions of an ounce. 1 point is 1/48th of an ounce. So 36 points is 36/48ths of an ounce. Ounces are listed as Y. So Y1 would be one ounce and one point, or 49 points. I believe it is easier to convert all the values with a Y to a point amount. For instance. 6Y would be 288 points. I could then add up all of the points of a certain ingredient and do a simple calculation.

    I have no idea where to start. Any ideas?
    Attached Files Attached Files

  2. #2
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, England
    MS-Off Ver
    MS 365 Subscription Insider (Windows 11 64-bit)
    Posts
    53,300

    Re: How to edit then add together values in a CSV file

    there are three rows of data in your sample file, but I don't see where you have mocked up what you want.

    Do you want a total point value for each row? Or are you looking to combine totals for rows where the ingredients are the same?

    Reattach the workbook showing the results you want for the sample data - mock these up manually, please.
    Ali


    Enthusiastic self-taught user of MS Excel who's always learning!

    Forum Rules (updated September 2018): please read them here.
    How to use the Power Query code you've been given: help here. More about the Power suite here.
    Don't forget to say "thank you" to those who have helped you in your thread. If you wish, you can also reward them by clicking on their reputation star bottom left.

  3. #3
    Registered User
    Join Date
    12-26-2006
    Posts
    17

    Re: How to edit then add together values in a CSV file

    Attached are what I would like calculated as a results.

    Here is how they are figured out.

    AX: Y = 48 shots, plus the 3 extra shots = 51
    B: (96 + 5) * 2 = 202
    C: (96 + 33) * 2 = 258
    D: 11.25 shots
    I: (96 + 39) * 2 = 270
    KX: (96) * 2 = 192
    V: 36 Shots
    Attached Files Attached Files

  4. #4
    Forum Contributor
    Join Date
    06-23-2021
    Location
    Romania
    MS-Off Ver
    2016,2019
    Posts
    240

    Re: How to edit then add together values in a CSV file

    Can the number of pairs (Ingredient and Amount) vary or there are only 6?
    Diana Tanase

  5. #5
    Registered User
    Join Date
    12-26-2006
    Posts
    17

    Re: How to edit then add together values in a CSV file

    There can be more than 6, in fact there are 13 total that will be in the full spreadsheet

  6. #6
    Forum Contributor
    Join Date
    06-23-2021
    Location
    Romania
    MS-Off Ver
    2016,2019
    Posts
    240

    Re: How to edit then add together values in a CSV file

    Please excuse my ignorance but, 2Y5.00 means 2*48+5?
    And question 2: the pairs always start from column Q?
    Last edited by tanasedn; 07-19-2021 at 08:45 AM.

  7. #7
    Registered User
    Join Date
    12-26-2006
    Posts
    17

    Re: How to edit then add together values in a CSV file

    Quote Originally Posted by tanasedn View Post
    Please excuse my ignorance but, 2Y5.00 means 2*48+5?
    And question 2: the pairs always start from column Q?
    Yes "Y" simply means 48 points, the rest of the data after the Y is also points.

    The First Ingredient is always in column Q, usually we only have a max of 5 ingredients used, rarely 6. However the file always lists 6 ingredient columns.

  8. #8
    Forum Contributor
    Join Date
    06-23-2021
    Location
    Romania
    MS-Off Ver
    2016,2019
    Posts
    240

    Re: How to edit then add together values in a CSV file

    I was able to do was to extract the list of ingredients and the corresponding values.
    From now on, I hope you can handle it yourself!
    In order to run the macro without errors, the Sheet with the table must be called "test" and you must have another sheet called "sheet1".
    HTML Code: 
    Sub consolidate()
    Dim lr, n As Long
    Dim s As String
    Dim vl As Range
    Dim s1, s2 As Worksheet
    Set s1 = Sheets("test")
    Set s2 = Sheets("sheet1")
    s2.Activate
    lr = s1.Range("a1").End(xlDown).Row
    n = CLng((s1.Range("xfd1").End(xlToLeft).Column - Range("q1").Column) / 2)
    
    s2.Cells(1, 1) = "Ingredient"
    s2.Cells(1, 2) = "Amount"
    s2.Cells(1, 3) = "Val"
    
        For i = 1 To n
            For j = 2 To lr
                s2.Cells(j + (lr - 1) * (i - 1), 1) = Trim(s1.Cells(j, 17 + (i - 1) * 2))
                s2.Cells(j + (lr - 1) * (i - 1), 2) = Trim(s1.Cells(j, 18 + (i - 1) * 2))
            s2.Cells(j + (lr - 1) * (i - 1), 3).FormulaR1C1 = "=IFERROR(LEFT(RC[-1],SEARCH(""y"",RC[-1])-1)*48+MID(RC[-1],SEARCH(""y"",RC[-1])+1,10),RC[-1])"
             
            Next
    
        Next
    
     End Sub
    Attached Files Attached Files
    Last edited by tanasedn; 07-20-2021 at 02:35 AM.

  9. #9
    Registered User
    Join Date
    12-26-2006
    Posts
    17

    Re: How to edit then add together values in a CSV file

    You are a genius! One question though, if the Ingredient column only had Y in it, it came over to the Val column as Y instead of 48. How would I fix that?

  10. #10
    Forum Contributor
    Join Date
    06-23-2021
    Location
    Romania
    MS-Off Ver
    2016,2019
    Posts
    240

    Re: How to edit then add together values in a CSV file

    HTML Code: 
    Sub consolidate()
    Dim lr, n As Long
    Dim s As String
    Dim vl As Range
    Dim s1, s2 As Worksheet
    Set s1 = Sheets("test")
    Set s2 = Sheets("sheet1")
    s2.Activate
    lr = s1.Range("a1").End(xlDown).Row
    n = CLng((s1.Range("xfd1").End(xlToLeft).Column - Range("q1").Column) / 2)
    
    s2.Cells(1, 1) = "Ingredient"
    s2.Cells(1, 2) = "Amount"
    s2.Cells(1, 3) = "Val"
    
        For i = 1 To n
            For j = 2 To lr
                s2.Cells(j + (lr - 1) * (i - 1), 1) = Trim(s1.Cells(j, 17 + (i - 1) * 2))
                s2.Cells(j + (lr - 1) * (i - 1), 2) = Trim(s1.Cells(j, 18 + (i - 1) * 2))
            s2.Cells(j + (lr - 1) * (i - 1), 3).FormulaR1C1 = "=IF(RC[-1]=""y"",48,IFERROR(LEFT(RC[-1],SEARCH(""y"",RC[-1])-1)*48+MID(RC[-1],SEARCH(""y"",RC[-1])+1,10),RC[-1]))"
             
            Next
    
        Next
    
     
    
    End Sub

  11. #11
    Registered User
    Join Date
    12-26-2006
    Posts
    17

    Re: How to edit then add together values in a CSV file

    That still brought over the Y into the Val column.

  12. #12
    Forum Contributor
    Join Date
    06-23-2021
    Location
    Romania
    MS-Off Ver
    2016,2019
    Posts
    240

    Re: How to edit then add together values in a CSV file

    I understand now. If you have only Y or 2y or whatever the previous formula returns those Y's
    Try this:
    HTML Code: 
    Sub consolidate()
    Dim lr, n As Long
    Dim s As String
    Dim vl As Range
    Dim s1, s2 As Worksheet
    Set s1 = Sheets("test")
    Set s2 = Sheets("sheet1")
    s2.Activate
    lr = s1.Range("a1").End(xlDown).Row
    n = CLng((s1.Range("xfd1").End(xlToLeft).Column - Range("q1").Column) / 2)
    
    s2.Cells(1, 1) = "Ingredient"
    s2.Cells(1, 2) = "Amount"
    s2.Cells(1, 3) = "Val"
    
        For i = 1 To n
            For j = 2 To lr
                s2.Cells(j + (lr - 1) * (i - 1), 1) = Trim(s1.Cells(j, 17 + (i - 1) * 2))
                s2.Cells(j + (lr - 1) * (i - 1), 2) = Trim(s1.Cells(j, 18 + (i - 1) * 2))
            s2.Cells(j + (lr - 1) * (i - 1), 3).FormulaR1C1 = "=IF(ISERROR(SEARCH(""y"",RC[-1])),RC[-1],IFERROR(1/(1/LEFT(RC[-1],SEARCH(""y"",RC[-1])-1)),1)*48+IFERROR(MID(RC[-1],SEARCH(""y"",RC[-1])+1,10)*1,0))"
             
            Next
    
        Next
    
     
    
    End Sub

  13. #13
    Registered User
    Join Date
    12-26-2006
    Posts
    17

    Re: How to edit then add together values in a CSV file

    In Sheets1 when the amount is 0y16 it is calculating the value as 64 which would be 1y16. The 0Y16 should display only 16.

    Also, are we able to add all of the ingredients up so it only has one total value for AX,

  14. #14
    Forum Contributor
    Join Date
    06-23-2021
    Location
    Romania
    MS-Off Ver
    2016,2019
    Posts
    240

    Re: How to edit then add together values in a CSV file

    I hope I have evaluated all the conversion possibilities.
    I also inserted a Pivot Table to aggregate quantities.
    Attached Files Attached Files

  15. #15
    Registered User
    Join Date
    12-26-2006
    Posts
    17

    Re: How to edit then add together values in a CSV file

    Quote Originally Posted by tanasedn View Post
    I hope I have evaluated all the conversion possibilities.
    I also inserted a Pivot Table to aggregate quantities.
    You are awesome! Thank you so much!

  16. #16
    Forum Contributor
    Join Date
    06-23-2021
    Location
    Romania
    MS-Off Ver
    2016,2019
    Posts
    240

    Re: How to edit then add together values in a CSV file

    I did a code review and created a YToVal UDF function that can be used directly in the spreadsheet for testing. Please test it and tell me how it works.
    Attached Files Attached Files

  17. #17
    Registered User
    Join Date
    12-26-2006
    Posts
    17

    Re: How to edit then add together values in a CSV file

    I get an error when I try it with my full spreadsheet. I attached the full spreadsheet.
    Attached Files Attached Files

  18. #18
    Forum Contributor
    Join Date
    06-23-2021
    Location
    Romania
    MS-Off Ver
    2016,2019
    Posts
    240

    Re: How to edit then add together values in a CSV file

    What about now?
    Attached Files Attached Files

+ 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. Edit HTMl File with values from a CSV using a Macro
    By Santy4tas in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 05-31-2021, 10:01 PM
  2. How to update file without clicking the EDit Links Update Values
    By A9kurs in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 02-26-2020, 11:48 AM
  3. Browse for a file edit the file then attach it to an email
    By Bobbbo in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 10-06-2016, 11:50 PM
  4. VBA edit to Update File Automatically based on Name and Date of File
    By Ngutierr in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 12-04-2015, 01:15 PM
  5. Copy, edit and save workbook to same location as source data file, not macro file.
    By Jasonhouse in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 04-02-2014, 09:01 AM
  6. Importing text file, only option to edit existing file
    By smokey99 in forum Excel General
    Replies: 8
    Last Post: 04-26-2006, 04:08 PM
  7. Replies: 1
    Last Post: 01-24-2006, 12:10 PM

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