I'm trying to compare two quantities on two different sheets. I've used VLOOKUP in the past on other sheets. This time round I cannot understand how to us it on this query when the data is contained and mixed in one cell.
Here goes explaining..
Sheet1, shows we have stock of Mini Speakers in blue, the 'Main SKU', 'Size' and 'Current Stock'
'Current Stock' is the cell we are trying to compare
Sheet1.PNG
Sheet2 shows the 'SKU', 'Name' and 'Option1'. Option1 contains the values I'm trying to compare... sku":"X-MiniB-S","value":"S","quantity":"1"
Sheet2.jpg
Basically, I need to check that 'Current Stock' matches the value in 'Options1' and return a value if they do not match.
I have a somewhat convoluted problem. I am trying to develop a QA tool that compares data from a user-created spreadsheet against data from an automated spreadsheet. From my (admittedly small) understanding of Excel, this would be easy to do using VLOOKUP, however there will not be any unique IDs between two columns.
To better visualize this, imagine on Sheet A the following:
Placement Name Ad Tag Clickthrough URL
[Code]....
Essentially what I am trying to do compare data from a manually filled in sheet against data that would output the above, and make sure that everything matches up correctly. .
I'm curious if there's a way to use the Vlookup function to compare TWO values in TWO cells with two values in two OTHER cells (same row though) and return one value from another column. Something like
Vlookup(A2 & C2, Sheet2!A1:C400, 3, FALSE & FALSE)....? Does that make sense?
Say the values in A2 and C2 are in A24 and B24 respectively in Sheet2. Can it return C24 for me?
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.
I need a Vlookup to look at two columns when searching. here is an example:
Sheet 1 is my report grid to hand out to the employees, A1 has a drop-down list with my employees names and A2 one for the month, when you click an employee name the vlookup formula fills in the information from sheet 2 (data sheet).
Sheet 2 has data like: Col 1= Month, col 2=employee L-name, col 3= production etc...
currently the vlookup formula messes up because the employee name is in there multiple times because of the months column, i have to delete all months except the one i need. formula is: =vlookup(A1,'sheet2'!,B:C,2,false)
this formula will pull the data in sheet two column 3 (production) for the employee that matches.
how can i make it compare first to col 1 (month) and then col 2 (employee).
SHEET1 Cell A1 HAS EIGHT CHARACTERS THAT MATCH THE FIRST 6 CHARACTERS IN A1 OF SHEET2. BUT, SHEET1 CELL A1 THE FIRST CELL HAS 8 OR MORE CHARACTERS.
HOW DO I GET A VLOOKUP TO COMPARE TO FIRST 6 CHARACTERS OF SHEET1 TO FIRST 6 IN SHEET 2? In columm B sheet1 i would like the result of column c sheet to, e.g. 9
E.G. SHEET1 A B C DOGGIEDO SHEET2 A B C DOGGIE 5 9
I'm having difficulties using vlookup to compare two columns of TEXT data. My goal is find out how many of items in the "Eligible Serial Numbers" list have been ordered for destruction. The items listed for destruction are listed in the "Serial Numbers (destroyed).
Whenever I put in a VLOOKUP, I get a serial number that doesn't make sense to me. For example, if I want to find out if "362351581" from the Eligible List appears on the destroyed list. What do you suggest?
Code:
Destroyed? (i.e. Vlookup formula in this column) Eligible Serial Numbers Serial Numbers (destroyed) 362351581 362351581
I have .csv file from which the data is importing to master schedule. i have a column in .csv file which is spitted into multiple columns. and my need is i have to compare this each individual cell value with the range of header values in master schedule and if match found i have to place that cell in the row.
I am adding 2 attachments one is .csv and other is master schedule.
I couldn't able to find where i have to attach my files.
In sheet1, I have the borrowers and outstanding per employee. In the Area Avg sheet, I have the average borrowers and outstanding in each area as this is the standard for comparison (I got this through the subtotal function).
Now what I have to do is in sheet1, format(change the color) each cell under borrowers which is greater than the average of the relevant area. Since, (in my actual data) the name of the area may be duplicate in different regions, the reference value (average borrowers of the area) from "Area Avg" sheet has to be traced through multiple criteria which are: Division, Region and Area. The same also has to be done for outstanding but the solution can be provided for borrowers only.
I'm trying to compare two very large files to find some that are in one AND also in the other. I'm using =VLOOKUP(C2,Parts2!C:D,1,FALSE) and my results are #N/A for all of them. I have formatted both columns as 'General' because there can be letters and numbers in the serial number, I have also tried formatting as text to no avail. I have confirmed no preceding or trailing spaces or other special characters.
I am however, able to locate values in both documents using Ctrl F. I'm totally puzzled because I've always been able to figure out what's causing my Vlookup issues when I have them but this time I'm not able to.
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
Excel seems to be miscalculating some formulas and I was hoping someone might have seen this before:
I'm attempting to do math on two cells and return the result in a third cell
Jx = time typed manually AAx = time that is the result of a lookup function from data in a different worksheet ABx = the result
Here is the formula that I am putting in ABx: =IF(ISERROR(AA39-J39),"",IF(AA39>=J39,(HOUR(AA39-J39)*60+MINUTE(AA39-J39)),-(HOUR(J39-AA39)*60+MINUTE(J39-AA39))))
J39 contains the time 18:31 and AA39 contains the time 18:30.
When the formula evaluates, the error #NUM is returned. Here are the calculation steps:
Using VBA, I want to import data from a text file into a worksheet, but only import a row of text if the three-letter code matches one I have in a named range. The data looks similar to this:
A formula that would compare any value in column 1 to any value in column 2 to see if they are a match for each other. If a match is detected it would be indicated in column 3:
I have a spreadsheet and everyday it would change in length.
But I would like a macro that compares the value of each cell in column B against the value in corresponding cell in column D then moves to the next roll - so eg B2 against D2,B3 against D3......B50 against D50 until all rows are compared.
And when it finds a different makes the background colours go red !
I have a bit of a code but I think I need to loop to compare all populated cells !
If our work for the intersection of two columns A, B supposed to be output 1, 2 and 3 Required to find the remainder of the A and show it automatically in column C The remainder of the B reveled in column D
Note: columns A,B repeat accepting of figures
To explain the result of the remaining column A is 1, 1, 5
If the record exists in both sheets then compare the value in col. H for both records, if the value is different then copy the entire record to a third sheet.
There are 16,000 rows of data to do comparisons. I have been thinking about a Vlookup method as one choice, my thought is this going to be slow. I have read about vba having some comparison choices, vbbinary, vbtext, but I do not have any knowledge of this, so cannot intelligently choose what options to begin w/.
i have a range of values and want to compare the value of a single cell to all those in the range, returning TRUE if a the value matches any value in the range.
I have a worksheet where I want to compare values in two of the columns. In column B there are EAN codes consisting of 13 digits. In column E there are 5-digit numbers.
What I want to do is to compare the 5 digit number with the EAN code where these digits should be in position 7-12 in the EAN code. Those numbers in column E that doesn't have a match in column B should be marked (for example that the background color of that cell is set to "red").
Example: B E XXXXXX12345X 12345 (match - do nothing) XXXXXX12346X 12346 (match - do nothing) XXXXXX12348X 12347 (no match - set background color to red) XXXXXX12349X 12348 (match - do nothing) XXXXXX12350X 12349 (match - do nothing) XXXXXX12351X 12352 (no match - set background color to red)
There are more values in column E than B.
In all there are close to 4500 rows so I really could use a nifty macro to do this job.
I have two columns with numbers. For every row I want to find out if either of the numbers in the two columns is in a third column that I have with numbers. Does anyone know hoe to write a formula for this?
I am trying to replicate a method , it reads: 'any data point that changes by more than or less than 10% from the previous value, and does not remain at the new level for more than 6 days, should be removed.'
Thats a bit vague. The process is going to involve if statements, etc. I've attached an excel spreadsheet with some solutions I've tried. Neither of which work. The aim is to get the same answers as the highlighted column. Columns B and C replicate the method answer, but do not state the process. The letters L represent values less than 25. Its the letters D I'm trying to eradicate.
how 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.
