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

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:

0 Comment(s)

OR
OR
Register

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

• Hire
• Post Projects

Post Projects

• All at 0 Cost ....
• Post Tech Job
• Select Best Bidder
• Track the Project
• Approve Work and Pay safely
• Browse Nerds
• Work
• Find Projects Find Projects
• UI Design and UX
• Software Engineering
View more...
View less...
• Marketing
• General
View more...
View less...
• Manage
• Company Company

Manage Company

• All at 0 Cost ....
• Manage Company and Employee Profiles
• Company wide Employee Productivity Reports
• Knowledge Sharing and Collaboration Tools
• Get Sales Lead and Bid for Tech Projects
• Send Invoices and Receive Payment Safely
• Learn
• Nerd Digest Nerd Digest
• UI Design and UX
• Software Engineering
View more...
View less...
• Marketing
• General
View more...
View less...
• Tech Q & A Tech Q & A
• UI Design and UX
• Software Engineering
View more...
View less...
• Marketing
• General
View more...
View less...