# How to edit then add together values in a CSV file

1. ## 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?  Register To Reply

2. ## 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.  Register To Reply

3. ## 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  Register To Reply

4. ## 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?  Register To Reply

5. ## 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  Register To Reply

6. ## 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?  Register To Reply

7. ## Re: How to edit then add together values in a CSV file Originally Posted by tanasedn 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.  Register To Reply

8. ## 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``````  Register To Reply

9. ## 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?  Register To Reply

10. ## 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``````  Register To Reply

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

That still brought over the Y into the Val column.  Register To Reply

12. ## 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``````  Register To Reply

13. ## 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,  Register To Reply

14. ## 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.  Register To Reply

15. ## Re: How to edit then add together values in a CSV file Originally Posted by tanasedn 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!  Register To Reply

16. ## 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.  Register To Reply

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.  Register To Reply

18. ## Re: How to edit then add together values in a CSV file  Register To Reply