I have one column which contains suppose first names & i have other two column which contains first name & last name in same sheet but like they may be having in g & h column.
so i want to exact last name of user form that column(g & h) to my first name column(a).
I have DAta in Col A (Med ID ) and B has number of events assocaited with the unique ID. The Unique ID is also in Col C from another data source. I wnat to return the value from Col B in in Col C that mathces the MD ID if available. There are several more col in the sheet that have other dta that is not assocaited with this. HAving an Excel formula or VB code will do some analysis. Example
Exported Med ID Data Source 1Exported Med ID Data Source 1 Number of Times Not Availle able Med ID- Matched Result From Col B to Med Id in C
I need to compare two columns A and B with a result in column E. BUT I don't want the result to be added together. ( since it's receipt numbers ) SO my formula is incorrect I need a formula that will show the receipt numbers next to each other divided by a comma or forward slash...
View 7 Replies View RelatedI have a spreadsheet with three years worth of data for a property I manage. Each column has cost data for the year and the specific department/cost for that year as the row value.
I have a column between the years that calculates the percent of gross revenue for the specific department/cost.
I would like to find (or create) a formula that will compare the percentage (or specific cost) for the three years within the specific row and if the increase year over year over year exceeds a trigger value it returns something (check me out/true/false) whatever.
The cost items for the property are in the hundreds so I'm trying to come up with a way to quickly see what specific cost items are going up (or down) more rapidly then what would be considered normal.
How can I compare the data of two columns, and if the same, to show me the number from a third column? I upload a quick sample
View 4 Replies View RelatedHere'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 the following code to compare two columns and delete adjacent rows if 1 is greater than or equal the other...
Sub LastReceipt_GT_Confirmed()
Dim intLstRow As Integer
For intLstRow = Range("E" & Rows.Count).End(xlUp).Row To 2 Step -1
With Range("E" & intLstRow)
If .Value > .Offset(0, 1).Value Then .EntireRow.Delete
End With
Next intLstRow
For intLstRow = Range("E" & Rows.Count).End(xlUp).Row To 2 Step -1 .............
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 have a pivot table and perhaps that isnt the best way to do this. (Sample attached). Ultimately I need to display two columns only:
Application (ColA) Business (ColC)
There are three columns of data.
Applications (ColA) Usage(ColB) Business (ColC)
There are multiple instances of the same application in ColA, ColB has usage =true or blank and ColC has multiple instances of the same business.
Based on the count of Application in ColA, and the count of Usage =True, I need to return the Business with the MAX count.
Note: the usage cannot be a filter, it must be a count.
Basically, I need to look for an item, lets use "apple" as an example, in a range of cells, A1:D100. Then the corresponding result would be in another column in the same row.
View 10 Replies View RelatedI have column A of 700 usernames and column b with 85, I want to compare b with a and if appears in the list place a 1 in column C
View 1 Replies View RelatedSheet2:
col A = contains the style#
col B = contains the color of the style
col C = contains the size of the style
col D = contains the qty of the style,color, size
Sheet1:
I would like to do the following:
A1 = input the style #
B1 = input the color of that style
C1 = input the size of that style
then D1 should automatically contain the qty of the mentioned style, color, and size.
I need to look colB and colC should be equal to colH and colI, IF both cells matched, then copy the EmpNo(ColB or colH) and Counter(colC or colI) get the difference of values in another colomn
If there is any row for Employee or Counter issing from either of tables populate with RED in either of 2 colomns,
for example:at row 17, table 1 has a missing colC, and row 17(table1) = row18(table2), it should populate the row17 for table2, as a reference.
I must match column A and column B to return the column C In colA there are all my countries In column B there are all my countries code and countries I want get the colC where all my colB match than colA.
View 14 Replies View RelatedI have column A that is 1,500 records.
I have column B with 40,000 records.
Among the 40,000 records in column B, duplicates of ALL 1,500 records from column A exist.
My question is: How do I find WHERE the 1,500 dupes are and how can I delete JUST those records?
I have column A with 228 rows and column D with 314 rows. Both columns have the same data except that D has different data. I would like to line up everything that is the same in A and D and everything else in column D that is not the same move to G. Is there a quick way to do this?
View 5 Replies View Relatedi want to compare data between 4 columns, 2 columns of data is in Sheet 1 and 2 columns of data is in sheet2. Data starts at A5 in both worksheets. Data consists of stock codes and there holdings. Sheet 1 data is real data the primary source, sheet 2 data is a bit of real data and incorrect data.
My data in sheet 1 is layed out as below, this is just a sample, real data has around 1500 stock codes and holdings.
Starting at A5
Col A (Stock) Col B (Holdings)
5 aap 10500
6 aax 10987
7 aas 76544
Sheet 2 data is layed out as above but holdings may differ, so
Col A(Stock) Col B (Holdings)
5 aap 10500
6 aax 10300
7 aas 76534
I need a formula which will tell me which stocks holdings have changed in sheet 2 compared to sheet1.
I'm working with Excel 2007, I have two tabs on my spreadsheet, each tab contains a column of numbers formatted like 5552220. Is there a way to run a macro or write a formula that would compare the data between the two columns and either automatically delete the numbers that don't have a match or highlight them? For example:
Tab A Tab B
5551112 5551112
6660001 6660001
1234567 1234567
2222222 2222222
6666445
6666445 needs to be deleted in tab B because it's not present in tab A. There could be up to 100 numbers that require deletion from tab B.
I have 2 columns say A and B (infinite number of rows)
A is an Indexed number starting at 1
B is numerical data.
I now have a second set of the same columns with the same data, however some rows are missing. I need a way of comparing the data and then display the rows I am missing in the second set of columns.
For example the first set will be the master list and look like:
Index Data
1 34
2 46
3 54
4 276
5 311
6 89
Second list has row 2 and row 5 missing.
Index Data
1 34
3 54
4 276
6 89
The desired result is to have the missing rows displayed;
Index Data
2 46
5 311
I have 2 sets of data/array/range. Each set consists of 2 columns with a large number of rows.
I want to compare my 2nd data to a master data. And list if anything is different in 2nd set of data from master set in column A than highlight the difference or copy the value to another place.
Also want to compare the 2nd column if column A was same and consider both column A and column B for that associated row different if column B is different.
The trouble for me comes in because the list is never alphabetical (sort doesnt work cause of funky naming) and never of the same size.
Attached is a photo of an example for maybe an clearer understanding. Also attached an example excel sheet I tried it within excel but cant seem to figure out how to look also for the 2nd column, so im trying to avoid the within excel route and go using vba ...
T3OCcxw.jpg
example.xlsx
I attempted it with a very basic code thats not working =/ just cant seem to figure how to code to get the desired result
VB:
Sub matchdiff()
Dim cell As Range
Dim found As Range
[Code].....
I have 2 worksheets in 2 spreadsheets 1 & 2. I would like to compare columns A & G in 2 to 1. If columns A & G in worksheet 2 matched worksheet 1, then it will fill in columns B, C, D, and AL automatically.
View 1 Replies View RelatedI'm having difficulties using vlookup to compare two columns of TEXT data. My goal is find out how many of items in the "Eligible Serial Numbers" list have been ordered for destruction. The items listed for destruction are listed in the "Serial Numbers (destroyed).
Whenever I put in a VLOOKUP, I get a serial number that doesn't make sense to me. For example, if I want to find out if "362351581" from the Eligible List appears on the destroyed list. What do you suggest?
Code:
Destroyed? (i.e. Vlookup formula in this column)
Eligible Serial Numbers
Serial Numbers (destroyed)
362351581
362351581
[Code]....
I have account numbers in Col A and in Col D .I would like a macro to highlight the color the items in Col D that are not in col D. Blanks can be ignored
View 3 Replies View RelatedI have a large spreadsheet converted from pdf whose data still appears in A4 reading format.
I need to move part columns of data from 6 columns to form 1 large column in column A.
For example, move range B8 to B76 beneath range A8 to A76 and range C8 to C76 beneath that etc, page by page working through all 270 pages !
Also need to delete unnecessary 'page headers' throughout as in rows 2-6
Looking for some code to do a simple compare column A to Column B (row 1 contains headings) and highlight any differences.
View 3 Replies View RelatedI'm using the code below to compare columns on two sheets (1 column for each) and where there is a match, copy data from the 'Source' to 'Destination' sheet.
[Code] ...........
The code works fine, but I'd like to amend this, but I'm a little unsure about how to proceed.
The script currently compares column C on the 'Source' sheet with column D on the 'Destination' sheet and where a match is found copy column G from the 'Source' sheet and pastes this to column O on the 'Destination' sheet.
I'd still like to copy and paste the same columns, but I'd like to compare two columns from each sheet, so from the 'Source' sheet compare columns C and E to columns D and J on the 'Destination' sheet.
I have two columns of adjacent data Call them A & B (although this could differ)
If the data in the second colum is not equal to, blank cell or contains the words "CAT", "DOG" or "ELEPHANT" then the content of the cell in the second column should REPLACE the content of the adjacent cell in column A (in this example) leaving B blank, otherwise column B remains unchanged.
Here's what I would like to accomplish
The result is in cell B18
If K18 is greater than D18 by 50 to 99 B18 shows 3
If K18 is greater than D18 by 100 to 149 B18 shows 6
If K18 is greater than D18 by 150 to 199 B18 shows 9
And so on. I tried with IF but doesn't seem to work.
I have two sets of data from columns A:N (O is blank) & P:AC. Column A & P are account numbers. I want to compare columns A & P for exact matches, there will never be any duplicates in either of these columns by themselves. If there is a match I would like that entire row to align, if there is no match I would like a row to be inserted. I have attached a copy of a worksheet of what I am looking to have done.
View 2 Replies View RelatedI have an Excel macro which works well in comparing two columns A and B. It populates column C with unique values in A and not in B, populates column D with unique values in column B and not in A and puts the values found in both A and B in the fifth column (E).
Data in the columns A and B of Sheet 1 has exceeded 1,048,576 (the maximum allowable in Excel 2007) and i will copy the overflow data in Sheet 2's column A and B and i will like the comparison to continue in the next worksheet.
I need the macro to treat the second worksheet as a continuation of the first sheet and not as a separate data. (The values i am comparing have exceeded 1million).
Sub twocols()
Dim d As Object, na&, nb&, a, b
Dim e, p&, q&, r&, m
Set d = CreateObject("scripting.dictionary")
[Code] .......