How 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)
I need to export this to Xcelsius which doesn't support any macros/vba. Btw I can;'t use Row() in xcelsius too.
[Code].....
I have 2 array variables in a block of code which I need to compare to check if the items in the arrays are exactly the same. The arrays are exactly the same size and I can see the contents of the arrays in the watch window.
When I try to run and IF statement as in: IF VARIABLE1 = VARIABLE2 THEN - I get a compile error saying "type mismatch" on the equal sign.
I am trying to create VBA function that will compare search a worksheet row by row for a value in column c. If column c matches any of the array values, i would like the cell value from column d to be entered in another array. I'm familiar with adding values to an array, but i'm having trouble with the code to compare the values. the array is a string and the cell values are text.
View 3 Replies View RelatedI have a set of old and new values that I need to compare.
We can assume that values in column X are the master values and values in Column Y are subordinate to column X
For example
Yesterday's values
x y
abc 1
def 2
ghi 3
Today's values
x y
abc 1
def 4
jkl 3
So first i need to find out that GHI is no longer available, and second that the amount for DEF has changed from 2 to 4. Hopefully this makes sense if not let me know and I can provide a sample spreadsheet or the like.
I am trying to compare the values in an array with the values in a range of cells. It worked fine until I moved the code into a sub. The specific problem that I am having is that is pulling cell values from "shippablegoodspriorday" and it is supposed to be pulling the cell values from "shippablegoods".
module1
Call compareSheets(priorShippable, Sheets("notfound"), Sheets("variance"), Sheets("shippablegoods"), [A:A], 11)
module2
Sub compareSheets(goods() As Variant, WS As Worksheet, WS2 As Worksheet, WS3 As Worksheet, col As Range, offset As Integer)
With WS3.............
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.
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.
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 RelatedIs 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
I am trying to create a list from an array. Said array contains formulas that return numbers from input contained in an other table. I would like the list to list the numbers in ascending numerical order.
View 5 Replies View RelatedI have a data array which i would like to convert to a list. Sort of the reverse of a Pivot Table
See example below
I would like to turn this
Account Jan Feb Mar April etc..
001 59 30 25 40
002 12 20 32 29
003 5 13 27 39
004 10 11 12 13
Into This
Account Month Amount
001 Jan 59
001 Feb 30
001 Mar 25
001 April 40
002 Jan 12
002 Feb 20
002 Mar 32
002 April 29
003 etc...
There is a sample of the data file attached.
I have a question regarding arrays. If I have too many elements in a 1D array(let's say 1000), how can I list all of them in a msg box (separated by comma)?
View 2 Replies View RelatedI have created an analysis worksheet but need to a formula that looks up against a list, hence why I think (and hope) that this might just need an array formula.
So the worksheet has a few thousand rows of data (one for each order) that is put against an email address. I need the formula to sum the revenue generated by 'club members' who placed on a specific date whos email appears in a list on a seperate tab. If it wasnt for the email list I would use a simple sumifs formula but I am stumped by the need to look up against a list of emails.
As close as I've got is the following;
=sumifs(data!AC:AC,data!Z:Z,"Club",data!L:L,B11,data!S:S,'Discount'!B:B)
the data tab is the one with all the data and the discount tab contains the list of email addresses to look against (B:B). AC is the revenue data, Z is whether they are club members, L is the data (B11 is the date to look up against).
I have a list of combo boxes embedded on a worksheet and I would like to have them accessible through an array. I am an old VB 5 coder and this was a method I used very often and found it to be quite the time saver, but I cannot find a way to do this in excel.
View 9 Replies View RelatedI am trying to create a purchase order that has blank item cells, but when you click on one a drop down list appears. By selecting the appropriate item, I would like the price to come up in the next cell for the item selected.
What would be even better is if once that was done there is a cell next to the item and price and this cell will be for the type of the item selected. In this case the flavour, flavours are specific to different item though?
I think the first bit can be done through a drop down list of some kind, not to sure about the flavours drop down menu though.