# Excel formula to handle duplicate records

Closed
Gerberreinette Posts 14 Registration date Friday March 4, 2016 Status Member Last seen July 31, 2023 - Updated on May 15, 2017 at 11:29 AM
TrowaD Posts 2921 Registration date Sunday September 12, 2010 Status Moderator Last seen December 27, 2022 - May 30, 2017 at 11:00 AM
Hello,

Thank you so much for taking the time to assist in resolving this for me.

Column A, Column B
30001234, 587
30001234, 586
30001235, 555
30001236, 632
30001237, 111
30001237, 122

Please assist with a formula to give the result in Column B
Column A, Column B
30001234, 587
586
30001235, 555
30001236, 632
30001237, 111
122

Related:

## 2 responses

TrowaD Posts 2921 Registration date Sunday September 12, 2010 Status Moderator Last seen December 27, 2022 552
Updated on May 11, 2017 at 11:06 AM
Hi Gerberreinette,

What I understand from your sample data is that you want to remove the duplicates from column A and replace it with the value from column B.

Assuming you used cells A1 to B6 for this, I used the formula's:
C1: =A1
D1: =B1
C2: =IF(A2=A1,B2,A2) drag formula down
D2: =IF(A2=A1,"",B2) drag formula down

This will put the desired data in column C and D, which you can then copy/pastespecial as values over column A and B. Remove column C and D afterwards.

Is this what you are after?

Best regards,
Trowa

Monday, Tuesday and Thursday are usually the days I'll respond. Bear this in mind when awaiting a reply.
Gerberreinette
May 29, 2017 at 03:27 AM
Good day

So sorry for getting back only now but I was on Study Leave.
Thank you for the formula I really appreciate the help.

When I enter the formula it gives me a #NAME? Like shown in yellow.

The results I am looking for are shown at the far right of the document.

Assignment DocumentNo Want this Result
3000034472 2000128977 3000034472 2000128977 3000034472 2000128977
3000034472 2000128977 #NAME? #NAME? 0
3000034592 2000130453 #NAME? #NAME? 3000034592 2000130453
3000034666 2000130443 #NAME? #NAME? 3000034666 2000130443
3000034666 2000102973 #NAME? #NAME? 2000102973
3000034703 2000103890 #NAME? #NAME? 3000034703 2000103890
3000034714 2000129989 #NAME? #NAME? 3000034714 2000129989
3000040927 2000125934 #NAME? #NAME? 3000040927 2000125934
3000040927 2000126494 #NAME? #NAME? 2000126494
3100000093 2000103557 #NAME? #NAME? 3100000093 2000103557
3100000093 2000128479 #NAME? #NAME? 2000128479
3100000094 2000104381 #NAME? #NAME? 3100000094 2000104381
3100000094 2000115460 #NAME? #NAME? 2000115460
3100000095 2000103517 #NAME? #NAME? 3100000095 2000103517
3100000095 2000115458 #NAME? #NAME? 2000115458
3100000097 2000123921 #NAME? #NAME? 3100000097 2000123921
3100000097 2000124534 #NAME? #NAME? 2000124534
3100000097 2000127074 #NAME? #NAME? 2000127074

Thank you for taking the time to assist.
Gerberreinette > Gerberreinette
May 29, 2017 at 03:09 PM
Good day

Thank you for the file but when I enter the formula's it puts the result that is suppose to go into column D into column C.

Column A Column B Column C Column D Column F Column G
3000034472, 2000128977, 3000034472, 2000128977, 3000034472, 2000128977
3000034472, 2000128977, 2000128977, 0, 0,
3000034592, 2000130453, 3000034592, 2000130453, 3000034592, 2000130453
3000034666, 2000130443, 3000034666, 2000130443, 3000034666, 2000130443
3000034666, 2000102973, 2000102973, 0, 2000102973
3000034703, 2000103890, 3000034703, 2000103890, 3000034703, 2000103890
3000034714, 2000129989, 3000034714, 2000129989, 3000034714, 2000129989
3000040927, 2000125934, 3000040927, 2000125934, 3000040927, 2000125934
3000040927, 2000126494, 2000126494, 0, 2000126494
3100000093, 2000103557, 3100000093, 2000103557, 3100000093, 2000103557
3100000093, 2000128479, 2000128479, 0, 2000128479
3100000094, 2000104381, 3100000094, 2000104381, 3100000094, 2000104381
3100000094, 2000115460, 2000115460, 0, 2000115460
3100000095, 2000103517, 3100000095, 2000103517, 3100000095, 2000103517
3100000095, 2000115458, 2000115458, 0, 2000115458
3100000097, 2000123921, 3100000097, 2000123921, 3100000097, 2000123921
3100000097, 2000124534, 2000124534, 0, 2000124534
3100000097, 2000127074, 2000127074, 0, 2000127074

If you look at column C where all the formulas are you will notice that there is "2" numbers coming in and that must be in a separate column.
It should look like Column F and G.

Thank you so much.
TrowaD Posts 2921 Registration date Sunday September 12, 2010 Status Moderator Last seen December 27, 2022 552
May 30, 2017 at 11:00 AM
The only difference I see between column C/D and F/G is a 0 instead of an empty cell.

I don't know what you mean by: there are 2 numbers coming in at column C. Did you put both formula's in column C, instead of column C and D?
Gerberreinette
May 29, 2017 at 03:32 AM
Good day

So sorry for getting back only now but I was on Study Leave.
Thank you for the formula I really appreciate the help.

When I enter the formula it gives me a #NAME? Like shown in yellow.

The results I am looking for are shown at the far right of the document.
This looks a bit better

Assignment DocumentNo Want this Result
3000034472 2000128977 3000034472 2000128977
3000034472 2000128977 0
3000034592 2000130453 3000034592 2000130453
3000034666 2000130443 3000034666 2000130443
3000034666 2000102973 2000102973
3000034703 2000103890 3000034703 2000103890
3000034714 2000129989 3000034714 2000129989
3000040927 2000125934 3000040927 2000125934
3000040927 2000126494 2000126494
3100000093 2000103557 3100000093 2000103557
3100000093 2000128479 2000128479
3100000094 2000104381 3100000094 2000104381
3100000094 2000115460 2000115460
3100000095 2000103517 3100000095 2000103517
3100000095 2000115458 2000115458
3100000097 2000123921 3100000097 2000123921
3100000097 2000124534 2000124534
3100000097 2000127074 2000127074
TrowaD Posts 2921 Registration date Sunday September 12, 2010 Status Moderator Last seen December 27, 2022 552
May 29, 2017 at 11:22 AM
Hi Gerberreinette,

That is exactly the result I get when using the provided formula's. Except for the 0 in C2 that should show 2000128977.

Let me upload the file so you can see for yourself.
Columns A:B How it is.
Columns C:D How you want it.
Columns E:F The formula's.