I have two columns that have similar information. I want to copy the unique information from one cell to a third, blank cell. In my case cells d2 and cell e2 are almost identical except cell d2 has addition information. I want to copy that information into cell f2. I have a feeling I am making this harder than it needs to be.
View 2 RepliesI am using (in Column "B") the formula: =IF(A2=A3."",1), copied down as required, to uniquely identify entries in Column "A". In this instance "10", "10A", "10B" and "10C" located in "A5" through "A11" are viewed as the same. Is it possible to have the formula recognize the enties in "A5:A11" as a unique entry and therfore have "1" entered into cell "B11"? The suffix, if used will always be A thorugh Z.
View 9 Replies View RelatedIF there is a whole number in either column a, b or c, I need to have column e reflect that with the insertion of a / for each column that has a whole number. i.e.
Column - A - B - C - D - E
3 4 / /
5 6 9 / / /
I am attaching a copy of the spreadsheet showing the examples, I can't seem to get the function to work.
I'll quote the example straight away.
Col A Col B
A,S,F A,S
A,R,S A,R,S,T and so on.
There is no fixed pattern for the values, but they are separated by commas only. All i need is to compare col A and col B and return me the value in Col C like this,
Col C (col C is to know the removed value from comparison)
F (for line 1)
Col D (to find the letters that are added from comparison)
T (for line 2)
I'm trying to compare cells in two spreadsheets and return a value to another cell in one of those spreadsheets.
SPREADSHEET_1
Column A - NAME (Contains Data)
Column B - AREA (Contains Data)
Column C - PLAN (Contains Data)
SPREADSHEET_2
Column A - AREA (Contains Data)
Column B - NAME (EMPTY)
Column C - PLAN (EMPTY)
I need to compare Spreadsheet_1 - Column B to Spreadsheet_2 - Column A and then fill in the cells in spreadsheet_2 with the values from spreadsheet_1 column A and column C.
I have three cells - A1, B1, C1 and each contains a value of yes or no.
If more than two of the cells say "yes", I want the fourth cell (D1) to display "yes."
If less than two columns say "yes", I want the fourth cell (D1) to display "no" .
I have a if function that i am creating in excel 2000, and it has 8 variables. I am trying to put it in a macro as it is not working as a nested if function. Here is the current if function...
=IF(B2=C2,"OK",IF(B2=D2,"OK",IF(B2=E2,"OK",IF(B2=F2,"OK",IF(C2=0,"OK",IF(D2=0,"OK",IF(E2=0, "OK", IF(F2=0, "OK", "INCORRECT"))))))
the excel file is attached for further clarity
I have a workbook with tons of programming I need to achieve. Here is my work book so it can be viewed.
Right now on "Protocol" Sheet I want the cells with the "Protocol 1" field to look at Admin_Panel Sheet and find the row with the same text. Then take the text in cell A of the same row and copy it to the cell below the "Protocol 1" listed on the protocol sheet.
I have a series of alpha numerics items in Cells A1:A999 (i.e AB0001 to AB0999). I need to be able to perform a search the range (A1:A999), and extract items that do not conform to the standard AB prefix. Example, an item of BC0001 could be present in the range. I would need to have that item, either highlighted or displayed in a different column.
View 2 Replies View Relatedi am attempting to recreate a 1 armed bandit in excel and was wonderin the following.
Is it possible for excel to look at a cell and determine how many of a certain character there are within it?
i.e. if a cell contains 112 can excel work out that there are two 1s and one 2.
I am hoping i can get excel to determine whether the random output is a jackpot or not i.e 3 of the same number.
I have two access databases which export results to two excel files. I am trying to compair the two excel files (generated by access databases) to find the common data in the two files. I am using Vlookup function.
The data looks like this
File 1
Serial No Fault
40293 A
40294 B
File 2
Serial No Solution
40293 Procedure 1
40294 Procedure 2
The result should be
Serial No Fault Solution
40293 A Procedure 1
etc...
But the column serial no in file 1 has some unknown characters like 40293followed by a small square or a vertical line. The datasource for the access file that generates file 1 is actually a lotus notes database. I think thats what causing this. Is there a solution to clear these spurious characters?
The VLookup function works fine if I manually delete those characters.
I have a dataset of shifts and want to compare each shift that needs to be filled to a list of requests for time off (vacation, etc).
For one cell the code is: =IF(ISERROR(SEARCH(B1, A1), 0,1)
Where A1 is the column of requests in comma delimited forme (ie: "AB,CD,EF").
Where B1 is the column containing the person assigned to shift 1 (ie: "AB")
In this case, would return a "1" as no error was returned, as AB was found in the list. Here "1" would represent a schedule conflict.
Without creating many cells for each shift- there are 20 shifts- can I create an array or string together this type of "SEARCH" function?
I want to get only unique characters in a text. For ex. ozgrid.com. I should get ozgrid.cm (o is already came). another one. For ABCEABC the result will be ABCE. So I have written an UDF. Unfortunately there was "Argument is not optional" error in "UNIQUECHARS = Join(JoinT.Item)".
How could I get rid from this error and get my result?
Below is my code.
VB:
Public Function UNIQUECHARS(chtxt As String)
Dim c() As Variant
Dim JoinT As New Collection
Dim sp As String
cchtxt = Application.WorksheetFunction.Substitute(chtxt, " ", "")
[Code] ......
I have a worksheet with five columns (A, B, C, D and E)
The cells in Column B contain letters and/or numbers (without spaces) in no particular order.
The cells in Column C contain letters and/or numbers (without spaces) in no particular order.
I want to compare all characters in 1st Cell of Column B with all characters in 1st Cell of Column C, and display the matching characters in 1st Cell of Column D, and the character count of 1st Cell in Column D must be displayed in 1st Cell of Column E. note that multiple instances of the same character must not be treated as duplicates. When execution on 1st Row is finished then repeat procedure for Row 2, etc... Stop execution when first empty cell in Column B is located.
Example:
B1 = LJLM12
C1 = KY2MLK
B2 = ZCG4GM
C2 = X4GGGC
B3 = KTCBNG
C3 = GNBTBB
The script/code/formula must output the following:
D1 = LM2
E1 = 3
D2 = CG4G
E2 = 4
D3 = TBNG
E3 = 4
I am using Excel 2003. Y
I have a somewhat convoluted problem. I am trying to develop a QA tool that compares data from a user-created spreadsheet against data from an automated spreadsheet. From my (admittedly small) understanding of Excel, this would be easy to do using VLOOKUP, however there will not be any unique IDs between two columns.
To better visualize this, imagine on Sheet A the following:
Placement Name
Ad Tag
Clickthrough URL
[Code]....
Essentially what I am trying to do compare data from a manually filled in sheet against data that would output the above, and make sure that everything matches up correctly. .
what i want to do is use 2 sets of data ss is attached to help understand
i would like to compare hometeam v player
therefore any games that steven gerrard didnt play in ( in the example )
it would leave all the games on sheet 3 where he wasnt involved.
hometeam has all the games liverpool involved in
player has all the games steven gerrard involved in
cross reference the 2 to leave all the games he wasnt involved in on a new tab
must be possible just not sure if i need look up or a macro
I have two columns in Excel.
I want to compare the two columns and pull out the unique entries.
So any entries that are in both column 1 and column 2 I don't want anymore.
I just want all the entries that are NOT IN BOTH columns.
I have a database with 2 un identical columns: A, and B. Each cell in Column A should have an equivelant cell somewhere in column B. I want a code that could compare each cell in column A with each cell in Column B. Cells in A that don't have equivelant in B should have their values printed in column C.
View 4 Replies View RelatedI 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 would like to compare some keyword lists if possible.
I have a large list of Unique keywords in Col A (From A3),
This Column is called Keyword List A - Large"
I then have a keyword list in Column C (From Cell C3),,
This Column is called "Keyword List B - Small.
I then have a column called "Unique Keywords Found",, This is Col E,, with hopefully returned results being entered from cell E3 downwards.
What I would like to be able to do if possible is run a Macro that would compare all the unique words in ColA and C and return only the difference, (The Unique words not found in ColC as Col A is the "Master List")
If possible could a pop up box appear saying
===================
List A No Rows:xyz
List B No Rows: xyz
No of Uniques Found : xyz
Time Elapsed (sec): xyz
===================
I'm running windows XP and Excel 2007.
I am trying to extract some data based on two criteria. The first is that one row cell value is greater in than an agreed date/time, in my case this is 7:00am yesterday. The second is that the first 5 characters of a second cell, in the same row but several columns along, are not equal to 'Monit'. If both of these are met then the code will strip out several cells of data from that row.
I can do the first and last parts of this but the 5 characters check is proving a little more difficult in my limited VBA knowledge. The second cell is simple text format and I can't do a LEFT equation in a different cell to get those 5 characters (restricted workbook range so no room). how to go about running a check? I'm thinking it's probably quite simple but trying to search for an answer to it is tricky.
Here's how my daily report is currently set up:
ExternalInternalDifferences
123000123000
234564234564
345456345456
456567378987
565456456567
654547524564
788879565456
865478654547
745654
788879
865478
The External column contains data provided by an external vendor. The Internal column contains data compiled internally. The data in the Internal column will always contain data duplicate to the External column, but will also contain unique data not present in the External column. I would normally cut out the data contained in Internal column that is not duplicated in the External column, and paste it into the Differences column, and move the remaining cells in the Internal column up. The result would be the External column data and Internal column data would align by row, and the data unique to the Internal column is segregated to the Differences column, like so:
ExternalInternalDifferences
123000123000378987
234564234564524564
345456345456745654
456567456567
565456565456
654547654547
788879788879
865478865478
While not a difficult task for 8 rows of data, the actual daily report contains over 1000 rows, on average. I would like to set up a template workbook, where I can simply open the template workbook, paste the data into both the External and Internal columns, then have the values that are unique to the Internal column extracted from the Internal column and inserted into the Differences column.
The end product would be used daily, with differing amounts of data (from 100 rows to 10,000 rows).
I have 1 workbook, with 3 sheets. Sheet1 (EVER) has 3000+ rows and 12 columns of customer information. This sheet is for all customers who have ever placed an order. Sheet2 (06-07) has 1500+ rows and 12 columns of customer information. This sheet has all customers who have placed an order in the last 2 years. Sheet3 has 1 row, which consists of the column titles (12 columns) that are on Sheet1 and Sheet2.
I need to put all customers that are on Sheet1, but not on Sheet2 in Sheet3. I have tried VLookup; advanced Filter and a number of codes in the last 3 days and have not been able to figure this out.
Is there any way to insert a carriage return after each instance of a character? I have a section of data that requires a carriage return after each instance of -v eg:
View 8 Replies View RelatedExcel 2007 - Windows XP
I have the below in cells A1 and A2. I need to find the last comma then return all characters after it. I need the same formula for both cells because the value is always changing.
HUG,PU-TUB,BW,-,-,64 Need to Return 64 in B1
HUG,PU-RFL,BW,-,-,176 Need to Return 176 in B2
how I would go about referencing a cell on another worksheet to return only the last so many characters from the data in that cell. EG:
Sheet 1 cell A3 contains the number (stored as text) 12345678
Sheet 1 cell A4 contains the number (stored as text) 98765432
On Sheet 2 I want to put into cell B2 5678 (the last four characters from the data in Sheet 1 A3.) Also on Sheet 2 I want to put into cell B3 5432.
I am trying to compare multiple column in a worksheet to find
common component in all the columns and what is unique to a particular
column only. And list the results/finding in adj column. What i am
trying to accomplish is something as below.
BEFORE
Sheet1 Sheet2 Sheet3
Column2 Column2 Column2
02-1234-12 07-1234-12 02-1234-12
04-1234-12 03-1234-12 02-1234-12
05-1234-12 02-1234-12 06-1234-34
AFTER
Common to all Unique to sheet1 Unique to Sheet2
02-1234-12 05-1234-12 07-1234-12
I'm trying to compile a VBA that would allow me to compare 2 columns "A" in different worksheets (same Workbook) and output any unique values to 3rd worksheet together with the rest of the values in the corresponding row.
Sheet1
A
B
C
[Code]....
Excel 2010
Hi, I desperately need help with a pivot-table and can't find anyone else who's ever had this problem...I have a very large healthcare data table which for simplicity I will describe as follows. In essence the first two columns respectively are (A) location and (B) month. Let's say the third column (C) is the number of cases of the disease kidpox treated in each health centre during that month. The fourth column (D) is the total number of cases of everything treated during that month in each clinic. The fifth column (E) is the proportion of cases of this disease over all consultations for each health centre and month, and the formula in E2 is .....
View 9 Replies View RelatedI am looking formula for below condition
1.Check Column A1 should not contain "$"
2.if Column A1 don't have "$" means then look Column A2 contains "$", if True means return string as "PASS"