I am trying to create macro in Workbook 1 and that looks up Workbook 2, searches column A (starts in cell A3) and compares value in Workbook 1, column B (starts in cell B30). If the value doesn't exist in Workbook 1, then create a new row in Workbook 1 and copy (or add) info from column A, B and C from Workbook 2.
View 9 RepliesBasically 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 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..................................
I am using vlookup to decode binary words. Some of the words begin with at least one zero (eg 00101001) when I enter such a word it is considered a number and removes the leading zeros. The comparison then fails. If I call it text or some such, the vlookup fails because, I guess, it is complaining I'm treating a number as text. If I seperate those words begining with a zero from those that don't and create two tables, each table works fine but this will lead to a lot of ranges and make things messy. How do I get the decoding done with just one range?
View 9 Replies View RelatedI have attached my spreadsheet I am working with. I have not been able to get a formula to work with what I am trying to accomplish.
Need to compare Column B to Column A... If there is a match anywhere in Column A, copy the item in that same row of Column C into the Column D
I have 2 xls file what I want to do to compare the 2 files see attachment
vlookup.zip
On sheet 2 is the main xls ... what I want to compare sheet 2 to sheet 1 and if it's on sheet 2 and sheet 1 if will post the result to sheet 3 pulling all the data to those colum ie
Employee IdInstrument IdQuantity Br Account
95086 Dec 300 511 2046
what has to match is sheet 2 Emp# and Qty to sheet 1 Employee Id
and Quantity
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).
A is a list of numbers 12345, 23456, 34567 etc all unique, ascending sort
B is a list of types, its either 1 or 11 or 3
C is a list of the same numbers as A but they are not sorted and that cant be changed
D is where I need to insert the type (1, 11, 3) for each number in C
Ideally, it will take A2, find it in column Cxxx, and insert the type from B2 to Dxxx and continue doing it for each row
I need to compare add Column A&B in sheet1 and column A&B in sheet two and compare the two result for the difference(like vlookup).
formula to merge two cells in two sheet and compare.
I have two cells which contain text, and I'd like to use IF function to compare between them. Something like: =(IF(A1=B1),"TRUE",FALSE"). But it seems like the function cannot do it with text..
View 3 Replies View RelatedI have two different tables, 1 table gives the conditions you need for specific stations. Stations is shown vertically (10 to 150), and conditions people must have are shown in the top row. (Alphabet). This data is given and can change alot. If I compare this data, a new table will be created that compares the conditions per station with the people if they have it too. Then it will need to give an answer, 'Yes or 'No''. Yes means he has met all the conditions needed, so has all the competences needed for the station.
I tried to do this with normal formulas, but no success. I can do it by doing like 100 IF's in 1 formula, but that isn't efficient.
Also I have done a formula:
IF(SUM(IF(Competentions!$D$21:$BT$21=Competention!$D10:$BT10,0,1))=0,"x",""),
But that only compares if the person has exact the same competences, if he has more then the needed on 1 location, a NO will be returned. Same with count, that doesn't work either, if he has the same amount of competences needed for example station 10, he is allowed to, but he doesnt have the competences that are needed for that station..
The following code gives me error message. There is problem in Format function. How can I write using VBA that "Scantime is less than 08:00"
[Code].......
Using Excel 2003 on XP.
In Workbook1 I have a list of users with a leave start date in column N and a leave end date in column O.
In Workbook2 some of these of users appear with a payment start date in column C and a payment end date in column D.
I need to find out if the any or all of the payment period for these users in Workbook2 falls within the date ranges of their leave in Workbook1. If not, I need to ignore (or delete) the payment record in Workbook2 for that user.
Is this possible using VBA? Mind you, I'm stuck either way as I simply have no idea about how to approach this (possibly ... would first need to work out how to identify if the dates cross and then perform a lookup ...?)
I am trying to compare one range to another. If an item in one list is not in another i am adding this item to an array called addarray. The problem is when i try to access the array to see if one of its entries is "" i get a type mismatch error. My code is shown below:
View 5 Replies View RelatedI have two large ranges of numbers stored as text.
I would like to compare first element from first one to each of the second, make a shortlist, and then further analyze the data on shortlist by some additional criteria.
After that the result is written to some place and the cycle continues with the second element from first range etc
Question:
Is it possible (in VBA) to create temporary arrays(baskets) where the fist set of data is analyzed and then after obtaining the result, array(basket) would be emptied and new sets of data is loaded and the cycle goes again?
how the code would look?
I have tried google, but not 100% sure of exactly what i am looking for, well how to describe it to google at least.
I have 2 spreadsheets for an online store, 1 is the CSV update with all active store products on it, the other is the suppliers main price list.
What i am trying to do is get some application or macro code to compare teh 2 sheets.
Update File has 4 fields, SKU, Product Name, Price, and Availability.
Suppliers sheet has many more Starting with SKU, Price incl, Price excl., Product Name and Descriptions.
What i would like to do i have is compare the SKU and Price inlc between the sheets and the if differences are found update it on the csv file.
The supplier sheet also has separations for different product categories on the 1 page, totalling about 60 000 rows. The separation rows just break the product areas and are not merged do the resulting scrip or application would need to search and replace not just flow down the column.
I have one xls sheet with employee’s information, at column A I have ID number -called Mater. I have second xls sheet with update employee’s information, at column A i have the same ID number - called update file. I need help with VBA code that update the Master file according to the update file (The ID at column A is the key between the files)and mark with color the update. The file contain more the 20,000 records and with 10 columns.
View 5 Replies View RelatedI have 2 Excel files that have 2 columns in each.
Product# and Price.
The Product#'s in the 1st sheet are in correct order and have outdated prices.
In the 2nd file the Product#'s are same but they cannot be sorted the same way as the 1st file. So I cannot just copy the column of updated prices from 2st one and paste it into the 1st.
So, my best bet would be to run a script or something that will get a Product# from 2nd file and find it in 1st, if it matches then copy the new price to the 1st file.
I know,there are "compare sheets" threads everywhere,but i think every thread is different,because we want different comparison and different results
To the point now.What i actually want to do is a "turnout" of difference between two sheets.Both sheets use columns A and B. On column A there is a code and column B the quantity of the code. So obvisously i want to compare those two columns of these two sheets,and paste the results on a different sheet. The difficult part is that the same code of a cell in Column A with its column B must be sorted so i can see the difference...else it would be a mess.
Hi Could someone please have a look at this please. I have a macro kindly supplied by jrdnoland that now needs a little tuning.
View 9 Replies View RelatedI am trying to find out if the articles in column A exist in column C. if yes, return the value of the same cell in column D, if not, return "no Match".
I know for a fact that there are money articles in column A do exist in column C, but I see the "no match" in the corresponding cell. for example, manuallyfin this article in column A13622356, and manually find it in column C. it is there, but the Vlook up returned "no match" value
I have two columns (G&H), one has expected date, and one has actual delivery date. The line amounts vary from 2500 to 8000, I need to calculate if column H is greater than column G for each line, if it is not then that line can be deleted.
View 4 Replies View RelatedI am green to Excel, but I am trying to create a worksheet for my own use. I have written a 2 column table of calculated numbers. Now I have another number generated that I wish to compare to the first column in the table. The generated number will not be an exact match. If the generated number is greater than a first column number but less than the next first column number, I wish to return the associated 2nd column number. Is this possible? There will be over 1000 numbers to compare. Is there one single string of code that will work for all comparisons? I can write this with if statements, but each cell is unique and I don't think I will live long enough to write 1000 cell.
View 9 Replies View RelatedI need to do a lookup and find that would work like this... look in Sheet1 Column A and see if any of the values match Sheet2 Column A if a match is found on Sheet2 then copy the Row value in Column B in sheet1
Example
Sheet1
Col A Col B
1234 <insert Sheet2 Col B2 match>
Sheet2
Col A Col B
0004 Info1
1234 Info1fdf
Im using nested if to compare one value and if its true it should count the values in the specified column. I tried this formula...
COUNT(IF($C$8:$C$1000="Defect", IF($J$8:$J$1000 <>"Ignored",$A$8:$A$1000,""),""))
Im checking whether its a defect and if its defect thenchecking the status whether it is ignored if so im counitng the number of defects by counting s.no in column A.
I would like to compare 2 cells, Sheet1!B2 and Sheet1!B3
(
If sheet1!b2 is between the values of "D101" AND "D105", OR EQUAL TO "D201", OR EQUAL TO "D203", OR EQUAL TO "E110", OR EQUAL TO "E111", OR between the values of "H107" AND "H110" OR between the values of "H107" AND "H110" OR between the values of "H203" AND H205
)
AND
(
if sheet!b3 is EQUAL TO "E" )
then output a 1, if not, output a zero
The formula, which I deleted, excel reports back as being too long
I put the following together. The is that it loops through row A until it gets to the last cell. For each loop it loops down the column to the last row of data looking at the same cell on sheet 2.
But I'm having trouble with the Range(RngCell, Y).select portion. As the active cell can't be a static column letter I need to reference the active column.
Code:
Sub SheetComparison()
Dim rngCell As Range, rngMyRange As Range
Dim lngLastRow As Long
[Code]....
I want to compare the value of D8 to the value of the last cell in Column D with data. I have the following code but am stuck! (This is code that I was writing from scratch and may not actually work)
Code:
Dim myRng As Range
Dim C As Range
Dim lr As Long
On Error Resume Next
[Code] .........
I am stuck on the "Else" statement...If they don't match then change the color of cell D8 if they do match I want to do nothing.
struggling with the syntax of this one. Lets say we have a variable called MYWEEK with a value of WEEK 9 and cells A1 and A2 have values of 9 and 10 respectively. I need to be able to compare the variable against cells A1 or A2 but with a prefix of WEEK .
I've tried the following:
if MYWEEK = "WEEK " & A1 then ...
if MYWEEK = "WEEK " + A1 then ...
if MYWEEK = ("WEEK " & A1) then ...
I know the answer is staring me in the face but I cant see it. Any use of the ampersand presumably invalidates the IF statement by introducing multiple conditions rather than a concatenated value. How do you compare a value against a concatenated set of criteria in VBA?