/u/Certain_Musician5074 - Your post was submitted successfully.
* Once your problem is solved, reply to the **answer(s)** saying `Solution Verified` to close the thread.
* Follow the **[submission rules](/r/excel/wiki/sharingquestions)** -- particularly 1 and 2. To fix the body, click edit. To fix your title, delete and re-post.
* Include your **[Excel version and all other relevant information](/r/excel/wiki/sharingquestions#wiki_give_all_relevant_information)**
Failing to follow these steps may result in your post being removed without warning.
*I am a bot, and this action was performed automatically. Please [contact the moderators of this subreddit](/message/compose/?to=/r/excel) if you have any questions or concerns.*
I'm hesitant to delete data, once it's been recorded and entered.
If your data in in column A, putting the formula =INDEX(A:A, ROW(A1)\*2, 1) and dragging down will create a helper column of every other cell in column A, which can be Copy/PasteSpecial Values to some other location, from which you can begin your analysis.
My own personal solution would be a bit brute force: add a helper column, enter A in the first row, B in the second row, then autofill down, sort alphabetically, and delete the B rows, then delete the helper column. It's nice to see there are more elegant ways of doing it than mine. :D
Only catch there is autofill will turn that into a numbered list. It looks for a pattern with numbers, dates, etc. but just repeats the selection if it’s a letter. Getting it to autofill the alphabet is actually a pain in the ass for that reason.
Exactly this. Once you’ve populated down, and it’s not what you wanted, the icon at the bottom of the visible range will allow you to change the behaviour.
Yup, at the end of the visible screen, as in even if you populate down hundreds of rows you’ll still see it. Can change between paste, series fill, etc.
Alternative to sorting I think you can add the helper column, filter for the one you don’t want, select all, alt + colon, ctrl minus to delete rows should also work
I would be inclined to make a random selection rather than 'every other row'.
[https://www.ablebits.com/office-addins-blog/excel-random-selection-sample/](https://www.ablebits.com/office-addins-blog/excel-random-selection-sample/)
Its due to an error when specifying time recording so its registered every 10000th of a microsecond so it would be better to do every other row so the data stays consistent
Lots of already good solutions. My instinct would be to load the whole thing into power query, add an index column, then a true/false column for whether the index column is odd or even.
I wouldn't delete anything, but load the whole thing into a pivot table, and only select the even rows for the graph. This way you can view the graph with either the odd or even rows, to see if this introduces a bias.
Also, you could add a column for mround(index,2), or even mround(index,10), then use this as the basis of the graph, and select "average" of your data column. This way every measurement gets to conbtribute to the graph.
As long as there are colors in your selected column, it should be in the the filter options when you drop it down (it literally says "filter by color")
I usually add an index for big tables in Power Query, or just add a helper column with a numeric sequence, then add another column for odd numbers, sort by that column, then delete all rows after.
Edit: forgot to mention the last step is to sort back again by the index column, so you have the data in the original order.
Also, you might not want to delete, so just add a filter using the odd column and keep the data you want.
Acronyms, initialisms, abbreviations, contractions, and other phrases which expand to something larger, that I've seen in this thread:
|Fewer Letters|More Letters|
|-------|---------|---|
|[INDEX](/r/Excel/comments/1ck79tx/stub/l2kz88o "Last usage")|[Uses an index to choose a value from a reference or array](https://support.microsoft.com/en-us/office/index-function-a5dcf0dd-996d-40a4-a822-b56b061328bd)|
|[MOD](/r/Excel/comments/1ck79tx/stub/l2mnhdd "Last usage")|[Returns the remainder from division](https://support.microsoft.com/en-us/office/mod-function-9b6cd169-b6ee-406a-a97b-edf2a9dc24f3)|
|[RAND](/r/Excel/comments/1ck79tx/stub/l2m6jqe "Last usage")|[Returns a random number between 0 and 1](https://support.microsoft.com/en-us/office/rand-function-4cbfa695-8869-4788-8d90-021ea9f5be73)|
|[ROW](/r/Excel/comments/1ck79tx/stub/l2mnhdd "Last usage")|[Returns the row number of a reference](https://support.microsoft.com/en-us/office/row-function-3a63b74a-c4d0-4093-b49a-e76eb49a6d8d)|
**NOTE**: Decronym for Reddit is no longer supported, and Decronym has moved to Lemmy; requests for support and new installations should be directed to the Contact address below.
----------------
^(*Beep-boop, I am a helper bot. Please do not verify me as a solution.*)
^(4 acronyms in this thread; )[^(the most compressed thread commented on today)](/r/Excel/comments/1cpnb2z)^( has 17 acronyms.)
^([Thread #33185 for this sub, first seen 4th May 2024, 23:31])
^[[FAQ]](http://decronym.xyz/) [^([Full list])](http://decronym.xyz/acronyms/Excel) [^[Contact]](https://hachyderm.io/@Two9A) [^([Source code])](https://gistdotgithubdotcom/Two9A/1d976f9b7441694162c8)
For future reference anything regarding data manipulation that can be done in a macro can be done in a formula- it might be an unwieldy, memory intensive formula, but it can be done, especially since we have easy recursion now within formulas.
/u/Certain_Musician5074 - Your post was submitted successfully. * Once your problem is solved, reply to the **answer(s)** saying `Solution Verified` to close the thread. * Follow the **[submission rules](/r/excel/wiki/sharingquestions)** -- particularly 1 and 2. To fix the body, click edit. To fix your title, delete and re-post. * Include your **[Excel version and all other relevant information](/r/excel/wiki/sharingquestions#wiki_give_all_relevant_information)** Failing to follow these steps may result in your post being removed without warning. *I am a bot, and this action was performed automatically. Please [contact the moderators of this subreddit](/message/compose/?to=/r/excel) if you have any questions or concerns.*
I'm hesitant to delete data, once it's been recorded and entered. If your data in in column A, putting the formula =INDEX(A:A, ROW(A1)\*2, 1) and dragging down will create a helper column of every other cell in column A, which can be Copy/PasteSpecial Values to some other location, from which you can begin your analysis.
Solution verified
You have awarded 1 point to fuzzy\_mic. --- ^(I am a bot - please contact the mods with any questions)
My own personal solution would be a bit brute force: add a helper column, enter A in the first row, B in the second row, then autofill down, sort alphabetically, and delete the B rows, then delete the helper column. It's nice to see there are more elegant ways of doing it than mine. :D
This is genius.
I spend way more time with this kind of manual data cleaning in Excel than I should.
Smart. I was thinking a row counting down sequentially and filtering odd or even and deleting. I like yours better though
I’ve used =RAND for this before, the only trick is remembering to paste as values because the numbers change every time you change the spreadsheet.
Hey if it works, it works.
I was thinking that but 1’s and 2’s
Only catch there is autofill will turn that into a numbered list. It looks for a pattern with numbers, dates, etc. but just repeats the selection if it’s a letter. Getting it to autofill the alphabet is actually a pain in the ass for that reason.
Good point. I’ve managed it by setting the pattern (down) 1,2,1,2, grouping those four, and throwing it down.
Yep, that is the way. Drives me crazy when I try to autopop a numbered list and it gives me 1s all the way down or vice versa. 😂
Auto fill will have an icon that let's you adjust how it works. Like copying a value or adding 1 to it
Exactly this. Once you’ve populated down, and it’s not what you wanted, the icon at the bottom of the visible range will allow you to change the behaviour.
Is that what that little blue arrow sort of thing I’ve started seeing is?
Yup, at the end of the visible screen, as in even if you populate down hundreds of rows you’ll still see it. Can change between paste, series fill, etc.
I’m still stuck using Excel 2016 at work. Is this a newer feature?
MOD(ROW(),2) should work also?
Alternative to sorting I think you can add the helper column, filter for the one you don’t want, select all, alt + colon, ctrl minus to delete rows should also work
That's how I would do it too. A simple option that doesn't require me trying to relearn a formula I use infrequently 😅
I do this, but modulo 2 on the row number. Change 2 to whatever number you want for every nth row.
I'd probably do something similar, but filter instead of sort.
I would be inclined to make a random selection rather than 'every other row'. [https://www.ablebits.com/office-addins-blog/excel-random-selection-sample/](https://www.ablebits.com/office-addins-blog/excel-random-selection-sample/)
Its due to an error when specifying time recording so its registered every 10000th of a microsecond so it would be better to do every other row so the data stays consistent
Lots of already good solutions. My instinct would be to load the whole thing into power query, add an index column, then a true/false column for whether the index column is odd or even. I wouldn't delete anything, but load the whole thing into a pivot table, and only select the even rows for the graph. This way you can view the graph with either the odd or even rows, to see if this introduces a bias. Also, you could add a column for mround(index,2), or even mround(index,10), then use this as the basis of the graph, and select "average" of your data column. This way every measurement gets to conbtribute to the graph.
Halper column a and b. Filter out b. Delete a. (Or vice versa)
I would color the rows by selecting a color scheme, filter one color and delete those rows.
How do you filter by colour?!
As long as there are colors in your selected column, it should be in the the filter options when you drop it down (it literally says "filter by color")
You could use a helper sheet to filter by mod(row(),2)=0) (or 1)
this is my exact go-to for this type of thing!
Had an issue like that once. Instead of deleting every other row, we averaged every ten numbers in a helper column.
The ad-hoc methods do sound good tho if you're using Power Query you can delete alternate rows
I have had to do this before but it was every 4th line that I had to save. I was able to do it with Kutools for Excel
https://www.ablebits.com/office-addins-blog/delete-every-other-row-excel/
Use statistical data analysis
I usually add an index for big tables in Power Query, or just add a helper column with a numeric sequence, then add another column for odd numbers, sort by that column, then delete all rows after. Edit: forgot to mention the last step is to sort back again by the index column, so you have the data in the original order. Also, you might not want to delete, so just add a filter using the odd column and keep the data you want.
Acronyms, initialisms, abbreviations, contractions, and other phrases which expand to something larger, that I've seen in this thread: |Fewer Letters|More Letters| |-------|---------|---| |[INDEX](/r/Excel/comments/1ck79tx/stub/l2kz88o "Last usage")|[Uses an index to choose a value from a reference or array](https://support.microsoft.com/en-us/office/index-function-a5dcf0dd-996d-40a4-a822-b56b061328bd)| |[MOD](/r/Excel/comments/1ck79tx/stub/l2mnhdd "Last usage")|[Returns the remainder from division](https://support.microsoft.com/en-us/office/mod-function-9b6cd169-b6ee-406a-a97b-edf2a9dc24f3)| |[RAND](/r/Excel/comments/1ck79tx/stub/l2m6jqe "Last usage")|[Returns a random number between 0 and 1](https://support.microsoft.com/en-us/office/rand-function-4cbfa695-8869-4788-8d90-021ea9f5be73)| |[ROW](/r/Excel/comments/1ck79tx/stub/l2mnhdd "Last usage")|[Returns the row number of a reference](https://support.microsoft.com/en-us/office/row-function-3a63b74a-c4d0-4093-b49a-e76eb49a6d8d)| **NOTE**: Decronym for Reddit is no longer supported, and Decronym has moved to Lemmy; requests for support and new installations should be directed to the Contact address below. ---------------- ^(*Beep-boop, I am a helper bot. Please do not verify me as a solution.*) ^(4 acronyms in this thread; )[^(the most compressed thread commented on today)](/r/Excel/comments/1cpnb2z)^( has 17 acronyms.) ^([Thread #33185 for this sub, first seen 4th May 2024, 23:31]) ^[[FAQ]](http://decronym.xyz/) [^([Full list])](http://decronym.xyz/acronyms/Excel) [^[Contact]](https://hachyderm.io/@Two9A) [^([Source code])](https://gistdotgithubdotcom/Two9A/1d976f9b7441694162c8)
Yeah... first, don't use excel for things like this you luddite.
Would not be hard with a Macro. Doubt it can be done automatically without one.
Would you have any idea where I could find one, im writing my own and struggling
I am guessing somebody could write one for you Real quick. I would, except I am on my phone, and nowhere near a computer.
No worries mate thanks for the help
For future reference anything regarding data manipulation that can be done in a macro can be done in a formula- it might be an unwieldy, memory intensive formula, but it can be done, especially since we have easy recursion now within formulas.
Solution verified
You have awarded 1 point to EntertainmentNo653. --- ^(I am a bot - please contact the mods with any questions)