• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 44
  • Last Modified:

moving data across formula

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
pma111
Asked:
pma111
  • 3
  • 2
  • 2
  • +2
3 Solutions
 
Rgonzo1971Commented:
HI,

You will probably  need vba
pls send example

Regards
0
 
Rob HensonFinance AnalystCommented:
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
 
pma111Author Commented:
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
Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

 
Subodh Tiwari (Neeraj)Excel & VBA ExpertCommented:
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
 
pma111Author Commented:
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
 
Rgonzo1971Commented:
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
 
Subodh Tiwari (Neeraj)Excel & VBA ExpertCommented:
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
 
pma111Author Commented:
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
 
frankhelkCommented:
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Cloud Class® Course: Microsoft Windows 7 Basic

This introductory course to Windows 7 environment will teach you about working with the Windows operating system. You will learn about basic functions including start menu; the desktop; managing files, folders, and libraries.

  • 3
  • 2
  • 2
  • +2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now