Help with converting data in Excel

  • Thread starter Thread starter bawalker
  • Start date Start date
B

bawalker

Hello all,

I have been working with a client of mine for the last few weeks to
get a simple data conversion taken care of. However it has turned out
to be more difficult than I expected. I was provided a *.txt file
that had tons of columns and extra data that wasn't needed. The
client just wanted the names & addresses from the files so they could
use those for mailing labels.

I was able to use Excel 2003 to convert the data into a spread sheet
where the names and addresses shows up. Currently the data looks like
this in Excel:

John Doe
123 Fake Street
New York, NY 12345

Jane Doe
456 Fake Street
New York, NY 12345

Thus each field is in a seperate row making it nice and readable.
However I spoke with the print shop and they said that this is NOT a
compatible format for their software. They want it in excel, but to
have each name & address appear on one field, seperated by a row, then
the next row with another name & address seperated by another row,
etc. They want it to look like the following:

John Doe 123 Fake Street New York, NY 12345

Jane Doe 456 Fake Street New York, NY 12345


However when I used a feature that I can't remember the name of now,
it puts ALL records in to one long row and looks like the following:

John Doe 123 Fake Street New York, NY 12345 Jane Doe 456 Fake
Street New York, NY 12345


After checking with the print shop, this too is incompatible with
their format. Does anyone know or can advise to get this data in the
format that the print shop is seeking???

Brad
 
I started a new sheet and put in a Edit->Fill->Series, Column, Linear,
Step value=4 and got this in column A:

1
5
9
13
17
21
25
29
33
37
.... (and so on ...)

Then in B1:D1, I put in these formulas:

=INDEX([Book1]Sheet1!$A$1:$A$31,$A1,1) <-belongs in B1
=INDEX([Book1]Sheet1!$A$1:$A$31,$A1+1,1) <-belongs in C1
=INDEX([Book1]Sheet1!$A$1:$A$31,$A1+2,1) <-belongs in D1

Where [Book1]Sheet1 is your name and address data. Then I did a Edit-
Fill->Down.

Assuming your names and addresses are three lines each, this should
work for you. Maybe not elegant, but it will work.
 
I'll give that a try, although with names and addresses, they are not
always in 3 rows. Some may have 4 rows or even 5 rows due to an extra
address line or in some cases the initial conversion left a name like:

John Doe
& Mrs Jane Doe
123 Fake Street
Apartment #567
New York, NY 12345

I would say about half the addresses are in a 4 or 5 line format like
that.
 
OK, names and addresses may be 3, 4 or 5 lines. I'm assuming
(according to your originally supplied data model) that there is a
blank line between the records. So instead of an Edit->Fill->Series in
column A, put a 1 in A1 and put this in A2:

=IF(INDEX([Book1]Sheet1!$A$1:$A$32,$A1+3,1)="",A1+4,IF(INDEX([Book1]
Sheet1!$A$1:$A$32,$A1+4,1)="",A1+5,IF(INDEX([Book1]Sheet1!$A$1:$A
$32,$A1+5,1)="",A1+6,A1+7)))

And Edit->Fill->Down a whole bunch.

You've already put those formulas in B1:D1, so now put these into E1
and F1

=IF(INDEX([Book1]Sheet1!$A$1:$A$32,$A1+3,1)<>"",INDEX([Book1]Sheet1!$A
$1:$A$32,$A1+3,1),"") <-belongs in E1
=IF(AND(INDEX([Book1]Sheet1!$A$1:$A$32,$A1+3,1)<>"",INDEX([Book1]
Sheet1!$A$1:$A$32,$A1+4,1)<>""),INDEX([Book1]Sheet1!$A$1:$A
$32,$A1+4,1),"") <-belongs in F1

And do an Edit->Fill->Down a whole bunch.

This solution won't fix things like:

John Doe
& Mrs Jane Doe

....being on two lines, but it *should* compensate and autorepair the
datapull if you make manual corrections as you progress down the
column looking for anomolies.
 
You said that you (or your print shop) want the names and addresses
combined into one field, with a blank row between each. If that is the
case, then insert a new blank row at the top of your sheet and insert
a new worksheet. Put "x" in A1 of Sheet2 and in A2 you can put this
formula:

