Compare 2 List and extract data in 3rd

  • Hi,


    I have one problem, want to compare two column and extract the data in 3rd column but the problem with my formula is that blank space are created how to remove it from in between.


    Data:


    Col - A
    xxx01
    xxx02
    xxx02
    xxx05
    xxx04
    xxx02


    Col - B
    xxx01
    xxx02
    xxx03
    xxx05


    Output
    xxx01
    xxx02


    xxx05


    Result Desired
    xxx01
    xxx02
    xxx05




    My formula is :
    =IF(ISERROR(MATCH(L6,$H$5:$H$24,0)),"",L6)

  • Re: Compare 2 List and extract data in 3rd


    Take a look to this.


    http://www.get-digital-help.co…mon-row-records-in-excel/

    Regards


    Fotis :hammerhe:


    [SIZE=4]. [/SIZE]Array(CSE) { }, formulae are confirmed with CONTROL+SHIFT+ENTER.


    [SIZE=4]. [/SIZE]-Replace commas ( , ) with semicolons ( ; )-or vice versa in formulae, if your locale setting demands.


    [SIZE=4].[/SIZE]--Don't attach a screenshot--Just attach your Excel file!


    [SIZE=4].[/SIZE]--[SIZE=3]KISS[/SIZE]([SIZE=2]Keep it simple Stupid[/SIZE])

  • Re: Compare 2 List and extract data in 3rd


    ...........................................:smile:

    Regards


    Fotis :hammerhe:


    [SIZE=4]. [/SIZE]Array(CSE) { }, formulae are confirmed with CONTROL+SHIFT+ENTER.


    [SIZE=4]. [/SIZE]-Replace commas ( , ) with semicolons ( ; )-or vice versa in formulae, if your locale setting demands.


    [SIZE=4].[/SIZE]--Don't attach a screenshot--Just attach your Excel file!


    [SIZE=4].[/SIZE]--[SIZE=3]KISS[/SIZE]([SIZE=2]Keep it simple Stupid[/SIZE])

  • Re: Compare 2 List and extract data in 3rd


    I think was not so brief about my problem, further would like to add on to clear my problem


    Range of criteria with two match, returning multiple rows


    Having 3 Sheets, as shown below. Now, the first sheet is the main sheet and 2nd & 3rd sheet are the first part of condition.



    First I have to check 2nd Sheet with 3rd Sheet, for those activity code matches which I did with this formula



    Formula in Cell Part1!A4:



    =IF(ISERROR(MATCH(Sheet2!A4,Sheet3!A:A,0)),"",Sheet2!A4)



    Formula in Cell Part1!B4:
    =IF((A4=Sheet2!A4),Sheet2!C4,"")



    So, that I pick up the matching data in Part1 as shown below, then there is Part2 where the result needed will be derived by matching Part1 column A & B, as condition and all the matching from Sheet1 will be copied to Part2 Sheet as shown (example):



    Sheet1 #



    [Blocked Image: http://s23.postimg.org/ma1ct2s93/Sheet1.jpg]



    Sheet#2
    [Blocked Image: http://s22.postimg.org/uei5cgy8t/Sheet2.jpg]



    Sheet#3
    [Blocked Image: http://s9.postimg.org/nmkr7w3mz/Sheet3.jpg]



    Part#1
    [Blocked Image: http://s24.postimg.org/h0amqfy9d/Part1.jpg]



    Part#2
    [Blocked Image: http://s24.postimg.org/cfa7mwtgx/Part2.jpg]

  • Re: Compare 2 List and extract data in 3rd


    Nice pictures, but a small sample workbook will be more usefull!

    Regards


    Fotis :hammerhe:


    [SIZE=4]. [/SIZE]Array(CSE) { }, formulae are confirmed with CONTROL+SHIFT+ENTER.


    [SIZE=4]. [/SIZE]-Replace commas ( , ) with semicolons ( ; )-or vice versa in formulae, if your locale setting demands.


    [SIZE=4].[/SIZE]--Don't attach a screenshot--Just attach your Excel file!


    [SIZE=4].[/SIZE]--[SIZE=3]KISS[/SIZE]([SIZE=2]Keep it simple Stupid[/SIZE])

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!