is there a formula I could use to compare two similar lists to determine what is missing, for example an old list of 200 lines and another of 275 lines, with a first column containing unique ID code.
View 1 RepliesI have two columns which i want to compare, they contain text data such as A123.
what I'd like is if its in column A and not in Column B then add to bottom of column A.
Once its in column A i can do the vlookup's to draw the other data, costs etc, over but don't know how to identify, and add, the missing codes to the list.
how to compare maximum similar characters within same colowmn,
Part numberEXTRA 30 BIANCOEXTRA 30 BIANCO SPREXTRA 30 GRIGIOEXTRA 30 GRIGIO SPRFMSTYLE ALPIFMSTYLE ASIAFMSTYLE ASIA SPRFMSTYLE BEIGEFMSTYLE BIANCOFMSTYLE CARAMELFMSTYLE GIALLOFMSTYLE GR.PERLAFMSTYLE GR.PERLA SPR
I have a list of over 2000 names and need to establish which names from column A appear in Column B, as well as which do not appear in column B.
I attach an example : NameCheck.xlsx
I am trying to find some missing values compared to 6 base values. For instance, I have a sheet with some names translated to another language, I am trying to find the languages some names have not been translated too.
For example, if I have six languages, Arabic, Japanese, Russian, Chinese-Simplified, Chinese- Traditional, and Korean to compare too, I want to find any names that are not translated in certain languages.
Sample:
John Japanese
John Chinese - Simplified
John Korean
Martin Arabic
Martin Chinese - Simplified
Martin Russian
Ramon Arabic
Ramon Russian
Sam Arabic
Sam Chinese- Traditional
Is it possible to have a formula that generate a Unique list (List 1 and List 2) of Missing values in List 2 by comparing two lists? Something like this
List 1 List 2 Missing in List 2
1 1 3
3 4 6
5 5
6 6
I would like to compare email addresses for dupes and add missing PIN number in Column F Sheet 1 from Column F in sheet 2. Is this feasable and relatively simple? I have enclosed a test example as teh acutual scenario has thousands of entries.
View 3 Replies View RelatedI have two list of names, mostly duplicates. List in column B has about 30 more names than the list in Column A and I need to identify which names are in column B that are not in column A. Names are in exact same format since they were pulled from the same data base.
View 1 Replies View RelatedI have 2 worksheets that need to be merged. More specifically, I have one 'master' XLS file with some columns included in it that are missing from a second XLS file. I need to merge the new columns (with column titles in row 5) into the second 'auxilary' sheet without touching data above row 5.
View 4 Replies View RelatedHow i could this one in formula or macro, first my 1st sheet is just as show below (maybe use for a template)
1st sheet (full list)
fruits
china
orange
0
0
0
0
fruits
china
apple
[Code]...
then 2nd sheet, report i get which something like this :
fruits
china
orange
1
25
1
500
fruits
jiapan
kiwi
2
[Code]...
then i want result like this on 3rd sheet :
fruits
china
orange
1
25
1
599
[Code]....
so basically i want add missing row on sheet 2 from sheet 1
I am using office 2010.
I have two separate lists, one in Column A and the other in Column B. Both lists are roughly 2,000 rows long. For the most part, the two lists are identical, but not quite.
I'm trying to determine what data is present in Column A that is absent from Column B, and vice versa. That's easy enough to do with COUNTIF (at least that's what I've tried), but to complicate matters, duplicate entries appear in both column A and column B.
For example, in column A, the word "Electric" appears 13 times...but in column B, it only appears 8. I need to know these differences in a quick and easily readable way (remember, the spreadsheet is roughly 2,000 rows).
I have two numbers I need to compare using and IF statement. The two numbers are virtually the same, and I want the IF statement to round the numbers so any difference under 100,000 is not detected. For example:
Cell A1 = $32,194,884.45
Cell B1 = $32,194,884.37
I would like the statement to look at this number as 32.19 (divided by 1M). I am using the below equation currently, but it is not working:
=IF(ROUND(A1/1000000,1)<>ROUND(B1/1000000,1),"CHECK","0")
Battling through a problem, but would like to get some feedback on some failing code.
basicly created a bunch of macros to get 2 columns. One has last months dates, and the other has the new ones.
I want it to check if the date in columnH is greater then columnJ then update (i have vlookups to get this info with offsets). You see if there is no result on columnH it means it does not add a date.
heres the code that i have. The error is that i am using an invalid statement in the case ( i suspect the >=)
I need to compare 2 lists (each list has 3 columns [first name, last name and email address).
I am trying to compare columns A(incl. First name), B(incl. Last name) and C(incl. email address) to columns D, E and F.
D, E, and F are also First name, Last name and e-mail address respectively.
I need to compare A, B, and C to D, E, and F and find duplicates and highlight them.
Every Monday, our database sends me a list in excel, with a snapshot of our inventory.
Here is (simplified) how it looks like
part#, desc, color, units
001 Camry Black 13
003 Toyota Green 2
009 Honda Yellow 17
and so on for about 8k rows.
Every Monday, the list is different. we sold some items, so the "units" in stock are less, we run out of some items, so that part# is not in the list,
we acquired some new items, with a different part#, and new amount of units, some part#s have more units in stock.
I need to send to our boss, a spreadsheet with a list of all part#s in stock every Monday, and the last 4 weeks, how many we sold of each in the last 7 days, so we can try to get rid of inventory that is getting obsolete.
It would be easy to do, if not for the fact of the "new" items that come in, and the items that we run out of, and are not in the list anymore. With 8k items, to to the corrections by hand, it gets old very quickly
I need to search a column of numbers in Excel (A) and compare it to another column (B). I need to find all of the numbers that apprear in column A but not in column B. Can this be done?
View 4 Replies View Relatedhow to compare the value in one cell on sheet "Block #1" to a list of values on sheet "Data". Specifically I want to be able to look at the value in a cell (which will be a date) and then compare it to a list of company holidays on another sheet in the workbook and if the date is a company holiday it will display the word "Holiday" in another cell I specify on "Block #1" I am doing this to automate generation of a calendar which will have 13 worksheets one for each of 13 4-week blocks. I can get the calendars generated, but can't seem to be able to get it to compare the values of each date to the list of holidays.
View 3 Replies View RelatedI'm sure this query has been answered somewhere else but I can't seem to find it. I basically have information in two worksheets in the same workbook which need to be compared and the missing values from one worksheet need to be listed in a message. There are duplicate values in both worksheets so only need a list of the unique missing values. For example:
Sheet1
Column A
1
2
1
5
5
2
3
5
4
Sheet2
Column A
2
3
3
4
3
4
The message box should state that we are missing 1 and 5 from the dataset as it is not in Sheet2. If the list could be sorted in ascending order that would be great too.
I have a list of two rows of over 20,000 items. I would like to know what data has been added to the second row from the first row and what has been removed.
View 4 Replies View RelatedI am currently trying to work out what the best way would be to search between two sheets and find out where the differences are. IO have sheet 1 and 2 which both have account numbers and details on. I searching off the first column on both sheets and trying to identify which records exist on sheet 1 but not on sheet 2. The code I have so far is
Sub check()
data_sheet = "Sheet1"
target_sheet = "sheet2"
rowcn = 2
Do
If Sheets(data_sheet). Cells(rowcn, 1) <> Sheets(target_sheet).Cells(rowcn, 1) Then
Rows(rowcn).Select
Selection.Font.ColorIndex = 3
End If
rowcn = rowcn + 1
Loop While Sheets(data_sheet).Cells(rowcn, 1) > 0
End Sub
This currently works 50%. Only problem is that this code is not independant to each list, it simply looks at the same cell reference on both sheets and check whether the value is there. It doesnt actually go down the whole list and checks whether it is there.
I receive a daily spreadsheet which contains (amongst other information) a column of postcodes. I'd like to run a macro which compares these postcodes with a list of other postcodes, and then to colour highlight each instance of that post code in the daily spreadhseet.
I have been able to find and modify a macro but that searches for one post code at a time and then adds a comments box to the cell.
Instead of copying that piece of code and editing it for the several hundred post codes I need to "look up" against, I'd like to know how to create a list of post codes in the macro, so that the macro searches the first post code in the lookup list, finds and highlights each cell instance, then moves onto the next post code in the lookup list, until the end of the lookup list. I would like this as the editing of the lookup list would be much easier than editing paragraphs of code.
i have a list in column "a" in sheet 1 and a list in sheet 2 column "b" range from row 1 to 50
i would kike to get the data from sheet1 that is not in sheet 2 and copy it in to sheet 3
I have two spreadsheets full of products one of new products and the other of old products. I want to filter out the old ones that are discontinued but keep the ones that are still there. The old list has data I want to preserve for the new listings that would be duplicates from the old ones. I want to delete the new listings that already exist in the old list. I'm not sure exactly how to do it. I tried to delete dups based on product id or name but I'm afraid it's deleting entries I want to keep. Both lists contain over 5000 products each. I need to compare two lists and filter out the old products then work on removing the dupes.
View 3 Replies View RelatedI am a newbie and need your expert help please.
I have two email lists. For example they are:
List 1:
a@spam.com
b@spam.com
c@spam.com
List 2:
abcdef@spam.com
b@spam.com
c@spam.com
In list 1 the non-matching address is a@spam.com. namely it is not common to both lists. I want to automatically go through the lists and output firstly a new list of the non-matches from column 1 and also a new list of the non-common items from list two.
So in this case the result would be that somewhere there is a new list from list 1 that has "a@spam.com" and secondly there is a new list from list 2 that has "abcdef@spam.com."
Obvioulsy the real list is more complicated and longer but the principle will be the same.
I have searched some of the other forums but have not found anything specifically related to what I'm trying to do.. An example is attached. Basically just want to compare data between two different lists and then in two additional columns (Differences & Similarities) display that corresponding data.
View 3 Replies View RelatedI have two lists that I have compare to compare.
Expected result: Mark items matched in next to them, output unmatched items in column "J"
I started the code below. It properly marks the correct items, but I have trouble outputting them in Column "J"
Sub BNKREC()
Counter = 0
For i = 3 To 9
For J = 3 To 6
If Cells(i, "B").Value = Cells(J, "F").Value And Cells(i, "C").Value = Cells(J, "G").Value Then
[Code] .....
List 1:
Date Check# Amount
03/02/2013 100 $1,000.00
03/03/2013 101 $1,045.25
03/05/2013 102 $280.56
03/06/2013 103 $456.31
03/08/2013 104 $250.55
03/15/2013 105 $2,456.12
03/25/2013 106 $844.76
List 2:
Date Check# Amount
03/02/2013 100 $1,000.00
03/05/2013 102 $280.56
03/08/2013 104 $250.55
03/25/2013 106 $844.76
Expected result:
Un-Matched List:
03/03/2013 101 $1,045.25
03/06/2013 103 $456.31
03/15/2013 105 $2,456.12
I am trying to compare two lists and extract from one to add back to the source. However neither are alphabetically.
List 1
accessories
belt
boots
cardigan
coat
glasses
gloves
hat
List 2
tshirt
tshirt
jumper
jumper
jumper
tshirt
shoes
cardigan
So i would the vlookup to add automatically back to list 1 tshirt, jumper and shoes.
List 1 would now look like this
accessories
belt
boots
cardigan
coat
glasses
gloves
hat
tshirt
jumper
shoes
The Master list:
Sheet1, Column C has unique numbers, i.e, 0608-211-1093-2.
(can have thousands of entries)
The Comparison list:
Sheet2, Column C will have duplicates of some of Sheet1, Column C numbers.
(Sheet2, Column C will have the numbers but other columns will have different
data than Sheet1 and may have a few hundred entries or less)
I need a macro that will that will compare the two sheets for duplicates based on
Column C and then write the entire row of Sheet1 with the duplicate number to a new Sheet3.
Need to find a column of numbers from one worksheet and delete them from another.
Example
To be deleted is below:
83221
83223
83232
83233
83236
to be deleted from is below:
83221
83223
83226
83227
83228
83229
83230
83232
83233
83234
83235
83236
Another issues is that the worksheet that needs to have the numbers deleted from has 3 columns total so the whole row would need deleting, or if its easier I could replace the numbers with 0 and then sort and delete.
I have 2 workbooks and I must list the differences between them. Here is an example of what I need to do -
Workbook 1
Column1Column2DUE0.044433NHF0.068691CBA0.059029RMD0.051539CSL0.047868WOW0.047744
Workbook 2
Column1Column2DUE0.044433NHF0.068691CBA8AGI5WOW0.047744
The report to look like this
Column1Column2StatusCBA8modifiedRMD0.051539deletedCSL0.047868deletedAGI5added