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.

View 14 Replies


ADVERTISEMENT

Searching For And Extracting Dollar Amount From Text String

May 8, 2014

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 ...

View 4 Replies View Related

How To Go End Of Huge Data Range

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

Working With Huge Data But Getting Hanged

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

Huge Data File Delete All Rows With X Value?

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

Getting Data From Huge Table To Smaller And Organized One

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

Extracting Numbers From Alphanumeric Strings (unite The X Amount Of Numbers)

Aug 16, 2008

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

View 14 Replies View Related

Find And Replace On Some Duplicates In Huge Data File

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

Nearest Number Match - Huge Data To Compare

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

Extracting Data From File In Directory And Extracting Filename

Mar 20, 2014

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.

View 4 Replies View Related

Combining Customers Data Into A Final Worksheet Using MACRO

Sep 11, 2007

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?

View 10 Replies View Related

Selecting And Then Importing Data From Huge Tab Text File To Excel

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

Excel 2003 :: Find Data From Huge Database Of Items Of Equipment

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

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 View Related

Macro Which Saves Data From Temp Worksheet To Customers Worksheet?

Mar 17, 2013

Dropbox - Final.xlsm

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.

View 9 Replies View Related

Formula- To Calculate The Amount Due Based On Cumulative Sales Once A Breakpoint Amount Is Reached

Jan 28, 2009

I need a formula to calculate the amount due based on cumulative sales once a breakpoint amount is reached.

Example:

Breakpoint:
cum sales are > 500 pay at 3%
cum sales are >1,000 pay at 2%

month/ sales/ cumul sales/ amount due
jan/ 100.00/ 100.00/ 0
feb/ 600.00/ 700.00/ 6.00
mar/ 600.00/ 1,300.00/ 18.00

and so on...until the end of year.

I tried using an if formula by could not get it to work.

View 9 Replies View Related

Reference Cell & Add Amount If Positive & Subtract Amount If Negative

Jun 5, 2008

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.

View 4 Replies View Related

Macro For Base Amount And Net Amount Calculation

Nov 20, 2012

For the below macro.

ABCDG
CodeTotalBaseNetA1 = 20%
A110083.3316.67A2 = 10%
A2350318.1831.82A3 = 7%
A3220205.6114.39A0 = 20%
A015015030

A1 is the code, take the total(B) and calculate the base(C) on the percentage given in the G, net = B-C. for A2 and A3 it is the same process.

For A0 the total (B) is equal to Base(C) and calculate the net on the given percentage in G.

View 1 Replies View Related

Converting Vertical Data To Horizontal For Varying Data - Large Amount

Jan 20, 2009

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.

View 9 Replies View Related

Top 5 Customers Using Formula

Apr 1, 2014

I have huge data of income of employees.

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.

View 1 Replies View Related

How To Calculate New Customers

Apr 13, 2012

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:

ABCDEF1February 2, 2011
Week1232February 15, 2011
Customers???3March 15, 2011
4April 15, 2011
5April 15, 2011

[code].....

View 4 Replies View Related

How Do I Create A HUGE Table?

Oct 26, 2008

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.

View 7 Replies View Related

File That Is 50mb And I Don't Know Why It Is That Huge

Jan 14, 2005

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?

View 9 Replies View Related

HUGE List Filtering

Sep 19, 2008

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.

View 9 Replies View Related

Extracting Data From One Massive Worksheet To Split To Smaller Sheets Of Data

May 20, 2008

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

View 10 Replies View Related

Search Data For Largest Amount

Nov 17, 2009

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.

View 4 Replies View Related

Sorting Large Amount Of Data

Nov 21, 2011

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 Related

Calculate Best 50 Percent Of Customers?

Jan 22, 2014

Making a customer analyse and now I want to know which customers are resposible for 50 % of my revenue.

View 4 Replies View Related

Counting Total £££ For Customers That Can Appear More Then Once

Apr 15, 2009

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.

View 3 Replies View Related

Retrieve Top 3 Customers In A Table?

Nov 12, 2012

Code:

Customer 1 5,000
Customer 2 10,000
Customer 3 6,000
Customer 4 4,000
Customer 5 3,000

If I have a Table as above, how do I return the Top 3 Customers in 3 cells?

I can get the Values with

=LARGE(Table1[[#All],[Sales]],1)
=LARGE(Table1[[#All],[Sales]],2)
=LARGE(Table1[[#All],[Sales],3)

How would I return the Customer Names?

View 4 Replies View Related







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