I am trying to use a formula that will populate A3 with the value of A2, all the way down the column. (as per the area highlighted in yellow) As you can see on the example sheet, the account code changes periodically, this has been causing me many problems!
As the list i am working on has around 100 differfent codes, it is too time consuming to copy paste manually
Reason for this is so that each invoice number in column B, will have a unique identifer in column A (account code)
I have an extract that includes unique attempts to complete a transaction. Sometimes these transactions fail 1 or multiple times. Sometimes they are successful in one try. other times they first fail and then are retried and succeed. I am trying to flag failures in a new column that later succeed in the same session. Here is my data:
Date Order # Session ID Mac Address Status
5/2/14 O123 A100 11111111F Failed
[Code] ............
I would what these flagged as such:
Date Order # Session ID Mac Address Status Successful Retry?
I would like to combine values from multiple records into a single record using a unique identifier. In the example below 'ID' is the unique identifier.
does anyone have any thoughts on any way to create a formula that will create a unique identifier for a single row? I thought about using concatenate to combine a few columns in a row which will probably work 99% of the time, but I cant risk it being wrong 1% of the time. Any ideas on how to incorporate checking if the concatenation (aka the unique identifier) already exists, and if so, add a number or something to it to make it unique?
The data has rows added and removed on a normal basis in random spots on the sheet so I cant simply just have a number incrementing for each row I add as the unique identifier. In other words, the unique identifier should never change even if I add or remove rows anywhere in the sheet.
I have a spreadsheet (Excel 2000) that contains around 10,000 rows. I now need to be able to identify each row via a unique identifier, but am having trouble working out what to do when new rows are added or when existing rows are copied and paste.
New rows and copied rows should get new unique identifiers, but I can see no way of doing this. Does anyone have any suggestions? There is nothing unique about the data that I can hang on to and the "SheetChanged" event does not fire for inserted rows in Excel 2000.
This spreadsheet is owned by one of our clients, so I can't add new worksheets or columns to the existing workbook - I can just add cell comment.
Here at work we have a set of 12 workbooks, that form an extensive activity plan. I have created an add-in that creates a worksheet menu bar item and adds different functionalities to these 12 files. Because any user might have more than one of these documents open at one time, I am forced to refer to the documents by ActiveWorkbook. A bonus added feature: the names of these files change periodically, but not at the same time.
Before running any procedure I must validate that the active book is one of these files. Currently I am testing the existence of a veryhidden sheet codenamed "shIsValid" in the workbook. Now I'm thinking, there has to be a better way to do this. Simplest way would be if ThisWorkbook object had a Tag property similar to UserForms, but this doesn't seem to be the case. I wonder, is there actually a way to give a workbook any identifier (boolean, string or number) that I can access directly, so I can get rid of these dummy worksheets.
I thought this would be very easy to achieve in Excel but is proving difficult. Basically I have a number of excel worksheets with the headers as below. What I would like to do is to use the staff number from each worksheet to populate and sum a master worksheet. Some staff numbers will be in each of the workbooks while some are only in one. While the employee name should also be the same in each of the workbooks there are some differences in spelling etc.
Mon - Fri Mon - Sat Saturday Sunday/Public Hols OVERTIME Staff NoEmployee Name6 am - 11 am5pm - 12 Midnight12 Midnight - 8am8am - 12 Midnight8am Sun - 8am Mon
How would I set up a formula that can search for a unique identifier if the unique identifier is not know.
I would like to set up this formula in Tab 1 where it looks to column A in Tab 2, which is a column of about 600 unique identifiers, to find any cells containing the string "VA". There will be multiple cells that include "VA" in the string
I would like this formula to collect all the cells in the column A in Tab 2 into a column of cells in Tab 1 beginning in Cell A5. I would copy the formula in Cell A5 of Tab 1 down multiple lines. I can add a segment to the formula that will return a blank if there are no more cells with the indicated string included.
Tab 1 formula begins in Cell A5Tab 2 of Unique IdentifiersFormula that will copy first cell in Tab 2 that includes VA in the string from Tab 2VANewsprint23005-2119Formula that will copy next cell in Tab 2 that includes VA in the string from Tab 2TNPackaging37309etcALPackaging35044etcVAPackaging24526-0040etcONPackagingP0K 1E0NHPackaging03743VAPackaging24521NYPackaging13667MIPackaging49099VA Packaging24426ONPackagingM1P2Y9ONPackagingM1P 2Y9WIPackaging53512-0386MIPackaging49042VAPackaging24504
I think a macro could probably do this, but I am worthless with VBA. What I need to do is consolidate lines of text if they have the same numeric identifier. See below.
ID Text 345 This is a sentence that 345 has line breaks in it, but 345 needs to be consolidated 345 into one cell, if you would 345 be so kind as to help me. 345 KTHX! 346 Oh no, here's another one 346 that has a different number 346 of rows. Eep!
Not sure how to consolidate text or concatenate it, since it's not always going to be the same number of rows each time this happens. I need something with an if statement and...some other crap.
I would like to copy values corresponding to a unique identifier from one sheet to another, using the vlookup function. The problem is that after a certain cell the values are given as N/A even though they are present in the sheet I want to copy them from.
I have tried a slew of different formulas but I can't seem to get the outcome I am looking for. I need to make a station comparison for an individual project build based off a master list. Sheet 1 has the individual build with column A as a part number, column b as a description and column c as a quantity required and column D is blank. Sheet 2 has a master list of every part we carry formatted as: column a as a part number, column b as a description, column c as a quantity required and column d as a unique station identifier for that part (which is always a number).
What I am trying to do is match the part number on Sheet 1 Column A to Sheet 2 Column A, and when a match is found, take the unique identifer under column D associated with that part number and have it displayed on Sheet 1 Column D.
Sheet 1 will change with each build, but it will always be the same format in columns a,b,c,d. Basically I have a format on sheet 1 for what is needed to build each custom kit (787 kits) and sheet 2 carries every part we offer along with the identifer in column d (always a number value). I want to create a formula I can easily copy to match the value in sheet 1 column a to sheet 2 column a and add the identifier from sheet 2 column D to sheet 1 column D. The average kit is roughly 120+ parts so doing that for each part 787 times will be a nightmare. Also in case this matters, sheet 1 column A will have the parts arranged in a random order based on what is desired for that kit. Sheet 2 column A has the part numbers in order from our lowest part number to the highest number (basically an entire inventory list sorted from lowest to highest).
Example
(For sheet 1, since it doesnt carry formating 11111111 is in column a, nut, flange is in column b, 1.00 is in column c and the output data i want would go into column D)
Sheet 1 A B C D 11111111Nut, Flange 1.00 *Starts blank* -looking for - Output from Sheet 2 Column D (1) 33333333Bolt, Squared 4.00 *Starts blank* -looking for - Output from Sheet 2 Column D (4) 55555555Bolt, Coated 3.00 *Starts blank* -looking for - Output from Sheet 2 Column D (2) 22222222Bolt, Hex 4.00 *Starts blank* -looking for - Output from Sheet 2 Column D (11) 44444444Bolt, Screw Type 2.00 *Starts blank* -looking for - Output from Sheet 2 Column D (3)
Sheet 2 A B C D 11111111Nut, Flange 1.00 1 22222222Bolt, Hex 4.00 11 33333333Bolt, Squared 4.00 4 44444444Bolt, Screw Type 2.00 3 55555555Bolt, Coated 3.00 2
I have got a spread sheet (Attached) that works out the rankings of youth football teams so that they can be given fixtures against teams of the same ability. I want to use the same system for another age group which has nearly 100 teams in it but I can't get it to work for over 50 teams.
With my basic Excel knowledge I have worked out how to add extra columns with the correct formula, but I can't work out how to add rows with the correct formula to the table that is in cells A12 -C12 down.
Ideally I would also like this table to show the team with the highest ranking in position 1 & in descending order from there - but I would be more than happy just to add the extra rows for now.
I have three columns (A) has a date (B) has the name of a person and (C) has an audit type.
I know I need an array but not sure how to structure it for 3 columns DateNameAudit type01/04/2008DavidCDM01/04/2008John1 in 1030/04/2008Bill1 in 1001/05/2008DavidVehicle01/05/2008DavidVehicle01/05/2008JohnCDM
The answer for these 3 columns is 5. Rows 4 and 5 are the same so are only counted once.
I can get the arrray to count unique items in the whole range and in a column but not for each row.
I have a feeling this is going to obvious but I can't work it out.
I have attached a before and after image of what I am looking to accomplish.
In the before image, you can see that there are 3 rows of data - a header - a repeating model number (in column F) with accompanying data (values in columns G - J are the items of interest)
In the after image, you can see that I took the 3 rows of data and turned it into simply two rows of data - a header - the repeating model number
What I did however in the after image, as can be seen, is I took certain data values that appeared on the repeating row (columns G - J), and included them as PRICE, COST, BEGIN, and END values in new columns on row 2.
Is there a way to accomplish this via some functions or maybe even a VBA script?
I have one worksheet which acts like a data dump (actuals) where data is just pasted in. This worksheet is sorted and edited on a continues basis. I need this data to be sorted into their respective coded worksheets (110, 120, 130).
I would like a function in Worksheet 110, 120, 130 that captures the data for that 'code'. Now the issue is that much of the data is non-unique, dates, codes, accounts may sometimes be duplicated however i still want to display the entries separately in their respective coded worksheets. (I had a solution using column numbers however it isn't viable because the data gets resorted often)....
I need creating a macro which compare the values of "Column B" of attached both "Sample1" and "Sample2" excel files and if any unique value found in Column B of "Sample2" file then the entire row should be get copied in "Sample1" file after row count.
For ex. the rows colored as yellow in "Sample2" file are unique and should be get copied in "Sample1" file.
I have rows labeled by days of week and columns by different departments. I need to add the data from Saturday and Sunday to Mondays data and then delete Saturday and Sunday. Also need to do this for some 800 days
I have a spreadsheet with a series of names in a column and respective dollar values in a separate column. I would like a formula to automatically total the value spent against a particular name (without having to enter the name as a value each time).
Example
John Doe 3.89 John Doe 1.27 John Doe 0.98 Amy Smith 1.56 Amy Smith 2.29 Amy Smith 1.11 Alex Brown 0.67 Alex Brown 4.56 Alex Brown 8.87
I would like a formula to automatically populate a column, spreasheet, etc as follows:
We have a client that has to total the number of days that people received medical care (for insurance purposes). BUT...they can't count duplicate days. Some of the dates show a range of days, others only one day. They've asked for my help. (We're actually a computer services firm, but I'm pretty good with Excel...until now!)
In column C I have placed the correct totals for the date ranges, according to the payment policies. But I can't figure out how to write the formula that accommodates all the variables (mostly since all the unique dates aren't entered--only ranges).
Each line is a different medical charge, so line 1 might be 22 days of hospital stay. Line 2 is for a different charge, but covers the same dates, so it can't count. Lines 3, 4, and 5 all occur within the previously calculated range, so they don't count. The date range in line 6 is six days but 4 of them have been counted already, only 2 days fall outside an already calculated range.
It would be easy enough to do this manually for a small list, but this patient has 1367 entries in 4 months of care! And there are others that are much larger.
Fortunately, all the dates are in calendar order. Also, fortunately, they don't need line-by-line totals, just the grand total at the end of all the unique days in the range. So if I can figure out how many unique days are in the columns ....
I am "designing" a time- tracking database. The way it works is that each user (there are multiple users) creates a new day, which is mirrored in the sheet name (i.e. if today is 06/09/07 and my name is Newuser, the sheet name is "NewusER 060907"). Each sheet is filled in, calculated based on in-sheet formulas, etc. At the end of said day, the user can " upload" the daily data from multiple days worth of data into another sheet, that the graphing macro draws from.
The one serious problem I'm having is that users can upload the daily time data for the same day multiple times, to no end. I would like to do one of two things. Either:
a. make it such that once data is uploaded it cannot be uploaded again (probably more difficult), or b. write a loop to pull out the highest data point up the sheet and delete all other rows for that one specific day's . (probably easier)
Because of the constant changing nature of the information within, I would like to to make this dynamic range selection, I would prefer to avoid using Advanced Filters, if possible.
I'm trying to set up a macro that will capture all the unique values in a column, and then create a new sheet for each value. What I've got so far technically works, but it's also adding an extraneous sheet (with a generic sheet name, not the specified name I'm giving the sheets) and also giving me "Run-time error '1004': Application-defined or object-defined error" when it runs.
I have a few dozen pictures created when a macro runs. They all have unique names. I'd like to add comments to cells, where the cell.value decides which picture to pull. All the examples I've found online show how to do this if you have pictures saved on your hard drive by referencing the file path "c://mydocs/...blahblah/"
Is there a way to reference the pictures I've created/named with my macro?
Here's the snippet of code that creates the pictures and names them:
Code: For i = 2 To Application.CountA(Sheets("Allocation").Rows(1)) Sheets("Allocation").Activate Set rInput = Sheets("Allocation").Range(Cells(1, i), Cells(10, i)) sPicName = "_" & Sheets("Allocation").Cells(1, i) & "_" sSheet = Sheets("Allocation").Cells(3, i) dDate = Sheets("Allocation").Cells(5, i)
[Code] ......
Here are some examples that are close to what I'm looking for.
VBA Popup Pictures - 1108 - Learn Excel from MrExcel Podcast - YouTube VBA Express : Excel - Add pictures that float like comments.
I have a userform that I'm using to add data to a worksheet, with the following
Private Sub CommandButton1_Click() Dim OutSH As Worksheet Set OutSH = Sheets("Sheet1")
OutSH.Cells(nextrow2, 1).Value = Surname.Value OutSH.cells(nextrow2,2).value = ID.value OutSH.cells(nextrow2,3).value = Date.value ...... I need to ensure that duplicate entries are not made for the same person on the same date. The ID is unique to each person.
IF statement that can check for a duplicate and then come up with a dialouge box with some custom text, and then exiting the sub?