Remove duplicate by row

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

How to total the visible cells?
From the first blank cell below a filtered data set, press Alt+=. Instead of SUM, you will get SUBTOTAL(9,)
How about in cell K4

Excel Formula:
=UNIQUE(C4:J10,TRUE)
 
Upvote 0
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​
How about in cell K4

Excel Formula:
=UNIQUE(C4:J10,TRUE)
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
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
I think the results can appear only Sunday, Monday, Tuesday, Wednesday, Thursday, Friday, Saturday, Sunday only one column or one row.

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
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.

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

Forum statistics

Threads
1,223,099
Messages
6,170,107
Members
452,302
Latest member
TaMere

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