dcunningham
Board Regular
- Joined
- Jul 14, 2015
- Messages
- 58
Hi Everyone,
So I have a series of columns. One contains a time stamp for a data point, the next is the data point, the one following that is a conversion from decimal to binary for that data point, and the remaining columns are that binary string split into each bit. It looks like this: Gyazo - b7c81a4c0081d207cecde858765bff68.png.
From that image you can see that each column has a title, No Engine Speed, Engine Derate, etc.
Here's what I'd like to do, but don't have the skill with VBA/Excel to do. I'm trying to take all that information and put it into something that's more friendly to a reader. Here's what I have in mind for that: Gyazo - 2d4e46e66f266f4dc0f2e3ace74343ce.png.
The description comes from the column titles, and the time range start/end would come from the first column. The error codes I'll get with an Excel IF function based on the contents of the description column. What I need is a VBA code or set of Excel functions that will populate those columns for me based on the contents of those bit columns. How I envisioning it working is as follows: each bit column is searched through, if a 1 is found and the four cells more more below it are also found to be 1s the date stamp of the first 1 and final 1 populate the Start/End times in the more readable report I'm creating. In addition to this, the column header is copied to the description field. So this: Gyazo - cdecc513525531c58751bd288da21505.png, would get me this: Gyazo - eb664abb7d5fe74e52790e638b644db9.png.
The reason I want to only get the time stamps if there are five or more 1s consecutively is that I want some time to pass before the state is considered to be an 'event'. A second condition I'd like to meet would be that the time stamps are reasonably close together, which is why I omitted the '16-May-15 21:52:47' even though there was a one in the column.
The numbers next to the time stamps (and the timestamps themselves) will change depending on when the user opens the workbook. Those columns are the result of a query to a database and change based on what the previous shift's start and end time were. As a side note, when copying the time stamps I know that you need to paste the cell value and not just a regular paste, otherwise you wind up copying a query array to the database. I don't know if that has any bearing on the coding solution but I thought it was worth mentioning.
Any guidance you all can provide would be greatly appreciated, I feel like I have an idea of how to do this but I'm new to VBA and my ability to code with it isn't up to the task just yet. I hope I was clear in my explanation of what I'm trying to do, feel free to ask questions if I wasn't.
Thanks,
Dan
So I have a series of columns. One contains a time stamp for a data point, the next is the data point, the one following that is a conversion from decimal to binary for that data point, and the remaining columns are that binary string split into each bit. It looks like this: Gyazo - b7c81a4c0081d207cecde858765bff68.png.
From that image you can see that each column has a title, No Engine Speed, Engine Derate, etc.
Here's what I'd like to do, but don't have the skill with VBA/Excel to do. I'm trying to take all that information and put it into something that's more friendly to a reader. Here's what I have in mind for that: Gyazo - 2d4e46e66f266f4dc0f2e3ace74343ce.png.
The description comes from the column titles, and the time range start/end would come from the first column. The error codes I'll get with an Excel IF function based on the contents of the description column. What I need is a VBA code or set of Excel functions that will populate those columns for me based on the contents of those bit columns. How I envisioning it working is as follows: each bit column is searched through, if a 1 is found and the four cells more more below it are also found to be 1s the date stamp of the first 1 and final 1 populate the Start/End times in the more readable report I'm creating. In addition to this, the column header is copied to the description field. So this: Gyazo - cdecc513525531c58751bd288da21505.png, would get me this: Gyazo - eb664abb7d5fe74e52790e638b644db9.png.
The reason I want to only get the time stamps if there are five or more 1s consecutively is that I want some time to pass before the state is considered to be an 'event'. A second condition I'd like to meet would be that the time stamps are reasonably close together, which is why I omitted the '16-May-15 21:52:47' even though there was a one in the column.
The numbers next to the time stamps (and the timestamps themselves) will change depending on when the user opens the workbook. Those columns are the result of a query to a database and change based on what the previous shift's start and end time were. As a side note, when copying the time stamps I know that you need to paste the cell value and not just a regular paste, otherwise you wind up copying a query array to the database. I don't know if that has any bearing on the coding solution but I thought it was worth mentioning.
Any guidance you all can provide would be greatly appreciated, I feel like I have an idea of how to do this but I'm new to VBA and my ability to code with it isn't up to the task just yet. I hope I was clear in my explanation of what I'm trying to do, feel free to ask questions if I wasn't.
Thanks,
Dan
Last edited:
