Match data from two columns and finding the difference to their corresponding values.

[pedexe90]

Hi everyone,

This might sound too easy to most of you, but I am struggling here.

So I have a list of names from A1:A1500 and C1:C1500 with their corresponding values from B1:B1500 and D1:D1500 respectively.

I have to populate a column that would find the difference in corresponding values only between matched names from the two lists.

Many thanks for your help in advance.

 

[iliace]

So is it as simple as:

E1 =IF(A1=C1, D1-B1, 0)

Or do you want to create a new list of names that only exist in both columns A and C, and then calculate the difference in values?

If the latter, where do you want to create the new list?

Are you looking for a formula, VBA, PivotTable, or whatever it takes? Or, perhaps more interestingly, where and how do you want to use the output of this operation/calculation?

 

[pedexe90]

Thanks a lot for your quick reply.

Its more of a latter situation actually but and the way I've done it for the time being is by doing a Vlookup and Match to pick of the values of the second column with their matching names and then on a separate column I am subtracting them.

 

[iliace]

OK, so let's think about this.

I was able to come up with an array formula that will bring you back a list of matching items, but with 1500 rows, this will be a heck of a calculation (several seconds each recalc) to have.

You can view/download my sample workbook here: https://1drv.ms/x/s!AtJ-3Lle_YMHgcR_R2ugY-iV0NXYVg

I created two independent lists in columns A and C, 1500 rows each, with a random 3-letter code (simulating your names). This has ~15,600 possible permutations. Of these, 135 match in both columns (roughly what you would expect, give or take). I also created in columns B and D some random values to calculate.

Now, in range H2:H136 (because I have 135 matches + header), I enter this array formula and press Ctrl+Shift+Enter:

=IFERROR(INDEX($A$1:$A$1500, LARGE(ROW($1:$1500)*ISNUMBER(MATCH($A$1:$A$1500, $C$1:$C$1500, 0)), ROW($1:$135))), "")

This gives me a list of matching values. In I2:I136 (add a row to account for header), I enter this:

=VLOOKUP($H2, $A$1:$B$1500, 2, FALSE)

...and copy down. Similarly, in J2, I enter this:

=VLOOKUP($H2, $C$1:$D$1500, 2, FALSE)

...and copy down.

Finally, in K2:K136, I calculate the variance:

=I2-J2

...and copy down.

Each recalc of the sheet (with no other sheets, or other workbooks opened) takes about 4 seconds on my machine (Win10, Office 365 64-bit, AMD A12-9720P Radeon R7, quad-core 2.7 GHz, 8GB RAM).

This will automate majority of the process for you, except you will need to update the size of the array any time the source data changes for the number of matches. In my situation, I had 135, and avoided using any dynamic calculations to speed up calculations (e.g. no ROW(INDIRECT()), etc). You can certainly incorporate those dynamics, but it will slow down the process. To determine the number of matches, I used this calculation (in cell F1):

=SUMPRODUCT(--ISNUMBER(MATCH($A$1:$A$1500, $C$1:$C$1500, 0)))

To use this, consider the following:
* You have to use a multi-cell array formula to get the list of matching names
* If your amount of matches is significantly higher than my 135 out of 1500 (~9%), the calculation times may get crazier
* You can sort the lists (A1:B1500 and C1:D1500) and then use exact MATCH() and VLOOKUP(). This will speed up the calculations dramatically, but requires that you be able to sort the lists in ascending order - and not forget this step each time you update.
* You can turn off automatic calculations while updating, which will let you make any necessary edits, then recalculate once, each time you need to update.

All that said - is this the output you are looking for? If yes, how do you feel about this solution? If no, what did I miss?

Please post back with feedback and/or questions.

 

[pedexe90]

Dear Iliace,

Let me start by saying how thankful and grateful I am that you have spent your precious time to come up with a solution for me and I am sure this solution would have worked. But, unfortunately I briefed the board yesterday and that project is over now.

However, I have a new project that I'm struggling with iswhich is very similar to this.

Can I tell you about it here or do I need to start a new thread?

Many many thanks again.

 

[iliace]

If your new project is not related to this, please start a new thread.

 

[pedexe90]

https://www.mrexcel.com/forum/excel...-data-skips-row-doesnt-match.html#post5066323

Dear Iliace you can find my other post here.

Many thanks.