Search : in
By :

How to match two columns

Last answer on Jul 15, 2009 10:36:01 am BST Paris, on Sep 21, 2008 11:09:45 am BST 
 Report this message to moderators

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

Configuration: Windows XP
Firefox 3.0.2

Best answers for « How to match two columns » in :
Excel – Comparing cell A1 to entire A column in Sheet 2 ShowExcel – Comparing cell A1 to entire A column in Sheet 2 Issue Solution Note Issue I have been trying to compare sheet1 A2 to sheet2 A2 through A500 and if it exists somewhere in sheet2's a col then copy that entire row to a new sheet....
Call of duty 2 (COD2) Bugs ShowCall of duty 2 (COD2) Bugs Call of duty 2 bugs on VISTA Patch Installation Direct X 10 configurations Multiplayer Mode You have problems running COD 2 on Vista? This is because this FPS game was released before vista So we will...
[Vista] Unable to install Service Pack 2 Show[Vista] Unable to install Service Pack 2 You try to install Service Pack 2 (SP2) for Vista and you get an error (0x80070057), here's the solution: Uninstall the 3 latest security updates following: KB970653 KB972036 KB973874...
PS/2 connector ShowPS/2 connector The PS/2 connector (mini-DIN6 format) is mainly used to connect computers to keyboards and mice. Pins Pin number Function 1 Clock 2 Ground 3 Data 4 Ground (or not...
Ask your question Reply

1

Ivan-hoe, on Sep 22, 2008 7:00:48 am BST
  • +13

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.

   
Reply to Ivan-hoe

10

 hai, on Jul 15, 2009 10:36:01 am BST
  • +1

Thanks...this solution really helped me to understand VLOOKUP....

   
Reply to hai

2

karthik, on Jan 22, 2009 4:00:13 pm GMT
  • +1

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

with regards
karthik

   
Reply to karthik

3

xiaoxiao, on Jan 22, 2009 8:34:48 pm GMT

In cell c1, type in fx=if(isna(vlookup(b1,sheet!a:b,2,false)),b1,"")

Good luck

   
Reply to xiaoxiao

4

karthik, on Jan 23, 2009 1:58:37 pm GMT

Hi xiao,

thank u for ur advice . i tried ur formula but i could not get the result as i desired. i explain my problem in the following way.

Sheet-1

A B C
Issued Encashed Un-encashed cheq
230001 230002 230001
230002 230005
230003 230008 230003
230004 230009 230004
230005 230014
230006 230020 230006
230007 230024 230007
230008 230029
230009 230030
230010 230032 230010
230011 230033 230011
230012 230012
230013 230013
230014
230015 230015
230016 230016
230017 230017
230018 230018
230019 230019
230020
230021 230021
230022 230022
230023 230023
230024
230025 230025
230026 230026
230027 230027
230028 230028
230029
230030
230031 230031
230032
230033
230034 230034

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

with regards
karthik

   
Reply to karthik

5

karthik, on Jan 23, 2009 2:04:50 pm GMT

Hi xiao,

the above clomns are not correct.
see thie below column orders.



A B C
Issued Encashed Un-encashed cheq
230001 230002 230001
230002 230005
230003 230008 230003
230004 230009 230004
230005 230014
230006 230020 230006
230007 230024 230007
230008 230029
230009 230030
230010 230032 230010
230011 230033 230011
230012 230012
230013 230013
230014
230015 230015
230016 230016
230017 230017
230018 230018
230019 230019
230020
230021 230021
230022 230022
230023 230023
230024
230025 230025
230026 230026
230027 230027
230028 230028
230029
230030
230031 230031
230032
230033
230034 230034

   
Reply to karthik

6

karthik, on Jan 23, 2009 2:18:23 pm GMT

Hi xiao,

sorry i tried to paste the columns from excel sheet but its alignment gets changed after posting.
hence i type directly here.

---------A ----- ---------B -------- -------C--------
Issued cheq Encashed cheq Un-encashed cheq
230001 230002 230001
230002 230004
230003 230006 230003
230004 230010
230005 230016 230005
230006 230020
230007 230007
230008 230008
230009 230009
230010
230011 230011
230012 230012
230013 230013
230014 230014
230015 230015
230016
230017 230017
230018 230018
230019 230019
230020
230021 230021
230022 230022


kindly help me
than u
with regards
karthik

   
Reply to karthik

7

karthik, on Jan 23, 2009 2:20:31 pm GMT

Ohhh my god

even now also the alignment got changed

i colud not post it properly

   
Reply to karthik

8

Benzolio, on Jan 30, 2009 4:31:17 am GMT

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.

   
Reply to Benzolio

9

Benzolio, on Jan 30, 2009 4:34:20 am GMT
  • +2

BTW, this was for karthik's issue not the first one

   
Reply to Benzolio
Ask your question Reply
They need your help