Remove Duplicates leaves some duplicates

GregStoner66

New Member
Joined
Jul 9, 2020
Messages
2
Office Version
  1. 2016
Platform
  1. Windows
I have a table of apartments in a new building.
I copied the "Type" column and pasted to a new column to delete the duplicates. I want a count to get a count of the unique Apartment types.
Duplicates are still in the new column. I can't figure out what I am missing. I changed the data type to text because the Unit Types include numbers and text and the occasional plus sign.

SQUARE 560 UNIT COUNT.xls
ABC
322012E
422022F
522031O-2
622041K-2
722051Q
822062B-2
922071+D D
1022081J ANSI A
1122092L
1222101+D I-2
1322111A
1422121L
1522131J
1622141A ANSI A
1722151J ANSI A
1822161+ D B
1922171T
2022181+D E
2122191+D F
2222241B
232225SC
2422261L
252227SE
2622282A
2722291V
282230SG
2922312+D A ANSI A
3022321C
3122331E-2
3222341+D K ANSI A
3333011F
3433022D
3533032C
3633041O
3733051K
3833061D
3933072B
4033081+D C
4133091A ANSI A
4233102M
4333111+D I
4433121A
4533131A ANSI A
4633141A
4733151A ANSI A
4833161+D H ANSI A
4933171+D B
5033181+D G
5133191U
5233201G
533321SA
543322SB ANSI A
5533231+D A
5633241B
573325SC
5833261I
5933271W
6033282A
6133291V
623330SG
6333312+D A
6433321C
6533331E
6633342A ANSI A
6744011F
6844022D
6944032C
7044041O
7144051K
7244061D
7344072B
7444081+D C
7544091A ANSI A
7644102M
7744111+D I
7844121A
7944131A ANSI A
8044141A
8144151A ANSI A
8244161+D H ANSI A
8344171+D B
8444181+D G
8544191U
8644201G
874421SA
884422SB ANSI A
8944231+D A
9044241B
914425SC
9244261I
9344271W
9444282A
9544291V
964430SG
9744312+D A
9844321C
9944331E
10044342A ANSI A
10155011F
10255022D
10355032C
10455041O
10555051K
10655061D
10755072B
10855081+D C
10955091A ANSI A
11055102M
11155111+D I
11255121A
11355131A ANSI A
11455141A
11555151A ANSI A
11655161+D H ANSI A
11755171+D B
11855181+D G
11955191U
12055201G
1215521SA
1225522SB ANSI A
12355231+D A
12455241B
1255525SC
12655261I
12755271W
12855282A
12955291V
1305530SG
13155312+D A
13255321C
13355331E
13455342A ANSI A
13566011F
13666022D
13766032C
13866041O
13966051K
14066061D
14166072B
14266081+D C
14366091A ANSI A
14466102M
14566111+D I
14666121A
14766131A ANSI A
14866141A
14966151A ANSI A
15066161+D H ANSI A
15166171+D B
15266181+D G
15366191U
15466201G
1556621SA
1566622SB ANSI A
15766231+D A
15866241B
1596625SC
16066261I
16166271W
16266282A
16366291V
1646630SG
16566312+D A
16666321C
16766331E
16866342A ANSI A
16977011F
17077022D
17177032C
17277041O
17377051K
17477061D
17577072B
17677081+D C
17777091A ANSI A
17877102M
17977111+D I
18077121A
18177131A ANSI A
18277141A
18377151A ANSI A
18477161+D H ANSI A
18577171+D B
18677181+D G
18777191U
18877201G
1897721SA
1907722SB ANSI A
19177231+D A
19277241B
1937725SC
19477261I
19577271W
19677282A
19777291V
1987730SG
19977312+D A
20077321C
20177331E
20277342A ANSI A
20388011F
20488022D
20588032C
20688041O
20788051K
20888061D
20988072B
21088081+D C
21188091A ANSI A
21288102M
21388111+D I
21488121A
21588131A ANSI A
21688141A
21788151A ANSI A
21888161+D H ANSI A
21988171+D B
22088181+D G
22188191U
22288201G
2238821SA
2248822SB ANSI A
22588231+D A
22688241B
2278825SC
22888261I
22988271W
23088282A
23188291V
2328830SG
23388312+D A
23488321C
23588331E
23688342A ANSI A
23799011F
23899022D
23999032C
24099041O
24199051N
24299061D
24399072B
24499081+D C
24599091A ANSI A
24699102M
24799111+D I
24899121A
24999131A
25099141A
25199151A ANSI A
25299161+D H ANSI A
25399171+D B
25499181+D G
25599191U
25699201S
2579921SF
2589922SB
25999231+D A
26099241R
2619925SC
26299261I
26399271W
26499282A
26599291V
2669930SD
26799312+D A
26899321C
26999331E
27099342A ANSI A
271PHPH012K
272PHPH022G
273PHPH032H
274PHPH042I
275PHPH051+D J
276PHPH062J
277PHPH072N
Sheet4

