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.
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 having an issue where I want to merge lots of tables held in different spreadsheets into one large table so that I can use it in a pivot table. The attached spreadsheet demonstrates what I mean:
So there are 3 different accounts held in the first 3 tabs. These all have the same columns and format. I want to merge these automatically into the table that is in tab 4 so that I can use the pivot table also demonstrated in this tab.
What I want to happen is when I add rows to the tables in the first 3 tabs, these are automatically populated onto the 'overall' table in the 4th tab. (I'm aware I could do this manually, but for what I'm using this for these will be dozens of tables with hundreds of rows updated monthly).
Is this possible? Basically it doesn't matter how it's done, as long as i can show the information held in the pivot table each month end.
I have a data dump, on this single excel sheet their are a large number of tables, up to 100 in all, and what i need to do is extract key figures to form a summary table which i then produce stats for.
What i need is a macro or code that will automatically scan the tables, retrieve the key figures and return them in my summary table.
Attached is an example, you will see three small tables labelled Servicing query complex, servicing query simple and total.
I need to extract the blue cell, being the name of the table, the purple cell being the last figure in column c in each table (total number or processes) and the green figure being the the average turnaround. The final figure is i think the hardest to get as its the total number of figures in column c that have either a 0 or a 1 in Colum A. Note that their will be table where neither 0 or 1 will appear.
All figures are dummy figures but the table structure is correct.
I have a lot of data to try and summarize but they are in multiple smaller tables. I wish to only extract 2 variables, 'number' and 'total' from all of my smaller tables (lots of them) so the problem looks like this, i have these tables for example where the 'total' title is in a different column depending on which of the smaller tables you are looking at:
cat 1
cat 2
cat 3
total
[Code]....
I am trying to get:
number
Total
123456
30
234567
60
[Code]...
Because the number column is fixed and all of the smaller tables will all have a number in the same column maybe i can insert a column next to the number column and say "find the value where the column title is 'total' - however the location of the column title would change so not sure if this would work? I was also thinking of sorting the columns but that will not work either because of the smaller multiple tables?
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.
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.
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
Using frequency in a data validation formula for ensuring unique entries.
Currently I use COUNTIF($B$2:$B$244,B2)
I use excel and would like to know how to copy a large volume of address data but at the same time filtering out irrelevant data placed under each other in a row, in this case, air compressors air conditioning web address etc ( see below for example). I need the first 5 lines only. The rows of unwanted data are irregular i.e some have 10 lines, others 5 , and others 2 or one line which makes using a formula difficult as there is no consistency. The data eventually need to be placed horizontally in columns to be compared to other address lists. To make matters worse, the text data has been merged and wrapped.
BDD LIMITED
3 Telford Place
L*****r QLD 4315
Phone: 07 5777 3622
I have a one column spreadsheet. The column contains this data:
1 Name
2 Address
3 City
4 State
5 Zip
6 Telephone
7 Fax
8 URL
9
10
11 Name
12 Address
13 City
14 State
15 Zip
16 Phone
17 URL
18
19 Name
20 Address ... and so on
Where there may be one or two blank rows between the individual records and where there may or may not be a Fax number (or row) in the record.
I am trying to convert this data to a horizontal column format - which works fine if I do a copy/paste special/transpose. However I have to do this for 1,800 records and cannot figure out how to do this reliably.
I gave the above illustration to simplify but, actually this is a two column spreadsheet with individual row labels for every record using the above terminology. In other words the above text is in the first column and the data is in the second. Just thought I'd mention in case there was a way to do some kind of if/then formula.
I've got a 80 Mb CSV file and would like to open and work with it. Too many lines (90000 or so).
Is there a way to split ( ) this file so I can open two files instead?
Using Excel 2010. I'm writing a macro that sets up a workbook to be used for estimating at the beginning of a project. In the code I need to create multiple tables (formerly known as "lists") in the workbook. Then later in the code I need to refer back to those newly created tables. Currently, the code that creates the table is part of a loop that creates the table on many different worksheets. The problem of course, is that I have to name the Table, and then it won't create a table of the same name on the next sheet. Then, later in the code, I need to make adjustments to the table that was just created before looping to the next sheet.
Is there a way to create a table without giving it a constant name? Or by giving it a name that builds off of other info in the sheet? For example, I would be good with the naming the table after the sheet name: "Sheet1_Table" or such.
Code:
Sub Auto_Open()
'
Dim sht As Worksheet
If Range("A1") = 1 Then
[Code].....
I want to use a Pivot table to filter data to show just the studies that contain patients from the 'South' area?
As per example below I want to be able to see all the patients in all areas but only for studies that have patients from the south. I put together an array formula that works well for small tables but is too much with one one my sheets that contains 200,000 rows.
Before filtering:
Study ID
Study Short Title
Study Patient ID
Area
1346
LLP
90126
Northwest
[code]....
Need to produce a formula to reference a large amount of data and pull one month at a time:
View 7 Replies View RelatedI have a list of IDs that recurs over a time period. It consists of a Start Data an ID Number. These IDs recur over and over again through one month with different start dates.
See Below:
Date ID
12/1/2013 10:00:00 AM 67890
12/6/2013 12:00:30 PM 67890
12/18/2013 06:30:05 AM 67890
From Another List I'd like to pull a max enddate that is within 24 hrs of the start date. There will be multiple end dates. Here is what the other list would look like.
End Date ID
12/1/2013 1:00:30PM 67890
12/6/2013 4:00:45PM 67890
12/18/2013 9:30:00 AM 67890
Seems like using vlookup with the ID as the lookup wouldnt work because it would just pull the first date it found over and over again.
I have a very large spreadsheet that was exported from an ecommerce site with close to 1000 products. I have one column that I need to extract some text from. This column holds all of the html from the product description and is huge. I only need to extract the actual description of the product, but am having a very hard time figuring out how to do it. I've tried using the mid, left, and right function; but not all of the html is the same so it's not really working the way I need it to.
I have multiple tags throughout the html that I can use with the mid function, but there is more than one occurrence of them. So, how can I tell it to start at the 4th occurrence? I've spent countless hours searching, but I'm a complete novice when it comes to excel and I don't even know what to search for. I end up looking through sites that explain how to pull the Y out of XYZ, which is what I need, just on a much larger (and more complicated) scale.
It was suggested that I set up a macro that will find the 4th occurrence of the word, and then uses the mid function to pull the data out, but when I try to find the word, it says it doesn't exist even though I can see it right in front of me.
I have enormous amounts of data to sort through (as we all do) and I am trying to link them to unique identifiers for easier data maintenance later.
What I want to do is vlookup from the 'solved' sheet a code, which will search the 'data' table and return the 'DFU' code.
Example 2905.xlsx
I have a large list of in rows with multiple column headings: ie. Name, date, amount. I need to generate a separate list whereby the rows that correspond to a given criteria are extracted and listed sequentially.
I have attached a simple example to demonsrate what i mean.
in the attached spreadsheet, i have a column of colours, and a column of objects- RED book, BLUE hat, etc.
I'd like the criteria that i type in the green cell (a choice of colour), to generate a list of all the items that match that colour, and be displayed in the orange cells. So, if the green cell says BLUE, the list should pick out the BLUE dog, BLUE hat etc.
I have a large data (more than 50k rows) in a spreadsheet and i want to consolidate the information's.
here is the sample source data.
Source Data
codeamt1amt2amt3
123456$16.00 $0.00 $0.00
789011$0.00 $18.00 $0.00
123456$12.00 $5.00 $0.00
123456$0.00 $0.00 $7.00
111111$11.00 $1.00 $3.00
789011$22.00 $0.00 $0.00
and the output should be.
Output
codeamt1amt2amt3
123456$28.00 $5.00 $7.00
789011$22.00 $18.00 $0.00
111111$11.00 $1.00 $3.00
currently i'm using the consolidate function of excel inside my vba (which is working fine) but i took forever before it finished, i'm just wondering if there is an alternative way which is more faster.
I have a large amount of data, 97000 rows and 4 columns that I need to sort every 26 rows starting with row 26. I only need 2 columns but I'll take all four, any easy way to accomplish this task. The data is position and time and the time is in seconds from which I need the 0,.5,1,1.5... all the way to 12577 seconds.
View 1 Replies View RelatedI am facing problem to make summary sheet of top 3 value from large number of value of my excel sheet like-
Sheet1
A
Group
B
Name
C
Value
1
Nashid
200145
[Code] ......
Now I want to make summary in sheet2 top 5 value among the list of sheet1 separately from group 1, 2 & 3.
If group number is 1 in sheet1 then top 3 value will be like this-
Group1
Name
Value
Nashid
200145
Jabed
184198
Eman
156622
If group number is 2 in sheet1 then top 3 value will be like this-
Group2
Name
Value
Rubel
172569
Hasin
152304
Monem
104463
If group number is 3 in sheet1 then top 3 value will be like this-
Name
Value
Romel
168251
Nasim
136357
Hasan
120410
I have a large list of data in sheet1. The relevant columns contain a unique identifier (column 'N') and a value of either "True" or "False" in column O. Because I don't want users to have to trawl through hundreds of lines looking for the 'False' entries, I have a macro that copies only those records to Sheet3. In this sheet the user can set the 'False' value to 'true' but I don't know how to return the new value to Sheet2 (possibly for multiple records).
View 9 Replies View RelatedI will try to keep this as short as possible. I have a huge amount of stringed data from dry kiln runs all well organized. I want to make something that will look at all this data (or at lease the ones that I specify) and give me a prediction of what would happen if I was to make a change somewhere in the schedule. I have alot more info if needed.
View 10 Replies View RelatedI have a worksheet that uses Excel 2003's automatic subtotalling feature witSelectihin one of my macros. It works fine on my sample data, but when I try to run it on the larger set of customer data it hangs.
Any suggestions? I've been up all night, maybe this is just something really dumb, but I don't know. I stepped thru the macros, and it is hanging when I press f8 for the line that inserts the subtotals.
According to the task manager it really is hung, not just slow. Waited over 2 hours.
The line of code where it is hanging is:
Selection.subtotal groupby:=3, function:=xlsum, totallist:=array(14, 15, 16, 17, 18, 19, 20, 21, 22), Replace:=true, Pagebreaks:=false, Summarybelowdata:=true
I am a total newbie, so if there is a painfully obvious reason for why this is happening, don't think I'll be upset if you point out how dumb I am. Speaking of dumb, if this issue is posted elsewhere I apologize, I searched for hours before asking, but I may have looked in the wrong place.
I have a large amount of data that I need to be able to summarize certain fields in order to be able to work with the data.
What I have is multiple lines which all pertain to one record and I need to be able to summarize these into one line - which would give me a sum in certain columns. I am working with thousands of lines of data.
There is about 10 columns in the worksheet. I want to be able to tell Excel that if certain columns all match, then to summarize these lines and give me a sum total on a couple of other columns. The data I am working with is invoice data, so I want to know if certain columns - ie - Vendor name, invoice number, cheque number are the same in each row, then to sum these records and provide a total for the invoice amount and cheque amount columns.
This sounds like a complex function to me but I am hoping that someone has a solution for me.
I want to use the attached Test.xls file below and do a lookup from the Data.csv (see zip file) where "Empl ID" (column A in Test.xls) matches "ID" (column A in Data.csv). I want to bring back "SupvID" (column B in Data.csv) into column D in Test.xls.
The problem I have is that in real life Data.csv is a very large file and contains more than Excel's maximum number of rows. I want to automate this using VBA to lookup the values without opening the csv file.
I have looked at a number of forums for answers and I can see where using an ADO connection will probably help accomplish this, but I can't seem to get it to work on these specific files.
I have a large amount of data, in columnA a list of cars, columnB a list of engines, in columnC the city built, in columnD the country to ship, in columnE distance, in columnF the time.
Now some makes, city's and destinations match and sometimes the engine, but i would like to be able to say cellX = (time to ship) where cellA = Ford AND cellB = 1.8 AND cellC = London and CellD = Ireland
The key data is the city built and shipping destination, so i'd like to say if cell A3 = London AND Cell B3 = Ireland AND Cell A5 = Ford AND CellA7 = 1.8 then CellA10 = (distance) & CellA11 = (time)
Vlookup's are of no use due to the large amount of data. But is there a way of writting this command in VB?? or am i missing something in Excel?
See attachment. For example, imagine data in cells A2:N25000 (obviously, this example in the attachment has been shortened). At this point, also imagine that rows 7 & 8 did not exist. So, there is a continuous stream of data for vaious counties in A2:N25000. For the purpose of context, the data contain mortgage information for all the lenders in a particular county for an entire US state.
Is it possible to create a macro that would insert two rows after each county in the data range (e.g., rows 7 & 8 in the attachment)? In other words, in the first row a macro would insert a row (row 7) that sums the results of the top 25 lenders in each county. Then, the macro would insert a second row (row 8) that sums the results of ALL the lenders in each county?
I have some spreadsheets one worksheet little data and it saved as it a 5MB file when it should only save as something like 500Kb, what would cause this?
View 3 Replies View Related