Macro: Frequency Of Data List

Oct 20, 2006

I'm looking for a macro that makes a single long list of data from 2 user
input columns of data (column A are the data values themselves & column B are the frequencies of those values). Example - Col. A contains 1, 2, 3, and
Col. B contains 5, 6, 9. This is actually a data set of 20 values that
includes 5 number 1's, along with 6 number 2's, and 9 number 3's. I'd like
Column C to be this long list of 20 data values
(1,1,1,1,1,2,2,2,2,2,2,3,3,3,3,3,3,3,3,3).
T

View 9 Replies


ADVERTISEMENT

Create Unique List From Matrix & List Frequency Of Each Occurrence

May 23, 2009

I've searched the forum and believe this thread to be the closest to what im looking for, but its doesnt completely apply: matrix to list conversion. I have an attachment to support my questions (see attached). I have a matrix of words (strings) that contain repeating and non-repeating contents. The matrix also has spaces which are of no value. Essentially, the VBA needs to ' analyze' this matrix and create a consolidated frequency list (as shown in the file).

The matrices are HUGE and therefore some of the clumsy VBA i am using is turning out to be a bit inefficient. The file also contains formulas and such that I cannot use AutoFilter nor can I use Insert or Delete rows...so the VBA shouldnt use those either.

View 6 Replies View Related

Song Set List And Frequency Chart?

Sep 13, 2013

I have the desire to create a spreadsheet that will, in the end, allow me to see the frequency of certain songs I've seen by a certain band (the same band). trying to figure out the best approach in creating such a spread sheet.

What I want to do is provide the date of a concert, and all of the songs played at that specific concert. I have that info. There are 78 concerts that I need to input. However, it seems redundant for me to list, for example, "Song A" if it was played on "date X", and then list "song A" again if was played on "date Y". This is where I'm not sure what makes the most sense.

Would it be better to use the A-Z axis and provide the 78 dates of concerts, and use the numbered axis to list all of the songs that were seen/heard. Then I could put some sort of check mark in the cell that corresponds to the song and date?This way seems like more work than is necessary.

View 6 Replies View Related

Generate Random Numbers From A Frequency List

Jun 21, 2007

I have a frequency list (frequency per year) resulted from previous calculation. What I want is to generate random number as many as the frequency in the specified year, so that every year I will get the same amount of random numbers as the frequency. It is kind of the opposite of FREQUENCY function (but with random numbers involved). I need to use the result for further calculation.

View 11 Replies View Related

Macro To Populate Cells From Frequency?

May 13, 2014

i have a sheet that looks like below.

I currently have a long winded formula in all the cells that just slows the whole sheet down, the populated cells range is M6:BM5010 this is the formula i have been using which works but slow !

