Excel: move every other row to a new column
Vložit
- čas přidán 13. 07. 2024
- Need to move every other row in Excel to another column? It is easy to do with the ISODD or ISEVEN function. In this video, I use ISODD / ISEVEN with IF and the ROW function. Finally, in this video, I filter the data and remove the blanks.
Chapters:
0:00 Intro
1:20 Row function
1:50 ISODD/ISEVEN
2:34 Row Function to number list
4:30 IF/ISODD/ROW
6:20 Delete - shift up
6:50 Filter to delete blank rows
Row Function
The Row function identifies what row you are working in. =ROW() returns 2 if you are in row 2. =ROW() returns 7 if you are in row 7. =ROW(120) returns 120 if you are in row 120. =ROW(4)-1 returns
3 if you are in row 4. It takes 4 and subtracts 1.
ISODD Function
The ISODD function returns true or false. An odd number is TRUE and an even number is FALSE. =ISODD(7) returns TRUE. =ISODD(14) returns FALSE.
ISEVEN Function
The ISEVEN returns true or false. An even number is TRUE and an odd number is FALSE. =ISEVEN(7) returns FALSE. ISEVEN(14) returns TRUE.
The file used in this video:
chrismenardtraining.com/Files...
#excel #microsofexcel #exceltips
Chris Menard's website: chrismenardtraining.com
Cristian's CZcams Channel: / @graphicious
And make sure you subscribe to my channel!
- EQUIPMENT USED --------------------------------
○ My camera - amzn.to/3vdgF5E
○ Microphone - amzn.to/3gphDXh
○ Camera tripod - amzn.to/3veN6Rg
○ Studio lights - amzn.to/3vaxyy5
○ Dual monitor mount stand - amzn.to/3vbZSjJ
○ Web camera - amzn.to/2Tg75Sn
○ Shock mount - amzn.to/3g96FGj
○ Boom Arm - amzn.to/3g8cNi6
- SOFTWARE USED --------------------------------
○ Screen recording - Camtasia - chrismenardtraining.com/camtasia
○ Screenshots - Snagit - chrismenardtraining.com/snagit
○ CZcams keyword search - TubeBuddy - www.tubebuddy.com/chrismenard
DISCLAIMER: Links included in this description might be affiliate links. If you purchase a product or service with the links I provide, I may receive a small commission. There is no additional charge to you! Thank you for supporting my channel, so I can continue to provide you with free content each week! - Jak na to + styl
I am going to bookmark this forever! You have no idea how I was cratching my head and trying to figure out what I need to do to get this done. I love it!
Exactly what I wanted - no waffle and straight to the answer whilst explaining the steps. Superb.
Thank you.
Straight Forward. Clear. Great Visual and Audio Instructions. Saved me a TON of Time! Thank You Brotha! Excellent Video. New Sub!
This was exactly what I needed. I had data with associated entries stacked on top of eachother because it was a dead pull from two different systems. I was able to get things all lined up exactly how I needed. Thank you for clearly writing this out and giving a solid explanation of what everything is doing. Glad it all still works three years later.
Thank you. I'm glad it is still working also. :-)
Thank you! Using your video, I was able to modify it and figured out a way to complete a task at work in 30 minutes rather than 4 hours.
I love learning new things. This is exactly what I needed. Thank you.
This is awesome. Worked great for a list of 4000 companies and addresses. Thank you!!
That is awesome! Thanks for letting me know it worked.
Thank you so much for this video and your extra explanations about each function! Perfect for what I'm working on today and will also help in the future.
This was so helpful and a great intro to IF and IS statments in excel. I really appreciate this!
Thanks a lot, Chris! You saved me a lof of time and headache, couldn't ask for a better Excel tutorial!
You're welcome! Thanks, Caesar11.
This man is a genius. . . Exactly what I needed. Felt like you could read my mind.
Thank you! I'm not sure about genius, but I appreciate the comment. :-)
Perfect Chris, just what I needed for a quick task. Not only did it do what I needed, similar to the example but it is very flexible and efficient so I will keep a note the method for other use cases. Great teaching style and yep, now another subscriber! Thank you 😀
Just saved my life...subscribed.
Thanks, Monique!
What I was exactly looking for. Thanks for keeping it simple :) Absolutely brilliant!
You're welcome!
So Helpful. Thank you for the clear steps and easy to understand:)
🙂
This was so so helpful thanks! very clearly explained
Thank you! Easy to follow and worked perfectly
This is amazing! Wonderful tutorial. Thank you so much. You saved me like three hours
You're welcome!
Thank you, Chris! This worked perfectly! You saved me lots of time.
Glad it helped!
Thank you Chris, this is awesome! Saved me hours of work!!
You're welcome!
Absolutely brilliant. This is just what I need for an excel task today. Thank you.😀
You're very welcome!
Helpful indeed! Thank you. You save me a bunch of time.
You're welcome!
Thank you a lot! This video lesson has saved me a ton of time! Super helpful!!!
I appreciate the positive feedback. Thank you.
Thank you Sir. Very well produced and easy to understand.
You are very welcome. Thanks, Tom.
Thank you so much, Chris! You are a life saver :)
YOU ARE AMAZING! Thank you so much!
Thanks for a great explanation.
Fabulous teaching.
The row tip for numbering is much appreciated too!
You're very welcome!
@@ChrisMenardTraining it worked well.
I ended copying the "values only" onto a new Sheet in correct alignment, and then filtering/deleting the blank cells. With almost 3000 rows in each of my 2 columns so this was WONDERFUL!
Thank you so much and so clearly explained!
Perfect explanation! Thank you.
You bet. Thanks, Stephen.
Absolute life saver. Pure gold thank you :)
Thank you!
Exactly what i was looking for :) Thank You :)
Glad I could help!
great - thank you so much, very easy to follow and exactly what I needed. Will be subscribing.
Awesome, thank you!
Thank you so very much, very clear and easy to follow, Thank you !
You're very welcome!
thanks so much, your method is way easier compared to other youtube videos ive seen
Glad to hear that!
Thanks for this! Great explanation.
You are welcome. Thanks for visiting.
Such a great help! Worked like a charm!
Thank you!
magic
wow
i was searching for this but i didn't know how to search for this
haha
many thanks dude , big help
WOW !!! I didnt know if this could be done. Thank you so much helped me out alot
You're welcome!!
Thanks...came in handy at just the right time
Glad to help. Thanks, Harsh,
Hello from the UK! Brilliant video Chris, thank you so much as I need this for a task at work. I have client lists and next of kin contact details for each client are on every other row, I will test this tomorrow as I think think this will work perfectly for moving the data from every other second row, to the first row. Thanks so much! Subscribed too ☺️
Fantastic! Thank you.
Chris thank you so much. Trully appreciate this video.
My pleasure!
Exactly what I need. You are awesome. Thanks a lot for this help. I have subscribe to your channel. Keep making such helpful contents
Thank you, Mohan! I appreciate the sub!
Thanks man. Auto subscribed
This really saved me! Thank you so much!!!!!!!!!!!!!!
Jazmine, glad to help.
Thanks Chris!
Thank you, you helped a lot with my sheet :)
Thank you.
I am having a hard time looking for the correct formula for my today's task. This video of yours helped me a lot :) Thanks!
Excellent! Thanks, Willen .
It's just amazing....you taught so calm ....🎉🔥
Thank you so much 😀
Thanks!
Very helpful, thanks!
You're welcome!
Thank you so much!
You're welcome!
Didn't work for me first, but then I realized that when you hit comma, I have to use Semikolon....But you saved my day (had a list with +1.000 entries I had to get sorted) THANKS A LOT
Thank you! That one keyboard key can get you. :-) I'm glad you got it to work.
@@ChrisMenardTraining wait, where do I need to use a semicolon...I'm in Europe. because this does not work for me - does this work for Excel 365?
oh wow - this is what I used - =WENN(ISTUNGERADE(ZEILE());A1;"") and it worked. (using a German version of Excel)
excellent tutorial
Thank you.
Thank you!!!
You're welcome!
Thank you. Awesome.
You bet!
wow! thank you!
welcome.
thank you, my dear
Welcome 😊
Thank you
You're welcome
That was great. Thnx
You're welcome!
amazing :)
Thank you.
Awesome! + new subscriber
Thanks for subbing!
Helps a lot. btw in my excel IF function uses ; instead of ,
That sounds like a Windows setting, but as long as it is working, that's all that matters. :-)
loved it
Thank you.
Hey Chris, I have a problem, I seem to not be able to use the function and Excel keeps popping up the message "there's a problem with this formula" (but in Spanish, I use it in Spanish because it's my main language). it keeps on saying that I'm not trying to use a formula and to rewrite the formula for it to just output text
what do I do?? i really need to separate even and odd values but doing it by hand would be incredibly tedious and hard...
Also, I use mac if that of any importance. Thanks for any help you can provide
Great information, which I'm sure will come handy in the future. How can I transfer data from Column B into Column A, If I have 100 part numbers in Column A and I need to insert a serial number from column B into Colum A after every other cell? For example, the Serial number has to be inserted below it's Part number, not across in row B.
Very nice! What if I want to copy every 5th row?
Good tip but for me (Mac) every time I click "delete row" (after applying the filter) the filter is no longer selected and ALL my highlighted rows are gone. Have you run across this before? Any tips? tnx
6:17 and 7:11 How do I select all items, if let's say, my data is not in 5 rows, but there is 500 rows of data?
Thank you
Excellently done. You refer to chapters. Where are those chapters to be found?
Those are the bookmarks throughout the video. The blue dots with the timestamps.
drive.google.com/file/d/1I5AM-SVzK-WvtQD3Vdxkf5hplUOLjjv3/view?usp=drivesdk
with excel (16.0.14326) - values is separted by ';" instead of "," so the command is
=IF(ISODD(ROW());A2;"")
What if the original data in Column A spans 3 rows E.g., Row 1 = "Delta Airlines", Row 2 = "11 Main Ave. Atlanta GA 30303" and Row 3 = "800 123 4567". Is there a formula to move data from Rows 2 and 3 to Columns B and C respectively?
When I type =IF(ISODD(row())),A2,"" Excel does not recognize as a formula. Can anybody help me?
ice man