NelonTHAMelon
New Member
- Joined
- Mar 20, 2015
- Messages
- 2
Hello! I'm a somewhat crappy yet “experienced” user of Microsoft Excel, however my current project demands skills and knowledge with Excel I simply do not possess. I would like to apologize in advance for my overall lack of knowledge of proper Excel vernacular and for my inability to put what is in my brain into comprehensible words. However, I thoroughly appreciate any and all attempts to help me out.
I'm attempting to sift through a somewhat large CSV file of a production report that contains dates and their respective production of two different types of products. The way our system outputs data is in a CSV file with data thrown out in a messy jumble. I've opened this CSV file with Excel 2010 and of course, because this is a CSV file, all the data on the sheet is in the raw text format. It is outputted by day with the pounds of production of each product back to back in one gigantic file. In short, it’s chaos – semi-organized, unformatted chaos.
I’ve linked four images, each of the same example chunk of two day’s output, two how I’d like the end result to appear and the other two the raw output from the system.
Now for my possibly ridiculous requests:
I would like all the dates, which are in the MM/DD/YYYY style but are in a raw text format, to be converted to an excel-recognizable format – preferably Long Date. Regularly, this would be a breeze with the DATEVALUE function, CTRL-F and replace, or the Find & Select functions. However, the dates are thrown into the same column as non-date entries. The dates I’m referring to are bold and light yellow highlighted.
On top of that, I’d like to sum up separately, by day, the total production of the two different classifications of product, Rod (blue) and Strip (red). Preferably, formatted as displayed in the image with “Sum of Rod Production” (bold blue lower right with the sum) and “Sum of Strip Production” (bold red lower right with the sum) listed for each day. The “Fill Mix” is generally ignored in our production assessments. Rod is seemingly always in one nice chunk as highlighted in yellow with blue text. And the Strip is in two separate, adjacent chunks as highlighted in yellow with red text. Each entry appears in this fashion with different variations and numbers of products produced in each group with varying production quantities.
I am also looking for a faster way for Excel to help me change the text color and highlight chunks of the data so it is easier to view, again in the same fashion as how I’ve pictured.
Normally I would just suck it up and try to process all this data manually, but considering that there are thousands (10 years’ worth) of these entries I don’t want to be wasting my time nor my company’s time doing it the slow/dumb way if there’s a macro or VBA based solution. A million internet chocolate chip cookies and thanks to anyone who can help shed some light on my issue! Thanks for reading!</SPAN>
I'm attempting to sift through a somewhat large CSV file of a production report that contains dates and their respective production of two different types of products. The way our system outputs data is in a CSV file with data thrown out in a messy jumble. I've opened this CSV file with Excel 2010 and of course, because this is a CSV file, all the data on the sheet is in the raw text format. It is outputted by day with the pounds of production of each product back to back in one gigantic file. In short, it’s chaos – semi-organized, unformatted chaos.
I’ve linked four images, each of the same example chunk of two day’s output, two how I’d like the end result to appear and the other two the raw output from the system.
Now for my possibly ridiculous requests:
I would like all the dates, which are in the MM/DD/YYYY style but are in a raw text format, to be converted to an excel-recognizable format – preferably Long Date. Regularly, this would be a breeze with the DATEVALUE function, CTRL-F and replace, or the Find & Select functions. However, the dates are thrown into the same column as non-date entries. The dates I’m referring to are bold and light yellow highlighted.
On top of that, I’d like to sum up separately, by day, the total production of the two different classifications of product, Rod (blue) and Strip (red). Preferably, formatted as displayed in the image with “Sum of Rod Production” (bold blue lower right with the sum) and “Sum of Strip Production” (bold red lower right with the sum) listed for each day. The “Fill Mix” is generally ignored in our production assessments. Rod is seemingly always in one nice chunk as highlighted in yellow with blue text. And the Strip is in two separate, adjacent chunks as highlighted in yellow with red text. Each entry appears in this fashion with different variations and numbers of products produced in each group with varying production quantities.
I am also looking for a faster way for Excel to help me change the text color and highlight chunks of the data so it is easier to view, again in the same fashion as how I’ve pictured.
Normally I would just suck it up and try to process all this data manually, but considering that there are thousands (10 years’ worth) of these entries I don’t want to be wasting my time nor my company’s time doing it the slow/dumb way if there’s a macro or VBA based solution. A million internet chocolate chip cookies and thanks to anyone who can help shed some light on my issue! Thanks for reading!</SPAN>
Last edited:
