I have 4 columns with about 13,000 rows.
Col A Col B Col AK Col AN
10020 122.4 10020
10020 26 112 10021
10021 24.5 10022
10021 26 12.94 10023
10022 45.6 10024
J0270
K1254
Columns A, B and AN are codes and column AK is fees.
The codes in columns A and AN have the same number of digits and are both sorted in ascending order.
Column AN has a longer list of codes in it than column A.
I want to make a new column of codes that lists any code that exists in AN as well as in A. If there is a code in column B then I want to ignore the code in column A.
So from the example above I would want these codes in my new column:
10020
10021
10022
Then, next to my new column, I want another column that shows the corresponding fee. So the resulting two new columns would be:
AQ AR
10020 122.40
10021 24.50
10022 45.60
The formula I have in AQ is:
=IF(B2150="",(IF(MATCH(AN2150,$A$2150:$A$11686,0),AN2150,"")),"")
This works most of the time but in spot checking I’ve found a couple of codes not in my new column AQ even though they are in both A and AN.
Is there a flaw in this formula or is there another formula I should be using instead?
Thank you.
Col A Col B Col AK Col AN
10020 122.4 10020
10020 26 112 10021
10021 24.5 10022
10021 26 12.94 10023
10022 45.6 10024
J0270
K1254
Columns A, B and AN are codes and column AK is fees.
The codes in columns A and AN have the same number of digits and are both sorted in ascending order.
Column AN has a longer list of codes in it than column A.
I want to make a new column of codes that lists any code that exists in AN as well as in A. If there is a code in column B then I want to ignore the code in column A.
So from the example above I would want these codes in my new column:
10020
10021
10022
Then, next to my new column, I want another column that shows the corresponding fee. So the resulting two new columns would be:
AQ AR
10020 122.40
10021 24.50
10022 45.60
The formula I have in AQ is:
=IF(B2150="",(IF(MATCH(AN2150,$A$2150:$A$11686,0),AN2150,"")),"")
This works most of the time but in spot checking I’ve found a couple of codes not in my new column AQ even though they are in both A and AN.
Is there a flaw in this formula or is there another formula I should be using instead?
Thank you.