How to Add a Blank Row to separate Data

[coloradobu]

I have a one column spreadsheet containg over 8000 rows. This is the final result of a customer dump that was provided in a text file.

I parsed out all of the data by importing the data into SQL DB and writing a statment to find and remove the extra junk I did not need. Then final goal is to obtain the names, address, city,state zip USA from the data.

I have that in one column in Excel 2010. I need to insert a row between the CITY,STATE,ZIP, USA fields to create a mailing label sort.

Short of manually inserting a new row betwen the USA field and the next customer's name, I need to find a way to do this with technology.

Formula or Macro..any help appreciated (but hardly know Excel, so need to be given general steps please)

Thank you in advance

 

[dannac]

It would help if you could post a screenshot of how your data is laid out.

But this may be a start......

An empty row after No1, and No2 etc like below

No 1
No 1
No 1
No 1
Emty Row
No 2
No 2
Empty Row
No 3
No 3
No 3
No 3
Empty Row etc....

Sub test()
Dim LR As Long, i As Long
LR = Range("A" & Rows.Count).End(xlUp).Row
For i = LR To 3 Step -1
    If Range("A" & i).Value <> Range("A" & i - 1).Value Then Rows(i).Insert
Next i
End Sub

 

[coloradobu]

Hi - This is a sample of the layout. Each line is a row in the spreadsheet</PRE>
AAAA, DONALD O 1244 A STREET SOME CITY, AZ 12345 USA 10867 125-55-9999 AAA, BUFFY 01/07/1999 Child BDO AAA, SLAYER O 02/13/1956 Self BDO AAAA, DONALD O 1244 A STREET SOME CITY, AZ 12345 USA 10867 125-55-9999 AAA, BUFFY 01/07/1999 Child BDO AAA, SLAYER O 02/13/1956 Self BDO AAAA, DONALD O 1244 A STREET SOME CITY, AZ 12345 USA 10867 125-55-9999 AAA, BUFFY 01/07/1999 Child BDO AAA, SLAYER O 02/13/1956 Self BDO AAAA, DONALD O 1244 A STREET SOME CITY, AZ 12345 USA 10867 125-55-9999 AAA, BUFFY 01/07/1999 Child BDO AAA, SLAYER O 02/13/1956 Self BDO</PRE>

 

[HalfAce]

If your data is laid out like it sounds (to me at least - name, address, city, state, zip all in a single column...) then the suggestion above will result a blank row between (pretty much) every entry.

If, however, you have (for example) all the zip codes in column A, then it should work fine, inserting a blank row where ever the zip code changes.

How exactly is your data set up?
If it's like I suspect (from the wording in your post) then you could do something like Text to Columns to separate each field (name, address, city, etc.) out into their own columns and then run the code above, tweaked to perform on the column containing just the zip codes.

 

[coloradobu]

Well, that did not post pretty.
LastName, FirstName
123 Main Street
City, State, zip USA

I want to have a line inserted after the USA line. That is a common identifier that could be used to locate when to insert a line. I just don't know how to do it.
Thanks

 

[coloradobu]

I tried your post for inserting a line and it works great -- however it puts a line after every row, which is what I do not want. I pretty much want: If the row has USA as a data element, then insert a new row afterwards.

 

[HalfAce]

So... are we to assume you have your data like this?
Row 1, column A = LastName, FirstName
Row 2, column A = 123 Main Street
Row 3, column A = City, State, zip USA

If so then yes, we can just look for "USA" and insert the rows.
If not, then... how? A1 = LastName B1 = FirstName C1 = 123 Main Street (etc.)?

 

[coloradobu]

Correct -
All information is in column A

Row 1, column A = LastName, FirstName
Row 2, column A = 123 Main Street
Row 3, column A = City, State, zip USA
and so on ...

 

[coloradobu]

Any ideas? I really need to have this file done today. Please...& Thank you

 

[dannac]

If your data will always have that format / layout, ths may work.

Sub test()
    
    Dim LR As Long, i As Long
    LR = Range("A" & Rows.Count).End(xlUp).Offset(1, 0).Row

    For i = LR To 1 Step -3

    Rows(i).Insert

        
Next i
End Sub

Make sure you have another saved copy of your work.

Right click on the sheet name lower left

View Code will open VBA editor

Paste the code into that window

Close editor

Tools - Macro - run macro