How to calculate the difference between values with columns of different codes

Hi,

I need help to calculate the difference in the price of each procedure code in two tables. Each table is from different organizations (the one on the right is my organization, and the one on the left is the other one), so the procedure codes are not all in the same order, and there are some codes that might be in one that are not in the other. I would just need to calculate the difference in price in the column named Fee and for the procedures that are in both tables.

The example I added has just one mismatch, but the original has more mismatches between the order of procedure codes due to missing codes, although I tried to sort it the best possible.

Thanks for any help

Could you give us a couple of examples of your expected results, and if not immediately obvious how you arrived at those figures some more detail on how they were derived?

BSB

Thank for the help BadlySpelledBuoy

So I am just trying to get the difference between the fee values for each code; a simple subtraction formula would not help because the procedures, although sorted, are not in the exact order; the example just shows 1 procedure that repeats, and the total order changes. Further down in the original data, there are more changes, so the order of cells in both tables changes.

These are medical procedure codes, so the data on the left table are the fees that are on the webpage from CMS (Medicare), and the table on the right is my organization's fees for the same code. There is a lot of data, and I was trying to find a formula that could do this. I have tried Chat GPT but the formulas that it has given me don't seem to subtract the different fees for each of the codes in the list

I hope this answers your question.

Thanks

Taking procedure code 10004 as an example, in the left table it's 57.64 and on the right it's 133.00, so your expected result there would be 75.36?

If so try the below formula somewhere in row 2 and copy down.

Formula:

Please Login or Register  to view this content.

BSB

Another formula using named tables.

Formula:

Please Login or Register  to view this content.

This one worked perfectly! Just what I needed. Thank you very much for the help!

Hi DJunqueira,

Thanks for the formula. I'll try this one to have another back up for other projects I might have to use this!

Tks for the feedback, glad to help.