Extracting Top 10 Customers From Huge Amount Of Data
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.
I have about 2000 rows of text. Each row is a short write up about prospective new business. There is a reference to a projected dollar amount Within 'MOST' of the write-ups. In order to generate a report about the potential dollars being projected, I need to find an easy way to extract the dollar amount from the text.
In most cases, the projected dollar amounts are preceded by "FY2014" then a "$". However, there are a handful of cases where there is no FY2014, but some variation of the year indicator. Most of the dollar amount entries are written is short text - FY2014 - $5k, 2014 $15k. While some others are written out - FY2014 - $ - $20,000. In still other cases, within the writeup reference is made to the amount of product projected to be shipped by using the dollar symbols. For example - Estimated ship totals $$ for FY2014 = $5k. I've tried writing some formulas, but as in the last example - the first dollar signs are recognized rather than the dollar sign immediately before the value.
Sample data - Estimated ship totals $$ for FY2014 = $5K New Customer Prospect 4/9/2012 Customer has still not decided if he
2014 $15K Funco 4/7/14 working on the program for the demo ...
Over 130 samples tested with about 90% accuracy. FY2014 Ship $$ = $20,000 at least. Setter Line has 7 plants ...
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.
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.
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).
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.
Is there a way either by VBA or manually (preferably both, if possible) to actually unite the X amount of numbers that are in a cell given the contents is alphanumeric? I'll give you the following examples to see if you can understand what I' referring to?
DATA output should be asd67,h876 --------> 67876 2,3,ujdj5&34 -------> 23534 909k86m34 --------> 9098634
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.
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.
I have a directory folder with an active workbook and another workbook id like to copy data from. The Following macro opens up a file in the same directory, copies some data and pastes it in the active workbook. However with this code I have to specify the filename, 'Data.xlsm' in the example code. I would like it to copy data from the only other workbook in the current directory WITHOUT having to specify the name in the code, so just opening it up no matter what filename it has.
In addition I would like to extract the filename from the workbook im copying data from and paste it into the activewoorkbook in sheet 1 Cell A1. I had a look at getopenfilename function but cant seem to make it work for my purpose.
I have many customers particulars and data coming in everyday. How i do create a code such that with one click i'll be able to update my worksheet in Excel without typing in manually?
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.
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.
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...
Here is the above link. Am looking at a button which saves whatever is the temp worksheet row in the customers worksheet. The temp worksheet basically takes the data from the Quotations worksheet and places it in a row.
Im trying to set up an active running inventory sheet where: (A)the progressive daily sheet cells reference back to the corresponding master sheet cells fluctuating the master values, (B) the same progressive daily sheet cells reference back to a cummulative totals-cell based on whether I added or subtracted inventory. I want to make a copy of the blank "sheet 2" with all of the formulas and move it to the end of the workbook each day and enter new values which will reference back to the master sheet so that I can click on a date sheet and see an individual day's values or click on the master sheet to see the fluctuating inventory on-hand and the cummulative +/- totals of all days combined. I've got a couple hundred individual cells to reference. I've tried and tried but I can't make it work. Heres what I need to do:
I need to reference individual cells from "sheet 2,3,etc" back to a corresponding cell in a master sheet. But I need the values in each cell in "sheet 2,3,ETC" to increase or decrease the corresponding cell values in the master sheet. For example: If the value in the master sheet B5 is 200. Then in sheet 2, I enter +50 in B5, I need the master sheet cell B5 to increase by 50 to 250. I also need a way to decrease the cell value in the master sheet B5 if I enter a negative value -50 in sheet 2 B5. I also want to know if I can reference the same cell values entered in "sheet 2,3,etc cell B5" back to totals columns C5 for adding inventory or D5 for subtracting inventory in the master sheet where the master totals columns would reflect cummulative totals added or subtracted. For example: if the value in sheet 2 B5 is +50, then the value in Master sheet C5 would add 50 to a progressive total. But if the value in sheet 2 B5 is -50 then the value in master sheet D5 would add -50 to a progressive total.
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.
In column "A" i have names of employees, in column "B" i have income of last month and in column "c" i have income of current month. In column "D" i have calculated difference between two month's income(C1-B1).
Now based on values in column D i want to find names of top 5 gainers and top decliners (i.e. i want names of top 5 people who are having highest value in column "D"). I don't want to use pivot and sort, I want these names by using any formula.
Its working perfectly. But now I have added one more column in the beginning as country names. Now i want to make a dashboard so that i will create a drop down with country names and country i will select top gainers should come with ur given formula.
I am working on a project and I have a list of customers. I need to calculate how many customers we had in week 1, week 2 etc of year. This would allow me to draw a beautiful chart of our company. How would you calculate this? Here is some data:
I have to create a 12 row 14 column table which will be printed out and put onto a (around) 20 by 20 poster board...so it has to be HUGEEEEEE. How do I do this, all the boxes inside the table I want to have control of meaning size and stuff. I will have to write in all boxes.
P.S: Yes. I know you guys prolly think im a idiot but this is my first time and my due date is appraoching fast, if anyone could help that would be great.
I have an excel file that is 50mb and I don't know why it is that huge. I did not create this file and was asked to look at it and figure out why it takes 10 minutes to open?
There is 3 tabs and very little data. How can it be so huge?
I'm working out some vba script, that could filter up to 300,000 rows long list.
here is sample from that list:
1000999999027FRESH PASTA FETTUCINE WITH TOM102002P N0809100000035000000012810000000+0000000+ 0000000000000000+0203001896409 2000999999027020300189640900000000000005740200070063 1000999999027FRESH PASTA FETTUCINE WITH SPI102002P N0809100000025000000015700000100+0000000+ 0000000000000000+0203001896454 20009999990270203001896454000000000000057010180265125740200070070
As you can see, the rows are starting with 1000 and 2000. And that would be the criteria.
I'm importing the loooooong list file onto sheet1, and i'd like to sort the list so it puts all the codes starting with 1000 onto sheet2 and rows starting with 2000 onto sheet 3. With regular filtering mode it's impossible and keeps giving errors. I know it's possible, but i can't figure it out anymore.
I was thinking about to make script that would copy one row from sheet to sheet and loop it. But my mind just crashes doing that.
10/05/0808:30:00 AMValid Card EntryLamDoor 1 10/05/0808:31:00 AMDoor Leave Open Door 1 10/05/0808:32:00 AMDoor Closed Door 1 10/05/0808:41:00 AMValid Card Exit Yap Door 1 10/05/0808:46:00 AMValid Card Exit Lam Door 2 10/05/0809:14:00 AMValid Card EntryLam Door 2 10/05/0810:18:00 AMValid Card Exit Lam Door 2 10/05/0810:19:00 AMValid Card Entry Yap Door 1 10/05/0810:40:00 AMValid Card Exit Yap Door 1 10/05/0810:42:00 AMValid Card EntryLamDoor 2 11/05/0808:30:00 AMValid Card EntryLamDoor 2 11/05/0808:20:00 AMValid Card EntryYapDoor 1 11/05/0810:20:00 AMValid Card Exit Lam Door 1 11/05/0811:40:00 AMValid Card Exit Yap Door 1
the above is the data i need to work with (dates are in dd/mm/yy). i need to find a way (a macro, preferably) to extract the date and time corresponding to one person, and paste it into a new sheet, e.g. at sheet 2 (renamed to "lam") should be
10/05/0808:30:00 AM 11/05/0808:30:00 AM 10/05/0808:46:00 AM 11/05/0810:20:00 AM 10/05/0809:14:00 AM 10/05/0810:18:00 AM 10/05/0810:42:00 AM
I have a file with 2 sheets. The first sheet contains a list of unique invoice numbers. For each invoice, I need to look in the data table (the second sheet) and locate all the products that were sold under each invoice and list the product ID with the largest sale. For example, invoice 12345678 had sales for 5 different products as follows:......
In this example, I would want to list the largest sales amount and the Product ID for this invoice (invoice 12345678). I have attached a sample of the file and completed the first 8 records as an example.
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.
I have the following sheet, each time a customer buys a savings product they are put onto the list, the same customer can appear as many times as they like on the list is they buy more than once, buys product on 1st of april then another product on the 2nd of april they appear 2ce on the sheet.
I have done the forumla to sum the amount of times they appear on the sheet ( total number of sales ) but I need to work out the total comission per person.
