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

Copy a format multiple times
Select a formatted range. Double-click the Format Painter (left side of Home tab). You can paste formatting multiple times. Esc to stop
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

C
  • Question Question
Macro Help - Transposing Some Data from Columns to Rows, Accounting for Duplicate Data
Replies
1
Views
116
Kaper
Kaper
B
  • Solved
need help finding cells with different values that contain the same mailing address
Replies
6
Views
860
Micron
M
U
  • Solved
VBA To Add rows to TABLE based on a cells value
Replies
3
Views
219
igold
igold
J
  • Question Question
Power Query editor OR VBA to extract data from Grants.gov
Replies
3
Views
355
Jagadeshrao
J
S
  • Solved
Comparing time stamp values to remove 'duplicate' time entries (rows).
Replies
7
Views
315
felixstraube
F

Forum statistics

Threads
1,223,888
Messages
6,175,212
Members
452,618
Latest member
Tam84

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