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

Which Excel functions can ignore hidden rows?
The SUBTOTAL and AGGREGATE functions ignore hidden rows. AGGREGATE can also exclude error cells and more.
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
DanteAmor said:
Try in cell L4:

Excel Formula: 
=UNIQUE(TEXTSPLIT(TEXTJOIN(",",,C4:J10),","),TRUE)
Click to expand...
the formula worked, very helpful, speeding up the processing of the data. Thank you very much 🙏 🙏 (y)
 
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

B
  • Question
How to get nearest dates for tues and fri
Replies
5
Views
251
felixstraube
F
J
  • Solved
COUNTA, remove null or blanks from count
Replies
8
Views
257
Fluff
Fluff
A
  • Question
Copy & Paste from workbook to another with criterias
Replies
2
Views
145
Anthony86
A
S
  • Question
count number of unique Mondays in a date column
Replies
4
Views
675
Dave Patton
D
F
  • Solved
Find and Replace VBA Depending on Day of Week
Replies
8
Views
377
FrenchCelt
F

Forum statistics

Threads
1,215,097
Messages
6,123,076
Members
449,094
Latest member
mystic19

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