Multiple Duplicate Rows to Single unique Row (Combine multiple duplicate rows to one)

S

signup

New Member
Joined
Feb 15, 2018
Messages
42
Office Version
  1. 2019
Platform
  1. Windows
Hi,

I have a sheet with the data like below, can some one help me with a formula or something else to get the expected output?

In my sheet i have a row with the same data multiple times, i need only one row with the same data and wanted to eliminate all duplicate rows and need same rule for all rows in my sheet. Can we do this by any formula or through VB Script?


[TABLE="class: grid"]
<tbody>[TR]
[TD="colspan: 4"]Multiple Duplicate Rows to Single unique Row:[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Firm Name
[/TD]
[TD]Firm Alias[/TD]
[TD]Office Alias[/TD]
[TD]Rep Alias[/TD]
[TD]TA Code[/TD]
[TD]Address 1[/TD]
[TD]Address 2[/TD]
[TD]City[/TD]
[TD]State[/TD]
[TD]ZIP[/TD]
[/TR]
[TR]
[TD]Some Fund Company 1[/TD]
[TD="align: center"]001[/TD]
[TD="align: center"]002[/TD]
[TD][/TD]
[TD]AAA[/TD]
[TD]Some Address 1[/TD]
[TD][/TD]
[TD]CITI 1[/TD]
[TD]STATE 1[/TD]
[TD]ZIP 1[/TD]
[/TR]
[TR]
[TD]Some Fund Company 1[/TD]
[TD="align: center"]001[/TD]
[TD="align: center"]002[/TD]
[TD][/TD]
[TD]AAA[/TD]
[TD]Some Address 1[/TD]
[TD][/TD]
[TD]CITI 1[/TD]
[TD]STATE 1[/TD]
[TD]ZIP 1[/TD]
[/TR]
[TR]
[TD]Some Fund Company 1[/TD]
[TD="align: center"]001[/TD]
[TD="align: center"]002[/TD]
[TD][/TD]
[TD]AAA[/TD]
[TD]Some Address 1[/TD]
[TD][/TD]
[TD]CITI 1[/TD]
[TD]STATE 1[/TD]
[TD]ZIP 1[/TD]
[/TR]
[TR]
[TD]Some Fund Company 2[/TD]
[TD="align: center"]011[/TD]
[TD="align: center"]022[/TD]
[TD][/TD]
[TD]BBB[/TD]
[TD]Some Address 2[/TD]
[TD][/TD]
[TD]CITI 2[/TD]
[TD]STATE 2[/TD]
[TD]ZIP 2[/TD]
[/TR]
[TR]
[TD]Some Fund Company 2[/TD]
[TD="align: center"]011[/TD]
[TD="align: center"]022[/TD]
[TD][/TD]
[TD]BBB[/TD]
[TD]Some Address 2[/TD]
[TD][/TD]
[TD]CITI 2[/TD]
[TD]STATE 2[/TD]
[TD]ZIP 2[/TD]
[/TR]
[TR]
[TD]Some Fund Company 2[/TD]
[TD="align: center"]011[/TD]
[TD="align: center"]022[/TD]
[TD][/TD]
[TD]BBB[/TD]
[TD]Some Address 2[/TD]
[TD][/TD]
[TD]CITI 2[/TD]
[TD]STATE 2[/TD]
[TD]ZIP 2[/TD]
[/TR]
[TR]
[TD]Some Fund Company 3[/TD]
[TD="align: center"]111[/TD]
[TD="align: center"]222[/TD]
[TD][/TD]
[TD]CCC[/TD]
[TD]Some Address 3[/TD]
[TD][/TD]
[TD]CITI 3[/TD]
[TD]STATE 3[/TD]
[TD]ZIP 3[/TD]
[/TR]
[TR]
[TD]Some Fund Company 3[/TD]
[TD="align: center"]111[/TD]
[TD="align: center"]222[/TD]
[TD][/TD]
[TD]CCC[/TD]
[TD]Some Address 3[/TD]
[TD][/TD]
[TD]CITI 3[/TD]
[TD]STATE 3[/TD]
[TD]ZIP 3[/TD]
[/TR]
[TR]
[TD]Some Fund Company 3[/TD]
[TD="align: center"]111[/TD]
[TD="align: center"]222[/TD]
[TD][/TD]
[TD]CCC[/TD]
[TD]Some Address 3[/TD]
[TD][/TD]
[TD]CITI 3[/TD]
[TD]STATE 3[/TD]
[TD]ZIP 3[/TD]
[/TR]
[TR]
[TD]Some Fund Company 4[/TD]
[TD="align: center"]100[/TD]
[TD="align: center"]333[/TD]
[TD][/TD]
[TD]DDD[/TD]
[TD]Some Address 4[/TD]
[TD][/TD]
[TD]CITI 4[/TD]
[TD]STATE 4[/TD]
[TD]ZIP 4[/TD]
[/TR]
[TR]
[TD]Some Fund Company 4[/TD]
[TD="align: center"]100[/TD]
[TD="align: center"]333[/TD]
[TD][/TD]
[TD]DDD[/TD]
[TD]Some Address 4[/TD]
[TD][/TD]
[TD]CITI 4[/TD]
[TD]STATE 4[/TD]
[TD]ZIP 4[/TD]
[/TR]
[TR]
[TD]Some Fund Company 4[/TD]
[TD="align: center"]100[/TD]
[TD="align: center"]333[/TD]
[TD][/TD]
[TD]DDD[/TD]
[TD]Some Address 4[/TD]
[TD][/TD]
[TD]CITI 4[/TD]
[TD]STATE 4[/TD]
[TD]ZIP 4[/TD]
[/TR]
[TR]
[TD]Expected Output:[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Firm Name
[/TD]
[TD="align: center"]Firm Alias[/TD]
[TD]Office Alias[/TD]
[TD]Rep Alias[/TD]
[TD]TA Code[/TD]
[TD]Address 1[/TD]
[TD]Address 2[/TD]
[TD]City[/TD]
[TD]State[/TD]
[TD]ZIP[/TD]
[/TR]
[TR]
[TD]Some Fund Company 1[/TD]
[TD="align: center"]001[/TD]
[TD="align: center"]002[/TD]
[TD][/TD]
[TD]AAA[/TD]
[TD]Some Address 1[/TD]
[TD][/TD]
[TD]CITI 1[/TD]
[TD]STATE 1[/TD]
[TD]ZIP 1[/TD]
[/TR]
[TR]
[TD]Some Fund Company 2[/TD]
[TD="align: center"]011[/TD]
[TD="align: center"]022[/TD]
[TD][/TD]
[TD]BBB[/TD]
[TD]Some Address 2[/TD]
[TD][/TD]
[TD]CITI 2[/TD]
[TD]STATE 2[/TD]
[TD]ZIP 2[/TD]
[/TR]
[TR]
[TD]Some Fund Company 3[/TD]
[TD="align: center"]111[/TD]
[TD="align: center"]222[/TD]
[TD][/TD]
[TD]CCC[/TD]
[TD]Some Address 3[/TD]
[TD][/TD]
[TD]CITI 3[/TD]
[TD]STATE 3[/TD]
[TD]ZIP 3[/TD]
[/TR]
[TR]
[TD]Some Fund Company 4[/TD]
[TD="align: center"]100[/TD]
[TD="align: center"]333[/TD]
[TD][/TD]
[TD]DDD[/TD]
[TD]Some Address 4[/TD]
[TD][/TD]
[TD]CITI 4[/TD]
[TD]STATE 4[/TD]
[TD]ZIP 4[/TD]
[/TR]
</tbody>[/TABLE]
 

Excel Facts

Select all contiguous cells
Pressing Ctrl+* (asterisk) will select the "current region" - all contiguous cells in all directions.
Sort by date Sort by votes
Try:
Code: 
Sub RemoveDups()
    Cells.RemoveDuplicates Columns:=Array(1), Header:=xlYes
End Sub
 
Upvote 0
mumps said:
Try:
Code: 
Sub RemoveDups()
    Cells.RemoveDuplicates Columns:=Array(1), Header:=xlYes
End Sub
Click to expand...


Thank You for the reply, I'm getting 400 error while running above code. Don't we have any formula for this?
 
Last edited:
Upvote 0
I tried the macro on the data you posted and it worked properly. Are you trying the macro on the same data?
 
Upvote 0
mumps said:
I tried the macro on the data you posted and it worked properly. Are you trying the macro on the same data?
Click to expand...

Yes @mumps I'm trying macro on same data..

I selected data and tried to run the macro but i'm getting error. should i do it in any specific way?
 
Upvote 0
Click here to download your file. Run the macro in Module1.
 
Upvote 0
You must log in or register to reply here.

Similar threads

T
  • Question Question
Moving address list to columns from rows
Replies
3
Views
97
Sufiyan97
Sufiyan97
C
  • Question Question
Macro Help - Transposing Some Data from Columns to Rows, Accounting for Duplicate Data
Replies
1
Views
128
Kaper
Kaper
B
  • Solved
need help finding cells with different values that contain the same mailing address
Replies
6
Views
875
Micron
M
U
  • Solved
VBA To Add rows to TABLE based on a cells value
Replies
3
Views
257
igold
igold
J
  • Question Question
Power Query editor OR VBA to extract data from Grants.gov
Replies
3
Views
390
Jagadeshrao
J

Forum statistics

Threads
1,224,823
Messages
6,181,181
Members
453,022
Latest member
Mohamed Magdi Tawfiq Emam

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top