LinkBack URL
About LinkBacks
Need some help please. I have a column of numbers like so in column B but what I need is what I'm showing in Column A.
20004 20004
20004
20138 20138
20138
20138
20138
20139 20139
20139
20139
20433 20433
20433
20433
20433
21060 21060
21061 21061
Need some help please. I have a column of numbers like so in column B but what I need is what I'm showing in Column A.
20004 20004
20004
20138 20138
20138
20138
20138
20139 20139
20139
20139
20433 20433
20433
20433
20433
21060 21060
21061 21061
Try this macro
Open up the VBA editor by hitting ALT F11
Please Login or Register to view this content.
Insert a new module by hitting Insert - Module
Paste the macro into the empty sheet
Hit ALT F11 to get back to the worksheet.
Run the macro by going to tools-macro in Excel 2003 or the view ribbon in Excel 2007/2010/2013.
Martin
Select the column. Hit F5 (opens GoTo Window)
Click Special> Blanks> OK (this selects all your blank cells in chosen range)
Type "=" then hit the up arrow
Then CNTRL + ENTER
You may want to then go back and copy >Paste Special Values over the cells to remove the formulas
Last edited by ChemistB; 08-13-2015 at 03:11 PM.
ChemistB
My 2?
substitute commas with semi-colons if your region settings requires
Don't forget to mark threads as "Solved" (Edit First post>Advanced>Change Prefix)
If I helped, Don't forget to add to my reputation (click on the little star at bottom of this post)
Forum Rules: How to use code tags, mark a thread solved, and keep yourself out of trouble
Sorry I don't use VBA so I was looking for a formula solution but I'll see if I can fumble my way through this.
My solution is not VBA and is much simplier than it looks.
I'm selecting Column A, the blank one correct?
I'm getting results of all zeros.??
Select the column with the values and blanks interspersed.
Then F5 (open GoTo Window), then Special>Blanks>OK
You'll see that the blanks are all selected in your active range
without touching anything else, type in = and then up arrow
(in the formula text box, you'll see = A1 or something like that (whichever is the cell above your first blank)
Then CNTRL + ENTER
Select the column with the blanks in it which you want to have filled in - probably Column B. Then follow the rest of ChemistB's instructions. As he suggested, you probably want to finish by copying the entire range then pasting it over itself as values.Originally Posted by shdwfx
Edit: ChemistB beat me to it...
Regards,
Aardigspook
If your problem is solved, please go to 'Thread Tools' above your first post and 'Mark Thread as Solved'.
You don't need to give me rep if I helped, but thanks are appreciated.
Last edited by Aardigspook; 08-13-2015 at 03:31 PM. Reason: beaten to it
Well I must be doing something wrong because it's not working. I selected Column B (with the values and blanks), hit F5, special ->blanks->OK but it's just sitting there in cell B1, not highlighting the column.
Oh and I get to where I see the =A1 in A1 but I hit CNTRL + ENTER and nothing happens.
Okay, I did a step by step screenshots. See if attached helps.
If it's still not working for you, here's an alternative.
Assuming data is in B starting in B1
First copy B1 over to A1
Then In A2 copied down as far as you want
=IF(ISBLANK(B2), A1, B2)
Then copy>Paste Special>Values to remove the formulas.
You last solution is working but only for the second occurrence, the second line of the same material number....not working for the other lines. I followed your step by step and it's still not working for me. Having just tried it one more time it now says No Cells Found after I hit the up arrow.
I figured it out. Very sloppy but it worked. I added in Column A, the values of H on all lines that had a number and L on all of the blank rows. Then I used =IF(AND(A2="H")B2,"") which I copied down to all of the blanks. That filled in all of the second blank lines. Then I used =IF(AND(A6="H"),B6,B5) on the remaining blanks and those were filled in. Like I said, the hard way but at least I got where I was going. Thanks!!
Glad to hear you got the result you needed.
Regards,
Aardigspook
If your problem is solved, please go to 'Thread Tools' above your first post and 'Mark Thread as Solved'.
You don't need to give me rep if I helped, but thanks are appreciated.
Yes, sorry you couldn't get my solutions working.The important thing is that it works for you now.
I kind of like Martin's macro in post #2.
Biff
Microsoft MVP Excel
Keep It Simple Stupid
Let's Go Pens. We Want The Cup.
LOL, I do too. Saved it to my personel macro book.
I worked out why this happened - if row 1 of the selected column is blank, this will give all zeroes. There are two ways round it - either select only the range needed (A3 to A50, or whatever) or put something into row 1 temporarily (like 'xyz', do the F5→Special→Blanks→=→Up→Ctrl-Enter trick, then delete the temporary text.
I know you don't need this info now, but maybe it'll be of use to someone...
Regards,
Aardigspook
If your problem is solved, please go to 'Thread Tools' above your first post and 'Mark Thread as Solved'.
You don't need to give me rep if I helped, but thanks are appreciated.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
Register To Reply
Bookmarks