About Us

Pete

Thanks, try

"Bernie Deitrick" wrote:

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

Thread Tools	Search this Thread
Show Printable Version	Search this Thread: Advanced Search
Display Modes
Switch to Linear Mode Switch to Hybrid Mode Threaded Mode

Similar Threads
Thread	Thread Starter	Forum	Replies	Last Post
Excel multiple variable data table	Evan McCarthy	Excel Discussion (Misc queries)	0	February 14th 06 07:35 PM
Linking Large Access Table into Excel	Steven M. Britton	Links and Linking in Excel	1	December 30th 05 11:28 PM
Data from Excel to Access Table	Secret Squirrel	Excel Discussion (Misc queries)	11	December 2nd 05 11:58 PM
Excel Spreadsheet from Access. List of names changes as names are	Gordy w/Hi Expectations	Excel Discussion (Misc queries)	1	October 21st 05 03:30 AM
data transfer from Excel to Access but the new table stru is diff	Deepa	Excel Discussion (Misc queries)	0	July 7th 05 04:19 PM

Menu

About Us