I am having trouble finding an efficient way to expand a set of data that I have by adding another column to it.
Look at attachment : help.xlsx
I can do it manually but I have 5000+ rows of data that I need to selectively expand do accommodate the data from the new data set.
IS there a way to filter a dataset using the criteria in a hidden column? Auto-Filter will not allow this, and Advanced Filter is not user-friendly for the end-user of this file.
View 2 Replies View RelatedData manipulation question here: how to dynamically filter and sort a multi-column data set? My end goal is to be able to (1) quickly collapse all columns into one single column, (2) remove all duplicates, and (3) sort the information in ascending order. A reference sheet is attached in case it's useful.
View 5 Replies View RelatedTrying to add a vlookup to a data set and cannot remember how to do.....
I have a table with State and Suburbs. I want to look at both these within another sheet to ensure the post code is correct......
In the attached I want to show in Sheet1 Column D the post code from Sheet2. As there may be suburbs with the same name within Sheet1 I need to ensure the correct Postcode for the suburb and the state.
(Checking manually entered data....)
Exceltest.xlsx
I have a grid that I need to lookup and return the project hours for each system from the data set. The grid has the projects listed in the rows and the systems listed across the columns. The data set has a list of each projects systems hours. I am having problems with the formula when the data set has multiple records (multiple systems) for each project. It seems very simple, but I just can't get my arms around it. Please see the attached for example.
View 7 Replies View RelatedI have a set of 5,800+ data points between 0 and 1 that I would like to multiply together. When I use PRODUCT for the whole set, the formula returns 0. However, I can use a smaller subset of the data to return a very small number. I'm curious if Excel has a closest-number-to-0 or number-of-cells-for-PRODUCT limitation. Is there another way to perform this calculation?
View 4 Replies View RelatedI was recently helped very much by the forum moderator and code from D_Rennie in a file to transpose about 25,000 rows into a few organized columns. The code worked very well. I have a slightly new twist on the problem. I have uploaded a different file with a different data structure in the columns but the same need for the data to be transposed to the columns under the same headings. This VBA worked very well before, but I can't seem to re-use it for this new dataset.
View 8 Replies View RelatedI have a spreadsheet which has general usage and summary data at the top for ease of use. Begining at Row 15 the data is a standard table which includes columns for the following.
Cust Name | Status | Loan Amt | etc.
I have created a macro to make it easier to add new data rows at the top of the data already in the file (this makes it easier to view with the newest data at the top). I would like to create a conditional sum of the data in "Loan Amt" if the value in "Status" ="Active". I had no problem achieving this with the conditional sum tool but the problem is that the range changes whenever a new row is added to the top of the list. Unfortunately the range in the conditional sum adjusts as the old data shifts down for the new row and the sum does not contain the new row data.
I have to do a search on a dataset (about 3000 entries) and part of the exercise is to look in some project titles if there is a country or other key words included.
I have look on the web and unable to find a formula that works. Basically what I want to do is to have a formula a looks in the column of titles for a list of keywords on another sheet in the same book.
a macro (that i will link to a control button) that will autofilter a data set. The problem i have is the macro i wrote below, It might not be the best looking macro in the world, but it would work for my purposes if i can get the part that does the autofiltering to be more dynamic. meaning, instead of a hard coding "Retail" in the macro, id like it to reference a cell so that the user can type whatever they want, then click the button and it will filter based on what they type in.
this is what i have
Sub Filter_Button()
With Sheet2
AutoFilterMode = False
Range("A6:M6").AutoFilter
Range("A6:M6").AutoFilter Field:=2, Criteria1:="Retail"
End With
End Sub
I want to create a simple data base of times but it must be as I have in col A .
I am not able to change from using these : .
I have tried scrolling down but when I get to .99 it rolls over to .100
My data starts at 44.00 seconds and wanting to go up to 9:00.0 minutes .
GOOD A1TIME259.97359.98459.9951:00.0061:00.0171:00.0281:00.0391:00.0410
All the way up to 1:00.97111:00.97121:00.98131:00.9914
Then next is 1:01.0015Then next is 1:01.01 16Then next is 1:01.0217
Then next is 1:01.0318Then next is 1:01.0419Then next is 1:01.0520 21
Wanting to go all the way to 229:00.0 and end there
I am looking for a macro that is capable of deleting all rows in a dataset where the following statements are not true: column B is equal to "OP00" (o, p zero zero), the left hand character of C is equal to "L" and D is equal to "CC", as in the scenario below. I basically need to keep all rows which match the structure below, i.e. to clean the data.
B C D
OP00LKAOCC
OP00LMRPCC
OP00LVOFCC
OP00LVOFCC
OP00LVOFCC
OP00LVOFCC
I need to capture the first date that the data appears for a long list of SKUs. I've downloaded the data to have SKUs in a row and dates in the column (consumed over 200 columns).
Would there be a systematic way to generate the first date the data appears instead of having to look at each row, get to the nearest data by using shirt right arrow and then manually type in the corresponding date?
I have 26 data points, and I want to be able to change those data points by a certain percentage, defined by a separate single Cell value
Now here's the tricky part: I want some data points to go up by the defined percentage, and some data points to come down by the defined percentage. Which data point goes up and which comes down, can be totally random.
Is there a way of doing this by means of a formula, or is it a VBA job?
This is the top of my dataset:
1 1 8 1,9 1 1 9 0,2 1 1 10 0,1 1 1 11 1,4
this is the end of my dataset:
30 11 19 0 30 11 20 0 30 11 21 0 30 11 22 0,5 30 11 23 0,6 1 12 0 0,5 1 12 1 0,2 1 12 2 0 1 12 3 0 1 12 4 0,7 1 12 5 0 1 12 6 0 1 12 7 0
First column should be day; 2nd column should be month; 3rd column should be time; 4th column should be value.
the dataset is arranged like that.
every row is one hour. but the dataset doesn't start with 0:00 am, every day starts with 8:00 am. so the dataset ends with 7:00 am.
first data are the months january, february, december of one year(winter). after that those three months from the next year follow. this goes on 30 years.
after winter follows spring(march, april, may). also for 30 years. then summer(june, july, august) and autumn follows, for 30 years too.
i have many different datasets. but everyone is about 30 years (262944 rows depending on the intercalary years).
now i would need a macro which adds the years and sorts the dataset. but i need to choose the years by my own(30 years from 1951-1980, or from 1961-1990, 1971-2000... and so on).
I have a large patient data set consisting of one column that includes their zip codes and one column that includes the number of times they were seen at a hospital over a period of time. I want to try to consolidate the data so I just have the number of total cases (for all patients) in a certain zip code over that time period. There are several thousand patients and it would be great to avoid doing it manually.
View 1 Replies View RelatedI currently have a data set for prices per metre on a range of products. I'm needing the ability to switch the data from "per metre" to "stock length" prices within the same cell range so my equations still work. I'm envisaging a drop down list which simply switches between the values for each. How I would implement this so 1 cell can have value A or value B applied to it depending which option from the drop down list was selected?
View 3 Replies View RelatedI am working on making a spreadsheet that will rack which guests are using which membership for a certain client. Here is what I've got so far. My goal is to have the spreadsheet work like a calendar where the dates are changing daily as well as all the information with it. I was able to make the dates change, but I am unable to have the specific data change with them. Is there a way to set a column of information to a specific date?
View 5 Replies View Relatedwe use # of days per disbursement as a performance measure to ensure that we are providing out grantees with the appropriate amount of service. I keep a tracking chart that I manage with overseas partners that use these dates to prioritize the 30+ grantees in their portfolio at any given time. It would be great if this # of days to disbursement #1, disbursement #2, etc could automatically pull to show them who they have neglected.
"Sheet 1" = Overview sheet to see general information (where I'm trying to pull to)
"Sheet 2" = table to track information as the disbursements or other actions are processed per grant
Column A (on both sheets) gives the grant reference
Column B (data entry sheet) gives the date the payment was sent
When I do =SMALL(('Sheet2'!B:B),2), I get the 2nd smallest in the whole sheet, but then when I try to make an IF function to tie it to the specific grant...
=IF('Sheet2'!A:A,A2,SMALL(('Sheet2'!B:B),2)) --> this gives me a 1905 date
I've tried a bunch of different formulas and tried reformatting the dates... but I'm having very little success...
I've attached a file that is the result of a product forecast.
What I'd like to do is minutely adjust the data in B4:K12 so that the totals shown in blue colour on Row 15 and Column O are respected.
Book1.xlsx
I have a database in sql server i want to make a excel form where in a user will enter the data and print the excel file
E.g.
S.NoDESCRIPTIONPART NO
I want a user to write the item description and data should come directly from database .
I have a code that generate some combinations for a specific sum from a given data set. This code has two errors.
1. code does not look into data. it generates
2. Code generate those combinations for which sum is greater than target value.
E.g. first combination code generates is 3+7+10+12+12
This wrong by two means sum is
1) 44 while we need 40
2) 12 is not in data.
I want to keep the raw data on one sheet, and have a graphical representation of that data on a second sheet. On the second sheet I would like the user to be able to filter the data on the first sheet which will update the graph as filters are applied.
Before I start trying to code something in VBA, is there a way to display the filter at the top of the data on the first sheet on my second sheet? So I would have an exact replica of the filter cell on the first sheet functioning exactly the same way, but on the second sheet?
I'm working with binned data. That is, something like this:
Value | Count
0.01 | 128
0.02 | 278
0.03 | 350
...
I found a way to get percentiles from binned data using an array formula here:
Calculating percentiles from bin/frequency data
I am wondering if there is a quick way to get the percentrank of a specified value when compared against my binned dataset.
I have a problem and I can't figure out how to do this, I have tried using both macros and functions (INDEX for example). The problem is as follows, I have a dataset of 27 worksheets, each worksheet has between 30k and 60k of rows and 25 columns. They are set up as follows:
------------------1990 1991 1992 etc.
Firm_A-Variable_1
Firm_A-Variable_2
|
|
|
Firm_B-Variable_1
Firm_B-Variable_2
|
|
|
etc.
Now what I am looking for is an easy way to transpose the data, I would like to have it looked as follows:
-------------Variable_1 Variable_2 Variable_3
Firm_A 1990
Firm_A 1991
Firm_A 1992
|
|
|- ---- 2012
Firm_B 1990
Firm_B 1991
Firm_B 1992
|
|
| ------2012
etc.
It is basically impossible to do this by hand, each of the 27 worksheet has between the 3000 and 6000 firms and each firm has 57 variables (these are identical for all firms). Also the the firm names and the variable names are in the same column, these should be seperated as well (they are connected with a hyphen).
From time to time a have columns of numeric data that I want to sum on the last row. Occasionally, I like to sort Top to Bottom. I have always wondered how to stop the bottom row or total column from moving directly to to the top?
View 1 Replies View Related1994
1995
1996
1997
1998
1999
2000
2001
2002
2003
2004
2005
2006
2007
2008
[code]......
I have a dataset with over a 1000 rows. I just pulled one row in my dataset as an example. My formula above finds the last cell with a value in the 2 row, finds the year associated with it in the first row, and pulls the year from the first row. What I am trying to find the last value highlighted red and pulling the year associated with it in the first row.
I am looking to calculate variance across a large data set and would like to know if a macro is possible to calculate for a specific unique cell ID. East, Central, or West and calculate variance across that region.
For instance, in my data set if I have something similar to below. How would I calculate variance in the different regions? Is it possible to automate this process? Also could the Analysis ToolPAk be used instead or in conjunction?
OrderDate
Region
Rep
Item
[Code]....
Rearrange dataset from columns to rows
However, the solutions do not work if "species " are text instead of numbers:
Parcel
Species1
Species2
Species3
[Code]...
Ok, so I have a dataset with 37 rows, column A labels each row 1-36 (labels in the first row) and data goes from column A-G
I have a sub that allows users to fill in some message boxes and answer a few questions so they can insert their own new data to the dataset. I want this data to go at the bottom of the entire dataset (so starting at row 38).
What i'm thinking of doing is, since the A column labels each row numerically, is to get a way to get VBA to take the cell in the A column above the new user-inputted data and add 1 to that number of the previous dataset (So take number in the above cell (36), add 1 (37) and insert that into the new A cell of the new row) just because I have an order function as well) then I would also have to fill the remaining cells with the information the user inputs into the message boxes.