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?

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.

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

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?

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

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?

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.

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``````

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?

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``````

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

That still brought over the Y into the Val column.

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``````

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,

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.

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!

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.

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.

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

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

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