|
Transpose a variable length list into Excel / Access Table
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 |
Transpose a variable length list into Excel / Access Table
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" wrote: 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 |
Transpose a variable length list into Excel / Access Table
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 |
Transpose a variable length list into Excel / Access Table
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" wrote: 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 |
Transpose a variable length list into Excel / Access Table
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/Pwebr...cal&PAGE=First "JLatham" wrote: 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" wrote: 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 |
Transpose a variable length list into Excel / Access Table
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 "Pete" wrote: 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/Pwebr...cal&PAGE=First "JLatham" wrote: 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" wrote: 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 |
Transpose a variable length list into Excel / Access Table
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 "JLatham" wrote: 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 "Pete" wrote: 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/Pwebr...cal&PAGE=First "JLatham" wrote: 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" wrote: 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 |
Transpose a variable length list into Excel / Access Table
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(xlCellTypeCo nstants, 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" wrote in message ... 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 |
Transpose a variable length list into Excel / Access Table
Bernie, I would be very grateful if you would send a working example with the
sample data posted. Pete "Bernie Deitrick" wrote: 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(xlCellTypeCo nstants, 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" wrote in message ... 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 |
Transpose a variable length list into Excel / Access Table
Pete,
You will need to post a working email or contact me privately..... HTH, Bernie MS Excel MVP "Pete" wrote in message ... Bernie, I would be very grateful if you would send a working example with the sample data posted. Pete "Bernie Deitrick" wrote: 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(xlCellTypeCo nstants, 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" wrote in message ... 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 |
Transpose a variable length list into Excel / Access Table
|
Transpose a variable length list into Excel / Access Table
|
| All times are GMT +1. The time now is 05:47 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com