I have an excel sheet with over 50thousand entries, each entry is identified by an ID code in the L column. I want a code that creates a new workbook with all entries having thesame ID code. if it's not too much, i want to have a pop-up window that asks me to enter the ID code.
View 9 RepliesI have two workbooks. I use excel 2003
I need to update the master workbook with data from the invoices workbook using the following criteria.
In the master workbook column C has unique job numbers.
In the invoice workbook column F has unique job numbers.
Both worksheets are the first worksheet in both workbooks.
If the job number in the invoice workbook is already in the master workbook do nothing.
If the job number is in the master workbook but not the invoice workbook then delete the entire row from the master workbook.
If the job number is not in the master workbook add it to the master workbook.
To complicate matters the master workbook is shared. It is my understanding that shared workbooks can't contain the macros needed ? It would be preferable that the master sheet had them but if not ...
on generating a unique ID for each row in a spreadsheet. My current manually entered ID numbers are alpha numeric, so I want to use a formula or macro to follow the same format.
For example the IDs may be ABC001, ABC002, AAA001, AAA002, AAA003. So user enters info in a form built in excel, and I run a macro which takes the data entered into that form and populates another spreadsheet and I assign an ID number.
The ID number should look for the next number in the sequence, based upon the first three characters.
I don't want to create a function to use within a cell, I need the Sub to run through a user form. I have a Userform to create a new account, and need to add a 6 digit unique identifier after a predetermined prefix ("T" for toddler, "Y" for youth, and "A" for adult). The constraints are that it cannot be duplicated (this is for multiple children registered within the company). I have tried using the GUID but having difficulty stripping characters and adding the prefix (this was my fix inclination).
View 8 Replies View RelatedI have a list of e-mail addresses stored in an Excel file (column A). I need to generate some passwords basing on these e-mail addresses which would then be automatically stored in the same row as the passwords, only this time in column B. The passwords could be numbers based on some text to number conversion (which I could additionally add some mathematical operation to, to make the password harder to crack). The passwods would then be distributed among the owners of these e-mail addresses.
View 5 Replies View RelatedI'm wanting some sort of formula to generate a reference number that relates to numbers i put in a cell. i want the end result to look something like this:
Reference number
362 3620001
456 4560001
362 3620002
487 4870001
456 4540002
As you can see id like it to generate a reference number to the number that i input, and also when i input that number twice or three times it picks that up on the reference number also.
How to generate all the possible combinations of 5 numbers, e.g. 1,2,3,4,5. Then 2,3,4,5,1 and so on, in a series of excel columns and rows. Also for 6 numbers etc.
View 14 Replies View RelatedI want to generate numbers (1 to 15) in cells A1 through O1, but the
number in each cells should be unique compare to the other cells, how
can I do it? If I use =randbetween(1,15), I can not get unique number
in each cell, some numbers are duplicated.
I want to add & generate unique numbers in a column of similiar words..
For Instance:
RED
RED
RED
BLUE
BLUE
BLUE
etc..etc..
I want it to look like this:
RED1
RED2
RED3
RED4
RED5
BLUE1
BLUE2
BLUE3
I want infinite numbers next to the same wording but unique.
I have 2 rows A & B.. NAME & MOBILE PHONE Title in line 1
I am not to familiar with excel. Step by Step please, anyone out there.
I saw a similar posting, but the formula did not work out for me.
Then i am going to save the file as a CSV
VBA that autogenerates a unique number when a cell in excel is filled?
View 5 Replies View RelatedI have 2 columns of data in one sheet that list dates (say Master Dates) and transactions (say Master Transactions).
In another sheet i would like a formula that can choose the correct transactions that fall between certain dates (call Date Beginning and Date End) and generate a list.
i have tried this array:
If((Master Dates>=Date Beginning)*(Master Dates<Date End),Master Transactions,"")
Problem with this is when used in different columns for different date ranges there are large gaps in the generated list...
I'm trying to use Advanced Filter to generate all the unique values within a list, only at times I want to Autofilter the data, then use the advanced filter to determine all the values of the slimmed list. For some reason, this works fine on column A, only returning the values that begin with a 1 when the column is filtered for all values beginning with 1. However, when I run the advanced filter on column 2 with column 1 filtered to all beginning with 1, all values all returned regardless of whether column 1 criteria is met. Either way, after the advanced filter, the autofilter has been removed.
View 7 Replies View RelatedIn an earlier life I was tasked with finding a "random" method of selecting two numbers from a "1 to 20" range so that the generated numbers can be applied to an set of people who will be partnered in a golf game draw.
It is only one draw per year so I don't care if the players have previously played together in past years.
easily be modified by a "passable knowledge level" person to be able to select a mystery "9" out of 18 holes that count for scores that particular round.
(btw: this is an issue only for the 20 guys who go away once a year to play golf, the world will not collapse if I have to draw numbers out of a hat, just looking for a slightly more elegant solution and I already have a few scoring macros so my first guess (but not only possibility) is VBA)
I'm trying to set up a system of sorts to track "services"
At the top of my 'new service" page, I need a button, which I then need to have a macro run on.
Sheet 1 is basically where I'd be entering the info, and sheet 2 where it would be stored. The button at the top is called Generate New "service' Number. This then needs to generate a new number on sheet 2 in A2 (then once 2 is populated, generate new in 3, then 4, then 5 etc...
I have a macro on Save Service Button at the bottom, which transposes the data into what would be used as a database to store all the services. That Macro then also needs to only transpose and paste data into the row corresponding with the "service number".
How do I create a 6 digit unique random number for use as an ID in column A. Once created the rows with preexisting 6 digit unique random ID numbers must not change every time new rows are added.
View 9 Replies View RelatedIs it possible to have a formula that generate a Unique list (List 1 and List 2) of Missing values in List 2 by comparing two lists? Something like this
List 1 List 2 Missing in List 2
1 1 3
3 4 6
5 5
6 6
I've read a lot of tutorials on how to use pivot tables, but found them either too basic, or too advanced, and never finding any example of what I am looking to do.
I have a database, column A holds names, and column B their phone numbers. I'd like to generate a list of unique names and their associated telephone numbers. I figure I can use a pivot table to do this.
But I do not quite understand how to format the pivot table to put the name in one column and the phone number in the next. I will use this pivot table as a vlookup range. I assume I can do this.
THE UNIQUE FIELD WILL BE THE "ARN" COLUMN
Each week / month I will get updated data which I will paste into the "updated data" tab. I then have a formulated tab that pulls through the information, performes some checks then calculates payments. Some records will be ready to be paid and some wont. Anything with a total claim amount will need to be paid so
1st button - Once I have pasted my data in and the sheet has calculated I need a button that copies any row from column a to y on the formulated tab that has a total claim amount. i then need the copied rows to be pasted into the paid tab and then for them to be removed from the updated reports tab. This will just leave data with totals in the paid sheet and data with none still in the Formulated tab
The following month / week I will receive another download which I will paste into "Updated reports". This data will contain records that have already been paid and once the formulated tab has calculated these records will show in the duplicate column so....
2nd Button - Anything that has a total claim amount and has a duplicate flag needs to be deleted from the updated reports sheet so it is not paid twice.
I am a hobby programmer trying to automate data entry into SAP. With GUI scripting switched on, i believe it is possible to get the unique identifiers of any field or control on a SAP screen. I am wondering if there is a dll or some mechanism whereby i would be able to get a tree structure of control identifiers for a particular screen so that i would not need to go through each control individually.
View 1 Replies View RelatedIs there a field setting in pivot tables to return the number of unique records?
Is there an excel formula to define a range of cells, and returns then number of unique records: (i.e =Unique(A1:A10)?
I have a worksheet which contains 2 columns which is needed to work my problem.
Unique Work ID and Description
The unique work ID are the same for each description, but there are up to 5 different description associated with each unique work id.
I'm looking for an automated process but where to start to convert the 5 rows in the unique row and 5 column for the descriptions
Using either excel VBA or a .NET tool/functionality, is it possible to generate a new excel workbook which contains macros? I need to dynamically create excel files which will have macro functionality already built in.
This should be equivalent to asking how to use VBA or .NET to change/create macros in an existing Excel file.
This is weird, meta-macro stuff, I know. Any help at all would be much appreciated. If any of you know how/if any of this is different in 2007, it'd be amazing.
How to create a sheet to generate Random numbers from 2 specific list of a main list range of 36 Numbers. (List A = Specific 15 numbers, List B = The remaining Numbers from these 36)
Example :
-The Main List Range is from Number 01 to 36
-The Specific random list will be 15 Numbers of these 36
List A-. The Specific 15 Numbers are: 01,02,03,10,11,12,13,20,21,22,23,30,31,32,33
List B-. Remaining Numbers : 04,05,06,07,08,09,14,15,16,17,18,19,24,25,26,27,28,29,34,35,36
So
I need to generate ONE Unique Random Numbers of List A ( 01,02,03,10,11,12,13,20,21,22,23,30,31,32,33)
In Cell A1
In Cell A2
In Cell A3
And from List B, (04,05,06,07,08,09,14,15,16,17,18,19,24,25,26,27,28,29,34,35,36)
I need to do the same thing In
Cell A4
Cell A5
Cell A6
It's possible to have a 6 cells with random but Unique Digits? (not repeated numbers between the 6 cell ??)
How is code or functions kept unique to a workbook? for instance i create a few toolbars that are relevant to "work book 1", however when i open another workbook "2" the toolbars do not function properly or are removed by the opening/closing of the work book, similarly other macros seem to struggle with more than one workbbok open at a time.
View 5 Replies View RelatedIf a user inputs "Dispatch" into any cell on column G, I need to generate a Msgbox pop up.
View 3 Replies View RelatedAutomating Excel from Access, I'm attempting to generate a single sheet workbook and save to a Sharepoint site. This process works fine on my machine (XL2007, XP), but on a coworkers computer (XL2003, XP), the code throws a 1004 error on the save as line. However, the really odd thing is that stepping through the code doesn't throw an error on the coworkers machine.
Here's the sub being ran; the line it errors out on is the first branch in the .saveas block.
Code:
Private Const csSharePointSaveAs = "\sharepoint-us.mycompany.comsitesfinance adminlah blahlah-blahCustomer Publication Tracking.xls"
Private Sub PublishXLtoMOSS()
Dim objXL As Excel.Application 'Object
Dim wb As Excel.workbook 'Object
Dim ws As Excel.Worksheet 'Object
Dim rs As DAO.Recordset
Dim i As Long
'Set objXL = CreateObject("Excel.Application")
[Code] ...........
find the attached, I've some formulas on cells m,n & o and they related to each other, i just need n & o should be zero if m is zero.but the same formulas
should exist also.
I'm trying to generate a property's name based on an address. I thought the index/match functions would best serve my purpose, but here's my problem.
The address I'm using to search may not exactly match the table I'm looking in. For instance:
The address I'm using to search is 456 E. Bell Rd.
The address in the table is 456 EAST Bell Rd.
How to generate a list base on the differences of another list. let me explain; I have a list of combination A1:F2200 each row of course the numbers have difference, example:
589162045620273342451261419316121721264271837414551
As you see the difference in the first row for this small example is 317425 and the list base on this difference is :
145121641256131742367141843478151944589162045691017214671011182247811
1219234891213202449101314212550111415222651121516232752131617242853
The maximum number is 53. doing this one time find, but I have to do it with my 2200 difference.
I have included a copy of the spreadsheet. What I need to do is get a cummulative duration report for each crew. This particular spreadsheet has less data than most.
Many times there are 8+ crews. I need to make a column or graph or report or something that will continually update as changes are made and report back something like; Sewer 142 hours, Water 200 hours etc...