Solved

moving data across formula

Posted on 2016-08-26
9
29 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 48

Expert Comment

by:Rgonzo1971
Comment Utility
HI,

You will probably  need vba
pls send example

Regards
0
 
LVL 31

Expert Comment

by:Rob Henson
Comment Utility
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
Comment Utility
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
 
LVL 28

Assisted Solution

by:Subodh Tiwari (Neeraj)
Subodh Tiwari (Neeraj) earned 334 total points
Comment Utility
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
Top 6 Sources for Identifying Threat Actor TTPs

Understanding your enemy is essential. These six sources will help you identify the most popular threat actor tactics, techniques, and procedures (TTPs).

 
LVL 3

Author Comment

by:pma111
Comment Utility
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 48

Assisted Solution

by:Rgonzo1971
Rgonzo1971 earned 166 total points
Comment Utility
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 28

Accepted Solution

by:
Subodh Tiwari (Neeraj) earned 334 total points
Comment Utility
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
Comment Utility
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 13

Expert Comment

by:frankhelk
Comment Utility
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

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

Drop Down List with Unique/Distinct Values (enhancing the Combo-Box with a few steps and a little code) David miller (dlmille) Intro Have you ever created a data validation list from a database field or spreadsheet column (e.g., Zip Codes or Co…
This article will guide you to convert a grid from a picture into Excel format using Microsoft OneNote and no other 3rd party application.
Viewers will learn the basics of slicers and timelines for both PivotTables and standard Excel tables in Excel 2013.
This Micro Tutorial will demonstrate how to use a scrolling table in Microsoft Excel using the INDEX function.

744 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

Need Help in Real-Time?

Connect with top rated Experts

15 Experts available now in Live!

Get 1:1 Help Now