Code:
=IFERROR(IF(AND(M$5>=$I6,M$5

View 9 Replies View Related

Splitting Frequency Data By A Category

May 21, 2014

It may be easier to view the attachment to see what I am trying to do.

I would like to split frequency data by groups.

So I have 2 different names in column A, with a bunch of their ratings in column C.

I would like to be able to view the frequency of their individual ratings to see who gave the most types of ratings.

View 3 Replies View Related

Frequency In Large Data Tables

Nov 11, 2009

I need to find the most frequent number for each name in a large datatable, eg. Name1=12.

ColumnA / ColumnB
Name1 / 12
Name1 / 11
Name1 / 12
Name2 / 100
Name2 / 105
Name2 / 105
Name2 / 98
Name3 / 14
Name3 / 14
Name3 / 2
NameX / ..

Since Pivot tabels cant do the trick as far as I know, i'm clueless of how to proceed.

View 10 Replies View Related

High Frequency Data Calculation

Oct 16, 2009

Suppose you have a large database, which consists of high frequency minutes data, you need to calculate differences in values between each different time intervals, e.g. from 1 min to 10000 mins.

the format is looked like below

1 min aaa
2 min bbb
3 min ccc
4 min ddd
.................
......................


More specifically, what I am supposed to do is to calculate aaa-bbb, bbb-ccc, ccc-ddd, and then aaa-ccc, bbb-ddd, and then aaa-ddd and so on.

I know the VBA has a function to do repeated executions but am not sure how to adjust the time intervals.

View 9 Replies View Related

Using Frequency In A Data Validation Formula To Ensure Unique Entries

Mar 11, 2014

Using frequency in a data validation formula for ensuring unique entries.

Currently I use COUNTIF($B$2:$B$244,B2)

View 4 Replies View Related

Excel 2010 :: Macro To Copy Data From A List In The Next Column As Per The Current Column List

Mar 27, 2014

I am using Excel 2010. I need to copy a list from any Column on the right to the existing Column. See the sample file.

View 10 Replies View Related

Macro To Take Data From List - Do Calculations And Record Data

Jul 21, 2014

I need to take data from spreadsheet Control Master.xlsx then apply calculations from another one Calculations.xlsx and record results to the first spreadsheet.Data to be taken from Control Master.xls columns C, D, E. After calculations results (Calculations.xls columns Q, R, S) to be recorded back to Control Master.xls columns F, G, H.

View 4 Replies View Related

Use Macro To Add Data To End Of A List

Sep 23, 2007

I have a very long list of data in several columns. I want to be able to enter new entries in separate area of the sheet with the same layout (like a standard entry form), then press a macro button to copy the data to the end of the list and clear the form (i.e. the macro needs to be able to know the length of the list with something similar to a COUNT function (I can stick this in a cell if macros can use indirect references...?)).

***
e.g. if my long list is:

Joe Bloggs / 18 / Manager / Male
...
May Yates / 36 / CEO / Female

I'd like to be able to add in my little form:

Colin Jackson / 27 / Cleaner / Male

and press a button to stick Mr. Jackson at the bottom of the list.

View 9 Replies View Related

Macro To Create Unique Rolling Data List

Feb 4, 2012

I need a Macro to create a unique, rolling data list. On Sheet 1, a report is pasted(from outside source) in cell A3(Rows 1 & 2 are headers) on a weekly basis. It is always the same amount of columns wide(138). The number of rows will always vary but will never be more than 500.

Here’s what I need for Sheet 2:

-Column A should be a copy from Column A on Sheet 1 (data is ITEMS)
-Column B should be a copy from Column BH on Sheet 1 (data is QUANTITY of items)
-Column C should be a copy from Column BI on Sheet 1 (data is PRICE of items)

Here’s the challenge (when the new report is pasted on Sheet 1):

-Sheet 1. Find new ITEMS not on Sheet 2 and paste them at the end of the report on Sheet 2 along with their QUANTITY and PRICE
-Sheet 1. Find any ITEMS already on Sheet 2, then update new QUANTITY and PRICE from Sheet 1 onto Sheet 2
-Sheet 2. Leave ITEMS alone that are now missing from the new report on Sheet 1

View 7 Replies View Related

Macro: List Worksheet Names & Count Of Data

Oct 26, 2006

I am desperately in need of a macro that will count the number of items in column A of each worksheet and then summarize the results next to the names of the worksheets (i.e. in two adjacent columns) on Sheet 1 of the book. Obviously, Sheet 1 needs to be ignored by the macro.

View 3 Replies View Related

Data Validation List To Select Worksheet In Macro

Apr 8, 2007

I have a Data Validation list in cell A1 of a worksheet called LIST. I would like to modify the macro that I have recorded, the macro uses data from various worksheets. I would like to sort out some code so that at certain points in the macro it will select the worksheet that is shown in cell A1 of the LIST sheet and then carry out the code already recorded. THis will allow me to quickly change a sheet name that is refererred to in the macro numerous times without actually manually editing the sheet name in the macro code.

View 7 Replies View Related

Macro For Spotting Text From One List To Another Then Pulling In Data With Vlookup?

Jun 3, 2014

I have a list of active accounts with its relevant data on one sheet, then I have a list of closed/inactive accounts that will be updated periodically by different users. I would like to run a query for the accounts on the closed list to pull in relevant data from the active list,if any, or maybe just highlight the closed accounts on the active list to ensure that the closed accounts aren't active again. Is that possible with a macro? Should it just be a vlookup? The accounts list is going to be a very big file and I am trying to find a solution that won't take up too much time to run.

View 4 Replies View Related

Macro - Hide Rows Based On Data Validation List?

Dec 30, 2011

I have a data validation cell that has 5 items to be selected, lets say A-E.

I would like a macro that checks and does the following:

A or B selected, hide row 25-34, rows 45- 53

C or D selected, hide row 24-44

E selected, hide rows 34-53

In each case, the macro should check and unhide all rows between 25-53 before doing the above

View 9 Replies View Related

Excel 2007 :: Data Validation List Crashes VBA Macro

Feb 28, 2012

I have a VBA macro for Excel 2007 below that loops through a workbook and deletes a picture (shape) in a range at the top of each worksheet.

The macro works fine until a cell which contains a seemingly unrelated data validation list on Sheets(1) is changed. The macro then repeatedly trips up with a 'Run-time error 1004 - Application defined or object defined error'.

The cell with the data validation is outside of the range in which the shapes are deleted and does not set any of the variables in the macro.

Sub DeleteLogos()
Dim Count As Integer
Dim NumberOfWorksheets As Integer
Dim Logo As Shape
Dim LogoZone As Range
NumberOfWorksheets = Worksheets.Count
For Count = 1 To NumberOfWorksheets
With Sheets(Count)

[code].....

View 4 Replies View Related

Clear List Macro For Data Validation - Multiple Lines In Table

Aug 1, 2014

I have a spreadsheet where I am tracking several entries in a table that will keep growing. Three fields are Data Validation Drop Down Lists. The macro below works well to clear the two lists to the right when the first one is changed by the user.

[Code] .....

I want this to affect the rows below it in the table as they are added.

View 2 Replies View Related

Mouse Scroll WITHIN A Data Validation List & Option Macro Assistance

Jan 7, 2010

I'm trying to do a few things actually. I am somewhat a newb at programming, although I do understand how C++ and the coding works within Excel. To me, it's like the English language...knowing all of the vocabulary and formatting it right is important. If I don't know the vocabulary, I don't know what options I have.

This tool is being used in the call center I work in. It is to help eliminate repititious information used to notate accounts. I have attached a picture of what the tool looks like so you can have a better idea of what I'm working with. Here are a list of things I'm trying to do which I have searched and not found answers to:

1. I am trying to use a middle mouse scroll to scroll through validation list. This list information is located on a different locked tab. Basically, I have a few rows where a drop down selection can be made and it would be easier to scroll rather than manually moving the slider within the validation list.

2. As you can see, this "tool" an employee and I have created has many buttons. What I would like to do is to be able to click a button on the right and have it "stack" text within a single cell. In other words, if I click the button once, the text may be entered into the "actions taken" cell. If I click another button, I can have the option (or maybe a little "+" next to the button) to add this button's information to the end of the information already entered within the "actions taken" box. I've tried recording and using the keyboard and selecting "end" and then having the text added, but it didn't work. I've seen "loop" VBA code, but I don't want something to repeat, I want it to stack in the cell when clicked manually.

I'm sure there is a way to make the tool much better using VBA code and not use Excel, but I don't have an extensive knowledge of Excel to do something like that. I'm a very fast learner and understand how the coding works, so if you have an idea which may help, and you understand what I'm trying to do here, please provide your .02.

Basically, it's used so the Customer's name and phone number are entered manually, and then just about everything else is automated by the use of the buttons on the right. If I could get the 2 above options to work, it'd be perfect. I just figured out I could eliminate screen flicker and hide the macros working by using:

View 12 Replies View Related

Sorting By Frequency

Dec 20, 2008

how can data sorted be more frequency as most frequent on top and as the following:
1
1
1
2
2
3
3
3
3
to
3
3
3....................
i attach file

View 3 Replies View Related

Mode Or Frequency

Jan 31, 2006

I have a large data set. Within the column I have determined the mode. How
do I find the second most common number, third most common, etc.?

240
240
240
240
240
240
240
240
288
288
288
300

View 10 Replies View Related

Frequency Of Numbers

Mar 9, 2006

I have a grid of random numbers. What I want to do is make a new,
corresponding table that has those numbers' frequencies from the initial
dataset.

So, if I have a row 1,2,3,4,5, the first row in my frequency table shows the
frequencies for those numbers, whatever they might be regarding the entire
set of numbers.

I'm just wondering if there's a more automated way of doing this than
running the historgram function and then manually matching the frequencies
with the original numbers to come up with my table. My dataset is growing,
so that's becoming too time-consuming.

View 9 Replies View Related

Formula For Sum Frequency

Apr 14, 2014

I have ID in column A and amounts in column B. ID's are duplicated, and I need to add amounts based on ID, so for example total of ID 156 should be $13,851.14 or 156 should be 7,290.63.

In the following example ID are in order, I need a formula that adds up the amounts in column C.

152
(14,542.81)

152
(199.25)

153
(37,554.98)

153
(19.92)

[Code ...........

View 5 Replies View Related

Frequency Function

Sep 15, 2009

My data is on order database as follows:

column B: customer name

column H: order value

column C: Total Order Value for the customer calculated as =SUMIF($B$2:$B$36383,B2,$H$2:$H$36383)

column D: number of orders for that customer, calculated as =COUNTIF($B$2:$B$23155,B2), and defined as a range called No_of_Orders

I'm trying to work out how many customers fall into the various categories. So on sheet 2, I've got an array formula:
{=FREQUENCY(No_of_Orders,$F$4:$F$12)}, where F4:F12 contain the 'bin' values.

The trouble is, that gives me the number of orders that fall into each category, not the number of customers.

How can I get it calculate the number of customers?

View 9 Replies View Related

Sum Character Frequency

May 29, 2008

I have large spreadsheet that analyses the number of times specific characters etc appear in individual cells, but only takes into consideration the filtered cells. My current formula that works fine is:

= sumproduct((subtotal(3,offset($c$66,row($c$66:$c$6959)-min(Row($C$66:$c$6959)),,1))),--isnumber(search("F",$s$66:$s$6959)))

So at the moment every time an F or other specified values is in one of the filtered cells it is counted and displayed in the respective cells. What I would like it to be able to do is recognise when a cell has say 3 F's, and increment the displayed product accordingly.

View 6 Replies View Related

Return Value With Highest Frequency

Apr 9, 2014

I need a formula that can lookup the sell price with the highest frequency. For example, I need to look up item 2100 in sheet2 and sheet1 contain data for vlookup like this:

ID No_Frequency Sell Price
2100 4 2.99
2100 5 3.49
2100 1 1.99

I need to be able to look up and return the sell price of 3.49 since it has the highest frequency.

View 4 Replies View Related

Frequency Using Date Range

May 5, 2014

In column A, I have dates.

In column C, I have shopping centre locations.

One or more sales promoters can be rostered at shopping centre locations performing full shifts and half shifts.

How can count the unique dates associated with the shopping centre using a Start date and an End date? Example below.

Col A Col B Col C Col D
02/03/2014 Joe Shopping Centre #1 D1=01/03/2014 (start date)
02/03/2014 Bob Shopping Centre #2 D2=10/03/2014 (end date)
02/03/2014 Lee Shopping Centre #1
07/03/2014 Tim Shopping Centre #1
07/03/2014 Kay Shopping Centre #3
12/03/2014 Joe Shopping Centre #1

The results for the Frequency formula should show:

Shopping Centre #1 - 2 (dates)
Shopping Centre #2 - 1 (date)
Shopping Centre #3 - 1 (date)

View 3 Replies View Related

Frequency Combined With IF Statement?

Mar 26, 2014

Sample Excel with Frequency.xls.Attached a sample sheet with some data. What I'm trying to do is combine the frequency function and add another variable. Not sure how to do this. I'm using Excel 2003, so I don't think I can use "countifs".

View 2 Replies View Related

Frequency Of Associated Numbers In A Table

Mar 8, 2009

I am looking to find the frequency of times 1 number appears with another number in a row in a table

Click here for an example

or see the attached spreadsheet. I included the excel 2007 and 2003 versions.

View 6 Replies View Related







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