• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

How can remove duplicates in the row.

Belal

New Member
Normally we remove duplicates through 'remove duplicate' or Unique() function in excel or through Power Query. But all these methods are applicable to identify duplicate as whole row or in a particular column.

Is there a way we can remove duplicates Horizontally i mean row wise.
 
Thank you appreciate your quick response,
the only issue with Unique() is its changing the column alignment of the data. Attached is the example of 'input file' what i get and it has some duplicates in the row, and 'Output file' is what i want this to be but it shouldn't change the column alignment of data, just all the other duplicate value should become blank.
 

Attachments

  • Removal of duplicate duplicate in rows.xlsx
    12.8 KB · Views: 11
A bit clunky, (not the spilling single-cell formula I was aiming for) but in any cell in column I:
Code:
=IF(MOD(COLUMN(),4)=1,IF(ISERROR(MATCH(I2,$E2:E2,0)),I2,""),I2) & ""
copy down and across as far as necessary.
Columns A:H can just be a straight reference to the cells above.
It only checks the phone numbers, not the IDs (the MOD part of the formula).
In the attached I've hidden rows 20:48 (your expected results).
 

Attachments

  • Chandoo49201Removal of duplicate duplicate in rows.xlsx
    16.1 KB · Views: 10
Thank you so much for the formula, It works but still leave some duplicates. Since my actual list is really big, so do i have to adjust '4' in the formula and the range that you mentioned as $E2:E2?
 
Sure, Attached the file for your reference. I have highlighted green part where formula is applied from E23 onwards.
From Row 2 to Row 18 - Original File.
From Row 23 to Row 39 - Formula applied from E23 onwards.
 

Attachments

  • Test.xlsx
    70.1 KB · Views: 4
