Comparing Data And Returning Changes
Feb 24, 2014
I would like compare data using a certain column to key off of and compare whether data has changed.
I have 5,000 rows and 26 columns. Cell C1 is CALLED PMNUM, cell I1 is called CHANGEDATE
I want to find all of the same PMNUMs (C2-C5000), look at the last change date (I2) (sorted in descending order), compare it to the previous change date (I2) and if anything has been changed in columns D2 through G2 and J2 through Z2 compared to the last time the data was reported, list the changes in in AA2.
View 4 Replies
ADVERTISEMENT
Aug 5, 2009
I have data exported as .txt from an external program in the following format:
Sample Name Marker Allele1 Allele 2 ....Allele n
AAA D8S1358 10 11 XX
AAA D16S539 16 19 XX
and so on.
Each "Sample Name" will have 16 different "Marker"s and upto 10 different "Allele" at each "Marker". There could be upwords of 200 Sample Names on each exported sheet. Is there a simple way to compare all of the data from each "Sample Name" to each other and possibly to another sample set that will always be static but of the sample general makup as this?
View 9 Replies
View Related
Apr 22, 2014
I have an attendance sheet that employees scan their badge number, and returns their name and time/date stamp using a simple VLOOKUP.
I want to create a report that compares the names in the attendance sheet against the all the names in the master sheet, and only returns the names that have not attended the training.
I have tried a few test formulas using COUNTIF function, but with no luck.
I have attached an example. Use the worksheet labeled April 2014 A (2nd shift)
New Six Point Safety with Attendance Sheet Barcode Scanning.xlsx
View 3 Replies
View Related
Jan 26, 2010
I've got a list of donor names, names listed multipled times in Column A, each row with different donation data. For instance:
Sheet 1
A1 - Mary Jones, B1- Internet Donation
A2 - Mary Jones, B2 - Silent Auction Purchase
A3 - Mary Jones, B3 - Event Ticket Purchase
A4 - Gary Jones, B4 - Foundation Grant
A5 - Gary Jones, B5 - Internet Donation
A6 - Sara Parker, B6 - Event Ticket Purchase
I want Excel to compare rows A1 and A2, and if the name has not changed, enter the donation data from Column B into another sheet that will just list that person's name once:
Sheet 2
A1 - Mary Jones, B1- Donation1, C1 -Donation2, D1 - Donation3
A2 - Gary Jones, B2 - Donation1, C2 - Donation2, D1 - Donation3 (which would return no value because there the name changes
Does this involve IF, MATCH or VLOOKUP formulas?
View 9 Replies
View Related
May 31, 2007
I want to return the proper value from sheet 2 into column C in sheet one. I have the current formula set up as an array but I only get the value that matches the first row in Sheet 2.
HTML Column A Column B Column C
Date Number Who
3/23/2007 902-555-2596=IF($B2=Sheet2!$A$2:$A$2001,Sheet2!$B$2:$B$2001,)
3/23/2007 980-123-6621=IF($B2=Sheet2!$A$2:$A$2001,Sheet2!$B$2:$B$2001,)
3/23/2007 980-123-6621=IF($B2=Sheet2!$A$2:$A$2001,Sheet2!$B$2:$B$2001,)
3/23/2007 980-555-6621=IF($B2=Sheet2!$A$2:$A$2001,Sheet2!$B$2:$B$2001,)
3/23/2007 980-678-0352=IF($B2=Sheet2!$A$2:$A$2001,Sheet2!$B$2:$B$2001,)
3/23/2007 902-555-7958=IF($B2=Sheet2!$A$2:$A$2001,Sheet2!$B$2:$B$2001,)
3/23/2007 980-555-6621=IF($B2=Sheet2!$A$2:$A$2001,Sheet2!$B$2:$B$2001,)
3/23/2007 980-555-6621=IF($B2=Sheet2!$A$2:$A$2001,Sheet2!$B$2:$B$2001,)
3/23/2007 902-555-7958=IF($B2=Sheet2!$A$2:$A$2001,Sheet2!$B$2:$B$2001,)
3/23/2007 902-555-7958=IF($B2=Sheet2!$A$2:$A$2001,Sheet2!$B$2:$B$2001,)
~~~~~~~~~~~ Sheet 2 ~~~~~~~~~~~~~~~~~
Column A Column B
902-555-7958Big Bird
902-555-5100Daffy Duck
902-678-0352Elmo
902-555-5752Donald Duck
902-555-7420Bert
980-123-6621Ernie
902-555-2596Grover
View 12 Replies
View Related
Feb 10, 2009
I'm quite new to array formulas in excel and was wondering whether or not I can advance on this formula:
=AND($C$5=B7,$K$5=G5)
The problem is I would like to compare the entire colums (or array) and return the number of 'TRUE' statements in the array as a value.
=AND($C$5:$C$65536=B7,$K$5:$K$65536=G5)
however, this returns all values as 'FALSE'
example of arrgument:
IF C5:C65535=customer then count how many cells in K5:K65536=completed
View 8 Replies
View Related
Jan 22, 2010
I need a function to compare two ranges and return a match. For example
Range A: {A56, B43, C98, D44}
Range B: {A33, G89, D44, K123, OB55}
the function would return D44. Is it possible at all with functions or do I need a macro?
View 14 Replies
View Related
May 30, 2014
Excel user that has been manually computing a 6000 row spreadsheet. Here is sample data and what I need. Column A goes through the alphabet (A,B, C, etc.) and Column B is numbers from 1-65. I need Column C to count the number of rows for each alphabet/number pair. K, 28 has 3 rows; K, 33 has 1 row; L, 21 has 4 rows; etc.
Column A
Column B
Column C
K
28
3
K
28
3
K
28
3
[code]....
View 2 Replies
View Related
Sep 18, 2013
If I am working from cell (D16), I am looking for a formula to compare the value in 2 different cells (D8 and D12) to a value in a third cell (D14) and the one with the closest value without going over returns a seperate value from either (D7 or D11), But if both D8 and D12 are above the value of (D14) then the closest value would be the answer and return the answer from (D7 or D11).
Example: D7=Bob and D8=25
D11=John and D12= 40
D14= 45
D16 (answer cell) =John
OR:
D7=Bob and D8=46
D11=John and D12=48
D14=45
D16 (answer cell) =Bob
View 2 Replies
View Related
Mar 21, 2014
I have two lists, one has 250 items, the other 4500, both contain a String
I need to compare any word within a cell in the short list, against any word within a cell in the long list.
The returned value needs to display both the short list item, as well as the long list item for manual comparison by an analyst.
View 4 Replies
View Related
Apr 8, 2014
I have two Columns C and E that have dollar amounts in them. I want to compare each row in those columns and then total the number in column C that are higher that column E.
So basically C6>E6, C7<E7, C8=E8,C9>E9 should return the result of 2. There are two instances where the price in C is higher than the price in E.
View 3 Replies
View Related
May 17, 2014
I have a parent spreadsheet with raw data(with errors) and a child spreadsheet without errors. I want to merge the child into parent. (:{). I am thinking of comparing multiple columns from each sheet to ensure maximum accuracy. And when those columns match up we paste the corrected column data from child sheet to parent one. I am using windows 7 and Excel 2010.
View 1 Replies
View Related
Apr 18, 2013
So I have this problem in excel with comparing 2 columns.
Basically, I have 2 columns(a &B) that I need to compare with one another and find out the matching data. I am trying to use that matching data to enter in our reports.
Both columns might have duplicate items because I am dealing with premium numbers.
Formula to compare these 2 columns and find the matching data and extract it to a separate column. Keep in mind, i am dealing with almost 20,000 lines of data.
View 2 Replies
View Related
Sep 19, 2013
I currently have 2 worksheets worth of data in the same workbook. Each data set has its own date and time stamp in columns A and B respectively with varying data then following in the row. I would like to write a macro that would look in worksheet 2 and find the matching date and time stamped row in worksheet 3. Then select the data from both worksheet 2 and worksheet 3 and paste them in the same row in worksheet 1.
View 9 Replies
View Related
Dec 4, 2012
I have need to do a comparison of 2 values located in different columns. As this is almost impossible to describe in text format, I have included a spreadsheet showing exactly the problem. Im sure the solution will involve INDEX and MATCH but every permutation I have tried has failed (possibly theres another way?).
View 1 Replies
View Related
Feb 15, 2013
I have two worksheets, whereby the headers pm each worksheet are the same. the data however may not be. If the entries are different, I have used =IF(ISNA(VLOOKUP(B2,'Master 0202'!B2:B50,1,FALSE)),"New","") to pick up new entries.
However, those that are not new, i.e. they are both in the old worksheet and the new worksheet, I need to find out whether there is any change in any of the cells. The headers run from columns A to W.
How can I find out if there are any changes in any of the cells in one go? Can I use another VLOOKUP?
View 2 Replies
View Related
Apr 13, 2014
Not sure if this should be in VBA or Formulas however what im trying to achieve:
I have a data report that i pull today, tomorrow i want to run the same report and see if there are any differences on the report. e.g show data that was not on the previous report.
Id imagine id have a sheet with yesterdays data, sheet with todays data, sheet with Differences that only shows the new lines between the report. Then after i have done i would just create a macro clear the difference sheet so its blank, copy todays data over to yesterdays data and repeat the process daily.
View 2 Replies
View Related
Jul 29, 2009
I am trying to compare a list of product codes on sheet 1 column A, with a larger list of product codes on sheet 2 column B.
My aim: when i click to button on sheet one the part numbers that are in both lists (sheet 1 and 2) Should be moved into sheet 3 columns B and be rearranged so that they match horzontally with the full sheet two pricelist which will have now moved to column A sheet 3. This should leave gaps in column B where there was no matching part number.
View 12 Replies
View Related
Sep 27, 2011
I have two lists of serial numbers which I want to compare to find those number which are not in both lists. I have used the following formula: =IF(A2=$G$2:$G$283,"",A2) where A2 equals the first cell in list 1, G2:G283 the range of the second list, true value "" false value display the contents of the original cell.
This works so far; where the contents in the A and G cells are the same on the row eg A2 and G2, but where they are different the contents of the A cell are being returned even if they are elsewhere in the range.
View 4 Replies
View Related
May 7, 2012
I would like to compare the data between 2 worksheets ("Sheet1") and ("Test") so that the contents of any cells in "Sheet1" that are different to the corresponding cells in "Test" are highlighted with a yellow background.
View 5 Replies
View Related
Feb 15, 2013
I have two worksheets, whereby the headers pm each worksheet are the same. the data however may not be. If the entries are different, I have used =IF(ISNA(VLOOKUP(B2,'Master 0202'!B2:B50,1,FALSE)),"New","") to pick up new entries. Fine.
However, those that are not new, i.e. they are both in the old worksheet and the new worksheet, I need to find out whether there is any change in any of the cells. The headers run from columns A to W.
How can I find out if there are any changes in any of the cells in one go? Can I use another VLOOKUP?
View 1 Replies
View Related
Nov 20, 2013
I want to compare two spreadsheets with the same data. One spreadsheet is for a certain type of beneficiary and the other spreadsheet is another type. We will call them Spreadsheet A and Spreadsheet B. They both contain Zip Codes and a count of zip codes. (Some of the zip codes is not a 5 digit code, but some letters or letters and numbers.) I want to compare the two spreadsheets and see if the same zip code appears in both spreadsheets and if they do, show them in a separate tab and total the two counts from the two spreadsheets.
Column A - Zip Code
Column B - Count
View 5 Replies
View Related
Oct 1, 2008
Right now i have a list of data in text format....they are stock tickers. One column is about 2000 entries long and the other is 500 entries long. I need to compare the two columns together and highlight the entries in the long column when they match to an entry in the 500 column. I have tried so many different formulas and conditional formatting on my own and can't figure it out
View 9 Replies
View Related
Feb 23, 2009
I am wondering if it is better to do this in excel or access. I am pretty familiar with access...i know this would be easy to do if querying from one table and doing a relationship between identifier, but i'm not sure how to capture data from both tables. But basically this is what I want. I have 2 files, which have identifiers and share amounts...both files will have some like identifiers, some not alike...basically this is what I want to do: ....
View 9 Replies
View Related
Aug 28, 2009
I'm trying to compare two sets of data on the same sheet. Each set has 4 columns...A - D and F - I. I would like to take the data from D and I (range 20 to 50) and compare them to see if any differences exist in the two columns. If there is something different I would like to bold and highlight the cells (A-D) in that row, and the same goes for the second set of data (F-I).
I'm trying to do this with macros so I can add it into my existing code.
View 9 Replies
View Related
Jul 10, 2014
I have two worksheets.
1 worksheet I have a value and I need next to it the result i take from other worksheet.
LETTERS
AMOUNT
A
Result
C
Result
G
Result
[Code]...
The list goes on.
So Pretty much I need to compare the value "LETTERS" on the first worksheet with the array of letters from second worksheet and insert in the field of the Result, the value next to the correct find in the array of letters ...
View 9 Replies
View Related
Oct 28, 2009
I am trying to compare two sets of data.
I have a set of All zip codes in 10 counties in one worksheet:
ex:
Column 1 / Column 2
30303 / Fulton
30307 / Fulton
30310 / DeKalb
30234 / Cobb
But this sheet has about 300 zip codes
Then in a second worksheet I have dollars by zip
30303 / $10,000
30456 / $56,000
I'd like to make a third column in this sheet that fills in what county. Does anyone know how to search sheet one for the appropriate zip, then stick the word beside that zip into the column 3 secion of the dollars by zip code?
I'd rather not have to search every zip code in the dollars section one at a time. Not all the zip codes in the state are represented in the second sheet, so it's not quite as simple as just lining them up.
View 13 Replies
View Related
Aug 21, 2014
So I played around with the macro i posted on the previous thread, two allow me to compare two columns of data to see if they contain the same information, and have come up with the below, which works almost flawlessly:
[Code]....
The idea being that it compares the two rows of data, then in the colmun to the right puts a y or n if the data is contained in both cells. I have added in some code that related to highlighting those cells with 'n' in the allow me to see it much easier. The macro works fine, however it seems that when it runs, it will sometimes highlight random cells not in column d, for example colum g etc.
View 5 Replies
View Related
Apr 20, 2009
I have a spreadsheet containing donation data for a non-profit organization with over 25,000 rows. The spreadsheet contains a lot of data, and is sorted by donor name, donation date (when the donation was made) and the feedback date (feedback on how the donation was used).
I'm looking to analyze how many transactions were made by a donor within 10 days of receiving feedback for a previous donation. The condition to check for is
(donor name is the same) AND (donation date >= feedback data for a previous donation) AND (donation date <= feedback data for a previous donation + 10 days).
How do I best perform this comparison? For instance, a donor may have made 20 donations, so I need to compare these donations amongst themselves and flag the ones where the donation date within 10 days of the feedback date for any other donation. I need to perform this exercise for each individual donor.
View 12 Replies
View Related
Nov 6, 2009
I need a macro to do the following:
I need Invoice # from column A to put in Column I by comparing Name in G and H with Name in D and E.
For Example
Compare Alfred Whitworth (Column G & H) in Column D & F would give Inv # 88530 ...this value will go in column I.
Compare Angela Owens and could not find in Column D and E so leave Column I Blank.
Compare Annabelle Deen would have Inv # 88740
And so on...
View 9 Replies
View Related
