Reducing Resolution Of Worksheet Data

Jan 16, 2008

I have a piece data acquisition hardware which stores data every 5 minutes into an excel sheet, I need to reduce the resolution of the data to every 30 minutes, currently I am doing this by deleting five rows leaving one and repeating the process, there is however 9285 rows
I am assuming there is a macro which can do this far quicker than me.

View 9 Replies


ADVERTISEMENT

Interpolating Data At 5-minute Resolution Down To 1-minute Resolution

Nov 27, 2013

Entering a formula which will allow me to expand my data set by interpolating between sets of x,y values. My data set is in 5-minute resolution and I would like to interpolate so that I can output a new set of x,y values in 1-minute resolution. Here is my example of what I have and what I would like my end result to be, and also I have attached a sample of my data set in the below excel file:

Original data set
Date TimeTemperature oC
6/12/13 13:30 18.28
6/12/13 13:35 17.9
6/12/13 13:40 17.9

Desired data set
Date TimeTemperature oC
6/12/13 13:30 y
6/12/13 13:31 y
6/12/13 13:32 y
6/12/13 13:33 y
6/12/13 13:34 y
6/12/13 13:35 y
6/12/13 13:36 y
6/12/13 13:37 y
6/12/13 13:38 y
6/12/13 13:39 y
6/12/13 13:40y

datetime_temp.xlsx

View 2 Replies View Related

Data Elimination- Resolution

Jul 17, 2009

I produce a report each month that requires manual manipulation to get to the end result. Because I have yet to find a macro or formula that can assist me in reducing the data for what I am looking for

Emp Name
Project Code
PRoject Name
LevelRevenueLbr RevenueLbr CostExp CostProfitVarianceRoy, Marcus L.
Project1
PORT RAIL
CM-89.530.000.000.00-89.53-89.53Roy, Marcus L.
Project1
PORT RAIL
PM293.600.000.000.00293.600.00Roy, Marcus L.
Project1............................

View 9 Replies View Related

Retrieving Variable Rows From Another Worksheet: Reducing "Calculation Time"

Aug 10, 2009

I'm using this formula to pick all rows from a huge range of cells "DATA" in another sheet, by matching the first column value (SAPDATA is the range that contains all the first column cells of DATA).

{=IF(ROW($B49)-ROW($B$48)>COUNTIF(SAPDATA,$D$4),"",INDEX(DATA,SMALL(IF(SAPDATA=$D$4,ROW(SAPDATA)),ROW($B49)-ROW($B$48)),2))}

All possible first column values have been stored as a dropdown in cell D4, and whenever I change the choice in the dropdown cell, the retrieval of those hundreds of rows takes ages in excel.

View 9 Replies View Related

Closed - No Resolution- Lookup Formula

Apr 16, 2009

I have a two worksheet file.

On one worksheet I have every day of the year (work day) and corresponding data for many topics for that day in the column.

On the other worksheet I want the user to type in a date and have the data for that day from the first worksheet display in a specific cell (based on the date entered at the top of the sheet) on the second worksheet.

I think it is some type of lookup formula but am not sure.

Enter todays date on one worksheet and have data display based on that date from a second worksheet. The data on the second worksheet would be manually updated every day.

View 10 Replies View Related

Auto-Adjust Workbook To Screen Resolution

Nov 6, 2012

I am using code to auto adjust screen resolution when opening a workbook so that my workbook will span that computer screen of anyone opening it.

How to taylor it specifically for each worksheet as they differ in size in terms of what cells are used??

Code:

Private Sub Workbook_Open()
Dim Vis_Rnge As Range
Dim Data_Rnge As Range
Dim X_Fact As Double
Dim Y_Fact As Double

ActiveWindow.Zoom = 100

[Code] ..........

View 1 Replies View Related

Set Window Size Based On Screen Resolution

Mar 5, 2008

I have an Excel based application that needs to run with a screen resolution of 1024*768 in order to display correctly. Is it possible to use VBA to detect the users screen resolution. Then: If the screen resolution is not set to 1024*768 then resize Excel's main window to run at that width and height?

View 2 Replies View Related

Auto-Adjust Screen Resolution When Opening A File

Sep 18, 2007

I have made an excel spreadsheet. The area with data exactly fills my screen.

When I send it to other colleagues, it perfectly fills their screen too. But for some colleagues the data area is either too big for their screen or takes up a small proportion of their screen based on their screen resolution. I would like the area with data to perfectly fill the screen of all people who will access the file.

