Using CORREL On A Subset Of Data

Jul 8, 2009

I have a set of data that can be divided into sub-sets. I've attached the sheet of data. There are 5 sub-sets and each of these has 5 sub-sets. The sets are defined by the Portfolio and End columns.

So, the 25 sub-sets look like:
10 INTL
20 INTL
30 INTL
40 INTL
50 INTL

10 LCG
20 LCG
30 LCG
...

I'm wondering if there is a way to write a formula in cell N10 that basically pulls in '10' and 'INTL' and finds the correlation on only that sub-set of data. So I guess I want a formula that returns a range such that =correl(Range1, Range2) will work. Also, the length of the ranges will vary, so trying to write an INDIRECT(ADDRESS... will not work. I tried an array-formula in cell N10, but it didn't work. I also included my expected output. in M18:O24.

I know I can do this in other ways, but I really want to learn if there is a way I can do this with 1 formula that I can just drag down. I guess its more of a learning opportunity than something that needs to get done.

View 4 Replies


ADVERTISEMENT

Ranking Within Subset Of Data?

Jun 24, 2014

I have a workbook of data that I created a pivot table with. From there I need to rank each value of the NES, column C, within the corresponding Reporting Structure, column A. The table that I created will be update with data on a monthly basis and I don't want to have to redo the rankings every month and just have it refresh.

View 2 Replies View Related

Extract Subset Of Data From .csv File

Oct 21, 2008

I would get the End of day bhavcopy from the exchange which is a .csv file. The first column contains the column heads like name, open price etc. and the first column. Now I have to extract the names and the corresponding column values from this list by referring to a notepad that contains the ticker names to be extracted, place in another workbook and save that file.

View 10 Replies View Related

Cannot Get Correl Property Of WorksheetFunction Class

May 10, 2008

I am trying to generate a number based on the Month, Year and then three digits. For Example: 1107-001. I would need it to refer to another sheet for the previous used last three digits. Each month it would start over at 001. On the Reference sheet it would always be in the first column.

View 2 Replies View Related

Unable To Get The Correl Property Of The Worksheetfunction Class

May 22, 2007

I get the "Unable to get the Correl Property of the worksheetfunction class" Error when I try to run the following code

CurrentRow = 2
CurrentColumn = 2
Finalrow = 5
WS2 = Sheet2
Finalrow2 = 5
Num_Records = 4