SQUARE 560 UNIT COUNT.xls
FG
32E1
42F1
51O-21
61K-21
71Q1
82B-21
91+D D1
101J ANSI A2
112L1
121+D I-21
131A9
141L2
151J1
161A ANSI A15
171+ D B1
181T1
191+D E1
201+D F1
211B7
22SC8
23SE1
242A8
251V8
26SG7
272+D A ANSI A1
281C8
291E-21
301+D K ANSI A1
311F7
322D7
332C7
341O7
351K6
361D7
372B7
381+D C7
392M7
401+D I7
411A 7
421A ANSI A 6
431+D H ANSI A 6
441+D B7
451+D G7
461U7
471G6
48SA6
49SB ANSI A 6
501+D A 6
511I7
521W7
532+D A7
541E7
552A ANSI A 6
561N1
571+D H ANSI A1
581S1
59SF1
60SB1
611+D A1
621R1
63SD1
642A ANSI A1
652K1
662G1
672H1
682I1
691+D J1
702J1
712N1
Sheet4
Cell Formulas
RangeFormula
G3:G71G3=COUNTIF($C$3:$C$277,F3)
 

Excel Facts

Can Excel fill bagel flavors?
You can teach Excel a new custom list. Type the list in cells, File, Options, Advanced, Edit Custom Lists, Import, OK

GregStoner66

New Member
Joined
Jul 9, 2020
Messages
2
Office Version
  1. 2016
Platform
  1. Windows
I apologize, this is my first post and I neglected to preview the post to see that column headers aren't showing.
 

Sufiyan97

Active Member
Joined
Apr 12, 2019
Messages
335
Office Version
  1. 2013
Platform
  1. Windows
Your text has space at the end see below
that's why similar text has different length
you can use TRIM function to remove extra spaces and then you can go ahead with removing duplicates

