Hello,
I have 2 excel sheets with exactly the same format, in column A, I have the name of my customers and in Column B, the value of their sales. the other sheet contains exactly the same information but not in the same order. Now what I want to do is : If the name of my first customer (A1,sheet1) exists in the A column of sheet 2 then bring the sales value to cell C1 of the first sheet.
Sheet 1 ------------------ Sheet 2
A --------- B ---------------- A ------ B
X --------- 500 ---------------- Y ------- 540
Y -------- 1000 ---------------- Z ------- 100
Z -------- 200 ----------------- X ------- 800
And I need the result as this :
A -------- B -------- C
X -------- 500 -------- 800
Y -------- 1000 -------- 540
Z -------- 200 -------- 100
Hello,
use a VLOOKUP formula ; something like =VLOOKUP(A2;Sheets2!A:B;2;FALSE)
if the customer does not exist in the second sheet, this will return #N/A
I.
HI IVAN
i expect a similar kind of solution for my problem.
i have a list of 6 digit cheque numbers in ascending continuos serial order. for example, 223000,223001,223002.....etc. in one column 'A'.
then I have a list of 6 digit enchashed cheque numbers randomly in ascending oreder. i.e., 223002,237652, 238645,245674......etc like this in coumn 'B'
now I want to match these two columns and form a 3rd column with all un-encashed cheque nunbers.
akindly assume that all cheque numbers in column 'B' are defenitely available in column 'A'.
in terms of set theory, I want A-B ( here 'B' is a subset of set 'A').
kindly provide me with some solution for this
thank u
Try putting the following in C1 and dragging it to fill down column C just as far as you have data in column A :
=IF(COUNTIF(B:B,A1)<1,A1,"")
Alternately, you could use soething like this array formula in column C:
{=IF(COUNTIF(B:B,A:A)<1,A:A,"")} [press ctrl-shift-enter]
Although it would be an inefficient solution as is, it should theoretically work with some tweaking. I tried it this way, but as an array function on the entire row C, it took a few minutes wasting time to compare all the empty cells.
i want a result in column 'C' as above. we can also put column B data in 1st column and column A data in 2nd column but the result must be column C as abobe.the no. of data in column'B'(as above) will be defenitely less than that in column'A'(as above).
in a nutshell, out of 1000 cheques issued 700 cheques have been encashed randomly. but how to find out the remaining 300 cheques from the original list.
all data are in the same sheet. kindly tel me about what 'sheet!a:b" represent in ur earlier formula.
kindly help me.
thank u