Remove duplicate by row

S

Sefty

Board Regular
Joined
Apr 5, 2022
Messages
68
Office Version
  1. 365
Platform
  1. Windows
Hello,
if i have data, duplicate by row how to remove duplicate data so the result is just "Sunday, Monday, Tuesday, Wednesday, Thursday, Friday, Saturday, Sunday". Because if use menu data-> remove duplicate just remove by coloumn. If i have data like this. Thanks for the respon 🙏
matrix_major_project (30).xlsx
BCDEFGHIJK
3
4SundayMondayTuesdayWednesdayThursdayFridaySaturdaySunday
5MondayTuesdayWednesdayThursdayFridaySaturdaySundayMonday
6TuesdayWednesdayThursdayFridaySaturdaySundayMondayTuesday
7WednesdayThursdayFridaySaturdaySundayMondayTuesdayWednesday
8ThursdayFridaySaturdaySundayMondayTuesdayWednesdayThursday
9FridaySaturdaySundayMondayTuesdayWednesdayThursdayFriday
10SaturdaySundayMondayTuesdayWednesdayThursdayFridaySaturday
11
12
Sheet5
 

Excel Facts

Can Excel fill bagel flavors?
You can teach Excel a new custom list. Type the list in cells, File, Options, Advanced, Edit Custom Lists, Import, OK
Sort by date Sort by votes
How about in cell K4

Excel Formula: 
=UNIQUE(C4:J10,TRUE)
 
Upvote 0
Sefty said:
ay​
Monday​
Tuesday​
Wednesday​
Thursday​
Friday​
Saturday​
Sunday​
5
Monday​
Tuesday​
Wednesday​
Thursday​
Friday​
Saturday​
Sunday​
Monday​
6
Tuesday​
Wednesday​
Thursday​
Friday​
Saturday​
Sunday​
Monday​
Tuesday​
7
Wednesday​
Thursday​
Friday​
Saturday​
Sunday​
Monday​
Tuesday​
Wednesday​
8
Thursday​
Friday​
Saturday​
Sunday​
Monday​
Tuesday​
Wedne​
Click to expand...
DanteAmor said:
How about in cell K4

Excel Formula: 
=UNIQUE(C4:J10,TRUE)
Click to expand...
the result still appears multiple coloumn and row, instead of just being unique in the selected column. I think the results can appear only Sunday, Monday, Tuesday, Wednesday, Thursday, Friday, Saturday, Sunday only one column or one row.
thank for help 🙏
 
Upvote 0
If you're open to a VBA advanced filter method (which will hide the rows - not delete them) then you could try the following:
VBA Code: 
Option Explicit
Sub Weekdays_Unique()
    Dim ws As Worksheet
    Set ws = Worksheets("Sheet5")
    Dim rCrit As Range
    With Range("C4").CurrentRegion
        Set rCrit = .Offset(, .Columns.Count).Resize(2, 1)
        rCrit.Cells(2).Formula = "=UNIQUE(RC[-8]:R[6]C[-8],TRUE,TRUE)"
        .AdvancedFilter Action:=xlFilterInPlace, CriteriaRange:=rCrit, Unique:=False
    End With
    rCrit.Cells(2).ClearContents
End Sub

Turns this:
Book1
BCDEFGHIJK
3
4SundayMondayTuesdayWednesdayThursdayFridaySaturdaySunday
5MondayTuesdayWednesdayThursdayFridaySaturdaySundayMonday
6TuesdayWednesdayThursdayFridaySaturdaySundayMondayTuesday
7WednesdayThursdayFridaySaturdaySundayMondayTuesdayWednesday
8ThursdayFridaySaturdaySundayMondayTuesdayWednesdayThursday
9FridaySaturdaySundayMondayTuesdayWednesdayThursdayFriday
10SaturdaySundayMondayTuesdayWednesdayThursdayFridaySaturday
11
12
Sheet5


Into this:
Book1
BCDEFGHIJK
3
4SundayMondayTuesdayWednesdayThursdayFridaySaturdaySunday
11
12
Sheet5
 
Upvote 0
WOW macros worked, thanks for help 🙏
Sorry, if the data looks like this, it has been tried but the results don't match. sorry I still need help
I have tried with the code macro above but it doesn't work if the data format is like this

Book2
CDEFGHIJK
1
2MondayTuesdayWednesday
3TuesdayWednesdayThursdayFridaySaturdaySundayMonday
4WednesdayThursdayFridaySaturdaySundayMondayTuesday
5ThursdayFriday
6FridaySaturdaySundayMonday
7SaturdaySundayMondayTuesdayWednesdayThursdayFriday
8
9
10
11
12
13
Sheet2

VBA Code: 
Sub Weekdays_Unique()
    Dim ws As Worksheet
    Set ws = Worksheets("Sheet2")
    Dim rCrit As Range
    With Range("D2:J7").CurrentRegion
        Set rCrit = .Offset(, .Columns.Count).Resize(2, 1)
        rCrit.Cells(2).Formula = "=UNIQUE(RC[-8]:R[6]C[-8],TRUE,TRUE)"
        .AdvancedFilter Action:=xlFilterInPlace, CriteriaRange:=rCrit, Unique:=False
    End With
    rCrit.Cells(2).ClearContents