Book2
ABCDEF
1Original Text LengthTrimmed TextTrimmed Text Length
222330.01
322161+ D B61+ D B6
499231+D A51+D A5
533231+D A 61+D A5
644231+D A 61+D A5
755231+D A 61+D A5
866231+D A 61+D A5
977231+D A 61+D A5
1088231+D A 61+D A5
1133171+D B51+D B5
1244171+D B51+D B5
1355171+D B51+D B5
1466171+D B51+D B5
1577171+D B51+D B5
1688171+D B51+D B5
1799171+D B51+D B5
1833081+D C51+D C5
1944081+D C51+D C5
2055081+D C51+D C5
2166081+D C51+D C5
2277081+D C51+D C5
2388081+D C51+D C5
2499081+D C51+D C5
2522071+D D51+D D5
2622181+D E51+D E5
2722191+D F51+D F5
2833181+D G51+D G5
2944181+D G51+D G5
3055181+D G51+D G5
3166181+D G51+D G5
3277181+D G51+D G5
3388181+D G51+D G5
3499181+D G51+D G5
3599161+D H ANSI A121+D H ANSI A12
3633161+D H ANSI A 131+D H ANSI A12
3744161+D H ANSI A 131+D H ANSI A12
3855161+D H ANSI A 131+D H ANSI A12
3966161+D H ANSI A 131+D H ANSI A12
4077161+D H ANSI A 131+D H ANSI A12
4188161+D H ANSI A 131+D H ANSI A12
4233111+D I51+D I5
4344111+D I51+D I5
4455111+D I51+D I5
4566111+D I51+D I5
4677111+D I51+D I5
4788111+D I51+D I5
4899111+D I51+D I5
4922101+D I-271+D I-27
50PHPH051+D J51+D J5
5122341+D K ANSI A121+D K ANSI A12
5222111A21A2
5333141A21A2
5444141A21A2
5555141A21A2
5666141A21A2
5777141A21A2
5888141A21A2
5999131A21A2
6099141A21A2
6133121A 41A2
6244121A 41A2
6355121A 41A2
6466121A 41A2
6577121A 41A2
6688121A 41A2
6799121A 41A2
6822141A ANSI A91A ANSI A9
6933091A ANSI A91A ANSI A9
7033131A ANSI A91A ANSI A9
7144091A ANSI A91A ANSI A9
7244131A ANSI A91A ANSI A9
7355091A ANSI A91A ANSI A9
7455131A ANSI A91A ANSI A9
7566091A ANSI A91A ANSI A9
7666131A ANSI A91A ANSI A9
7777091A ANSI A91A ANSI A9
7877131A ANSI A91A ANSI A9
7988091A ANSI A91A ANSI A9
8088131A ANSI A91A ANSI A9
8199091A ANSI A91A ANSI A9
8299151A ANSI A91A ANSI A9
8333151A ANSI A 101A ANSI A9
8444151A ANSI A 101A ANSI A9
8555151A ANSI A 101A ANSI A9
8666151A ANSI A 101A ANSI A9
8777151A ANSI A 101A ANSI A9
8888151A ANSI A 101A ANSI A9
8922241B21B2
9033241B21B2
9144241B21B2
9255241B21B2
9366241B21B2
9477241B21B2
9588241B21B2
9622321C21C2
9733321C21C2
9844321C21C2
9955321C21C2
10066321C21C2
10177321C21C2
10288321C21C2
10399321C21C2
10433061D21D2
10544061D21D2
10655061D21D2
10766061D21D2
10877061D21D2
10988061D21D2
11099061D21D2
11133331E21E2
11244331E21E2
11355331E21E2
11466331E21E2
11577331E21E2
11688331E21E2
11799331E21E2
11833011F21F2
11944011F21F2
12055011F21F2
12166011F21F2
12277011F21F2
12388011F21F2
12499011F21F2
12533201G21G2
12644201G21G2
12755201G21G2
12866201G21G2
12977201G21G2
13088201G21G2
13133261I21I2
13244261I21I2
13355261I21I2
13466261I21I2
13577261I21I2
13688261I21I2
13799261I21I2
13822131J21J2
13922081J ANSI A91J ANSI A9
14022151J ANSI A91J ANSI A9
14133051K21K2
14244051K21K2
14355051K21K2
14466051K21K2
14577051K21K2
14688051K21K2
14722041K-241K-24
14822121L21L2
14922261L21L2
15099051N21N2
15133041O21O2
15244041O21O2
15355041O21O2
15466041O21O2
15577041O21O2
15688041O21O2
15799041O21O2
15822031O-241O-24
15922051Q21Q2
16099241R21R2
16199201S21S2
16222171T21T2
16333191U21U2
16444191U21U2
16555191U21U2
16666191U21U2
16777191U21U2
16888191U21U2
16999191U21U2
17022291V21V2
17133291V21V2
17244291V21V2
17355291V21V2
17466291V21V2
17577291V21V2
17688291V21V2
17799291V21V2
17833271W21W2
17944271W21W2
18055271W21W2
18166271W21W2
18277271W21W2
18388271W21W2
18499271W21W2
18533312+D A52+D A5
18644312+D A52+D A5
18755312+D A52+D A5
18866312+D A52+D A5
18977312+D A52+D A5
19088312+D A52+D A5
19199312+D A52+D A5
19222312+D A ANSI A122+D A ANSI A12
19322282A22A2
19433282A22A2
19544282A22A2
19655282A22A2
19766282A22A2
19877282A22A2
19988282A22A2
20099282A22A2
20199342A ANSI A92A ANSI A9
20233342A ANSI A 102A ANSI A9
20344342A ANSI A 102A ANSI A9
20455342A ANSI A 102A ANSI A9
20566342A ANSI A 102A ANSI A9
20677342A ANSI A 102A ANSI A9
20788342A ANSI A 102A ANSI A9
20833072B22B2
20944072B22B2
21055072B22B2
21166072B22B2
21277072B22B2
21388072B22B2
21499072B22B2
21522062B-242B-24
21633032C22C2
21744032C22C2
21855032C22C2
21966032C22C2
22077032C22C2
22188032C22C2
22299032C22C2
22333022D22D2
22444022D22D2
22555022D22D2
22666022D22D2
22777022D22D2
22888022D22D2
22999022D22D2
23022012E22E2
23122022F22F2
232PHPH022G22G2
233PHPH032H22H2
234PHPH042I22I2
235PHPH062J22J2
236PHPH012K22K2
23722092L22L2
23833102M22M2
23944102M22M2
24055102M22M2
24166102M22M2
24277102M22M2
24388102M22M2
24499102M22M2
245PHPH072N22N2
2463321SA2SA2
2474421SA2SA2
2485521SA2SA2
2496621SA2SA2
2507721SA2SA2
2518821SA2SA2
2529922SB2SB2
2533322SB ANSI A 10SB ANSI A9
2544422SB ANSI A 10SB ANSI A9
2555522SB ANSI A 10SB ANSI A9
2566622SB ANSI A 10SB ANSI A9
2577722SB ANSI A 10SB ANSI A9
2588822SB ANSI A 10SB ANSI A9
2592225SC2SC2
2603325SC2SC2
2614425SC2SC2
2625525SC2SC2
2636625SC2SC2
2647725SC2SC2
2658825SC2SC2
2669925SC2SC2
2679930SD2SD2
2682227SE2SE2
2699921SF2SF2
2702230SG2SG2
2713330SG2SG2
2724430SG2SG2
2735530SG2SG2
2746630SG2SG2
2757730SG2SG2
2768830SG2SG2
Sheet1
Cell Formulas
RangeFormula
D3:D276,F3:F276D3=LEN(C3)
E3:E276E3=TRIM(C3)
 
Solution

Forum statistics

Threads
1,136,952
Messages
5,678,745
Members
419,782
Latest member
gc75150

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top