Flag Similar Rows Across Unique Identifier
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
ADVERTISEMENT
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
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 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
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
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
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
Feb 14, 2008
Assist me in developing a macro that would search for unique values in column A and then place a "1" in column N of that row to signify it as the unique value. I am trying to do this in order to get a unique count of projects listed because the list I receive has multiplie occurences of the same project # in column A.
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
Aug 9, 2007
I have been trying to work on this thing for like 3hours and i could not make even a single line since I dont get what loops I should make and how I should do it. Could someone suggest me how to do this.
1. check the date.Calculate two weeks backwards.
2. Check for id no which is scanned more than once in that two weeks from the current date.
3. Check for type of ppe if its different then leave it if they are same and exceeds 3 of the same type it should BOLD the badge id.
Have attached the sample file.
View 5 Replies
View Related
Mar 14, 2008
I want to get a Macro to delete all rows wherever "DELETE" appears in a certain column - I tried using the Delete Entire Row Based on Criteria Macro on Ozgrid but I'm looking for a fully automated solution, rather than the question boxes coming up and asking which cell/criteria to use. The spreadsheets I'm using this on are big, so because this Macro uses filters, it takes a long time and I also need to run this macro on multiple sheets so its not practical to use this.
DELETE is just the word I'm using as the TRUE statement generated from an IF formula that I'm using to compare cells in adjacent rows ie =IF(AND(E2=E3,G2=G3,Q2=Q3),"DELETE",FALSE) - Its completely fine for me to copy/paste values of this formula first and then sort the column so that all cells containing DELETE will appear at the top of the column if that helps. Any ideas on how I could get a Macro to delete those rows at this stage?
View 9 Replies
View Related
Apr 25, 2014
I am processing an infinite set of data from a meteorological station here in Alaska which gives me half hourly data reading with a time stamp 00.00, 00.30, 01.00, 01.30, 02.00, 02.30 and so on.
I am using this formula to detect every time half hour reading is skipped (=IF(TEXT(MOD(B1936-B1934,1),"[M]")="30","","missing")) and it works pretty well.
Still I have to check and manually insert extra missing for every half hour missing but that's bearable.
This formula inserts a "missing" every time it finds a gap.
My question is: How can I insert a row above every cell with "missing"?
How do I do that? Here is also my excel sheet.
CR1000_Meteo_20131113_2_CLEANED.xls
View 3 Replies
View Related
Jan 29, 2014
I want to delete rows that are a subset of any other row. Not manually, as there are thousands of rows. For example, in the attached file, row 4 is a subset of row 5 (also row 12 of row 13, row 14 of row 15, and both rows 21 and 22 of row 23).
View 2 Replies
View Related
Oct 16, 2008
Ideally I'm looking for a way to do this with formula's but for the life of me I just can't think of one. Attached is a sample that explains what I'm trying to achieve.
View 7 Replies
View Related
Aug 18, 2006
I'm trying to use the following formula in conditional formatting in cell A2 when the range A2:AW64 is selected (this conditional
formatting will be copied to all cells in the selected range):
Formula is: =isodd($A2)
The error I keep getting is this:
"You may not use references to other worksheets or other workbooks for Conditional Formatting criteria."
I'm trying to use this conditional formatting because column A is our "Order" column. "Order" can be from 1 to 13 and this column
is sorted ascending. There can be multiple rows in each Order number. So I want to shade all 1's, 3's, 5's, etc... so that each
Order group has alternating shading.
I also want to use conditional formatting, not VBA, to get this to work. In the future, rows might be added to an Order number or two and I want the shading to automatically adjust.
View 10 Replies
View Related
Nov 29, 2012
I have a spreadsheet that has a long list of charges... some of those charges have multiple codes attached to them... when this happens I am wanting to group them together and let the user pick which one they want to view at one time.
Here is a very small example of what exactly I am referring to... with charge #14 I am wanting it to combine and let the user pick between 2565 and 2566. The problem is that the values can range and will not always be 2565 & 2566 and there is 10,000 + records similar to this.
test.xlsx
View 1 Replies
View Related
Apr 2, 2014
I have a lot of rows of data all divided with an empty row and if they are 6 or less all rows need to be entirely deleted any 7 or above need to be kept.
View 2 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
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
Nov 29, 2012
This is a large database. I've attached a sample.
One code is for one animal, date the animal was weighed and weight. Animals were weighed several time, hence the repetition of the code.
What I need is the first and last rows only of every code. This is in order to then calculate the weight difference and divide it by the time difference to get the daily weight gain.
The trick is to keep the first and the last rows for every code, bearing in mind there's no consistent pattern for repetition of the code (as you see, sometimes 6, 5, 3, 7). Sometimes it is even repeated twice (in this case nothing is required to be deleted).
Sample.xlsx
View 14 Replies
View Related
Apr 7, 2014
Col A Col B
163401 1
163401 1
163401 0
163402 1
163402 0
163402 0
GOAL -I want to be able to count Col B only once for the same set of records in COL A.
Based on Distinct criteria on COL A, I need to be able to count COL B. The count should be 1 for 163401 and not 2. Similarly for 163402 the count should be 1
View 11 Replies
View Related
