Home

Duplicate value/text prevention

%3CLINGO-SUB%20id%3D%22lingo-sub-871701%22%20slang%3D%22en-US%22%3EDuplicate%20value%2Ftext%20prevention%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-871701%22%20slang%3D%22en-US%22%3E%3CP%3EHello%3C%2FP%3E%3CP%3EI%20inherited%20an%20Excel%20sheet%20from%20my%20previous%20worker.%20The%20sheet%20has%20data%20organised%20in%20a%20lot%20of%20columns.%20For%20the%20heading%20of%20these%20columns%2C%20repeated%20values%2Ftexts%20are%20somehow%20prevented%2C%20but%20I%20don't%20know%20how%20(it's%20not%20via%20data%20validation)%20and%20would%20like%20to%20get%20rid%20of%20that.%20For%20example%2C%20I%20have%20a%20column%20with%20the%20heading%20%22Data%22.%20If%20I%20try%20to%20add%20more%20columns%20with%20the%20same%20heading%20%22Data%22%2C%20it%20get's%20automatically%20changed%20to%20%22Data2%22%2C%20Data3%22%20etc.%3C%2FP%3E%3CP%3EThanks%20for%20your%20help!%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-871701%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-871723%22%20slang%3D%22en-US%22%3ERe%3A%20Duplicate%20value%2Ftext%20prevention%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-871723%22%20slang%3D%22en-US%22%3E%3CP%3ESounds%20like%20it's%20a%20Table%26nbsp%3B%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F414135%22%20target%3D%22_blank%22%3E%40esr89%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EYou%20can%20click%20inside%20the%20Table%20then%20choose%20Design%20and%20Convert%20To%20Range%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EGenerally%20tables%20are%20really%20good%20things%20to%20have%2C%20so%20I'd%20be%20interested%20in%20why%20you%20need%20to%20change%20it.%3C%2FP%3E%0A%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F414135%22%20target%3D%22_blank%22%3E%40esr89%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-871726%22%20slang%3D%22en-US%22%3ERe%3A%20Duplicate%20value%2Ftext%20prevention%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-871726%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F414135%22%20target%3D%22_blank%22%3E%40esr89%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EI%20guess%20you%20have%20Excel%20Table%20which%20doesn't%20allow%20columns'%20names%20duplication.%20The%20workaround%20depends%20on%20how%20do%20you%20use%20your%20data%2C%20possible%20variants%3C%2FP%3E%0A%3CP%3E-%20convert%20table%20to%20the%20range%3C%2FP%3E%0A%3CP%3E-%20instead%20of%20Data1%2C%20Data2%2C%20etc%20us%20%22Data%20%22%2C%20%22Data%26nbsp%3B%20%22%2C%20etc%3C%2FP%3E%0A%3CP%3E-%20split%20one%20table%20one%20two%20and%20second%20one%20put%20directly%20next%20to%20first%20one%3C%2FP%3E%0A%3CP%3E-%20you%20may%20highlight%20numbers%20in%26nbsp%3BData1%2C%20Data2%2C%20so%20the%20visible%20will%20be%20only%20Data%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EBut%20from%20my%20point%20view%20it's%20better%20to%20use%20different%20columns'%20names.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-871747%22%20slang%3D%22en-US%22%3ERe%3A%20Duplicate%20value%2Ftext%20prevention%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-871747%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F414135%22%20target%3D%22_blank%22%3E%40esr89%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EScreenshot%20attached%3C%2FP%3E%0A%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20style%3D%22width%3A%20999px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F133271i6FAC25F15E2CD23E%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20alt%3D%22image.png%22%20title%3D%22image.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-873860%22%20slang%3D%22en-US%22%3ERe%3A%20Duplicate%20value%2Ftext%20prevention%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-873860%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F9832%22%20target%3D%22_blank%22%3E%40Wyn%20Hopkins%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F521%22%20target%3D%22_blank%22%3E%40Sergei%20Baklan%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThanks%20to%20you%20both.%20It%20is%2Fwas%20indeed%20a%20table%2C%20and%20converting%20to%20range%20solves%20the%20problem.%3C%2FP%3E%3CP%3EThe%20issue%20with%20a%20table%20in%20this%20case%20is%20that%20I%20have%20a%20lot%20of%20reporting%20figures%2Fcolumns%20which%20repeat%20every%20year.%20It%20would%20be%20very%20tedious%20to%20add%20the%20year%20in%20every%20column%2C%20instead%20the%20year%20is%20written%20above%20all%20the%20headings%20for%20a%20certain%20year%20(and%20so%20the%20columns%20all%20have%20the%20same%20heading).%3C%2FP%3E%3CP%3EI%20will%20check%20now%20if%20everything%20still%20works%20the%20intended%20way%20despite%20the%20data%20not%20being%20in%20a%20table.%3C%2FP%3E%3C%2FLINGO-BODY%3E
esr89
New Contributor

Hello

I inherited an Excel sheet from my previous worker. The sheet has data organised in a lot of columns. For the heading of these columns, repeated values/texts are somehow prevented, but I don't know how (it's not via data validation) and would like to get rid of that. For example, I have a column with the heading "Data". If I try to add more columns with the same heading "Data", it get's automatically changed to "Data2", Data3" etc.

Thanks for your help!

4 Replies

Sounds like it's a Table @esr89 

 

You can click inside the Table then choose Design and Convert To Range

 

Generally tables are really good things to have, so I'd be interested in why you need to change it.

@esr89 

@esr89 

I guess you have Excel Table which doesn't allow columns' names duplication. The workaround depends on how do you use your data, possible variants

- convert table to the range

- instead of Data1, Data2, etc us "Data ", "Data  ", etc

- split one table one two and second one put directly next to first one

- you may highlight numbers in Data1, Data2, so the visible will be only Data

 

But from my point view it's better to use different columns' names.

@esr89 

 

Screenshot attached

image.png

@Wyn Hopkins 

@Sergei Baklan 

 

Thanks to you both. It is/was indeed a table, and converting to range solves the problem.

The issue with a table in this case is that I have a lot of reporting figures/columns which repeat every year. It would be very tedious to add the year in every column, instead the year is written above all the headings for a certain year (and so the columns all have the same heading).

I will check now if everything still works the intended way despite the data not being in a table.

Related Conversations
Tabs and Dark Mode
cjc2112 in Discussions on
22 Replies
flashing a white screen while open new tab
cntvertex in Discussions on
13 Replies
Stable version of Edge insider browser
HotCakeX in Discussions on
35 Replies
How to Prevent Teams from Auto-Launch
chenrylee in Microsoft Teams on
28 Replies
IIS extension is not working - WAC 1909
HotCakeX in Windows Admin Center on
11 Replies