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
TrowaD Posts 2921 Registration date Sunday September 12, 2010 Status Moderator Last seen December 27, 2022 - May 30, 2017 at 11:00 AM
Related:
- Excel formula to handle duplicate records
- Date formula in excel dd/mm/yyyy - Guide
- Number to words in excel formula - Guide
- Excel grade formula - Guide
- How to duplicate a google doc - Guide
- Logitech formula vibration feedback wheel driver - Download - Drivers
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
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.
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.
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
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
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
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
May 29, 2017 at 03:27 AM
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.
May 29, 2017 at 03:09 PM
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.
May 30, 2017 at 11:00 AM
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?