Transpose a variable length list into Excel / Access Table

[Pete]

Example given of a variable length (library) list, but which may actually be
1000 long. How can I transpose the data into a Table in either Excel or
Access?

LC Control Number 79013607
Published/Created "New York Seabury Press, 1978, c1977."
Personal Name Boros, Ladislaus, 1927-1981.
ISBN 816404402
Dewey Class No. 248/.48/2

LC Control Number 76007353
Personal Name Boros, Ladislaus, 1927-1981.
ISBN 816411999
Dewey Class No. 248/.3

LC Control Number 78052262
Personal Name Boros, Ladislaus, 1927-1981.
ISBN 816421757
Dewey Class No. 231/.4

 

[JLatham]

Lets begin with some basic information: What kind of file is the information
in right now? Is it a Word document, or a .txt ASCII file, or what.

Are you wanting all information in different cells on the same row for one
entry, or do you want it to end up in Excel roughly as you have it laid out
now? I presume the first?

I see you have 2 variations of entries shown now, are there more? The
variation being that only that first one contains the "Published/Created" row
of information.

 

[Pete]

Information illustrated is a text file from Library of Congress Internet
database pasted into Excel. There are many variables (about a dozen, though
the majority would be consistent). I have been thinking that if I can grasp
the principle with a few I can then apply it to a wider selection. I would
start by separating the text in column one so that the label (e.g., LC
Control Number would be in column A; and the actual number would be in column
B)

I want to end up with the Labels in column A being transposed as column
headings along the top row of an Excel spreadsheet, which would be linked to
the MS Access database for import. The data then needs to be added to the
rows beneath the column headings. This is the difficult bit. I can do it for
one set of data, but not for subsequent ones, especially when the row of
headings may not be exactly the same. (Of course, all possible could be types
along the top row beforehand if this makes it more feasible).

Thanks,

Pete

 

[JLatham]

Have you got a link to that document? That would be nice to look at along
with this.

Having it already in Excel makes it pretty nice. Your plan to start by
splitting each current row into columns, etc. is a good, logical way to
approach it.

Here are 2 formulas that should give you a leg up on it, assume first "LC
Control Number 79013607" is on row 1, then in B1 enter this formula:
=IF(ISERR(TRIM(LEFT(A1,FIND(" ",A1)))),"",TRIM(LEFT(A1,FIND(" ",A1))))

