I am accustomed to using filters to find a lot of my information in large datasets.
However, now I am trying to use formulas to return specific values. For simplicity's sake, I have included a sample below with a couple types of scenarios I am looking to solve through the use of formulas. Would this involve sub-arrays perhaps?
sabinfire1.xlsx
Excel questions.docx
we 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 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 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).
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]....
I have the following structure in values found within a large dataset
6A-6200 A
6A-6156-A
I used =SUBSTITUTE(CELL," ","") to remove the space in the first value: Works fine
I am trying to remove the 2nd occurence of the - in the second value: Desired Result 6A-6156A
And how I may be able to complete both within one formula.
We collect loan payments for 36 months from customers.
Column A lists 1000+ customers.
Column J lists the date we received payment 1 ... Column Q lists the amount we received on payment 1.
Column R lists the date we received payment 2 ... Column Y lists the amount we received on payment 2.
Column Z lists the date we received payment 3 ... Column AG lists the amount we received on payment 3.
This repeats for all 36 payments.
New customers are loaded in each month, so be aware that Column J, Column R, Column Z (and so on) have dates from 2011 and 2012 and 2013.
We'd like to create a list of all customers that have not made a payment for the current month as of a certain day (say the 12th). So this month, on January 12th, we'd like to search our data for all customers that don't have a payment listed between January 1st - January 12th.
I have a large dataset. under the heading Theme there are blocks of text, ie not single words. i have created a simplified version below
Sample table:
StateThemeVictoriatradeNew South Walestrade, ownershipNew South WalestradeVictoriaownership,
test textVictoriatest text, tradeVictoriatrade, ownershipVictoriaownership, trade
I want to count, for example the number of times the words 'ownership' OR 'trade' appear for Victoria.
The formula i am using is doubling up on the last 2 entries.
This is what I have:
=SUM(COUNTIFS(A2:A8,{"Victoria"},B2:B8, {"*ownership*";"*trade*"}))
I have a large itemised call bill that i need to do some regular analysis on and wondered if I could automate most of it.
In column C is a list of mobile numbers, in column F the numbers they called (this is an itemised bill so each line represents one call, meaning each number has multiple rows) finally in column K is the cost of each call.
I want the macro to look through column F (number called) and if there are less than 5 instances of that number that are under 0.30 each in cost to be deleted.
Example: if in column F the number 07500 100100 appeared once with a cost of 0.29 I want it deleted but if it appears 6 times with an accumulated cost of 3.50 i.e. more than 0.30 per call averaged out, then i want it to remain on the sheet
I have the following two bar charts. (see links below). I would like to overlay both these bar charts together and obtain the chart shown in link 3.
For example, at 4.4 GHz and 1.8m antenna, two values (downtime/year) are possible 15 min or 557min. This is represented in the third figure Since the first chart contains small values and the second chart contains large values for the x-axis, will I able to change this to log scale for ease of analysis?
[URL]
The code below works fine on smaller data sets. I tried it on a data set of over 165000 records and it gives me the error:
"Runtime Error '6': Overflow"
Clicking debug highlights this line:
rowCount = .DataBodyRange.rows.Count
Code:
Sub Fixtable()
Dim lo As Excel.ListObject
Dim loRow As Excel.ListRow
[Code]....
I've been unsuccessful in trying to write a formula that retrieves a single result based on two criteria (from a large set of data on a separate worksheet). I've tried various INDEX MATCH combinations but no luck.
A
B
C
D
E
1
DATE
TEAM
PITCHERS
RESULT
2
4/1
nyy
Sabathia
???????
[Code] ........
So this is a very simplified version of my real data set which is about 20 times this size. The first worksheet is where I want to store my retrieved results (lets say D2 for example). I want to retrieve data from the second worksheet that matches two criteria (exactly) originating from my first worksheet. The two criteria to be matched from the first worksheet are, for example, A1 (sabathia) and F2 (the date 4/8). The complicated part is the desired result should be from the corresponding K/9 column in the second sheet, which in this case (based on sabathia and 4/8 criteria) is I2 (result would be 3). It's complicated since I can't just tell the formula to look down a specific K/9 column, I need to search ALL the K/9 columns in the sheet (of which there are many). Is this even possible with some sort of nested INDEX MATCH? Any possibilities outside of VBA programming, or is that the only way?
I have a table that can at any point have from a couple hundred up to a couple thousand rows. Within this table lies a column entitled " Offer ". I want to plot the figures in the offer column as a frequency distribution chart.
I plan to do this by listing the x-values (Offer figures), and then using count if formulas to calculate the frequency of that x-value. Then using a simple clustsered column chart to create the visualisation of the frequency distribution.
My question is.... in my large data set, is there any way to get VBA to insert a list of the range of figures in the Offer Column, ? I can figure out how to copy down the countif formula to populate the corresponding frequency column, but how can I have some VBA to dynamically adjust my x-values (offer figures)?
For example... say in the first data set I have
Offer, Frequency
1 10
2 20
3 25
4 20
5 15
that's fine if I make the chart, but what if the data set changes, I want VBA to give me a list of all the offer values, and then I can write some code to insert and copy down along the frequency column the countif formula.
The ultimate goal is to have a frequency chart that will be synced to the self-updating dataset.
I'm trying to count how many production orders i have per week. However, there are duplicated production orders per week. I only want to count how many unique orders there are for each week. I only see the ability to "Count", which counts my duplicates as well so it over inflates my true quantity.
View 3 Replies View RelatedI have a spreadsheet with info about students in a summer program. I need to pull out the students who have allergies along with what their allergy is and their emergency contact phone number. I would like this new info in a new spreadsheet.
Here is an example of the spreadsheet:
allergiesnamephoneallergy type
nobob410.555.1234
nojim410.555.5843
yesmeg410.555.7458nuts
nolex410.555.1159
yeswill410.555.5005fish
Is there a formula that I can use?
I'm copying and pasting data from a PDF and need to eliminate some unnecessary data. The original .pdf has 4 columns: Account, Dollar Amount, Name, Notes
When I copy this to an excel document, it copies the row from all 4 columns into column A. The two columns I need are Account and Dollar Amount. All account numbers are 8 digits so I was able to create a formula to weed that out with this: =left(A1,8)
The problem I'm running into is obtaining the dollar amount within the cell. For example:
A1 contains: 11112222 $1234.56 Sample, Name Sample Note
I pull the 11112222 with =left(A1,8) in column B but not sure how to pull dollar amount to column C.
Further, the dollar amount varies from $1.01 to $10,000+
I have a list of cities, counties, and schools with dates for each account. I want to put these into a workbook with tabs along the bottom for each one, then I want to be able to select all of the accounts that have a Jan 1st effective date and show them on a list in the first worksheet.
View 9 Replies View RelatedI have a regular table of data and dependent on the info within a column of cells I would like the info to appear within one of 3 new sheets. BUT.... I can't figure it out because the info forms an irregular part of text within the cells in a specific column - Obviously if it was dependent on the whole of the text I could use Vlookup but it's not.
If it needs further clarification, a column of cells may have, say, "blahblah234/PLblah" -> If this cell contains within the text "PL" then it should have the whole of the row in the new sheet, if not then blank.
I am trying to pull info from Access into Excel using Data, Import External Data, New Database Query. I follow the menu and when I get to the end for the info to be pulled to Excel I get an error.
Microsoft Query:
Too Few Parameters. Expected 1.
I have monthly reports of sales by client number. I am now trying to pull the annual sales info by month for one client by name. Eg. each month, XYZ ltd has sales figures for various products. Is there a way of me grabbing all his sales info for the year with out having to open each spreadsheet
View 3 Replies View RelatedI have found a code to pull outlook calendar information from outlook to excel. I am trying to determine how to pull from Shared Calendars. This code looks as if they calendar has a number (9), but I can't figure out what the other calendar's numbers would be. How to pull from Shared Calendars and how to pull the month for which the calendar is on. My shared calendars are under Calendars>Shared Calendars in outlook. So for example, I would need to pull John Doe's calendar information for January if the calendar was on January. My code is listed below.
Sub ListAppointments()
Dim olApp As Object
Dim olNS As Object
Dim olFolder As Object
Dim olApt As Object
Dim NextRow As Long
Set olApp = CreateObject("Outlook.Application")
[Code] ......
I have a column filled with 5 digit numbers and a suffix. (Ex: 12345-001) I need to delete all of the '-001' from all of the numbers in the column. There are over 11,000 numbers. Is there a formula or function that I can apply to the entire column that will delete the suffix?
View 2 Replies View RelatedHere is what I am trying to do with no luck so far.
If I type RS23U1R109000 in a cell A1, I want B1 to read the 5th letter or
number and fill B1 with E86.
Example
A1= RS23U1R109000 B1=E86
A1= RS23V1R109000 B1=E87
A1= RS23R1R109000 B1=E84
As you can see in my example, the 5th letter could be U,V,R or whatever, but
I need cell B1 to read that letter and populate B1 with E86, E87, E84 or
whatever.
I have a template work book that has several worksheets with numerous formulas thorughout.
For example;
=SUMPRODUCT(--ISNUMBER(SEARCH("MCSH",P9:P218)),--ISNUMBER(SEARCH("HWD",G9:G218))).
When I set up another template for a different crew I manually have to go through the entire work book and change the crew identifier (MCSH) in every formula.
i was wondering if there was a way i can combine these two formulas into one large one
=IF(AF8="","",INDEX($A$3:$A$102,AGGREGATE(15,6,(ROW($L$3:$L$103)-ROW($L$3)+1)/($L$3:$L$103=AF8),COUNTIF($AF$8:AF8,AF8))))
=IF(AG8="","",INDEX($A$3:$A$102,AGGREGATE(15,6,(ROW($M$3:$M$103)-ROW($M$3)+1)/($M$3:$M$103=AG8),COUNTIF($AG$8:AG8,AG8))))
So in Cell K12 there is a rather large formula (I condensed it for this example).
This formula is to average out the respective cells in column J. For each cell there is a possibility of 9 different entries. NRT, N/O, 1,2,3,4,5,6,7
So based on the formula in K12 I need Cell J12 to Display the answer.
if any one of the Cells referenced in the formula are NRT then I need it to Display NRT. Which the Formula does now.
The problem comes in when a Cell is N/O (Not Observed)
How do I get the formula to Ignore N/O currently I have it set up to recognize N/O as 0. But excel averages 0 in and it affects the answer.
I know in a simple formula I can add <>0 to the formula to ignore zeros. How to write it into a bigger formula such as in K12.
For example the current numbers in Cells J17:J25 should average 5 however the formula averages it as 1 because it calculates N/O as 0.
I am trying to calculate a matrix of array formulas that is roughly 365 x 137, or about 50,000 cells being calculated from a range of roughly 12,000 x 137. There are multiple if statements within the array formula, and then those 50,000 cells are referenced to another, but smaller matrix of array formulas (25 x 137). Each of these matrix sets is for one year, and there are three years that need calculated.
Problem: When calculating the 50,000 cells it takes roughly 2.5 hours to complete all calculations. So, for 3 years it will take roughly one work day of tying up my computer to just compile data that will then require several days of calculation/manipulation.
Question(s): Is there a way to speed this up to a reasonable amount of time (I'd even take 30 minutes at this point)? Or, is Excel simply the wrong tool to be using for this amount of data? Do I need a better machine to run these calculations? I am currently using a Dell XT3 with 2.5 Ghz i5 quad core processor.
i have a spreadsheet with a certain data (rate of return) associated to a specific day of a year. (ex. 8-dec.-08 0.99865), and i have to calculate the rate of return (r) for every month, which is done by applying a formula (GEOMEAN) to all the rate of returns for the days of a month (ex. GEOMEAN(r[01 dec.]:r[31 dec.])).
i don't have any problems with that part. the hard part is that i have every single trading day from jan 00 to dec 08 (2000+ days), and i do not want to manually select the ranges. also, the dates do not include weekends (trading days per year = 252) which means that i cant (a) automatically determine a range or (b) automatically associate a number of days to a month (ex. oct 08 doesn't have the same amount of days as oct 07, because of the way week-ends and holidays are arranged..)
how to return the top 5 results in a set of data.
In a nutshell, I have data that needs to be sorted but some intermediate to advanced (to me) excel formula needs to be used to sort the ranking properly. I've already used index, match and large but it does not suffice.
I have a range of purchase order rows, with the formulas stored in the first row (TemplateRow) which is hidden. The users may add any number of rows to this range, depending upon the number of different products being purchased.
Code:
'Copy the template row into the first newly inserted row
Rows(TemplateRow).Copy Destination:=Rows(insertionPoint)
'fill down from the inserted row down to the last new row
With Rows(InsertionPoint & ":" & NumberOfInsertedRows.Rows.Hidden = False
.FillDownEnd With
The problem is copying the formulas down to the new rows can take terribly long (minutes) in scenarios of thousands of products. Is there a faster method of copying down my formulas?