Solved

moving data across formula

Posted on 2016-08-26
9
32 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 49

Expert Comment

by:Rgonzo1971
ID: 41771353
HI,

You will probably  need vba
pls send example

Regards
0
 
LVL 32

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
VMware Disaster Recovery and Data Protection

In this expert guide, you’ll learn about the components of a Modern Data Center. You will use cases for the value-added capabilities of Veeam®, including combining backup and replication for VMware disaster recovery and using replication for data center migration.

 
LVL 29

Assisted Solution

by:Subodh Tiwari (Neeraj)
Subodh Tiwari (Neeraj) earned 334 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 49

Assisted Solution

by:Rgonzo1971
Rgonzo1971 earned 166 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 29

Accepted Solution

by:
Subodh Tiwari (Neeraj) earned 334 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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

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

Introduction This Article is a follow-up to my Mappit! Addin Article (http://www.experts-exchange.com/A_2613.html), it was inspired by an email posting I made to EUSPRIG (http://www.eusprig.org/index.htm), I will briefly cover: 1) An overvie…
This article descibes how to create a connection between Excel and SAP and how to move data from Excel to SAP or the other way around.
The viewer will learn how to use the =DISCRINV command to create a discrete random variable, use this command to model a set of probabilities and outcomes in a Monte Carlo simulation, and learn how to find the standard deviation of a set of probabil…
This Micro Tutorial demonstrates in Microsoft Excel how to consolidate your marketing data by creating an interactive charts using form controls. This creates cool drop-downs for viewers of your chart to choose from.

809 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