Writing duplicates to another column

asked 2020-03-17 10:38:26 +0100

updated 2020-10-21 15:45:17 +0100

flag of United Kingdom of Great Britain and Northern Ireland

20859 ●19 ●60 ●59 http://etmg.altervista...

if I have a data of 10 columns and 12000 rows in which a few columns have few duplicates in certain rows. How can I write another column, in which it says which column has a duplicate entry with respect to that particular row? IN LIBRE CALC, please help me :)

edit: if you look into the "M" column you can see the type of output I need. if I have to find the duplicates that way for thousands of records .i I can't do it manually so I need a generalized formula to show me the output.

please do the neeedful :) C:\fakepath\copy_doc.xlsx

1st half of the file : C:\fakepath\1.xlsx 2nd half : C:\fakepath\2.xlsx

thankyou :)

edit retag flag offensive close merge delete

add a comment

answered 2020-03-17 13:35:06 +0100

gregors15
181 ●3 ●5

updated 2020-03-19 21:13:00 +0100

Hi, have a look at the attached, column B is =COUNTIF($A$2:$A$29,A2) which you can then filter to show where the count is not 1, column C =IF(B2>1,A2," ") just shows column A where the count is greater than 1. Let us know if it helps. C:\fakepath\LOQ_20200317.ods

HI, as a starting point, C:\fakepath\LOQ_20200318.xlsxhave a look at the attached, I have added a sheet to show the duplicates. Have a look and let us know what you think. I have added the results to column N on your sample sheet.

Hi, if you click in the cells in "Checksheet" you will see the formula used, another sheet attached, which I tidied a bit and added the conditional formatting, so it looks more like your sheet. Not quite sure I understand your last comment, can you explain a little more of what your trying to achieve. C:\fakepath\LOQ_20200318a.xlsx

Hi I have done the first file, across as far as column K(main mailing address), however there are very few duplicates in each category. Don’t know if when I will get chance to do the 2nd file. Had to split 1st in 2 to be able to upload it. C:\fakepath\LOQ_20200319a1.xlsx C:\fakepath\LOQ_20200319a2.xlsx

edit flag offensive delete link

Comments

Thank you so much. But I need it for multiple columns. I have tried applying your solution but I couldn't wrap my head around how to execute it for so many columns. Please look into my question as I have updated it with a document. I tried identifying duplicates using conditional formatting but the output I need should be almost the same way as I've manually typed in the M column in the document

can you do the needful? :) thankyou so much for your help

saranya chaganti ( 2020-03-18 10:05:29 +0100 )edit

That was helpful. Thank you and I really want to know how you implemented it. But is there any chance I could print the duplicates in the same column, and the same page as the one in my spreadsheet? or instead of the column's respective names like instead of printing "name", it could print "B"

Sorry to bother you I'm new to this :( and I have to get this task done

saranya chaganti ( 2020-03-18 16:49:54 +0100 )edit

I just wanted to know if there is any specific formula that I can apply and the answers display like the one in my spreadsheet in the "M" column. for a non-technical person, they should easily be able to apply the formula and get the output on the go on the same spreadsheet instead of another sheet. is it possible?

thankyou :)

saranya chaganti ( 2020-03-19 10:00:08 +0100 )edit

Hi, as you have 11 categories to check for duplicates, the use of the 2nd sheet is just as a “working sheet”, just makes it easier to work with and then the results can be posted on your original data, as you can see column N on the last file posted is like your column M. The COUNTIF (which I used to find the duplicates) function is fairly simple, once you have used a few times it becomes easier. If you use the same layout and 2nd sheet name as I have you should be able to copy the functions over your 12000 lines. If you want to see how the conditional formatting was applied, let me know and I will post it. Let us know how you get on.

gregors15 ( 2020-03-19 11:53:00 +0100 )edit

that was helpful, but I have a small issue here. I'll forward you another document I tried to apply the same formula but I'm not getting any answers. can you implement the same on this sheet? because I've been working on it since you gave me. I'm not getting the answers I desired

I'm uploading that document in the question section. thankyou so much for taking your time to help me out :)

saranya chaganti ( 2020-03-19 12:39:11 +0100 )edit

Sure, don't see your new file, now many records(rows) are in it?

gregors15 ( 2020-03-19 12:58:35 +0100 )edit

it says the file is too large. it has around 12k records or may I get your email id so that I can forward it through that?

saranya chaganti ( 2020-03-19 13:19:24 +0100 )edit

How large is the file? can you split it?

gregors15 ( 2020-03-19 13:23:06 +0100 )edit

Heyy, I just split the file into two halves please take a look at it

saranya chaganti ( 2020-03-19 13:51:01 +0100 )edit

Hi, which columns are to checked for duplicates, there are more columns in this file than the previous.

gregors15 ( 2020-03-19 13:57:37 +0100 )edit

see more comments

Writing duplicates to another column

1 Answer

Comments

Question Tools

Stats

Related questions

Writing duplicates to another column edit

1 Answer

Comments

Question Tools

Stats

Related questions

Writing duplicates to another column