I am thinking that I would do an automatic event so that I can change the settings upon opening the file. I have Excel VBA programming for Dummies which explains about automatic events but I have no idea what sort of sub procedure to use.

View 2 Replies View Related

Reducing List To One Of Each Type?

Jan 17, 2014

Supposing I have a list where each data set appears multiple times. How do I by formula make a list of the unique values?

I've uploaded to show what I mean.

View 11 Replies View Related

Reducing File Size

Sep 19, 2012

I have a spreadsheet which is currently about 10 MB, I would like to reduce this as much as possible.

I am using lots of Vlookups but on each I am only referencing the data I need, there are also quite a few macros.

I paste value all the formula I do not need.

View 5 Replies View Related

Reducing File Size

Jul 3, 2006

I don;t know if there is a way around this but we have a Excel workbook that holds around 14 worksheets taht is getting too large. It currently is around 2.5mb and we constantly use it as a template which is completed and emailed. Sometimes we have to email 4 of these all together and so teh email is too large, even zipping them has next to no effect on the size. The sheet that seems to causing the problem is one that has around 500 lines of formula across 20 columns. Most of these fields have conditional formatting and its this taht is boosting the size.

View 6 Replies View Related

Reducing A Date To A Single Number

Mar 30, 2007

I would like to take a date and reduce it to single number. Eg.

Reduce the day to a number. Reduce the month to a number. Reduce the year to a number.

Add the 3 together. This gives you a number.

Reduce this number to a number.

e.g. today's number date 30 / 03 / 2007
Day is 30
Month is 3
Year = 9

Sum of these is 15 which reduces to 6.

3+0+0+3+2+0+0+7=15
1+5=6

Basically 9 is the highest number. The number 13 would be 1+3=4 and so on. The idea is to return a single number between 1-9.

View 14 Replies View Related

Reducing Width Of Command Button

Jan 8, 2012

i want o make some command buttons on a sheet with the width 50 and when you move the mouse over to any one the width of that one should increase to 300. when you move he mouse over to the other one the size of the previous one should return to 50 and the new one (now under the mouse) should be 300.

i have written the code for it how ever it tends to increase the width twice i.e. it will increase the size of the command button, then reduce it to 50 and increase it again. this action makes it look like it flickering

Code below

Code:

Option Explicit
Dim sShapes As Shape
Dim ActShapes As Shape
Dim wsStart As Worksheet, WsNew As Worksheet
Dim wd
Dim mybutton
Dim ShapeChk As Boolean

[Code]....

View 2 Replies View Related

Reducing Number Of If Statements In Code

Sep 3, 2006

i have a simple if statment

If cboname.Value = Sheets("Data Sheet"). Range("E2") Then
txtpay.Value = Sheets("Data Sheet").Range("F2")
End If

this need to be done for a large list of names and input into another sheet. what i wanted to know was if there is a quicker way of doing this code without repeating the code loads of times

View 9 Replies View Related

Calculating Reducing Daily Pro Rata Amounts

Dec 5, 2011

Here's my problem.

Charter default premium is a calculated as a rate on outstanding charter hire.

Therefore if charter period is 1 year and daily hire is 20,000, outstanding charter hire (policy limit) at inception would be 20,000 x 365 = $7,300,000

However, by day 365 outstanding charter hire and prevailing policy limit would only be 20,000 x 1 day = $20,000

The premium is calculated on a daily pro rata basis on the reducing outstanding amounts.

I've calculated the premium on the attached spreadsheet assuming hire is monthly not daily, but it would be too laborious to try and calculate that for 12 months or more on a daily basis. What I'm hoping is that there's a formula, covering the range of data, presumably using the calculation on the first day and the last day, but which cuts out all the intermediate steps.

View 3 Replies View Related

Combobox And Radio Button Reducing In Size

Sep 17, 2012

I have an issue with a combo box and radio button.

Anytime I click on either of them, they reduce in size.

It's as if it is reducing by the same ratio every time until you can't click on it anymore.

View 3 Replies View Related

Reducing Multiple Rows - Duplicates In Column A

Nov 13, 2006

I can't figure out a solution.

4 columns, thousands of rows.

Column A has alphanumeric data that can appear, once, twice, 3 times or 4, but never more than 4.

I want to see this spreadsheet so that each piece of data in column A is presented only once.

