Report

Excel formula to handle duplicate records

Ask a question Gerberreinette 3Posts Friday March 4, 2016Registration date May 11, 2017 Last seen - Last answered on May 30, 2017 at 11:00 AM by TrowaD
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

See more 
Helpful
+0
plus moins
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

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.
Reply
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.
Reply
TrowaD 2245Posts Sunday September 12, 2010Registration date ModeratorStatus June 27, 2017 Last seen - 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?
Reply
Leave a comment
Helpful
+0
plus moins
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 2245Posts Sunday September 12, 2010Registration date ModeratorStatus June 27, 2017 Last seen - 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.

Download file here:
http://ge.tt/1cEjjHk2

What did you do differently?

Best regards,
Trowa
Reply
Leave a comment

Member requests are more likely to be responded to.

Members can monitor the statuses of their requests from their account pages.

A CCM membership gives you access to additional options.

Not a member yet?

Sign up now. It takes less than a minute and is completely free!