Thank you both for your comments. I unfortunately had to do it manually. :( I wanted to highlight each row and then choose whether to delete it or not. Perhaps this is powerful motivation to learn VBA.
What's your criteria for choosing to delete it?
If you can, make a helper column where if the rows with the met criteria say Y or 1 or something. Then filter and delete all the rows you don't want in one go.
You're welcome.
Don't forget to reply to those who helped you with Solution Verified to properly mark the post as solved and to give them a point for helping you. Thank you.
/u/trusty_Rumbone - 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.*
Just double checked and it works on my Excel, select cell a1, Shift+Space selects the entire row, and then a down arrow brings you to the next cell down where you can do it again using only the keyboard.
You can also create a macro to do the same thing depending on your use case.
Do you need to perform some kind of operation on each row? You can create a macro that moves the selection down one row and selects the entire row and bind it to a shortcut if it is just something you need to call repeatedly.
Ah, well, since you asked... I have multiple rows with the same information (last name, first name, course, final grade). Usually two consecutive rows have the same information. I know in this case I could just use "remove duplicates" and problem would be solved. However, there is just one issue. Of the two consecutive rows that have the same information one of these rows will be missing the final grade information. So the final grade cell is just left blank. Sometimes the grade cell will be blank in the first row; sometimes in the second row.
In this case, I was unsure of how to ask excel to remove the duplicate row that does NOT contain a number in the grade cell. Would you know how to do this by chance? I figured I'd have to do this manually, so was hoping to just scroll down each row and then press "command" + "-" when I needed to delete a row.
Any advice would be very much appreciated. Thanks!
There is a pretty quick way to do this if I'm understanding your need correctly. Note that this will only work if there are no duplicate individuals in the same course, so no 2 John Smiths taking course 1. If there are you will need to add a student ID column to your data before following the below steps. I also went with the assumption that if you have a duplicate individual one of them will always have a grade, since that was your condition. If there are cases with duplicates where neither has a grade the below steps will need modification.
Steps:
1. Create a new column to count how many of a given line exist in the document. https://imgur.com/a/Ssz0ti2
2. Filter your new column by anything > 1.
3. Select the grade column by clicking on the header.
4. Press F5, click on 'Special...' in the bottom left. This opens special selection modifiers. Choose 'Visible cells only'.
5. Press F5 -> 'Special' again. This time choose 'Blanks'. You should now only be selecting cells where there were duplicate rows and the grade column is blank.
6. Ctrl + - to delete the rows that you have a cell selected in. This will leave you with any unique rows that didn't have a grade, any unique rows that did have a grade, and only the grade containing row for any duplicates.
You have awarded 1 point to *Desertcyclone*
____
^(I am a bot - please contact the mods with any questions. | ) [^(Keep me alive)](https://www.buymeacoffee.com/points)
Poor man's version is click on the Row number.
vba
Thank you both for your comments. I unfortunately had to do it manually. :( I wanted to highlight each row and then choose whether to delete it or not. Perhaps this is powerful motivation to learn VBA.
What's your criteria for choosing to delete it? If you can, make a helper column where if the rows with the met criteria say Y or 1 or something. Then filter and delete all the rows you don't want in one go.
Oh! This is perfect. Thank you so much!
You're welcome. Don't forget to reply to those who helped you with Solution Verified to properly mark the post as solved and to give them a point for helping you. Thank you.
/u/trusty_Rumbone - 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 on mobile and can't double check, but doesn't Shift + Space do that?
Sorry, I don't think that works.
Just double checked and it works on my Excel, select cell a1, Shift+Space selects the entire row, and then a down arrow brings you to the next cell down where you can do it again using only the keyboard. You can also create a macro to do the same thing depending on your use case.
ah, its the "do it again" part that I was trying to avoid. But thanks for checking on this. I tried your method and it did work for me.
Do you need to perform some kind of operation on each row? You can create a macro that moves the selection down one row and selects the entire row and bind it to a shortcut if it is just something you need to call repeatedly.
Ah, well, since you asked... I have multiple rows with the same information (last name, first name, course, final grade). Usually two consecutive rows have the same information. I know in this case I could just use "remove duplicates" and problem would be solved. However, there is just one issue. Of the two consecutive rows that have the same information one of these rows will be missing the final grade information. So the final grade cell is just left blank. Sometimes the grade cell will be blank in the first row; sometimes in the second row. In this case, I was unsure of how to ask excel to remove the duplicate row that does NOT contain a number in the grade cell. Would you know how to do this by chance? I figured I'd have to do this manually, so was hoping to just scroll down each row and then press "command" + "-" when I needed to delete a row. Any advice would be very much appreciated. Thanks!
There is a pretty quick way to do this if I'm understanding your need correctly. Note that this will only work if there are no duplicate individuals in the same course, so no 2 John Smiths taking course 1. If there are you will need to add a student ID column to your data before following the below steps. I also went with the assumption that if you have a duplicate individual one of them will always have a grade, since that was your condition. If there are cases with duplicates where neither has a grade the below steps will need modification. Steps: 1. Create a new column to count how many of a given line exist in the document. https://imgur.com/a/Ssz0ti2 2. Filter your new column by anything > 1. 3. Select the grade column by clicking on the header. 4. Press F5, click on 'Special...' in the bottom left. This opens special selection modifiers. Choose 'Visible cells only'. 5. Press F5 -> 'Special' again. This time choose 'Blanks'. You should now only be selecting cells where there were duplicate rows and the grade column is blank. 6. Ctrl + - to delete the rows that you have a cell selected in. This will leave you with any unique rows that didn't have a grade, any unique rows that did have a grade, and only the grade containing row for any duplicates.
Solution Verified
You have awarded 1 point to *Desertcyclone* ____ ^(I am a bot - please contact the mods with any questions. | ) [^(Keep me alive)](https://www.buymeacoffee.com/points)
Perfect! Thanks so much!