Formula to obtain different rows between ranges

[cgkmal]

Hi everyone,

Maybe some expert in formulas could help me with this complex formula for me.

I want to compare row by row the range A1:D7 with H1:K7 and print begin in A9 the rows of A1:D7 that are different
and corresponding values of column G for each different row.

In attached file I show in color the values that I would like to get.

Any help would be very appreaciated.

[hemesh]

Hello Ckgmal what criteria you want to match from other column as per your data sheet i think you are trying to match only first names that is available in B9. If this is the case you can use =INDEX($G$2:$G$6,MATCH($B9,$H$2:$H$6,0)) in A9.


If this helps click" * " from bottom left corner of my post

[mlcb]

Hi,

What version of Excel are you using?

[cgkmal]

Hello hemesh and Marcelo,

Thanks for the help. I'm using Excel 2007 and 2010.

Actually what I would like in the output is as follow:

Please Login or Register  to view this content.

So, the output, beginning in A9 would be:

32	Jordan	Rone	3	2000
91	Sanchez	Guzzler		2250
	Belcher		8	2000

I hope make sense and could be able to get with a array formula.

Thanks for the help.

[mlcb]

Try this

1st step
Put this array formula in B9

=IFERROR(INDEX(A:A,SMALL(IF(COUNTIFS($H:$H,$A$2:$A$7,$I:$I,$B$2:$B$7,$J:$J,$C$2:$C$7,$K:$K,$D$2:$D$7)=0,ROW(A$2:A$7)),ROWS(B$9:B9))),"")
confirmed with Ctrl+Shift+Enter, not just Enter

copy (drag) across to E9 and down

2nd step
Put this regular formula in A9

=IFERROR(INDEX($G:$G,MATCH($B9,H:H,0)),"")
confirmed with just Enter

copy down


Remark: to get rid of 0 (zeros) you can use Custom format [=0]""

Hope this helps

[cgkmal]

Hello Marcelo,

Thank you for the help, it works.

Do you think that the result of this formula can be stored in a variable within a VBA macro? or due to is needed to drag across and down
is not possible to store it in a variable?

PS: I didn't understanf where to put custom format to get rid of "0s", within the formula?

Thanks again.

[mlcb]

Hello Marcelo,

Thank you for the help, it works.

Do you think that the result of this formula can be stored in a variable within a VBA macro? or due to is needed to drag across and down
is not possible to store it in a variable?

PS: I didn't understanf where to put custom format to get rid of "0s", within the formula?

Thanks again.

A VBA macro is another problem but I *think* that, yes, it's possible. You can turn on the macro recorder, do all the process manually (enter the formulas; drag etc etc); turn off the macro recorder and see (adapt) the code.

About the Custom Format
Select the range B9:E11; right-click and pick Format Cells...
In the tab Numbers pick Custom and in the box enter
[=0]""
OK

[cgkmal]

Thank you Marcelo,

I've able to get rid of zeros with your suggestion.

Evaluating the formula step by step I see that the formula returns a value for each cell, but is possible in some way that the result
of the formula be an array?

So evaluating the formula step by step the result be shown something like this:

Please Login or Register  to view this content.

Thanks for the help again