Formula to move a row to another sheet?
I have 58386 rows...
Column A has numbers (SKU's) and I need to move all those that are > 11 numbers to Sheet 2
=LEN(A1:A58386)>11
This works, but I need to move all the data from the entire row that satisfies [=LEN(A1:A58386)>11] to a second sheet NOT just Column A, and id like the code to OMIT (not look at) any data that has letters, if that is doable.
Any help appreciated.
Thanks, Chris
Last edited by chef423; 03-22-2017 at 06:05 PM.
I must have posted this in the wrong forum?
Last edited by chef423; 03-22-2017 at 06:53 PM.
when you say move to you mean just replicate the row or physically move it? This looks like it may be a VBA solution
Replace? Yes, just move the data from sheet 1 to sheet 2Originally Posted by philaugust2004
If you don't mind using vba, try this.. hope it helps
Please Login or Register to view this content.
I guess the noob question is, how and where do I run this?If you don't mind using vba, try this.. hope it helps
Please Login or Register to view this content.
Thank you!
If you have the developer tab visible on your worksheet, you can just insert a Command Button on your Sheet1 and then right click the button and click to view code. paste the code between the Private Sub line and the End Sub line. then make sure you turn off 'Design Mode' in your developer tab and click the button. If you do not have the developer tab showing, google how to get it. It's very easy to find. If you have further questions just let me know i'm happy to help where I can.
PS. You may need to save your workbook as a Macro-Enabled Workbook.
Last edited by unit285; 03-23-2017 at 11:08 AM.
Very kind of you!! Thanks, Unit. Ill google the Dev tab..
Ok, so I click on the Developer tab, then Visual Basic...I pasted the code in. I dont see how to execute this. What am I doing wrong?
Go to the developer tab, then in the "Insert" dropdown find Command Button, click that then click on your sheet to create the button on your sheet. Once the button is on your sheet, right click it and click View Code, then insert the code between the Private Sub line and the End Sub line. then make sure you turn off 'Design Mode' in your developer tab and click the button to run the code.
http://prntscr.com/enlrkj
http://prntscr.com/enlsg0
http://prntscr.com/enlt4c
http://prntscr.com/enltsj
Last edited by unit285; 03-23-2017 at 01:58 PM.
Thanks. I now have my data in the second sheet. I have used =LEFT(A1, LEN(A1)-1) to remove the last digit from the 12 numbered column (they are SKU's from retail items) now I want to compare the two sheets and give me which rows do not match From Sheet1 & Sheet2. I assume this can be done without complex code?
Do you need to compare every cell in each row on each sheet? or just 1 column from each sheet?
Just for the record, formulas cannot move, delete, copy, edit or anything else to other cells, they only work on the cell they reside in
1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
2. If your question is resolved, mark it SOLVED using the thread tools
3. Click on the star if you think someone helped you
Regards
Ford
Just the one column...the additional data in the rows are not pertinent.
Could use a formula for that.. typing from my phone so cant test this:
Formula:
Please Login or Register to view this content.
Last edited by unit285; 03-25-2017 at 12:11 AM.
How about, 'IF they match any cell to any cell from A column? Anotherwords, A1 may be the same as say, A5...but I just need to see if any values match no matter the order.Could use a formula for that.. typing from my phone so cant test this:
Formula:Please Login or Register to view this content.
Last edited by chef423; 03-26-2017 at 09:09 PM.
So if I understand correct you are wanting to find duplicates in column A, Are you wanting to remove all duplicates? or just know when they occur?
Just know they occur...mainly.
Thanks again for your tutelage.
Well.. I know you were hoping for a non vba solution.. But i gotta admit I'm not as familiar with formulas..
Here is a vba code that should color red any Duplicate cells in Column A of Sheet1. You would run it the same way as the other code.. you could insert a 2nd command button etc..
Please Login or Register to view this content.
I am attempting to compare column A with Column B, but, Column A has 12 digits and Column B has 11....I need to try and match the first 11 numbers. So if the first 11 match, highlight it red, or something.
Column A Column B
101200000759 10120000075
So the example above would be a match.
will VLOOKUP work here?
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks