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

What is the fastest way to copy a formula?
If A2:A50000 contain data. Enter a formula in B2. Select B2. Double-click the Fill Handle and Excel will shoot the formula down to B50000.
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
464
Vogateer
V
S
  • Solved
NETWORKDAYS.INTL
Replies
7
Views
125
Fluff
Fluff
D
  • Solved
Is there a quick way to create / fill this list
2 3
Replies
20
Views
422
Eric W
Eric W
W
  • Solved
Formula Error: Error applying formula with VBA even though it works manually
Replies
3
Views
120
6StringJazzer
6StringJazzer
S
  • Question Question
Compressing large amount of rows but adding totals?
Replies
6
Views
133
jkpieterse
jkpieterse

Forum statistics

Threads
1,224,822
Messages
6,181,165
Members
453,021
Latest member
Justyna P

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