=IF(OR(Sheet1!A1="",Sheet1!A2=""),"x","")

Copy this down as far as you need (i.e. for as many rows as there are
in Sheet1). Then in B2 you can use this formula:

=IF(AND(A2="x",A1="x"),Sheet1!A2&" "&Sheet1!A3&" "Sheet1!A4,"")&IF
(A5="x",""," "&Sheet1!A5)&IF(OR(A5="x",A6="x"),""," "&Sheet1!A6)

Then copy this formula down (select B2 and double-click the fill-
handle, which is the small black square at the bottom right corner of
the cursor). Then you can highlight columns A and B by clicking on the
column identifiers and fix the values by clicking <copy>, then Edit |
Paste Special | Values (check) | OK then <Esc>.

Select column A and then apply Autofilter to the column - choose
Blanks from the filter pull-down. Starting with row 3, highlight all
the rows that are visible after filtering (although they will not
contain anything, but the row identifier will have changed colour) and
Edit | Delete Rows. Then from the filter pull-down in A1 you can
select All.

Finally, delete column A, and you should be left with all your data in
column A on alternate rows.

Hope this helps.

Pete
 
Select the column and run this macro.

Public Sub TransposePersonalData()
'ken johnson July 29, 2006
'transpose uneven sets of data........must have a blank row between
Application.ScreenUpdating = False
Dim rngData As Range
Dim iLastRow As Long
Dim i As Long
Dim iDataColumn As Integer
iDataColumn = Selection.Column
iLastRow = Cells(Application.Rows.Count, iDataColumn).End(xlUp).Row
i = Selection.Row - 1
Do While ActiveCell.Row < iLastRow
i = i + 1
Set rngData = Range(ActiveCell, ActiveCell.End(xlDown))
rngData.Copy
Cells(i, iDataColumn + 1).PasteSpecial Transpose:=True
rngData.Cells(rngData.Cells.Count + 2, 1).Activate
Loop
Application.CutCopyMode = False
Application.ScreenUpdating = True
End Sub

Delete the orinal column after running the macro.


Gord Dibben MS Excel MVP
 
(e-mail address removed) wrote...
....
I was able to use Excel 2003 to convert the data into a spread sheet
where the names and addresses shows up.  Currently the data looks like
this in Excel:

John Doe
123 Fake Street
New York, NY  12345

Jane Doe
456 Fake Street
New York, NY  12345
Click to expand...

So there's a blank cell between each name/address block?
Thus each field is in a seperate row making it nice and readable. ....
. . . They want it in excel, but to
have each name & address appear on one field, seperated by a row, then
the next row with another name & address seperated by another row,
. . . They want it to look like the following:

John Doe 123 Fake Street New York, NY 12345

Jane Doe 456 Fake Street New York, NY 12345
Click to expand...
....

One 'field'? Do you mean on one _row_? If so, would each line in the
original name/address (so field?) be in a separate column in a single
row, or would the lines from the original name/address be separated
from each other by 2 spaces (or a tab?), so the entire reformatted
name/address would be in a single column in a single row?

If your original data were in multiple rows with blank cells between
name/address blocks and the printer needed different name/address
components (the separate lines/fields? in your original name/address
blocks) in separate columns, you could name your entire original data
range SRC and use formulas of the form (in a new worksheet)

A1:
=T(SRC)

B1:
=IF(A1<>"",T(INDEX(SRC,COUNTIF($A1:A1,"?*")+1)),"")

Fill B1 right into C1:E1.

A2:
=IF(AND(A1="",COUNTIF($A$1:$E1,"?*")<COUNTA(SRC)),
T(INDEX(SRC,COUNTIF($A$1:$E1,"?*")+INT((ROWS($A$1:$A2)+1)/2))),"")

B2:
=IF(A2<>"",T(INDEX(SRC,COUNTIF($A$1:$E1,"?*")+COUNTIF($A2:A2,"?*")
+INT((ROWS($A$1:$A2)+1)/2))),"")

Fill B2 right into C2:E2. Then select A2:E2 and fill down as far as
needed.

There are much, much better tools to do this, especially scripting
languages. Excel is a rather poor text processing tool.
 
Back
Top