The information in the other 3 columns isn't important for what I want to do, so I need to reduce 20000 + rows, which includes many duplicates in Column A to about 5-60000, but need help figuring the correct formula/macro ....

View 9 Replies View Related

Reducing Decimal Points Without Rounding Up The Numbers

Jul 25, 2006

I have columns of geological data in number form which may have about 4 or 5 decimal points. I want to reduce them to 2 decimal points without rounding the numbers up. Is there a simple way to do this?

View 4 Replies View Related

Reducing Balance Asset - Get Back To Purchase Date?

Dec 20, 2012

i am trying to get back to the original purchase date of an asset.

It has being depreciating using the reducing balance method.

I have the Net book value of the asset currently, the original cost and the depreciation rate.

However i do not know the quickest way in excel to work back to the original purchase date.

See attached file for examples and my long workaround to estimate purchase date.

View 1 Replies View Related

Excel 2007 :: How To Create Dropdown List But Options Reducing After Every Selection

Aug 17, 2011

Is it possible to do a drop down list that allow user to select pre-defined selections but everytime when a selection is chosen, the list reduced?

Example,

I have can choose Apple, Orange, Durian and Mango.

The drop down can only select these 4 fruits.

So there are 4 cells in Column A1, A2, A3, A4.

A1 selects Mango and then A2 will only have 3 options to choose. So until the last cell, he can only choose the last fruit left.

A1, A2, A3, A4 are not selecting in descending order thus it could be A3 selecting first, then A2 and then A4.

I'm using excel 2007

View 13 Replies View Related

Search Worksheet For Data In Multiple Textboxes On Userform - Display In Temp Worksheet

Dec 23, 2013

I have a workbook with 4 worksheet that store different type of data. It also has a userform that load at start of the application which is to search the data in the workbook. The userform has a combobox where the names of the sheets are stored. when the user selects say Sheet2 in the combobox, it enables the relevant textboxes on the userform and activates the worksheet at the change event. The userform has a search button that searches all the worksheets based on the text entered in a textbox.