Correlation1 = Application.WorksheetFunction.Correl(Range(Cells(CurrentRow, CurrentColumn) & ":" & Cells(Finalrow, CurrentColumn)), WS2.Range("B" & Finalrow2 - Num_Records + 1 & ":B" & Finalrow2

View 9 Replies View Related

Data Validation - Creating Subset Choices Based On Adjacent Column Choice

Oct 27, 2013

I am limiting choices in a certain cell to a list via data validation (using data validation, allow - list and pointing to the source). I want to limit the next set of choices in the next column (in this example in Column C) for the user based on the choice made in the adjoining cell. For example to keep it simple I will use the following: if the user selected "Fruit" in B1, then the options in C1 would only show "Oranges","Apples", "Pears". If however the user selected "Veg" it would only show "Cucumber", "Lettuce", "Spinach", "Radishes" in the drop down in column C. I can put a prefix in front of the column C data validation list if that is needed to narrow down e.g. FR-oranges, FR-apples etc.

A B C
1 Fruit Oranges
2 Veg Lettuce
3

Data Validation source for Column B (H2:H4)
Fruit
Veg
Nuts

Data Validation for Column C: (I2:I10)
Oranges
Apples
Pears
Cucumber
Lettuce
Spinach
Radishes
Almonds
Peanuts

View 3 Replies View Related

Sum Of The Max's For Each Subset

Mar 16, 2009

Looking for a "oneliner: or CSE perhaps that sums the maximum values for each subset. See example below. The answer should be "9".....

View 9 Replies View Related

How To Subset A Range

Oct 8, 2013

I have a sub where I pass in a 1200 row by 8 column named range. Inside the sub I want to create a single column, 1200 row range. I also pass in which column I want to use of the 8 in the big range. So I pass in a number between 1 and 8. I create a new range, but i am not sure how to reference a particular column. I am trying to avoid looping.

View 6 Replies View Related

Create Subset Table

Feb 18, 2009

I've got a table on a worksheet (daylies) with daily data, dates in A6, A7, A8, ..., various data in columns B, C, D, ... .

Now I would like to make a subset table on a separate worksheet (weeklies) with just weekly snapshots, i.e.,

snapshot from daylies off row 6 on row 2 of weeklies,
snapshot from daylies off row 13 on row 3 of weeklies,
snapshot from daylies off row 20 on row 4 of weeklies etc.

Is this possible or do I have to use the sledge hammer technique of copy and paste?

View 4 Replies View Related

Comparing Subset And Getting Respective Value

Jan 20, 2013

I'm facing a problem wherein i have two tables A& B.

Table A has two columns and 40834 records(rows).first column is userid and second column is activeyn with respective values (0 or 1) for each userid

Table B userid column is a subset of Table A and has two columns and 411 records(rows).first columns is userid and second column is activeyn with respective values (0 or 1) for each userid.

Now I want to get activeyn value from table A of all the 411 records(userid's) in Table B.

View 3 Replies View Related

Finding First Item In Subset

Jun 12, 2013

I have a list that kind of looks like this. I sorted it by date, then by location, and then by time. I need to find the earliest item in one location on a given day and display as a list.

datelocationtime
6/1/2013 A730
6/1/2013 A745
6/1/2013 A750
6/1/2013 B800
6/1/2013 B810
6/1/2013 C730
6/1/2013 C745
6/1/2013 C800
6/1/2013 C815
6/2/2013 A730
6/2/2013 A745
6/2/2013 A800
6/2/2013 A815
6/2/2013 B700
6/2/2013 B800
6/2/2013 B815
6/2/2013 C800
6/2/2013 C815

View 10 Replies View Related

Saving Subset Of Worksheets

Nov 4, 2008

I am trying to e-mail a portion of a spreadsheet that I have. My approach is the following: I save a copy in a temporary folder, open that copy, remove all the sheets that I don't need to send out, save it (same name as the e-mail subject), and then e-mail as an attachment.

The current approach does not remove the macros and references though. I use the following to remove the macros and references:

View 3 Replies View Related

Copy Subset To Different Sheet

Jan 24, 2007

I've got a list of employees with their division and a bunch of other data in each row. I want to copy each and every row to another sheet based on the division.
_______________________________
ID | Name | Division | Other
4 | Joe | South | up
5 | James | South | over
8 | Jim | North | down
9 | John | East | left
14 | Jerry | East | right

Assuming "ID" is A1 and the list is sorted by Division, I need some code to accomplish my goal. I know C/C++ so I understand the principles of programming but I'm just learning the VBA syntax. First thought is to loop through each row moving them individually but then I have to worry about putting it in an open row on the other sheet. Then I thought about finding the first row that contains the target division and, since the data is sorted by division, select each subsequent row and copy the entire selection at once to avoid fumbling around in the destination sheet. Hopefully someone out there can make sense of this and help out. Please let me know if I need to elaborate.

View 9 Replies View Related

Loop Through Subset Of Worksheets Will Not Work

Feb 9, 2014

I'm trying to insert two rows in a subset of worksheets in a workbook by defining a range in a worksheet and loop through the worksheets in that range, but the code will only run on the active worksheet.

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

How I could get this loop to work?

View 1 Replies View Related

Loop Through Subset Of Worksheets Will Not Work

Feb 9, 2014

I'm trying to insert two rows in a subset of worksheets in a workbook by defining a range in a worksheet and loop through the worksheets in that range, but the code will only run on the active worksheet.

Sub StatePIPData()
Dim sheet_name As Range
For Each sheet_name In Sheets("WS").Range("A:A")
If sheet_name.Value = "" Then

[Code] .......

View 1 Replies View Related

Function To Match Subset Of Characters?

Mar 25, 2014

Can I use MATCH to match a subset of characters?

Example....Looking for value in a range that matches cell A1 (which reads 'DOGS')

MATCH(A1,'SEARCH_SHEET'!H4:H2590, FALSE)

This will look for a cell with 'DOGS' in it.

But what if I want a positive match on a cell that has 'DOGS & CATS'

This fails because it doesn't match exactly.

View 9 Replies View Related

Random Selection Of A Subset Of Numbers

Aug 29, 2008

I have a set of 20 numbers that I regularly wish to select 6 numbers from, without duplicates

View 9 Replies View Related

Working With A Subset Of A Named Range

Dec 19, 2008

I have a sheet of data that is imported from an external database. Depending on which report gets imported, the column count and column location will vary. However, the headers for the columns I need to work with are constant, therefore I create a named range for all columns, and reference the ranges as I work with the data and avoid hard-coded cell references. Therefore, the ranges are one cell wide, with up to 2000+ rows.

Here's my problem. I need to reference a sub-set of a named range, to get the min of the subset. I've searched this board and others, but can't find an example of this method. The code is within a loop, and the range subset location is determined by a count on another named range.

The data involves Tasks and Subtasks. Each task can have multiple subtasks, and each subtask has it's own start and end dates. I'm trying to determine the earliest start date and the latest end date for each individual Task so I can determine the total duration for the Task.

Here's the code that determines the cell range. This code is working.

d2 = WorksheetFunction.CountIf(Range("ASR"), Range("ASR").Cells(y, 1).Value) ' count number of tasks in this ASR, store in d2

The code that I can't figure out is below. The compiler can't figure out what I'm trying to do. (Neither can I!!)

d3 = WorksheetFunction.Min("Planned_Start_Date").Cells(y, 1), Cells(y + d2 - 1, 1).Value) ' Get the min Planned Start Date for this ASR

In english: Look in the Planned_Start_Date named range, and more specifically a certain subset of that range, and return the minimum date found.

I've tried several ways, none of which will compile. I realize the above code is way off; I include it to show how lost I really am!

Slightly off-topic: I'm fairly new to VBA, and am expected to create executive dashboards with the data. I've purchased Excel 2003 Power Programming with VBA and Excel Charts (both Walkenbach); Excel Programming 2nd Edition; Excel Edition of Using Visual Basic for Applications; and Dashboard Reporting with Excel, Kyd. These are all useful books, but I think they expect me to have a better understanding than I have.

View 9 Replies View Related

Vba Code Finds Max Value Of Column Subset

Oct 17, 2006

I need a piece of VBA code to assign to an Excel form that determines the maximum value of a subset of one column whose cognate rows in an adjoining column satisfy a particular value.

View 2 Replies View Related

Highlight And Delete Lower Value Of A Subset Of Cells?

Apr 30, 2014

I have a huge list of data with numbers in cells. each number also has a corresponding percentage in the next cell over. In some cases, some of the numbers appear twice with differentiating percentages in the cells. What I am trying to do is automatically highlight and delete the duplicate number with the lower of the two percentages. See the below example.

2069091%
2069088%

As per above, I would want to delete the duplicate number with the 88% since it is the lower of the two percentages of that number. I have almost 2000 cells and have been going through manually deleting the lower of the two percentages for the duplicate numbers.

View 6 Replies View Related

Determine The Largest Magnitude Of A Subset Of Four Values

Feb 16, 2010

I've got four tables with negative and positive values and want to determine the largest magnitude of a subset of four values within those four tables. In other words if the largest magnitude is negative I want the negative value of the four subsets or vise-versa.

I've tried the following but got an error stating it was too long:

= IF(ABS(VLOOKUP(table1)) = MAX(ABS(VLOOKUP(table1)), ABS(VLOOKUP(table2)), ABS(VLOOKUP(table3)), ABS(VLOOKUP(table4))), VLOOKUP(table1), IF(ABS(VLOOKUP(table2)) = MAX(ABS(VLOOKUP(table1)), ABS(VLOOKUP(table2)), ABS(VLOOKUP(table3)), ABS(VLOOKUP(table4))), VLOOKUP(table2), IF(ABS(VLOOKUP(table3)) = MAX(ABS(VLOOKUP(table1)), ABS(VLOOKUP(table2)), ABS(VLOOKUP(table3)), ABS(VLOOKUP(table4))), VLOOKUP(table3), VLOOKUP(table4))))

The repeating line needs attention in my mind:

MAX(ABS(VLOOKUP(table1)), ABS(VLOOKUP(table2)), ABS(VLOOKUP(table3)), ABS(VLOOKUP(table4)))

View 6 Replies View Related

How Do I Find The Median Of A Subset Of A Large Group

Apr 10, 2009

So what I have is a large list of items, and each item has the price of the product, and a product type.

What I want to do is find the median price for each specific product type, using a formula, without having to sort or filter my large list of products.

View 6 Replies View Related

Formula To Calculate Average/wtd Avg On A Subset Of A List

Jun 2, 2006

I will do my best to explain but just in case I have attached a worksheet to make things easier. I have a list of a few thousand products with data on each product. When I run through a series of cuts, I get a subset list and want to be able to calculated a weighted average by somehow saying to do a weighted average (and/or count, and/or average, etc.) on the characteristics of only the products in the subset.

View 9 Replies View Related

Add Data Monthly And Then Have To Manually Update The "source Data" To Reflect The Added Data On Chart

Apr 28, 2009

I have a column line chart to which I add data monthly and then have to manually update the "source data" to reflect the added data on chart. This is a rolling graph, which mean that I have to remove data for one month(from last year) and then include the new month's data. Is there any way on automating this process...like a macro or something, so once I add the data excel automatically removes one month of old data and make changes to include fresh data. Eg Currently chart is based on data from A2:F2 and I add new data to cell G2. I need something which automatically update the source data to cell B2:G2.

View 4 Replies View Related

Update Data By Ado - Change/delete Data And Then Run Macro For Update Data In Source Spreadsheet

Dec 6, 2006

I have one source spreadsheet, where are columns NAME, DATE. I read these data by ADO to other spreadsheet, where I can change/delete data and then run macro for update data in source spreadsheet. The problem: In source spreadsheet is column "NAME" and column "DATE", with values e.g. "Joseph"; 1.1.1980. I read this data to other spreadsheet, then I delete in it value 1.1.1980. When I run macro Update, it messages error.

Sub UpdateItem
...
.Fields.Item(1).value = activecell 'activecell value = "Joseph"
If Not isempty(activecell.offset(0,1)) Then
.Fields.Item(2).value = activecell.offset(0,1).value
Else
.Fields.Item(2).value = "" 'I tried Empty and 0 too but when I read data again then, it displays 0.1.1900, nothing works
End If
...
End Sub

It seems that in source spreadsheet has data in column "Date" format Date and when I try to update data in format String ("") in source spreadsheet by Update macro, it messages error. When I used

.Fields.Item(2).Value = Empty
' or
.Fields.Item(2).Value = 0

after rereading data it displays 0.1.1900 What I want to get is that if the cell with date (in other spreadsheet) is empty, the cell in column Date in source spreadsheet after updating will be blank (contains no values).

View 2 Replies View Related

Create Userform That Adds Integer Data To Existing Data In Specific Cells

Jan 29, 2013

I am trying to create a user form that will allow the user to type int values in boxes. Then when the user clicks the submit button the code needs to add the values from each user input box to the existing values in specific cells accross multiple worksheets. Then the form needs to be cleared after the cell values are updated. I can create the form it is the code on the submit button i am lost on. Also it is important that some boxes may be left blank.

If you click the Grey "Qty Form" button on sheet1 the form will open. User data numbers can be entered in the blank boxes. When the submit button is pressed the form needs to add the user entered numbers to the numbers in the corresponding cells in sheet 1 and sheet 2. How to code the submit button to do this properly. Also after the data on the spreadsheet is updated the form needs to be cleared and start the cursor back in the Item 1 box on the form.

View 1 Replies View Related

Formula / Function To Collect Data From Multiple Sheets And Store Data In One Sheet?

Dec 17, 2013

- I have total of 13 sheets in a workbook - 12 sheets represent 12 months with data; 13th sheet is single sheet in which i would like to get complete overview of 12 months

- each of 12 sheets has actually sales results for multiple products with following data: internal code, manufacturer part number, name, and qty sold in that month

- situation is that some products have been phased out during the year and some were introduced so each sheet is slightly different in terms of in which row certain product is located

What i would like to achive is to make 13th sheet (whole year overview) do the following:
- there is a list of all the products in it, each product has unique internal code - this code (from each line) should be used to find that code in each monthly sheet, then find its monthly sales value (copy it) and paste it in sheet 13 in cell that represents this product and particular month.

In other words i would like to see for each product what was monthly sales throughout this year, but avoid manually filling in qty for each product per month.

View 2 Replies View Related

Formula To Separate Specific Data From Data List In Sheet1 Into Sheet2 / Sheet3

Jun 30, 2014

I have attached a sheet that has part of a data list, sheet called (Full Data) what i am trying to do is seperate the data into event locations into individual sheets.

The data ref will be column F which is the different event locations.

I thought the best way to try and do this was to create an if/ match formula using the sheet tab names as the if match, with the event locations in column F.

I have added two sheet tabs so you can see that i require the data for (Ain) to be put into the Ain sheet.

The data list in Full Data sheet will be continuously updated so i will need the range to be around 10,000 entries.

View 5 Replies View Related

Macro To Generate Outlook Emails With Data / Information From Multiple Rows Of Data?

Apr 30, 2014

I am attempting to create a macro to generate emails based on data in a sheet. The goal is to run the Macro, and have it generate emails to send to contractors letting them know what they are going to be paid. For instance:

Name in Column J
Email in Column L
Memo in Column N
Balance in Column T
Due Date in Column P
Week Ending Date in Column H

Now what I would like to happen, is to tie a macro into a button that will create the email as follows:

To Field: Email address from Column L
Subject: "Company Payment Remittance Payment Date *Date from Column P*"
Body: Hello *Name from Column J*,
For *WE Date in Column H* you will be paid *Balance from Column T* for the time worked of *Memo in Column N*

Now the tricky part is that I want the email to contain all line items for each email address. So instead of sending one email per line, have the macro automatically put all of the information that needs to be sent to one email address into the message. I don't know if that is possible, but it sure would make my life easier if it was.

I have attached a sample workbook of the data that will be used

Example Workbook for Email Macro.xlsx

View 1 Replies View Related

Pull Data From Sheet Based On Criteria - Populate UserForm And Ask For Missing Data

Feb 8, 2014

I have a spreadsheet that is updated weekly -- but every week new info is added that needs a user to input corresponding info. I use a vlookup function to link to another spreadsheet that populates the info from previous weeks and the info that is missing shows up as #N/A...

First I was using a msgbox function to get the info:

HTML Code: 

For Each b In myrange
If Application.IsNA(b.Value) Then
Employee = b.Offset(0, -2).Value
SSID = InputBox("Please enter ID# for " & Employee & " :", "New Employee Found")
b.Value = SSID
End If
Next b

But it can be up to 30 different new employees... and that is time consuming.

I would like to make it more user friendly by creating ONE userform that displays all of the employees as labels -- has a text box in which to put the ID # -- and then has a drop down box to choose the type of employee (2 options). I want all of that info to go back to the reference spreadsheet so it will be saved for following weeks, and then redo the vlookup to get the info into the new weekly spreadsheet (I can do that part)....

HTML Code: 

Private Sub CloseButton_Click()
Unload UserForm1
End Sub

Private Sub ComboBox1_Change()

[Code] ......

View 2 Replies View Related







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