• 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.
 

Belal

New Member
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

p45cal

Well-Known Member
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

Belal

New Member
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?
 

Belal

New Member
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

p45cal

Well-Known Member
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

Belal

New Member
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.
 

bosco_yip

Excel Ninja
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
 

bosco_yip

Excel Ninja
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
 

p45cal

Well-Known Member
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.
 

Belal

New Member
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.
 

Peter Bartholomew

Well-Known Member
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!)
 

Peter Bartholomew

Well-Known Member
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

Belal

New Member
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

p45cal

Well-Known Member
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

Top