# 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

=IFERROR(INDEX(B:B,SMALL(IF(NOT(ISNA(MATCH(\$B\$1:\$B\$8,\$A\$1:\$A\$8,0))),ROW(\$1:\$8),""),ROW(A1))),"")
Confirm COntrol+Shift+Enter

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

Thanks;

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

You are welcome

• 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]

• 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!