The IF(ISERR( portion keeps #VALUE from appearing when you fill this down
the sheet and cell in column A is empty. There are 2 spaces between the
double quotes with then FIND statement, so we look for 2 location of 2 spaces
within the text in An (n being any given row number) and just keep what's to
the left of them.

In C1, type this formula:
=TRIM(RIGHT(A1,LEN(A1)-LEN(B1)))

that will then get the 2nd half of whatever is in column A and strip any
leading/trailing blanks from it. Don't know if what you put in your first
post was copied from your Excel entries or not, but I copied them from the
post into an Excel sheet here and applied the formulas and they worked well.
Only odd thing I noted was that the name, Boros, Ladislaus, ends up with what
looks like a couple of leading spaces - they are not true space characters,
just some odd, 'invisible' characters.

Fill those formulas on down the sheet and you will have started breaking up
the text into two parts.

To get the displayed information in columns B and C to change from a formula
result to an actual value, you can select them and use Copy followed by Edit
| Paste Special and choose the [Values] option. Turns formula results into
values. Then you could do away with column A - or use the Edit | Paste
Special [Values] to copy them to another sheet for more processing if you get
concerned about losing the original information in column A.

 

[Pete]

Thanks, I have been travelling for two days, hence delay.

I post the link to Library of Congress. Click 'Author' and enter, e.g.,
Boros, etc

I then download the Full text version. The Marc format would obviously be
more sophisticated, but one step at a time.

If you have any further ideas I remain highly interested.
Meanwhile I will test out what you have written in your response. It is
certainly the track I want to travel down.

Pete

http://catalog.loc.gov/cgi-bin/Pwebrecon.cgi?DB=local&PAGE=First

Have you got a link to that document? That would be nice to look at along
with this.

Having it already in Excel makes it pretty nice. Your plan to start by
splitting each current row into columns, etc. is a good, logical way to
approach it.

Here are 2 formulas that should give you a leg up on it, assume first "LC
Control Number 79013607" is on row 1, then in B1 enter this formula:
=IF(ISERR(TRIM(LEFT(A1,FIND(" ",A1)))),"",TRIM(LEFT(A1,FIND(" ",A1))))

The IF(ISERR( portion keeps #VALUE from appearing when you fill this down
the sheet and cell in column A is empty. There are 2 spaces between the
double quotes with then FIND statement, so we look for 2 location of 2 spaces
within the text in An (n being any given row number) and just keep what's to
the left of them.

In C1, type this formula:
=TRIM(RIGHT(A1,LEN(A1)-LEN(B1)))

that will then get the 2nd half of whatever is in column A and strip any
leading/trailing blanks from it. Don't know if what you put in your first
post was copied from your Excel entries or not, but I copied them from the
post into an Excel sheet here and applied the formulas and they worked well.
Only odd thing I noted was that the name, Boros, Ladislaus, ends up with what
looks like a couple of leading spaces - they are not true space characters,
just some odd, 'invisible' characters.

Fill those formulas on down the sheet and you will have started breaking up
the text into two parts.

To get the displayed information in columns B and C to change from a formula
result to an actual value, you can select them and use Copy followed by Edit
| Paste Special and choose the [Values] option. Turns formula results into
values. Then you could do away with column A - or use the Edit | Paste
Special [Values] to copy them to another sheet for more processing if you get
concerned about losing the original information in column A.

Information illustrated is a text file from Library of Congress Internet
database pasted into Excel. There are many variables (about a dozen, though
the majority would be consistent). I have been thinking that if I can grasp
the principle with a few I can then apply it to a wider selection. I would
start by separating the text in column one so that the label (e.g., LC
Control Number would be in column A; and the actual number would be in column
B)

I want to end up with the Labels in column A being transposed as column
headings along the top row of an Excel spreadsheet, which would be linked to
the MS Access database for import. The data then needs to be added to the
rows beneath the column headings. This is the difficult bit. I can do it for
one set of data, but not for subsequent ones, especially when the row of
headings may not be exactly the same. (Of course, all possible could be types
along the top row beforehand if this makes it more feasible).

Thanks,

Pete

 

[JLatham]

Great, I'll get myself a copy for an author or two and play around with it as
I have time and see what bells go off in my head. May not be very fast in
coming, someone just backed a 5-ton dump truck up to my desk at work and just
dumped it all there yesterday, and unfortunately my job is not always just
8-5.

JLatham

Thanks, I have been travelling for two days, hence delay.

I post the link to Library of Congress. Click 'Author' and enter, e.g.,
Boros, etc

I then download the Full text version. The Marc format would obviously be
more sophisticated, but one step at a time.

If you have any further ideas I remain highly interested.
Meanwhile I will test out what you have written in your response. It is
certainly the track I want to travel down.

Pete

http://catalog.loc.gov/cgi-bin/Pwebrecon.cgi?DB=local&PAGE=First

Have you got a link to that document? That would be nice to look at along
with this.

Having it already in Excel makes it pretty nice. Your plan to start by
splitting each current row into columns, etc. is a good, logical way to
approach it.

Here are 2 formulas that should give you a leg up on it, assume first "LC
Control Number 79013607" is on row 1, then in B1 enter this formula:
=IF(ISERR(TRIM(LEFT(A1,FIND(" ",A1)))),"",TRIM(LEFT(A1,FIND(" ",A1))))

The IF(ISERR( portion keeps #VALUE from appearing when you fill this down
the sheet and cell in column A is empty. There are 2 spaces between the
double quotes with then FIND statement, so we look for 2 location of 2 spaces
within the text in An (n being any given row number) and just keep what's to
the left of them.

In C1, type this formula:
=TRIM(RIGHT(A1,LEN(A1)-LEN(B1)))

that will then get the 2nd half of whatever is in column A and strip any
leading/trailing blanks from it. Don't know if what you put in your first
post was copied from your Excel entries or not, but I copied them from the
post into an Excel sheet here and applied the formulas and they worked well.
Only odd thing I noted was that the name, Boros, Ladislaus, ends up with what
looks like a couple of leading spaces - they are not true space characters,
just some odd, 'invisible' characters.

Fill those formulas on down the sheet and you will have started breaking up
the text into two parts.

To get the displayed information in columns B and C to change from a formula
result to an actual value, you can select them and use Copy followed by Edit
| Paste Special and choose the [Values] option. Turns formula results into
values. Then you could do away with column A - or use the Edit | Paste
Special [Values] to copy them to another sheet for more processing if you get
concerned about losing the original information in column A.

Information illustrated is a text file from Library of Congress Internet
database pasted into Excel. There are many variables (about a dozen, though
the majority would be consistent). I have been thinking that if I can grasp
the principle with a few I can then apply it to a wider selection. I would
start by separating the text in column one so that the label (e.g., LC
Control Number would be in column A; and the actual number would be in column
B)

I want to end up with the Labels in column A being transposed as column
headings along the top row of an Excel spreadsheet, which would be linked to
the MS Access database for import. The data then needs to be added to the
rows beneath the column headings. This is the difficult bit. I can do it for
one set of data, but not for subsequent ones, especially when the row of
headings may not be exactly the same. (Of course, all possible could be types
along the top row beforehand if this makes it more feasible).

Thanks,

Pete

 

[Pete]

Thanks. Definite interest retained. Do not feel pressed. Better to get this
right. Getting one reference to fall into place for direct append to the
Access database from Excel seems quite viable. It is getting a long list to
neatly line up that I find to be a big challenge. This is being done for a
little monastery library in Eureka, Northern California - as total gift.

Pete

Great, I'll get myself a copy for an author or two and play around with it as
I have time and see what bells go off in my head. May not be very fast in
coming, someone just backed a 5-ton dump truck up to my desk at work and just
dumped it all there yesterday, and unfortunately my job is not always just
8-5.

JLatham

Thanks, I have been travelling for two days, hence delay.

I post the link to Library of Congress. Click 'Author' and enter, e.g.,
Boros, etc

I then download the Full text version. The Marc format would obviously be
more sophisticated, but one step at a time.

If you have any further ideas I remain highly interested.
Meanwhile I will test out what you have written in your response. It is
certainly the track I want to travel down.

Pete

http://catalog.loc.gov/cgi-bin/Pwebrecon.cgi?DB=local&PAGE=First

Have you got a link to that document? That would be nice to look at along
with this.

Having it already in Excel makes it pretty nice. Your plan to start by
splitting each current row into columns, etc. is a good, logical way to
approach it.

Here are 2 formulas that should give you a leg up on it, assume first "LC
Control Number 79013607" is on row 1, then in B1 enter this formula:
=IF(ISERR(TRIM(LEFT(A1,FIND(" ",A1)))),"",TRIM(LEFT(A1,FIND(" ",A1))))

The IF(ISERR( portion keeps #VALUE from appearing when you fill this down
the sheet and cell in column A is empty. There are 2 spaces between the
double quotes with then FIND statement, so we look for 2 location of 2 spaces
within the text in An (n being any given row number) and just keep what's to
the left of them.

In C1, type this formula:
=TRIM(RIGHT(A1,LEN(A1)-LEN(B1)))

that will then get the 2nd half of whatever is in column A and strip any
leading/trailing blanks from it. Don't know if what you put in your first
post was copied from your Excel entries or not, but I copied them from the
post into an Excel sheet here and applied the formulas and they worked well.
Only odd thing I noted was that the name, Boros, Ladislaus, ends up with what
looks like a couple of leading spaces - they are not true space characters,
just some odd, 'invisible' characters.

Fill those formulas on down the sheet and you will have started breaking up
the text into two parts.

To get the displayed information in columns B and C to change from a formula
result to an actual value, you can select them and use Copy followed by Edit
| Paste Special and choose the [Values] option. Turns formula results into
values. Then you could do away with column A - or use the Edit | Paste
Special [Values] to copy them to another sheet for more processing if you get
concerned about losing the original information in column A.

:

Information illustrated is a text file from Library of Congress Internet
database pasted into Excel. There are many variables (about a dozen, though
the majority would be consistent). I have been thinking that if I can grasp
the principle with a few I can then apply it to a wider selection. I would
start by separating the text in column one so that the label (e.g., LC
Control Number would be in column A; and the actual number would be in column
B)

I want to end up with the Labels in column A being transposed as column
headings along the top row of an Excel spreadsheet, which would be linked to
the MS Access database for import. The data then needs to be added to the
rows beneath the column headings. This is the difficult bit. I can do it for
one set of data, but not for subsequent ones, especially when the row of
headings may not be exactly the same. (Of course, all possible could be types
along the top row beforehand if this makes it more feasible).

Thanks,

Pete

 

[Bernie Deitrick]

Pete,

Put your list into column A of one sheet. On another sheet, starting in Cell A1 and going across
Row 1, put the identifiers for the data that you want to extract, for example:

LC Control Number
Published/Created
Personal Name
ISBN
Dewey Class No.

These whould be entered into A1, B1, C1, D1, E1.

Then select those cells (you can have as many as you want, as long as the strings appear within your
database) and name them "Headers" (Select them, then use Insert / Names... Define...)

Then select your sheet with the database of values, and run the macro below.

If you cannot get it to work, I will send you a working example with the sample data you posted.

HTH,
Bernie
MS Excel MVP

Sub MakeDataBase()
Dim myData As Range
Dim myArea As Range
Dim myCell As Range
Dim i As Integer
Dim myRow As Long

myRow = 2

Set myData = ActiveSheet.Range("A:A").SpecialCells(xlCellTypeConstants, 2)

For Each myArea In myData.Areas
For Each myCell In myArea
For i = 1 To Range("Headers").Cells.Count
If InStr(1, myCell.Value, Range("Headers").Cells(1, i).Value) > 0 Then
Range("Headers").Parent.Cells(myRow, i).Value = _
Trim(Replace(myCell.Value, Range("Headers").Cells(1, i).Value, ""))
End If
Next i
Next myCell
myRow = myRow + 1
Next myArea

End Sub

 

[Pete]

Bernie, I would be very grateful if you would send a working example with the
sample data posted. Pete

 

[Bernie Deitrick]

Pete,

You will need to post a working email or contact me privately.....

HTH,
Bernie
MS Excel MVP

 

[Pete]

Thanks, try (e-mail address removed)

Pete,

You will need to post a working email or contact me privately.....

HTH,
Bernie
MS Excel MVP

 

[Bernie Deitrick]

Sent....

HTH,
Bernie
MS Excel MVP

Thanks, try (e-mail address removed)