The problem: how to search based on 1 textbox. What I want is: say for e.g the end-user selects sheet2 from the combobox, this intern enables 4 textboxes (Name, DOB, Nationality, ID #) on the userform. The end-user should have the liberty to enter data in 1 and/or any of the textboxes. The search should be performed, that if data is only in 1 of any of textboxes then give all rows that fit that criteria and display in a temp worksheet. if say the name and dob is filled by the user than what matches both should be displayed in a temp worksheet. if say dob, name and ID# given so the search button should narrow down to fit all 3 criteria and then display result in temp worksheet. As if mentioned data can be entered in either just 1 or any or all textboxes.

E.g. the worksheet is (Columns are Name, Nationality, DOB, ID#)

row 1 = name: Steven Martin, DOB: 27-may-1993, Nationality: Trinidad & Tobago, ID #: 1234567
row 2 = name: Gary Richards, DOB: 2-FEB-1993, Nationality: British, ID #: 456789
row 3 = name: David Cohen, DOB: 27-May 1993, Nationality: American, ID #: 98765
row 4 = name: Roberto McDonalds, DOB 21-Jul-1962, Nationality: British, ID # 654321
row 5= name: Gary Richards, DOB: 01-Dec-1978, Nationality: Australian, ID # 1234567

Now if the user enters only name as "Gary Richards" and search then row 2 and 5 should be displayed in a temp worksheet. if user enters name Roberto McDonald and ID# 1234567 then it should not display anything. if user enters DOB 27-may-1993 and nationality British and ID # 1234567 then as well shouldn't display anything and should a msgbox "no data found".

View 4 Replies View Related

Create A Separate Worksheet That Has The Data In And Reference Cells On Budget Worksheet?

Feb 7, 2014

I need to reference the projected and actual expenses from the total on worksheet 'expenses' So i did so, however, if I change the drop down on the expenses worksheet to only display housing data, then the projected and actual expenses on my budget worksheet changes as well to the new data portrayed on the expenses worksheet.

I need to reference the cell, without it changing when I change the category display, but I need the cell to change accordingly if I enter new data in the overall tablet on the expenses worksheet.

Or do I need to create a seperate worksheet that has the data in and reference my cells on my budget worksheet to that new worksheet?

View 1 Replies View Related

Comparing 2 Lists And Counting Data From The First Worksheet To Fill Up A Second Worksheet

Aug 22, 2014

I've got a spreadsheet with 2 worksheets in it.

On the first one we've got:

Name of the agent | petition REf num | Task

Each petition can generate several tasks, one line per task.

John Doe | XXXX-YYYY | NCO
John Doe | | RIL
John Doe | XERT-WWWW | RMT
Jane Doe | QSZE-AQWC | RIL

On the second worksheet:

Complete list of agents | number of petitions | Status

John Doe | 2 | OK
Jane Doe | 1 | [BLANK]

I want to be able to fill in the second worksheet automatically. For each agent in my worksheet 2, I want to check if they appear in worksheet 1 and if so count the number of petitions related.

I don't know where to start

View 6 Replies View Related

Macro Which Saves Data From Temp Worksheet To Customers Worksheet?

Mar 17, 2013

Dropbox - Final.xlsm

Here is the above link. Am looking at a button which saves whatever is the temp worksheet row in the customers worksheet. The temp worksheet basically takes the data from the Quotations worksheet and places it in a row.

View 9 Replies View Related

Code To Auto Input Worksheet Name From Data Copied From That Worksheet

Feb 23, 2014

I am copying data from worksheet "Microsoft" to another workbook and paste in sheet1, i want the cell G1 to auto input the worksheet name "Microsoft" where i copy the data from,

How to have G1 show the worksheet name after i copy and paste the data from worksheet name "Microsoft".

View 6 Replies View Related

Moving Data From A Monthly Worksheet To A Master Worksheet

Apr 9, 2014

Each month I get a report that is formatted by the customers and this sheet never changes. The problem is that the master sheet my job uses is not formatted this way. The master workbook has several sheets that breaks down the data from the monthly sheet. Instead of having to enter the data manually I'm looking for a way to export the data from the monthly sheets into the master workbook. As I stated the sheets are all the same but come from different providers and all contain the same amount/style of data.

View 1 Replies View Related

Autofilter Another Worksheet Data Based On Cell Value In Another Worksheet?

Aug 13, 2014

I have a 'Summary' worksheet which is shown as:

August
September
October

Example 1
1
4
5

The table, which works correctly, looks in another worksheet 'Report' for 'Example 1' within the date range of August. This uses COUNTIFS.

Ideally, what I'm wanting to do is have an auto-filter on each of the cells - 'Example 1', 'Example 2', so if a user wants to see what rows on the 'Report' sheet, falls into this criteria, it auto filters the other worksheet, and takes them too it.

The action would be - Click on the cell of 'Example 1', the macro auto-filters based on the cells value in column F of the 'Report' sheet, and takes the user there. The macro would have to take into account the date filter as well, which uses cells A1,B1 for August, and C1,D1, for September.

View 1 Replies View Related

Populating One Worksheet With Data From Other Worksheet Based On Criteria?

May 22, 2013

I have two sheets in one workbook; one has a list of names in column A (136 of them) and another is a master list with all the names of people who work for the company (over 2000). Sheet 1 and Sheet 2 respectively. I need to populate columns B-E in Sheet 1 with the data in columns B-E in Sheet 2, but only for the names that match in column A. I've tried applying filters but can't get to the data I need for some reason. The names are formatted the same way in both sheets (Last, First). In Sheet 2 the names are links but in Sheet 1 they aren't; not sure if that's important.

View 7 Replies View Related

Copy Data From One Worksheet To The Alternate Line In Another Worksheet

Jun 19, 2013

I would like to copy the data from one worksheet to the alternate row in another worksheet as follows. I've attached a file showing the source file and the desired output.

1. Copy column B's data in worksheet "Working" to worksheet "Upload file" Column V. B2's data goes to V1, B3's data goes to V3, B3's data goes to V5, so on and so forth.

2. Copy column H's data in worksheet "Working" to worksheet "Upload file" Column F. H2's data goes to F1, H3's data goes to F3, H3's data goes to F5, so on and so forth.

The number of rows with data in worksheet "Working" varies. It could be 30 lines one time and over 100 lines another time.

View 2 Replies View Related

Creating Macros To Transfer Data From Worksheet To Worksheet?

Mar 26, 2014

transferring data from a worksheet (Passdown Report) to another worksheet (Data Base) located in the same workbook. In the source worksheet (Passdown Report) there are 2 cells (B2 and D2) in which I would like the data to be transferred along with the data from B4 to AQ33. All the cells contain a formula which I want to stay after the information is transferred to the target worksheet (Data Base). This will be a daily transfer to the target worksheet (Data Base), so the macros should also identify the next available open row to transfer the data to.

View 1 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved