Extract 30% Of Data From Huge Data?
Dec 15, 2011
I have a huge data, from that i need to extract only 30% of data randomly.
Problem Set Types Numbers PP 8769 MM 8894 PP 9019 PP 9144 PP 9269 PP 9394 PP 9519 SS 9644 SS 9769 MM 9894 MM 10019 SS 10144 MM 10269 PP 10394 PP 10519 PP 10644 PP 10769 PP 10894 PP 11019
30% of Type PP is 3.6,That means i need 4 numbers to be extracted under type PP.And 30% of type is 1.2 That means i need 1 numbers to be extracted under type MM and so on...
View 9 Replies
ADVERTISEMENT
Mar 31, 2014
use CTRL+ Navigation Arrow Key on keyboard i.e. if you wanna go to the last Non-Blank row in a column press CTRL+Down Arrow key if your range contains empty cells then use this shortcut SHIFT+CTRL+Arrow key
if you wanna Get the Value of cell in a column you can use this formula
=INDIRECT("A"&COUNTA(A:A))
This formula will work only if there is no Blank Cell in the range.
View 1 Replies
View Related
Apr 27, 2009
When i'm working with huge data on excel say for exam 50000 records and about 25 to 30 columns, everytime i use any formaula and while saving it the file is not responding, what ever the formula like vlookup, countif, converting in values applying filters and removing filters and with that whatever the changes i do that is not seen next time when i open file.
View 9 Replies
View Related
Jul 21, 2014
I have a huge data file. I would like to have excel automatically delete all rows with the value of 7 in column b. Can I do this without manually selecting all of the rows (I can sort by that value, but there are 120,000 rows).
View 6 Replies
View Related
Apr 12, 2013
The project I am currently working on is to extract the from over 25,000 lines of data the top ten of our customers for 2012. Our customers are billed monthly. We have about 2,300 customers. There are several customers who have more than 1 account.
The easy part has been to create a "charges" report and download into an excel format. The information can is alphabetized, but then the challenge comes in. Ideally it would be a snap to total each customer seperately and then somehow be able to extract the top ten from those totals.
View 14 Replies
View Related
Oct 24, 2013
I have this table with some information that I need to work with. The data is simple, but it has many issues, at least for me, to be able to work with it properly.
Basically, I just need to transfer part of the data from this table to another table (in another order). I failed to use vlookup because I need more than one parameter to search the data (Date, Shift and Sector).
The problem I am having to get the codes from the source table is that the same code sometimes shows up more than once per date, per shift and per sector. It's also not following a numeric pattern. I couldn't elaborate any combination of formulas to get the info I need....
I also can't change the format of the source table, because I'm not the one who made it, it has years already, and is constantly updated day by day by a good old man who doesn't know much about computers.
The file is attached : Example.xlsx‎
View 4 Replies
View Related
Mar 6, 2014
I have a file with 300k lines of SKU data. This is data by month for 12 months (so roughly 25K SKUs per month, with changes in SKUs each month.) I take this data and pivot it and drop it into a file.
I noticed last month when I prepared that the SKUs have names next to them, and sometimes the information is pull by the name instead of the SKU. Unfortunately, in the master data, someone entered different names in a few of the months. So for example, in some months SKU 0012 is Green B and in other months it's Green Beans. I need the names to be the same.
Is there a quick way to fix this given that I'll have 12 records and maybe 3 will be one name and 9 will be there other? Finding and replacing every one is a chore because on pivot there are 40k. I don't think I can do a quick remove duplicates because it would remove multiple months. I just want each SKU to have the same name.
View 3 Replies
View Related
Aug 2, 2006
i have two columns in tow seperate workbooks to be compared:
in say inp.xls, there is a column(COL A) containting a lot of entries(numbers) - roughly around 60,000. Now in the other workbook, say mem.xls, i have a smaller set of numbers in a colum(COL B) - roughly around 300.
Now, the problem is, COL B, has numbers which may or may not match with numbers in COL A.
* If there is a match(between COL A and COL B), the row number at the match in COL A.
* If there is no match, the row of the closest number (in COL A) before to the number in COL B.
Example:
ROW COL A-----> COL B---------->
1 2184058252 2184276560
2 2184058280
3 2184058296
4 2184058312
5 2184058320
6 2184773640
7 2184774216
8 2184774272
9 2184774424
Now, i want to find 2184276560 in COLA, since it is not there, i want it to return the row number of the closest(previous) viz. row 5(2184058320).
Now, since this is a huge comparision set, im wondering what is the best way to do it. Im using VBA....
View 9 Replies
View Related
Apr 11, 2012
I wanted to see if there is a VBA code to do the following :
a) Select a TabText Delimited file based on a criteria
b) Import the Selected Data to Excel
I have the vba code where I can open the tab text delimited file in excel, use a selection criteria and then copy the data into excel. But I am having problems with the case where the Tab Text Delimited file exceeds the row limit that excel currently has and wanted to see if the data import can be done without opening the text file into excel at all.
Here is my current query :
Sub TEST()
' TEST Macro
Workbooks.OpenText Filename:="E:Testdata.txt"
ActiveSheet.Range("$A$1:$B$1048576").AutoFilter Field:=1, Criteria1:="1234"
Range(Selection, Selection.End(xlToRight)).Select
[Code] .......
View 3 Replies
View Related
May 23, 2012
Macro to find data from a huge database of items of equipment and find certain ones relevant to an area. I am using Excel 2003.
I have a spreadsheet which has three tabs.
Tab one has a list of equipment with a Ref (Col A), ID (Col B), mile (Col C) and chain (Col D) start and a mile (Col E) and chain (Col F) finish.
There are about 25 different Refs and IDs, but all rows have different Mile and Chains.
The second tab is an identical template of the third tab which is where I want the results to go (see below)
The third tab is raw data which list thousands of items but I want the macro to find the items which are in the first tab using the ref, ID, mile and chain information.
View 3 Replies
View Related
Feb 28, 2014
I am looking for VBA code to extract data from each tab into master tab based on data.
I have 3 tabs (inputs)
Tab1 (Dept A-NAME) (RANGE B4:I7)
SL NO
ID
Date
Customer
Start Time
End Time
Trucks
Supervisor
1
A
2/25/2014
Customer 1
10
1
3
ABC
[Code] .....
Tab 2 (Dept B-NAME) (RANGE B4:I11)
SL NO
ID
Date
Customer
Start Time
End Time
Trucks
Supervisor
1
B
2/25/2014
Customer 3
10
1
3
RTY
[Code] .......
Tab 3 (Dept C-NAME) (RANGE B4:I7)
Is it possible to run vba code to get below result in new tab
SL NO
ID
Date
Customer
Start Time
End Time
Trucks
Supervisor
Result
1
A
2/25/2014
Customer 1
10
1
3
ABC
Dept A
[Code] ......
Condition here is date 2/25/2014, is possible when running code message box pops up to ask date, when we give conditional date it extracts those dates.
Excel -2007 & Windows 7
View 7 Replies
View Related
Apr 18, 2013
So I have this problem in excel with comparing 2 columns.
Basically, I have 2 columns(a &B) that I need to compare with one another and find out the matching data. I am trying to use that matching data to enter in our reports.
Both columns might have duplicate items because I am dealing with premium numbers.
Formula to compare these 2 columns and find the matching data and extract it to a separate column. Keep in mind, i am dealing with almost 20,000 lines of data.
View 2 Replies
View Related
Jul 2, 2014
I am trying to extract unique Data from the information in the output data in the file attached after i have extracted data based on criteria from the database sheet. i have noticed that because i extract data from the database sheet and from there i perform the extraction of the of the unique data , it is not able to extract unique isser name form the output sheet.
View 1 Replies
View Related
Dec 15, 2013
I have a workbook that has ben imported into excel and I need to extract the Cabinet sizes (red text) from ColumnC On Sheet2 leaving out the rest of the info. I have a button on the home sheet that has the code that I am running to generate this report it will do most of what I want but I am stuck at extracting the cabinet sizes.
The data is coming from the SheetComponetListing worksheet and going to Sheet2. I have manually created the end result that I am looking for on the CabinetSize worksheet. [URL]....
View 3 Replies
View Related
Mar 17, 2009
I have a table: A1: K28879
I need only certain rows to be extracted and placed on same worksheet or a seperate one BUT
The rows to be extracted are under a heading above it which that says 9 number and Count eg: 123456789 Count. (Count is alwatys there the number vary however its always in column A)
EG:
45621234 Count
data data etc..... (i need this row)
...another row
...another row
..etc
456345678 Count
data data etc.. (this one)
more data... (not this row)
etc..
The first row under that heading needs to be extracted only.. some under that heading might have 1-15 rows it varies...
View 9 Replies
View Related
Feb 21, 2014
I have 4 columns of numbers. How to randomly extract a certain set numbers from each column. Then display the results from smallest to largest. I provided a sample worksheet..
View 3 Replies
View Related
Jun 22, 2008
I'm running a small messenger company and I've got a geographic area divided into five zones: 1,2,3E,3W,& 4
Deliveries within 1 zone will be charged for one zone.
Deliveries from Zone 1 to Zone 2 will be charged for 2 zones. Etc.
I've got a list of zones in my From column and also in my To column.
I need to write a formula that will give my customers the Zones Charged in a seperate cell.
I learned how to do something like this back in school, but don't even know what to call it anymore. Maybe it's some kind of comples IF function.
FREE Deliveries to anyone in Long Beach, CA to the winner!!!
View 12 Replies
View Related
Oct 9, 2008
how to extract all members of group of data (column) - for example data is (2,4,2,5,6,6,2,6,5,5,2) and to get as result only members of group (2,4,5,6)?
View 6 Replies
View Related
Sep 7, 2009
I have a string (as below - Call them A1:A4) which I would like to seperate into 4 columns (Call them B1:E4).
I have successfully seperated the first part using MID (It's always 5 digits) but the second part has a varing length which then impacts on the third and fourth parts of the string.... Any ideas?
87261 WIMBLEDON 10:08 10:10
87169 NEWMALDEN PASS 10:13
87171 SURBITON PASS 10:15
87177 HMPTNCTJN PASS 10:16
To add to this I am using the POCKET PC version of Excel which does not have all functions so at the moment I am limited to which functions I can use (Can you add functions to the PPC?).
View 6 Replies
View Related
Mar 4, 2013
I want to extract data from Serrial number 2 columnwhich are not match to Serrial number 1 column and past into in third column
View 5 Replies
View Related
Mar 7, 2013
I have 2 columns. One is the years and one is the the associated data.
I need the associated data(BP values) to be sorted in years like shown in excel sheet attached.
View 3 Replies
View Related
May 13, 2014
I have data in ms excel and in column "F" is the Qty
If the Qty is Less then 40 then extract the data and put into the sheet "Less then 40"
I have attached sample excel file : Extr.xls‎
View 2 Replies
View Related
Oct 7, 2008
I would like to create a macro so that it will pull all the data from the worksheet "Data" and put it onto worksheet "Extracted Data". However I only require the rows which have Booking Office No SU17.
View 3 Replies
View Related
Jan 12, 2009
I have some code that is importing data from a text file that contains ~35,000 line items and is ~50 columns wide (this part of the code is working fine).
Once the file has been imported, I need to copy certain line items to a sheet called “output”. The code would be something like this if reading line by line:
If the value in row x, column 5 (it is always in the 5th column) in “AccountList” then
copy entire row and to next empty row in sheet(“output”)
The “AccountList” is separate sheet with a list of account numbers in column A with ~250 items.
I am not sure if the code to extract this data should occur while I am importing the data or if I should do it after and run through the data again.
View 4 Replies
View Related
Apr 13, 2009
I have a report that is downloaded off a company database and creates a report that is attached.
Is there a way to create a macro to extract some data that I need and leave the rest:
I need to keep and everything in the assigned column
PB ID (A3), Issue (B3), Group Code (J3), Group Name (K3), Sub group id (L3), Sub Group Name (M3),Subject ID (L3), Subject Group Name (M3), Subject ID (N3), Subject (O3), PB Status (P3), CPM Report Name (T3), CPM Profile Name (U3), Profile State (V3).
The worksheet's name changes so I can't reference the worksheet. Attached is a copy of what I get when downloaded.
View 11 Replies
View Related
Jul 14, 2009
I have an excel work sheet with 2 columns and hundreds of rows. The first column is time in minutes over a number a years and the second is a list of corresponding data readings (numbers). I want to only select on the hour data from the list however I cannot seem to do it. The times vary in the sense that they are not all just 1 minute apart, some are every minute, then there might be a 10 minute gap in data, a few more minutes, then another gap etc.. All I want to be able to do is to select all the on the hour data from the list and its corresponding data value.
View 6 Replies
View Related
Jan 11, 2010
I built an estimating spreadsheet for the electrical construction industry and am trying put together a "Materials List" on another worksheet. I want the materials list to display materials which have a value greater than zero.
Example, the 1st worksheet is my estimating worksheet which contains a list of 30 materials. The 2nd worksheet is a "Bill of Materials" that I would like to display in a proposal format to the customer. I only want to show them materials that have a quanitity of more than zero from the estimating worksheet.
View 6 Replies
View Related
Aug 28, 2008
how to use COUNTIF to count up the number of times something appears. What I want to do is add up values if an H is present in the cell with the value eg. if 3 cells contained:
H, 3.5
7.5
H, 3
...then 6.5 would be returned.
View 14 Replies
View Related
Dec 28, 2011
I have a sheet of data where I need to extract data from between 2 dates, if the result of the 2 dates are
View 1 Replies
View Related
Feb 3, 2012
In column M I have data that lists a number, then number of metres between brackets. For instance
Column M
11 (79m)
1 (5m)
22 (123m)
Basically what I want to do is to always return the number of metres without m, so the data between "(" and "m".
I tried to do this using the find and mid formulas, but I either haven't got it quite right. I'm only after a spreadsheet solution (not VBA).
View 6 Replies
View Related