kevin9999 said:
If you're open to a VBA advanced filter method (which will hide the rows - not delete them) then you could try the following:
VBA Code: 
Option Explicit
Sub Weekdays_Unique()
    Dim ws As Worksheet
    Set ws = Worksheets("Sheet5")
    Dim rCrit As Range
    With Range("C4").CurrentRegion
        Set rCrit = .Offset(, .Columns.Count).Resize(2, 1)
        rCrit.Cells(2).Formula = "=UNIQUE(RC[-8]:R[6]C[-8],TRUE,TRUE)"
        .AdvancedFilter Action:=xlFilterInPlace, CriteriaRange:=rCrit, Unique:=False
    End With
    rCrit.Cells(2).ClearContents
End Sub

Turns this:
Book1
BCDEFGHIJK
3
4SundayMondayTuesdayWednesdayThursdayFridaySaturdaySunday
5MondayTuesdayWednesdayThursdayFridaySaturdaySundayMonday
6TuesdayWednesdayThursdayFridaySaturdaySundayMondayTuesday
7WednesdayThursdayFridaySaturdaySundayMondayTuesdayWednesday
8ThursdayFridaySaturdaySundayMondayTuesdayWednesdayThursday
9FridaySaturdaySundayMondayTuesdayWednesdayThursdayFriday
10SaturdaySundayMondayTuesdayWednesdayThursdayFridaySaturday
11
12
Sheet5


Into this:
Book1
BCDEFGHIJK
3
4SundayMondayTuesdayWednesdayThursdayFridaySaturdaySunday
11
12
Sheet5
Click to expand...
 
Upvote 0
Sefty said:
I think the results can appear only Sunday, Monday, Tuesday, Wednesday, Thursday, Friday, Saturday, Sunday only one column or one row.
Click to expand...

Try in cell L4:

Excel Formula: 
=UNIQUE(TEXTSPLIT(TEXTJOIN(",",,C4:J10),","),TRUE)
 
Upvote 0
Solution
How about
Fluff.xlsm
DEFGHIJKL
1
2MondayTuesdayWednesday
3TuesdayWednesdayThursdayFridaySaturdaySundayMonday
4WednesdayThursdayFridaySaturdaySundayMondayTuesday
5ThursdayFriday
6FridaySaturdaySundayMonday
7SaturdaySundayMondayTuesdayWednesdayThursdayFriday
8
9
10
11MondayMondayTuesdayWednesdayThursdayFridaySaturdaySunday
12Tuesday
13Wednesday
14Thursday
15Friday
16Saturday
17Sunday
18
19
Master
Cell Formulas
RangeFormula
D11:D17D11=UNIQUE(TOCOL(IF(D2:J7<>"",D2:J7,1/0),2))
F11:L11F11=UNIQUE(TOROW(IF(D2:J7<>"",D2:J7,1/0),2),1)
Dynamic array formulas.
 
Upvote 0
Fluff said:
How about
Fluff.xlsm
DEFGHIJKL
1
2MondayTuesdayWednesday
3TuesdayWednesdayThursdayFridaySaturdaySundayMonday
4WednesdayThursdayFridaySaturdaySundayMondayTuesday
5ThursdayFriday
6FridaySaturdaySundayMonday
7SaturdaySundayMondayTuesdayWednesdayThursdayFriday
8
9
10
11MondayMondayTuesdayWednesdayThursdayFridaySaturdaySunday
12Tuesday
13Wednesday
14Thursday
15Friday
16Saturday
17Sunday
18
19
Master
Cell Formulas
RangeFormula
D11:D17D11=UNIQUE(TOCOL(IF(D2:J7<>"",D2:J7,1/0),2))
F11:L11F11=UNIQUE(TOROW(IF(D2:J7<>"",D2:J7,1/0),2),1)
Dynamic array formulas.
Click to expand...

wow impressive, this really helps to transpose the results of the data right away. Thank you very much, really helped speed up the processing of the data (y)🙏
 
Upvote 0
Glad we could help & thanks for the feedback.
 
Upvote 0
You must log in or register to reply here.

Similar threads

L
  • Solved
=unique(vstack in a table
Replies
6
Views
331
Vogateer
V
S
  • Solved
NETWORKDAYS.INTL
Replies
7
Views
112
Fluff
Fluff
D
  • Solved
Is there a quick way to create / fill this list
2 3
Replies
20
Views
379
Eric W
Eric W
W
  • Solved
Formula Error: Error applying formula with VBA even though it works manually
Replies
3
Views
91
6StringJazzer
6StringJazzer
S
  • Question Question
Compressing large amount of rows but adding totals?
Replies
6
Views
119
jkpieterse
jkpieterse

Forum statistics

Threads
1,223,894
Messages
6,175,250
Members
452,623
Latest member
Techenthusiast

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