Hi,
I have 4 columns with data. This is a thickness measurement of a textile on 4 different positions.
I like to remove all the identical adjacent rows.
I found a way to remove duplicate rows, but i only like to remove them if they are duplicate AND adjacent.
Anyone an idea to do this?
Kind regards,
Maarten
Welcome to the forum.
You may well require VBA for this - shall I move the thread for you?
Ali
Enthusiastic self-taught user of MS Excel who's always learning!
Don't forget to say "thank you" in your thread to anyone who has offered you help.
You can reward them by clicking on * Add Reputation below their user name on the left, if you wish.
Forum Rules (updated August 2023): please read them here.
Yes please! Feel free to move it to the right position on this forum!
Thanks!
Thread moved.
Your sample file should include an example of before AND after scenarios. It should not just be a data dump.
EDIT: And it should be a small sample - limit it to 100 rows, please.
Last edited by AliGW; 06-05-2023 at 04:55 AM. Reason: Typo fixed.
Hi Maarten - do you want to remove the first instance AND the duplicates, or just the duplicates?
Clicking the Add Reputation star below helpful posts is a great way to show your appreciation.
Please mark your threads as SOLVED upon conclusion (Thread Tools above Post # 1). - Lee
What do you mean with after scenarios?
This is the raw data. This is the thickness of a textile measured with a measuring tool on 4 positions on a textile.
This measurement is done during cutting of the textile with a sampling frequency of 1 second.
The cutting process is as follows:
Roll to sheet principle --> 60 seconds (=60 measurements) during the cutting procedure and 6 seconds (=6 measurements) during transport/feed of the textile roll.
As you can see in the data. Around 60 adjacent positions are identical (= cutting procedure) and then around 6 adjacent positions are different. I only like the keep the 6 positions during transport.
The difficult part is that the 60 identical positons are nog always 60.
Normally around 210 sheets are transported, so i should reduce this file from 14000 datapoints to around 1200 datapoints (taken during transport)
See the question in post #5.
A before and after sample workbook would make it clear what you were expecting to happen. You have provided 1000s of rows of data with not one bit of annotation. Of course, you know what you want, but you need to convey that clearly to your helpers.
I only like to remove a row if the duplicate value is adjacent.
Which of these rows needs removing and why?
AliGW on MS365 Beta Channel (Windows 11) 64 bit
Sheet: Blad1
Yes , but if 60 rows are identical and adjacent, do you want to remove all 60, or just 59, leaving 1?Originally Posted by MaartenColaers
Also, is it possible that there will be identical rows within the parts you want to keep?
Sorry for the haziness. It is my first post, but i have to be more clear from the beginning
I like to keep indeed one value, so "around' 60 identical and adjacent values to 1 value. (so removing 59 identical values)
As you can see, sometimes in one column there are '60' identical values, but in the other columns there is 1 value changing. I think we need to use only one column to look for the duplicates and then broaden the selection to the other columns.
Last edited by AliGW; 06-05-2023 at 05:23 AM. Reason: Please do NOT quote unnecessarily!
Does this give what you want?
=UNIQUE(B4:E13195)
Can you please provide a SMALL sample dataset showing before and after?
Hi
May be
Please Login or Register to view this content.
Attachment 831846
Attached, you can see what i like to become.
The function should only work on one column and broaden to change to the other columns.
In this example the one column is B3:B26 and despite the change from C7 to C8, these values are not kept.
Did you try my suggestion? Did you try post #13? If so, in what way did they NOT meet your requirements?
You are illustrating now with a different dataset to the one you shared.
Share the dataset that you are using to illustrate the issue. Workbook - NOT a screenshot!
Your data doesn't really line up so neatly as you describe. I used this formula (in F5, copied down):...to look at the aggregate difference between each row and the one above it (0 = identical). As shown below, the values sometimes jump around rather randomly.Formula:
Please Login or Register to view this content.
Row\Col
Rows 414 and 417 are identical. I guess you want to keep them both since row 416 is different from them. In the meantime, you want to delete row 415. Consider you are using 365, if you can see Automate in your tab menu, the following Script will do the job. Backup your data first. Worksheet Result contains 4303 rows.
Please Login or Register to view this content.
Last edited by Torontohrb; 06-05-2023 at 10:15 AM.
Office Scripts is only available in Enterprise and educational versions of Office 365 - a standalone user won't have it.
Scripts is powerful to deal with arrays. If using VBA, it is also easy. Just loop through from bottom to top: If the current row is identical to the previous one, delete the row.
Here is the VBA code. Comparing with Scripts, VBA is much slower.
Please Login or Register to view this content.
Have you tired RemoveDuplicates?
Please Login or Register to view this content.
This removes adjacent duplicates. You can reset the LOWER_LIMIT constant higher to remove more rows i.e. those that vary by insignificant amounts.I made a copy of Blad1 ("Blad1 (2)") so I could restore the data for testing purposes.Please Login or Register to view this content.
You can do the same; just make the copy (before running this) and un-comment the lines in red.
PS. Could you just filter the data, as I did, without removing the rows? You could adjust the filtering formula to read lower limit from a cell.
Last edited by leelnich; 06-05-2023 at 04:02 PM.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks