Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

Need help with an excel formula

Posted on 2014-03-05
11
Medium Priority
?
296 Views
Last Modified: 2014-03-09
I need a excel formula to move data from one cell to another cell with the following condition.

If A1 = NULL then move B1 to A1 else both remain the same. The same for B1 and C1

Sample:
sample
Notice that row 7 was fine and no data moving was needed there.
0
Comment
Question by:Gerhardpet
  • 3
  • 2
  • 2
  • +2
10 Comments
 
LVL 10

Expert Comment

by:etech0
ID: 39907288
Sounds like you would be better off with a macro. Something like:
Sub macro()
For x = 2 To ActiveSheet.UsedRange.Rows.Count
    If Cells(x, 1).Value = "" Then
        Cells(x, 1).Value = Cells(x, 2).Value
        Cells(x, 2).Value = Cells(x, 3).Value
        Cells(x, 3).Value = ""
    End If
Next
    
End Sub

Open in new window


Note: you would have to put that into a blank module in the VB Editor.
0
 
LVL 15

Expert Comment

by:DrTribos
ID: 39907307
I am not sure if what you want is possible with formulas alone, at least not on the same sheet.  

Reason is that the input for the formula changes so I think you end up with a circular type of reference.

Would you be happy to have your original sheet as raw data and a 2nd sheet as corrected data?

Other wise you can definitely do it with a VBA macro Or by processing the data to additional rows and then manually deleting after copying and pasting 'without formulas'.

HTH
0
 
LVL 1

Author Comment

by:Gerhardpet
ID: 39907309
I have no clue how to use a macro. How would I use it or set it up?
0
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
LVL 10

Expert Comment

by:etech0
ID: 39907333
Press alt-F11 to open the VB Editor.

Click Insert > Module.

Paste the code above into the blank window that opens. Note: You should probably change the word "macro" in the code to something that describes what we're doing. EG: FixAddresses.

How to run the macro would depend on what version of excel you're using. You can tell us what version for more specific instructions, or google "run macro in excel 2010" or whatever version it is.

Good luck!
0
 
LVL 43

Accepted Solution

by:
Saqib Husain, Syed earned 2000 total points
ID: 39907351
Without a macro:

Select the column A data........In this case......A2:A4
Press F5
Click on special
Select Blanks
OK
Now right-click on one of the selected blank cells
Select delete
Select Shift cells left
Click OK
0
 
LVL 1

Author Comment

by:Gerhardpet
ID: 39907357
I'm using Excel 2010
0
 
LVL 13

Expert Comment

by:Santosh Gupta
ID: 39907375
you can also try this.... it will move all cell to left in case of blank cell.

Sub Foo()
Lr = Range("A" & Rows.Count).End(xlUp).Row
Set Rng = Range("A1:A" & Lr)
For Each C In Rng
    If C = "" Then
    C.Delete Shift:=xlToLeft
    End If
Next C
End Sub
0
 
LVL 1

Author Closing Comment

by:Gerhardpet
ID: 39907381
Syed,
Your solution worked perfect for what I wanted. Thanks for your help!
0
 
LVL 15

Expert Comment

by:DrTribos
ID: 39907400
Syed - I'd have mucked around with formulas for an hour or macros for a minute to sort this, learnt something simple and new :-)  - thanks +1
0
 
LVL 43

Expert Comment

by:Saqib Husain, Syed
ID: 39907512
Be my guest.
0

Featured Post

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Windows Explorer lets you open cabinet (cab) files like any other folder. In VBA you can easily handle normal files and folders, but opening and indeed creating cabinet files takes a lot more - and that's you'll find here.
If Skype for Business came with your office 2016 or office 365 installation, you may find that it's almost impossible to either disable or remove it. The application will often launch with each start of Windows, even when explicitly configured not t…
In this video you will find out how to export Office 365 mailboxes using the built in eDiscovery tool. Bear in mind that although this method might be useful in some cases, using PST files as Office 365 backup is troublesome in a long run (more on t…
Enter Foreign and Special Characters Enter characters you can't find on a keyboard using its ASCII code ... and learn how to make a handy reference for yourself using Excel ~ Use these codes in any Windows application! ... whether it is a Micr…

564 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question