Able To Identify Each Row Via A Unique Identifier
Jan 5, 2006
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.
View 14 Replies
ADVERTISEMENT
Jan 24, 2010
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.
View 9 Replies
View Related
Aug 10, 2007
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.
View 3 Replies
View Related
Jun 7, 2014
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
View 6 Replies
View Related
Dec 14, 2011
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
View 1 Replies
View Related
Dec 8, 2009
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.
View 9 Replies
View Related
Jun 15, 2009
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)
View 12 Replies
View Related
May 5, 2014
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?
5/2/14
O123
A100
11111111F
Failed
Yes
[Code] ........
View 2 Replies
View Related
Jul 8, 2014
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.
View 3 Replies
View Related
Sep 22, 2008
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.
For example:
View 3 Replies
View Related
Jan 12, 2010
I have a list of objects:
A
B
C
D
X
D
A
F
G
H
I know how to get a unique list. How do I identify those that are in the list more than once and how many times it is in the list?
View 2 Replies
View Related
Mar 11, 2013
I am trying to get an Array formula to pull unique values from an ENTIRE table. I was able to write one just for a column but not for an entire table. The unique value column is my desired results - t
View 11 Replies
View Related
Aug 27, 2012
What kind of formula I need to identify if in a given range there are only unique values?
The range (containing 12 cells) may contain only two text values: "Actual" or "Forecast".
If all values are equal to "Actual" I would like the formula to return "A".
If all values are equal to "Forecast" I would like the formula to return "F".
If there are both "Actual" and "Forecast" values found in the range I would like the formula to return "A/F".
View 2 Replies
View Related
Nov 20, 2013
I have a (phylogenetic) matrix with a species in each row, and morphological character in each column, scored as 0, 1, 2, 3 or 4 for each different species, so something like the following but much bigger:
Character 1 Character 2 Character 3
Species A 1 0 1
Species B 3 2 2
Species C 1 2 0
I want to identify the minimum combination of different columns (i.e. their values, but also which columns) that make a particular row unique.
View 14 Replies
View Related
May 13, 2008
when I code VBA .. I am constantly referring to cells like:
Worksheets("").Range("")
or Workbooks("").worksheets("").Range("")
is it possible to do something like
Dim var_1 as Range
var_1 = workbooks("blah").Worksheets("blahagain")
then to use:
msgbox var_1.Range("A1").value
View 9 Replies
View Related
Jul 29, 2012
I'm looking for a code where all those rows which contain some identifier be deleted.
Eg, If any row contains identifiers like "----" , "PROG:" , etc shall be deleted.
NOTE: The identifiers are found on the A Column of any row.
All rows which do not contain the above identifiers shall remain untouched.
View 9 Replies
View Related
Sep 3, 2008
I have undertaken a project and have hit a brick wall.
I'll try and translate my request in the easiest way I can.
I have a piece of software that stores what software is installed on a machine when it connects to my domain.
I therefore have a list of software that is installed on machines that connect to my domain.
Each machine that connects to the domain has a unique identifier code.
At present I have about 500 different pieces of software on the various machines.
The unique identifier for the software installed on each machine is a combination of the application (column B), the program (column C) and the version (column D)
I wanted to give each seperate piece of software a unique ID number.
I was wanting to run a formula similar to the pseudocode below:
If $b$1="adobe" and $c$1="8.0.0.1" and $d$1="adobe reader", then "1",
elseif $b$1="adobe" and $c$1="7.0.0.1 and $d$1="adobe pro" then "2", else "no software ID"
I am thinking that if I can get every piece of software that is installed on the machines to have a software ID then I can create a software ID allowed list then I can create a formula to identify what laptops have software on the "allowed" and "banned" lists.
The problem at present is getting the sotfware to lookup what software ID they have as they need to check 3 columns with a lookup list.
View 9 Replies
View Related
Oct 6, 2012
I have a table with an identifier in column A and values in columns B to Z.
The structure is like this:
IdentifierA
value1
value2
value3
value4
IdentifierB
value5
value6
[Code] ......
There is 3.000 rows of data. As you can see some of the identifiers have no values.
On another sheet I need the following table to be generated in this format (two columns):
IdentifierA Value1
IdentifierA Value2
IdentifierA Value3
IdentifierA Value4
IdentifierB Value5
[Code] .....
Note that as IdentifierD has no values it shouldn't be in the new table.
View 3 Replies
View Related
Feb 10, 2010
this formula is on Sheet1 and works ok but would be a LOT better if I could use some variables in it.
=SUMIF('Sheet2'!$D$56:$D$96,Sheet1!G3,'Sheet2'!$E$56:$E$96)
G3 is a date
$d$56 to $d$96 are dates
$e$56 to $e$96 are Dollar amounts
I would like to be able to replace the $d$56 to something like $D
and $d$96 with $D
same with $e...
View 9 Replies
View Related
Feb 20, 2008
I am trying to achieve the following. In D2: AQ2 there a values indicating the subject identifier for that column, eg in D2 it is SEN and E2 is Art. Each row represents a pupil name into which grades are inputted. I want to compile a list of column identifiers that corresponds to a D grade (it could be D, D- or D+) eg
A2 Name ----D2 SEN --- E2 Art --- F2 Business Studies
A3 Bob ------D3 D+ ----- E3 A -----F3 C
A4 Tim ------D4 C -----E4 D------F4 D
A5 Pete------D5 D------E5 D+ -----F5 D-
Expected output for Bob is SEN. Expected output for Tim is Art, Business Studies. Expected output for Pete is SEN, Art, Business Studies.
I've enclosed a small sample of the actual worksheet. Expected results for row 3 start in cell CP3.
View 5 Replies
View Related
Oct 6, 2008
I have a Sheet in a workbook that has about 250 Unique supervisors in it (column A) It has about 2300 rows of employees. Is there a macro that can take the employees supervisor column (A) and break the rows associated to that supervisor into a new sheet & and name the sheet with the supervisor’s name? Can excel have 250 sheets (2003 version)?
View 3 Replies
View Related
Oct 15, 2007
I have been attempting to solve a problem for quite some time and would love any help anyone can provide. I'm on Win XP, Excel 2003.
I have a series of assets each with a unique number to identify it in it's own row. In a separate worksheet I have a column with just these numbers next to a column with a word assigned to each number. Some are only listed once but many are listed multiple times. I was looking for a function that will retrieve the information in the second worksheet and place it in the row that it corresponds to the number in the first sheet. I've listed an idea of what it looks like below. I'm looking for it to search for every instance of that number and provide what's next to it. The first set of information is what I have, the last is what I'd like it to look like.
1 hamster..............1 hamster
2 cat......................2 cat, feline, kitten
2 feline..................3 dog, puppy
2 kitten..................4. etc.....
3 dog
3 puppy
View 9 Replies
View Related
Apr 1, 2014
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
View 1 Replies
View Related
Mar 23, 2014
I have a set of 4 multiple choice answers B,C,D,E column "F" lists a # 1-4 which lets you know which column letter (b,c,d,e) the correct answer is in. I want to set up a formula that will copy the contents of the correct answer (based on that # identifier) and copy it into column "A" which is currently blank.
View 7 Replies
View Related
Mar 23, 2014
Here's an example of what i want to do
A_________ B Frank Sinatra C Sammy Davis D Dean Martin E Joey Bishop F 3
I want to put "D"'s value 'Dean Martin' in column A - i know it belongs in A because the "3" in column F indicates its the 3rd answer listed i.e Dean Martin. This format would be the same where there are 4 possible answers on the column to the right dictates which answer is correct.
View 2 Replies
View Related
Aug 24, 2009
If I have multiple entries with different but repeatable text values in one column - how do I count all unique ones ? Is there a function or does it have to be a pivot table of sth ?
View 14 Replies
View Related
Jun 5, 2014
I'm trying to think of a way to do this. I need a unique ID number per company, so I would want it to look something like this:
0001: Apple
0001: Apple
0002: Google
0003: Sony
0003: Sony
0003: Sony
0003: Sony
0004: Ford
0004: Ford
0004: Ford
.....and on and on for about 400+ rows. I think I know how to state the condition. IF B15 =/= B14 then....assign unique ID, else A15 = A14....or something to that effect.
View 1 Replies
View Related
Oct 22, 2009
Each product is represented by a serial number (column A).
The can be sorted on column A from smallest to largest prior to calculating results if that helps.
The repair list contains 1 entry per spare part used, so the same serial number may occur several times.
Furthermore, a product may have been repaired on several instances - so the serial numbers can span several dates (column B).
The solution i am looking for should return the number of unique repair dates per serial number. That way i can see, how many times each product has been repaired. Results can be displayed in an individual column.
Sample list:
Serial........Repair date
207742052008-09-04
207755082008-12-17
207755212008-12-31
207755212009-01-22
207755212009-01-22
207755212009-01-22
207755212009-02-13
207755212009-07-24
207755362009-05-20................................
View 8 Replies
View Related
Jul 30, 2009
I have a data table that repeats as follows:
CITI
SCB
SCB
SCB
SCB
SCB
SCB
SCB
RBC
RBC
RBC
RBC
From the data above I need to make a new unqie list that would grab all unique entries.
CITI
SCB
RBC
I am trying to use the following guide:
http://blogs.techrepublic.com.com/howdoi/?p=111
Get stuck on "Listing A:
View 9 Replies
View Related
Dec 3, 2013
I have a worksheet with a large amount of data in it. Every couple of months the data will change and I place this in a different sheet (sheet 2, this comes from a software tool) . Is there's some code that can run a check on sheet 1 compare all values against sheet 2 and highlight the rows that have a different value between sheet 1 and sheet 2? Perhaps paste the rows into sheet 3 with the different value highlighted??
Example of original and changed data attached : example_2.xlsx
View 4 Replies
View Related
