1

I have an Excel file that contains two different columns having Customer Numbers (let's say CustomerA and CustomerB columns). What I would like to do to check which of the CustomerA values match the CustomerB values. Is it possible?


Update

@sancho.s: Here is the excel file and values I used.There is no error and no value as you mentioned on your answer. But after changing some values in the formula I get error "There is an error in teh formula...". Thanks for your kind help.

    A             B                         C               D
1   CustA         InB
2   6077890       =MATCH(A2,$D$2:$D$5,0)                   6077890
3   48414792                                               5861291
4   50267662                                               3082960
5   51242034
6   6079377
7   6077580
9
  • When you say match what exactly do you mean? Do mean match the adjacent customer, or do you want to know whether a given customer in column A exists in column B?
    – CLockeWork
    Dec 3, 2013 at 11:43
  • @CLockeWork: I mean "if all of the customers in column A exists in column B". Thanks.
    – Jack
    Dec 3, 2013 at 12:00
  • @Chenmunka : I have an excel workbook. There are 2 columns; ColumnA and ColumnB. There are 3 customer numbers (i.e. 100,101,102) in ColumnA. There are 100 records (customer numbers) in ColumnB. I want to search all the ColumnA values in ColumnB. Is it clear?
    – Jack
    Dec 3, 2013 at 12:49
  • @H.Johnson - Please specify: 1) What results you get in B2:B7 when copying the formula in B2, 2) What is the exact formula that gives you the error quoted. Dec 3, 2013 at 19:20
  • @sancho.s: Dear Sancho, I added the file on this link: wikisend.com/download/290184/check.xlsx I think there is no need to take your time and you can try/test on it. Thanks.
    – Jack
    Dec 3, 2013 at 20:47

1 Answer 1

2

With reference to the figure

enter image description here

cell B2 has =MATCH(A2,$D$2:$D$5,0). It gives the row number in range D2:D5 (relative to the upper row in the range) where each customer number first appears. If a given Customer ID is not present in column D (e.g., delete cell D5), you will get #N/A (in B3 in this case).

Then you copy formula from B2 to other cells in column B. It is easy to adapt the formulas for expanded customer lists.

Hope it helps.

4
  • +1 for a solution that not only tells you if it exists, but also tells you where... awesome :D
    – CLockeWork
    Dec 3, 2013 at 13:13
  • @CLockeWork: Thanks for reply. But I cannot provide this using the file here: we.tl/gn5FW8AIdd Could you please check it?
    – Jack
    Dec 3, 2013 at 18:05
  • @H.Johnson - It was sancho.s (me) who answered. I cannot check the link you provided. I suggest you post a text version of the worksheet you have and the results you want to obtain (see this example), and explain why the answer provided is not useful for you. Dec 3, 2013 at 18:27
  • @sancho.s: I updated the question by description. Muchas gracias :)
    – Jack
    Dec 3, 2013 at 19:16

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .