Solved

moving data across formula

Posted on 2016-08-26
9
34 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 2
  • 2
  • +2
9 Comments
 
LVL 50

Expert Comment

by:Rgonzo1971
ID: 41771353
HI,

You will probably  need vba
pls send example

Regards
0
 
LVL 33

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
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 30

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 50

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 30

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

Free Tool: Postgres Monitoring System

A PHP and Perl based system to collect and display usage statistics from PostgreSQL databases.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

How to quickly and accurately populate Word documents with Excel data, charts and images (including Automated Bookmark generation) David Miller (dlmille) Synopsis In this article you’ll learn how to use ExcelToWord! to copy data,charts, shapes …
This code takes an Excel list of URL’s and adds a header titled “URL List”. It then searches through all URL’s in column “A”, looking for duplicates. When a duplicate is found, it is moved to the top of the list. The duplicate URL’s are then highlig…
This Micro Tutorial demonstrate the bugs in Microsoft Excel for Mac with Pivot Charts.
This Micro Tutorial will demonstrate how to use longer labels with horizontal bar charts instead of the vertical column chart.

756 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