Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

moving data across formula

Posted on 2016-08-26
9
Medium Priority
?
39 Views
Last Modified: 2016-09-26
I have a batch of data, and in columns A-E, are address fields, (address 1, address 2, address 3, address 4, zip).

In some cases, address 1 is empty, and the application has for some reason stared the addresses in address 2. What I need is a formula to basically say - if column A is blank, move everything from column B-F over a cell so all records start in column A. If column A is not blank, take no action.
0
Comment
Question by:pma111
  • 3
  • 2
  • 2
  • +2
9 Comments
 
LVL 53

Expert Comment

by:Rgonzo1971
ID: 41771353
HI,

You will probably  need vba
pls send example

Regards
0
 
LVL 34

Expert Comment

by:Rob Henson
ID: 41771356
Where  is the data coming from? Is it an import from text file or csv.

I suspect if its an import the first column contains a space and the import is recognising it as a value.

Thanks
Rob H
0
 
LVL 3

Author Comment

by:pma111
ID: 41771363
Hi

it was an extract of data from a 3rd party system, in *.csv format, but that is genuinely how its stored in the database it was extracted from.
0
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
LVL 33

Assisted Solution

by:Subodh Tiwari (Neeraj)
Subodh Tiwari (Neeraj) earned 1336 total points
ID: 41771365
You may try something like this....

Sub DeleteBlankCells()
Dim lr As Long, i As Long
lr = ActiveSheet.UsedRange.Rows.Count
For i = 1 To lr
   If Cells(i, 1) = "" Then
      Range(Cells(i, 2), Cells(i, 5)).Cut Cells(i, 1)
   End If
Next i
End Sub

Open in new window

0
 
LVL 3

Author Comment

by:pma111
ID: 41771392
Sample data:

addr1      addr2      addr3      addr4      zip      
a      a      a      a      1      
      a      a      a      a      1
a      a      a      a      1      
      a      a      a      a      1
0
 
LVL 53

Assisted Solution

by:Rgonzo1971
Rgonzo1971 earned 664 total points
ID: 41771396
Hi,

pls try

Sub DeleteBlankCells()
Set Rng = ActiveSheet.UsedRange.Resize(, 1).SpecialCells(xlCellTypeBlanks)
Rng.Delete shift:=xlShiftToLeft
End Sub

Open in new window

Regards
0
 
LVL 33

Accepted Solution

by:
Subodh Tiwari (Neeraj) earned 1336 total points
ID: 41771405
Why not only this? :)

Sub DeleteBlankCells()
On Error Resume Next
ActiveSheet.UsedRange.Resize(, 1).SpecialCells(xlCellTypeBlanks).Delete xlToLeft
End Sub

Open in new window


Error handling is required because if there are no blank cells in col. A or the code is run twice, the code will throw an error.
0
 
LVL 3

Author Comment

by:pma111
ID: 41810511
I have been off sick for a while so didnt have chance to split the points... happy with the suggestion or if you reopen it I can do it instead
0
 
LVL 14

Expert Comment

by:frankhelk
ID: 41815707
No comment has been added to this question in more than 21 days, so it is now classified as abandoned.

I have recommended this question be closed as follows:

Split:
-- Subodh Tiwari (Neeraj) (https:#a41771405)
-- Rgonzo1971 (https:#a41771396)
-- Subodh Tiwari (Neeraj) (https:#a41771365)


If you feel this question should be closed differently, post an objection and the moderators will review all objections and close it as they feel fit. If no one objects, this question will be closed automatically the way described above.

frankhelk
Experts-Exchange Cleanup Volunteer
0

Featured Post

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

Question has a verified solution.

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

This article will guide you to convert a grid from a picture into Excel format using Microsoft OneNote and no other 3rd party application.
Excel can be a tricky bit of software to get your head around. Whilst you’ll be able to eventually get to grips with the basic understanding of how to get by, there are a few Excel tips that not everybody will even know about let alone know how to d…
The viewer will learn how to use a discrete random variable to simulate the return on an investment over a period of years, create a Monte Carlo simulation using the discrete random variable, and create a graph to represent the possible returns over…
This Micro Tutorial will demonstrate on a Mac how to change the sort order for chart legend values and decrpyt the intimidating chart menu.

885 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