If MyValue has been defined as "100003" and I have a list of codes in cells A1:A5 :
____A____
1| 100001
2| 100002
3| 100003
4| 100004
5| 100005
For a = 1 To 5
If MyValue = Sheets("Test").Cells(a, 1) Then
MyResult = "Yes"
Exit For
Else
MyResult = "No"
End If
Next a
Do I have to compare MyValue by "running through" the values in the list or can I set the list as a range and have MyValue compared against that.
When we buy company 1 we need to import all of the products into our product database, however many of them are already the same. Each product has a manufacture code, and a supplier code. Company 1 uses 1 code for each product but it could be either a supplier or manufacture code - they don't use a consistent standard.
My company has a list of all supplier codes and manufacture codes for each product.
I need excel to compare the 2 companies lists and check if any of the products in their list are in my company's lists. I need excel to make a new list of any products from company 1 that are not already in the company 2 product list. The products that are not already in my list will need to be looked at manually, and so need to be easily identifiable after the matching (ie in their own list or the ability to copy into a new list.
I've tried a few different tactics to compare two enormous lists of names, but I cannot get it to work to my satisfaction. I am attempting to get them to follow approximately the same formats, but because they are from unique sources they have very different information leaving me with no choice but to actually compare by name, rather than customer number or something useful like that. Given the variable nature of names (in this case company/corporation names) I've had a difficult time getting the lists to match in a way that provides meaningful results, but one of my lists is 9,000 strong so I need to figure out a way to automate this task.
View 9 Replies View RelatedHow to compare to a string of objects
1. TH0102
2. KJ1403
3. KJ1404
4. TC0910
5. TC0905
Example :
if column data A2 (TC0905) match to any of the objects 1 to 5 , it will return me TRUE. if(A2=(1,2,3,4,5),TRUE)
Is there a way I can compare the names in column A to those in both B&C and highlight them a certain color if they are the same?
View 3 Replies View RelatedI have a list of 800 lat/long coordinates. I need to see how many of those 800 are within 3 miles of another list of 7,000 lat/long coordinates.
I have found the following formular to find the distance between 2 lat/longs:
=ACOS(COS(RADIANS(90-Lat1)) *COS(RADIANS(90-Lat2)) +SIN(RADIANS(90-Lat1))
*SIN(RADIANS(90-Lat2)) *COS(RADIANS(Long1-Long2))) *3958.756
Is there a formula that would allow me to run each of the 800 Lat/longs against the list of 7,000 and indicate how many on the original list have a match within 3 miles?
I was thinking a combination of countif with an array formula, but I can get the order of operations correct.
I am looking for VBA Macros to compare 2 price list. I am trying to achieve the following.
1. Script look in sheet one i.e. Old list for duplicate if it is their it should display in 3rd sheet i.e.duplicate with sheet name and row number
2. Script look in sheet two i.e. New list for duplicate if it is their it should display in 3rd sheet i.e.duplicate with sheet name and row number
3. Script look in both the sheet for duplicate values it it is there then it should display in 3rd sheet i.e. duplicate
Also it should count the number of records on each sheet in column F1. If possible can we display the work % while checking both the lists.
Sample file is attached.
I have included a small example file. What I would like to do is compare column A - Code on -CORP REC - on first sheet, with column A -code -SUPR REC on the second sheet. Then compare the differences and insert them into the third worksheet - discrepancy -Column A- Code and Column B Name.
Also is it possible to convert all the uppercase names on sheet one to lowercase as I would like the discrepancy sheet to names to be in lower case.
If other threads list exactly this I apologize. I am not really good at using others for adaption. but I can try if you want to direct me
I have two excel sheets, one loads up in Listbox1 and the other in Listbox2, i would like to be able to see if any entries in listbox2 match with entries in listbox1 and then to display the matches in listbox3 with a click of a command button. Is this possible? If so, how would i go about it?
View 3 Replies View RelatedI have 2 Data Lists, want to make compare and sort as matched values.
So, I need to find a solution with a formula or maybe Macro.(Formula is preferred)
At the attached file you will find the detail : CompareList&Order.xlsx‎
I am trying to see if an email address matches with a list of names in a worksheet. If so, it would proceed with code. For example, I have an email address 'middlesburg.john@gmail.com' that we get through an input box and in the list, we find John Middlesburg. Since it is obvious that John Middlesburg is middlesburg.john@gmail.com, how can I get it to go on through?
View 4 Replies View RelatedI 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
I am trying to create my own budgeting tool. I have most of the tracker set up but I hit a stumbling block. I downloaded transations from Bank of America as a CSV file into excel. I want to categorize these transactions.
I created a keyword list that has words like "Starbucks" "Exxon" etc. Each of these keywords is then listed next to a category like "food" and "gas". So I want a formula that will compare the transactions to my keyword list and return the category type. Here is an example transaction:
CHECKCARD 0256 STARBUCKS USA 00029801 GAINESVILLE VA
I have a keyword "Starbucks" in f2 and "food" in g2.
1) Background Info
We are trying to summarise some data that has been exported from an ancient database into a poorly delimited csv file. My colleague has imported the csv file into Excel ( attached), and we are trying to work out what to do next.
The dataset contains approx 300 records - each record being called a "sample" and having a unique sample number. NB: I have had to attach a cut-down version with only 3 samples, due to file-size - but it will hopefully give an idea.
Each sample contains 2 types of information that we are interested in (and a lot of irrelevant data besides). The relevant bits are:
- predicted occurrence of various species (given as a percentage for each species)
- observed occurrence of various species (given as positive/negative for each species, where positive is indicated by an asterisk)
2) What We're Trying To Do
We are trying to compare this dataset with a master-list of 80 species names. For each species on the master-list, we want to:
a) check whether it has a "predicted occurrence" value in each of the 300 samples (and if so, record the value in a summary sheet)
b) check whether it was observed in each of the 300 samples (and if so, record this in the summary sheet)
3) The Problem
Unfortunately, the dataset has imported into Excel as a gigantic list: 48000 rows (including loads of blanks) and only 3 columns across. To find the relevant data, it's necessary to:
a) open the attached workbook, and go to the worksheet named "Data"
b) Scroll through the rows, looking for the string "RIVPA" in column A. This tells you where each new sample begins. (The sampleID is stored in the same row as this, in column C. It is mixed up with a load of text, which we will need to separate out at some point, but that's a secondary consideration at the moment.)
c) Scroll down further until you find the text string "Predi" in column A. This indicates the beginning of the data we're interested in, for each sample (i.e. for sample 1, I'm talking about row 58). Count down a further 2 blank rows, and then you find the data itself:
- Column A contains the observed occurrence (a positive result is indicated by an asterisk)
- Column B contains the predicted occurrence
- Column C contains the species name
My colleague was trying to build a summary table, in the worksheet named "Summary". He was using the LOOKUP function to extract the data, but because there are so many samples, it's beyond unwieldy. He asked me if I could write a macro do do the job, and this is where I'm stuck.
How could I set up a macro that can identify where one sample starts and finishes, and where the data is within each sample? What is the most sensible shape for such a macro? If I could get the overarching logic worked out, I could make a start on writing the individual bits of code, but at present I just can't figure out how to begin.
To make things more difficult, the samples are not all the same length, and do not all contain the same list of species as each other. (However, at least there should be no species in the samples that are not in the master-list). The first sample begins on row 5, the second sample begins on row 173, the third on row 340, and so on.
I got 2 sheets. (1 called "Revenue", the other "101") in the sheet revenue are department codes (variable 100 to 999) they are situated in row "B". the corresponding data in row "D".
this data needs to be transported to the sheet "101"
in the sheet "101" are put the numbers 100 to 999 in row "A"
in row "B" we need the data from the revenue sheet that is corresponding with the department code.
I have a list of nine teams from B13:B21 with Wins in column C, Losses in Column D, Ties in Column E. Pts are in Column F (all of these run row 13 down to 21). The team listed in B13 has a cell name of One in the Name Box, team in B14 has a cell name of Two, etc up to Nine.
The pts total for each team is also named, so F13 is called ptsone and has a current value of 8. F14 is called ptstwo and has a value of 10, etc.
I'm trying to do is in a different area of the spreadsheet put a formula that will compare the pts values and then list the team standings based on those points.
I am trying to create a way to easily maintain a list of outstanding checks. I am able to import cleared checks into the spread sheet but cannot find a way to compare the two lists and then delete the cleared checks off the original list.
View 2 Replies View RelatedI need to compare two lists of client names. One list has 59k names to be compared to second list of 1000 names.
The list of 59k are listed in column D and the other list is in Column E. I tried conditional formatting but unfortunately some of the names are slightly different I.e. fair point communications vs fair point communications inc. I was trying to put in v lookup with a trim function but it didn't seem to be working.
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
compare some list of values in order to fiind the common values.
View 1 Replies View RelatedI am trying to write a function that will compare the text of cell "A2" to the text of cell "B2" and display cell "A2"'s text characters that didnt match from cell "B2" in column C. Can anyone help me out with writing this formula?
EX: ....
Basically I have two sets of data. One will be new each week. I'd like to use the non-changing data as a base to compare new data to. The formula would need to match multiple values, including a 'time between', and then return whether a minimum rate has been met.
SampleRateExamine.xlsx‎
I have data in 2 workbooks. Book1 - Sheet1 - Col A and Book2 - Sheet2 - Col A. I want to compare Book2 with Book1 and list all values in Book2 that do not find a match in Book1. I want to run a macro for this
View 7 Replies View RelatedI have two ranges of numbers stored as text on two different sheets.
The first one is from product code and the second is from product category.
The problem is both ranges are of different lengths and I have to find out if a product from the right is part of a particular product category. Even if the length is different the first digits are indicative of the belonging of a code. For example 1234 and 12345 are “family”-their first 4 digits match.
Just to give you an example of what is desired:
Category____ Code
2200 ________22002
2323________ 232347
So, the loop should do the following:Compare the first string from the “Category” column to each and every entry on the right, if a match exists (we have no match here for 2200) write “ok” next to it.
Next trim one digit from the right from every string in the “Code” column.
Compare same first string from the “Category” column to each trimmed string from “Code” column (here we should have a match 2200=2200)
Write “ok” next to it
Now the loop goes to the second string from “Category” column and for this one we will have to trim 2 digits from the right of each string in “Code” column to achieve the result (2323=2323) and so on.
I need to compare the date from the user input and the date listed on excel. How can I compare it? Is it correct? lngCmp = Val( Cells(I, 31))
Dim lngBegin As Long, lngEnd As Long, lngCmp As Date, lngResults As Long
lngBegin = 9 'beggining of data
lngEnd = 232 'end of data
lngCmp = InputBox("Please enter the date", "Begining of the week")
Lngcmp1 = DateAdd("d", 1, lngCmp)
lngCmp2 = DateAdd("d", 2, lngCmp)
lngCmp3 = DateAdd("d", 3, lngCmp)
lngCmp4 = DateAdd("d", 4, lngCmp)
lngCmp5 = DateAdd("d", 5, lngCmp)
'lngCmp1 = lngCmp + 1
'lngCmp2 = lngCmp + 2
'lngCmp3 = lngCmp + 3
'lngCmp4 = lngCmp + 4
'lngCmp5 = lngCmp + 5
lngResults = 0
lngResults1 = 0
lngResults2 = 0..................................
how I can list 3 different values to a list of list of 3 in a combo box, is.
combo box
select high = 35
select middle 30
select low = 25
I have the names in the box I just need it to add a value (which I have listed 1 in each separate cells) to each selection
I have my dummy data, and I have (what I think) is how I want the data to be shown. My friend uses Google Sheets, but I prefer Excel. I am trying to convert the code because I am a stickler for excel. Typically I can convert codes some easily, but this is way beyond me.
For Column A: I want to create a list on sheet 'Setup!' based on ids!D2:D="yes". If that list has duplicate entries, I would like only the first entry to show up, but for the next entries I would like the cell to be blank. (this is important for the next step) For Column C: I want to have the corresponding dates go with the name entry. For Column D: I want to have the notes go with the corresponding date entry. (I believe I can manipulate Column C's code to do Column D myself).
I am also going to upload a data sheet, and an expected results sheet.
unique list.xlsx
I have pulled a SharePoint list into my workbook. The list object (table) is still linked to the SharePoint list, as I'd like to synchronize it later on. I have filtered it with an autofilter. I'd like to delete all of the visible rows. I have tried a billion things to no avail. I have been searching Google for hours now. None of the examples work.
View 5 Replies View RelatedI can't seem to find a way to make a data validation list automatically show the first item in the list rather than showing blank.
View 10 Replies View RelatedI'm using Excel 2010 on a Windows 7 machine.
I have one sheet with a large list of dates and total prices.
I have a second sheet with a list of dates and unit prices.
I want to divide the total price in my first list by the unit price on my second list.
I want to somehow point Excel to the date in the first list, and then lookup the corresponding date in the second list to use the correct unit price.
My thoughts thus far have been along using VLOOKUP with WEEKNUM and YEAR but I've been unsuccessful.