Your original sample file had phone numbers in every 4 columns, your recent sample file has them all over the place.
So I've used a new method to determine whether a column is phone number or not; In row 1 (the headers) when a cell contains the word Phone and does not contain the words Seen or Type then it's deemed a phone number. This is handled by the first part of the formula:
=IF(CONCAT(--ISNUMBER(SEARCH({"phone","seen","type"},B$1)))="100"
There are probably better ways of doing that.
The second part of the formula is much the same as before but handles dates and numbers better.
The whole formula in cell B40 of the attached is:
Code:
=IF(CONCAT(--ISNUMBER(SEARCH({"phone","seen","type"},B$1)))="100",IF(ISERROR(MATCH(B1,$A1:A1,0)),IF(B1="","",B1),""),IF(B1="","",B1))
copied down and across. Column A is a simpler formula.

This may be solvable in different ways and it depends on your version of Excel; do you have the LET & LAMBDA functions available to you on the sheet?
What is your version of Excel?
It's likely a Power Query solution will be better.
Where are you getting the data from? Are you pasting it into Excel from elsewhere? If so it's very likely you'll be able to get that data into Excel, transform it, then present it all in the background, using Power Query.

If you answer the underlined questions above, and perhaps state what you're ultimately trying to do we could have a slicker and a more robust solution for you.
 

Attachments

  • Chandoo49201Test-10.xlsx
    148.7 KB · Views: 4
It works, Thank you so much for your help. It would be my pleasure if i can take you for dinner.
What is your version of Excel? : 2021
Where are you getting the data from :From a tool that produces excel format only.
do you have the LET & LAMBDA functions : Yes, i do.

AGAIN, THANK YOU SO MUCH FOR YOUR TIME.
 
It works, Thank you so much for your help. It would be my pleasure if i can take you for dinner.
What is your version of Excel? : 2021
Where are you getting the data from :From a tool that produces excel format only.
do you have the LET & LAMBDA functions : Yes, i do.

AGAIN, THANK YOU SO MUCH FOR YOUR TIME.

LAMBDA function do not have in Excel 2021, but in Office 365

81203
 
somehow its available in my 2021 version.
Thanks to know about that

Your Excel 2021 is professional version, so you have Lambda function

while, my Excel 2021 is general version (non-professional version), I don't have Lambda function.

Regards
 
Where are you getting the data from :From a tool that produces excel format only.
Could you attach such a file (preferably without opening it in Excel first, especially if it's a CSV file (which many systems assume is an Excel file) because even opening such a file in Excel can change the file - in my experience).
Then say/show what your ultimate goal is with this data.
 
I wish i can share original file with you as it has some sensitive data. But i will try your above solution and see if it works with next file, i will be getting new file by end of this month.
 
I used the 10-digit length to identify a phone number and provided a counter as a helper range (required because of a limitation of the IFS... family of functions)
Code:
= LET(
      repeat, COUNTIFS(record,record, counter,"<"&counter),
      phone,  LEN(record)=10,
      IF(repeat*phone, "", record)
  )
I would love to have used BYROW to return the entire table as a single spilt range but, at present, Lambda functions do not behave correctly when the need is to return nested arrays (you get an error message that says that the correct result is not supported!)
 
Strictly speaking, it will remove any 10-character string that is duplicated, so could miss some local phone numbers if they are held more concisely and "Residential" only just survives by having 11 characters. The test could be refined to apply only to 10-digit numbers or strings that can be converted to numbers. The formula would also be more efficient if the COUNTIFS were only performed for phone numbers.
Code:
= LET(
     phone,  LEN(record)=10,
     repeat, IF(phone, COUNTIFS(record, record, counter,"<"&counter), 0),
     IF(repeat*phone, "", record)
  )
The formula works on a single record at a time, so some form of relative referencing is required.
 

Attachments

  • Chandoo49201Test-10.xlsx
    51.9 KB · Views: 4
Hello,

I am back again as got fresh data for November and i tried applying both the formulae you suggested. Please see if you can help for one last time to achieve this goal.

Attaching a workbook with three Tabs,

Sheet1 (Original Sheet ),

Mod - Where below formula is applied.
IF(MOD(COLUMN(),4)=1,IF(ISERROR(MATCH(Sheet1!L2,Sheet1!$H2:Sheet1!H2,0)),Sheet1!L2,""),Sheet1!L2) & ""

Concat - Where below formula is applied.
=IF(CONCAT(--ISNUMBER(SEARCH({"phone","seen","type"},Sheet1!L$1)))="100",IF(ISERROR(MATCH(Sheet1!L2,Sheet1!$H2:Sheet1!K2,0)),IF(Sheet1!L2="","",Sheet1!L2),""),"")

My plan is to put my monthly data in Sheet1 every month and get the cleaned one from one of the sheet whichever is more efficient.

Mod - seems to be doing its work but still leaving some duplicated, you can check in the sheet there are few duplicates in few rows.
Concat - seems not working, I am sure i am doing something wrong.

Again, i can't thank you enough for whatever you already tried.
 

Attachments

  • duplicae - 453-chandoo.xlsx
    171.8 KB · Views: 5
See attached sheet Concat2. I've added conditional formatting to that sheet to highlight differences between it and Sheet1
Differences start in column BD. The formula is the same as in my msg#9 of this thread.
You've added a new kind of header which was missed by my test for a phone number column, the header PH: Phone (Y/N/U) in column G.
It qualifed as a phone number but as it happened it doesn't matter, but I'll give an alternative formula to take that new header into account later.
There are only two formulae on that sheet, in cell A1:
Code:
=IF(Sheet1!A1="","",Sheet1!A1)
and that's copied down as far as necessary.
In cell B1:
Code:
=IF(CONCAT(--ISNUMBER(SEARCH({"phone","seen","type"},Sheet1!B$1)))="100",IF(ISERROR(MATCH(Sheet1!B1,Sheet1!$A1:A1,0)),IF(Sheet1!B1="","",Sheet1!B1),""),IF(Sheet1!B1="","",Sheet1!B1))
which is copied down and across as far as you need.
The alternative to that in cell B1 to take into account the new header is:
Code:
=IF(CONCAT(--ISNUMBER(SEARCH({"phone","seen","type","Y/N/U"},Sheet1!B$1)))="1000",IF(ISERROR(MATCH(Sheet1!B1,Sheet1!$A1:A1,0)),IF(Sheet1!B1="","",Sheet1!B1),""),IF(Sheet1!B1="","",Sheet1!B1))
(copied down and across, of course).

Obviously you'll have to format the results in the same way and you can do this in one shot by selecting the range A1:ES20 on Sheet1, click on the Fornat Painter on the Clipboard section of the Home tab of the ribbon, then clicking on the single cell A1 on the sheet Concat2 will paste all the formats.
 

Attachments

  • Chandoo49201duplicae - 453-chandoo.xlsx
    280.8 KB · Views: 3
Back
Top