i have one excel sheet (refer attach) i give some example 7 to 8 customers, but in sheet nos. Of customer name a to z suppose to 3000 customer list. Now i want to create automatic customer alfa- numaric code.
View 2 RepliesLook at the image I've attached.
I'm trying to have the table on the right match the first two columns to display the customer name taken from the table on the right.
testtable.jpg
I have a column that contains sale # and another column that has cust names.
How could I get the highest 10 sale #s for a specific customer?
Is this a place for index / match?
Using Excel 2003 and am trying to do a calculation by customer, by service type. Attached is the workbook and I've tried numerous ways to do this. On the revenue calculation worksheet, I am tryng to calculate the type of service minutes from the data retrieve worksheet, multipled by the service rate on the rate table for the specifc type of minute by customer. I'd like to build it as a vlookup by customer and then by service type, but have run into difficulty.
View 4 Replies View RelatedI`m trying to create a macro that produces an index of jpgs contained in a folder.
I need also some properties of the file. With these I will create a new naming system and rename existing files. To do part of this I copied from here a macro and adapted it. Copied: i.e. I don`t know much!!!
Problem 1)
Initially it worked, then "Run-time error 53, file not found" started appearing without me being aware of making any changes to the code.
Problem 2)
I would like to capture some of the properties of the files (pics from digital camera). Which jpgs properties can I access to and how?
What about exif data?
Explorer shows 3000x2000 pixels. Can I get this too?
Sub Get_Folder_Contents()
Dim folderstring As String
Sheets("Sheet1").Select
folderstring = ThisWorkbook.Path
f = Dir(CStr(folderstring & "*.jpg"))
n = 2
Do While Len(f) > 0
Cells(n, 1) = folderstring
Cells(n, 2) = f
Dim fs, fl
Set fs = CreateObject("Scripting.FileSystemObject")................
I am running Excel 2003.
In my Excel file, I have one worksheet with about 36 columns and 2500 rows of data.
I am looking for a way to create an automatic filter which will look at one column and filter out 13 specific phrases, placing the entire rows which contain the phrases in a new worksheet in the same file. Every row that does not contain one of the phrases I would like to place in another new worksheet in the same file.
I want to run this macro/add-in on multiple files.
I have the following formula copied down from A3 to A200 in a spreadsheet, which creates a unique standardized customer number for the each names listed in B3 to B200:
=IF(B3="","","S"&LEFT(B3,1)&"-"&TEXT(SUMPRODUCT(--(LEFT($B$3:B3,1)=LEFT(B3,1)))*10,"0000"))
Therefore, with the following names listed in B3 to B10:
UPS
FedEx
Fisher Price
Bell Canada
Grand & Toy
Rogers Cable
Dominion
Blue Jays
The respective customer numbers would be:
SU-0010
SF-0010
SF-0020
SB-0010
SG-0010
SR-0010
SD-0010
SB-0020
I would like these numbers to be static, so I would appreciate it very much if someone can give me a VBA code to replace the formula.
I currently have a number of data sets relating to customer's and values, eg.
Customer 1 12 12 12 12 12
However within this data set I also have a number of items where different customers have been grouped but are separated with a “/”, eg.
Customer 1 / Customer 2 / Customer 3 12 12 12 12 12
I want to be able to, find these rows, and break them out so that each customer with relating data is on a separate row, eg.
Customer 1 4 4 4 4 4
Customer 2 4 4 4 4 4
Customer 3 4 4 4 4 4
We've got a bug in our finance system where it can't handle any transactions that have sales but no related commission. The BI team provides a CSV file separately with this information and the sales team has to manually input it. I know how to create a template that can be uploaded into the system but don't know how to pull the data into the template from the CSV file.
I've created the attached example and what i'd like is a drop down box in cell B1 (template tab) listing all the customer codes in column B on the data tab and then based on your selection all the related transaction lines pull into columns A to F (starting on row 4).
Manual Invoicing Query.xlsx
I have a master worksheet that contains a lot of datas. Example below:
Invoice # Code Customer Money
1 1101 a 10
2 1102 a 20
3 1102 b 30
4 1103 c 40
5 1104 d 50
6 1101 a 60
7 1103 c 70
After creating a pivot table with code, customer as Row and money as Data, I get this:
Sum of money
Code Customer Total
1101 a 70
a Total 70
1102 a 20
a Total 20
b 30
b Total 30
1103 c 110
c Total 110
1104 d 50
d Total 50
Grand Total 150
Suppose now, I have another data named Rate that I need to associate with each pair of Code and Customer. See below:
Sum of money
Code Customer Total Rate..................
I am building a country ranking model which ranks several different macroeconomic indicators and applies a weighting to each of the indicators. I have an overall ranking column which is an average of all the ranks. The problem is whenever I change any of the individual indicator's weighting it obviously affects the country's overall ranking. Is there a way that the model can automatically "re-sort" the overall rankings without me having to manually click on the sort by smallest to largest filter each time?
PS I'm using Excel 2007.
I have an excel file which contains following data in it.
Col-A Col-B Col-C ......
Cust Cd Name Sales
=======================
101 AAA 1000
101 AAA 500
101 AAA 3000
102 BBB 800
102 BBB 200
103 CCC 200
103 CCC 200
103 CCC 200
I need to create following three workbooks with name based on Cust Cd from above excel file.
Workbook - 1 : 101.xls which contains records only pertaining to Cust Cd 101.
Workbook - 2 : 102.xls which contains records only pertaining to Cust Cd 102.
Workbook - 3 : 103.xls which contains records only pertaining to Cust Cd 103.
I'm attempting to create a database on Excel 2007 to create a record of all the documents I use at work. At the moment I have an index sheet where I enter the indivudual project numbers. Is it possible in VB to take a value from this index sheet, copy an existing (template within the workbook) sheet and rename the new tab with the project number from the index?
Example:
As there will be many projects within the database is it possible to code it such that:
New Sheet1 created from template within workbook -> Tab renamed according to value of 'Index!B4'
New Sheet2 created from template within workbook -> Tab renamed according to value of 'Index!B5'
New Sheet3 created from template within workbook -> Tab renamed according to value of 'Index!B6'
and so on......
Further, is it also possible to automatically create the new sheet as soon as a project number is entered into 'Index!B4', 'Index!B5', 'Index!B6'?
ie: Text entered in 'Index!B4' -> new sheet created and renamed automatically
I would like to create a macro which will copy the names of the files and folders of a chosen directory into a spreadsheet, effectively creating an index. I don't know how to go about this or even if it is possible. I'm trying to avoid having to do it manually as the directory in question spans 1500 folders...
View 9 Replies View RelatedIs there any way, in which i can make my code dynamic?? For example, in a code written in VBA, there is a reference to particular sheet, say "ABC". If i change the name from "ABC" to "ABCD" from excel, the underlying code should change automatically from "ABC" to "ABCD".
I want to make my code dynamic..
I have a list of Spid numbers and a service category identifying if it is a Water Spid or a Waste spid.
The Spid is split into 2 parts. The Core is the identifier and the Category states if it is a Water or a Waste spid.
If the 1st digit in the Category is a 1 = Water.
If the 1st digit in the Category is a 2 = Waste.
There may only be a water spid or a property may have a water and waste spid.
What I want to do is have the information on 1 row rather than 2 rows
I have data in this format
SPID
Core
Category
Service Category
100000000151
100000000
151
1
[code].....
and I need to convert it into this format.
Core spid
Water
Waste
100000020
100000020101
100000020251
I have tried IF(lookup) and it works for the 1st row but I can't get it to look at the 2nd row.My data consists of 400,000 plus rows
I have this macro below and would like the code changed to find the "Customer Number" column by name rather than by column B. Note that the "Customer Number" column will always be somewhere in row 1.
View 12 Replies View RelatedI have a macro that gets activated as soon as i select the sheet which the macro is situated in. This is not so great because if i am bringing something from the clipboard the automatic macro erases what i have copied and therefore i cannot paste anything. I can make the automatic macro run from a different sheet. Here's how:
[Code]....
My data is in the following format,
Company Name Bill Status
GTAEPL ACCEPTED
GACEPL REJECTED
I need a vba code if the in Bill status column rejected is there all those rows to be deleted.
I have finaly completed my project to include all the bells and whistle needed to make an ok task a bit more exciting. However, i am having a problem when the code is executing. It seems to "break" at random points in the process. I am not able to duplicate this error at my home (nor do i want to) and I can not quite figure out what is causing this as it only happens at my work computer. I have tried my coworkers computer and the code executes flawlessly like at my home.
Strangely enough, when the code breaks I am able to hit 'continue' and it continues on its way until the next hiccup. Please assist as this fluke is causing my brain to spiral.
I have already got an anwer for this long back from this site. The code was writted by Mr. Krishnakumar
the thread is here :[url]
i need some changes to be made in this code. The existing code creates and updates the details in the sheets automatically from the master data. I just need the sum of Column I in all the sheets after the last row of Column I.
selecting all the sheets and typing the formula in I column is not possible because, the last row in Column I is different in all the sheets.
In sheet 1, the last row of Column I is Row 15, in sheet 2 Row150 is the last row.
I guess something could be done in macros.
follwing is the existing
Sub TestIt()
Dim sWS As Worksheet
Dim Sellers As Range, Seller As Range
Dim lRow As Long, fRow As Integer
Dim CopyRng As Range, ws As Worksheet
The below code is for pulling the titles of a series of tables into a worksheet and make it an index with link to each table. It worked when the title of the table is in one cell. However, the title now combined with project name and no long appear in e.g. A1 (before) now: it is merged in A1-BB1 (width of the table). How the below code could be modified to pull the title as the index page now?
Before: Table 02: XXXXX (Appear in A1)
Now: Project Complex: Table 02: XXXXX (Appear in A1-BB1 merged)
I have a worksheet that has user fillable cells from B11-AR53. I want the user to be able to attach comments to cells and then to be able to print them on secondary sheet. I know that excel already can perform this function, but it references the cell address and I don't like that method. I want to have the comments on the printout indexed and tied to an index number in the cell. Creating another worksheet with the comments only is perfectly fine. I am currently using this formula (below) to index the comments on the worksheet that they are attached to.
Ideally my report/second worksheet would list-
1 - Comment that is indexed as 1 would be here
2 - Comment that is indexed as 2 would be here
....and so forth
Sub CoverCommentIndicator()
Dim ws As Worksheet
Dim cmt As Comment
Dim lCmt As Long
Dim rngCmt As Range
Dim shpCmt As Shape
[Code] .......
I have a workbook containing 2 sheets called ('EPM Extract' and 'Sheet B'). Column 'B' of the 'EPM Extract' sheet contains a list of Project IDs (numerical), and 'Sheet B' also contains a list of Project IDs (also in column B), plus all of the additional data I require.
Now, from the required cell within the 'EPM Extract' sheet, the normal Excel the code would read as =vlookup(B3,'Sheet B'!B5:W32,11,False), upon which I would drag down the code from that cell to all other required cells below. However, these are my issues:
* How do I even begin to write the code within VB, as I know that variables and arrays have to be set
* I need to apply the vlookup across 12 columns as I am bringing back 12 different results (i.e. I need to copy results from column counts 11 to 22 from 'Sheet B' to columns in 'EPM Extract' sheet.
*The amount of data in the sheets will vary each month, so I need to know how to apply the code only to the amount of rows in the 'EPM Extract' sheet, so some kind of R1C1xl.UP will need to be defined also.
I have a workbook with four worksheets named
FilteredData
FilteredDataFormat
array
arrayFormat
The two sheets named FilteredDataFormat and arrayFormat exist strictly to preserve headers , and column widths.
So that for convenience sake I can clear my data without specifying a range that excludes the headers.
I simple clear the contents of the 'FilteredData' and 'array' then copy and paste 'arrayFormat' and 'FilteredDataFormat'.
Sort of an unconventional and lazy approach but I see no reason why this will not work.
Here is my original code and what happens is that excel creates another workbook for some reason unknown to me
For some reason the following code creates a blank page everytime. Is there a way to have it not do this that I am not seeing?
View 2 Replies View Relatedhow to create commandbutton code so let me explain what I am trying to do and anyone that could provide me with some basic code details to solve my problem:
When the command button is clicked in an excel worksheet I want a pop up box to come up containing specific information sourced from two other excel worksheets depending on the commandbutton. What I want to know is what code do I enter from the visual basic view in order for the box to appear with the right information.
I need to distribute it to several areas of my company. Each area only needs part of the report. Therefore I have written some code which splits the existing report by area and creates a brand new report with that smaller chunk of data in it.
However I now realize that the new sub-report will require some code of it's own in order to function. My problem is that only the sub-report will be distributed to the end user. The core report will not. Therefore any code written for the new sub-report must be included within the sub-report.
How do I translate this to a vba code?
=INDEX('Workers List'!D3:D13,MATCH('Workers List'!C4,'Workers List'!C3:C13,0),1)
what i have done is
test = Application.Index(Sheets("Workers List").Range("C3;C13"), Application.Match(1, Sheets("Workers List").Range("D3:D13"), 0), 1)
But i keep getting an error, may i know whats wrong with it?
I want to write the following function using VBA. =index(B2:D8, match(lookup_value, A2:A8, 0), match(lookup_value, B1:D1, 0))
View 8 Replies View Related