Join the social network of Tech Nerds, increase skill rank, get work, manage projects...
 
  • Sort and align duplicates valueson the same rows in Excel or Google Spreadsheet?

    • 1
    • 0
    • 0
    • 0
    • 0
    • 0
    • 1
    • 0
    • 5.10k
    Comment on it

    Hello friend, I found this interesting question on reddit so after answering their I thought to share this with you here doing sorting and align duplicates in excel are both different lets discuss this one bu one separately.

     

    1. How to sort duplicate values in Excel or Google spreadsheet

    For example, I have two columns A and B as shown below:

    A B
    Sachin Manish
    Manish Manoj
    Nitin Sushil
    Manoj Nitin
    Ankit  
    Sushil Ankit

     

    So to see the duplicate data you can sort columns, You have to put filter on particular column, then sort the data from A to Z or Z to A , To put filter on indivusal or particular column you have to select the single column and then click on filter. 

    For example select column b only put filter only on B column and then sort it But this will only sort the data and it do not align duplicates values on the same rows.

     

    2. So Now if you the question is How to align duplicates values on the same rows:

    taking above example first of all select the column B and put filer note that filet should be applied only on column B then select sort it A-Z, Now in column c you have to put the below formula and then drag it to the last row :

    =IF(ISNA(MATCH(A1,B:B,0)),"",INDEX(B:B,MATCH(A1,B:B,0)))

     

    See the below screenshot for output:

    As in above screenshot you can clearly see at the top bar the formula used and in column C the output generated is align to column A.

     

    3. Now one more thing, What if you want to find out duplicate values in Column A and B.

    Considering above screen-shot and example, For that you can put the below Vlookup function in column D:

    =VLOOKUP(C2, A:A, 1,0)

    It will provides the matching data in column D to you, if exists, else the data which are uniques will be showed as #N/A error (didn't find the value).

    See the below screenshot for output:

     

    Sort and align duplicates valueson the same rows in Excel or Google Spreadsheet?

 0 Comment(s)

Sign In
                           OR                           
                           OR                           
Register

Sign up using

                           OR                           
Forgot Password
Fill out the form below and instructions to reset your password will be emailed to you:
Reset Password
Fill out the form below and reset your password: