VBA and Macros for Microsoft Excel Question-custom transpose

[anna2005]

Question:

I want to take the data I have which is in one column:

ColumnA

Record1xxx
Descriptionxxx
Typexxx

Record2xxx
Descriptionxxx
Typexxx

Record3xxx
Descriptionxxx
Typexxx


and transpose it across so that it is in a database format.

I tried the code from your book, but when I type it in my worksheet, it doesn't work.

Is this the best code to use to tackle this problem?

 

[Tom Urtis]

Hard to say unless you explain...

- what section of the book (chapter and page number) where "the code" is that you are asking about.

- how your original source data is really laid out: empty row in between records, always 3 rows per record, what row does it start on, etc.

- how exactly your expected results should look like: what does "database format" mean to you, should the original data column be taken over by a new column or left intact, any other data on the sheet needing to be avoided, etc..

 

[anna2005]

Here's Some Dummy data.

The code was on page 316 - it is called Custom Transpose

My sample data comes down as such

COLUMN A
Field1=ABC123
Field2=BLUE
Field3=Soccer
Field4=999999
LocationID=426
Street1= Main Street
Street2=Downstream
City=Houston
Country=USA
Application="DNST"
====================================================
====================================================
Field1=ABC124
Field2=Red
Field3=Football
Field4=888888
LocationID=426
Street1= Main Street
Street2=Downstream
City=Houston
Country=USA
Application="DNST"
====================================================
====================================================
Field1=ABC125
Field2=Orange
Field3=Swimming
Field4=777777
LocationID=426
Street1= Main Street
Street2=Downstream
City=Houston
Country=USA
Application="DNST"
====================================================
====================================================


I need it to appear in sheet two Across as such:

Field1 Field2 Field3 Field4
ABC123 Blue Soccer 426
ABC124 Red Football 426
ABC125 Orange Swimming 426

 

[Tom Urtis]

I asked you a couple questions you did not answer which would help someone here to understand your original data layout and what code to suggest.

Maybe you don't know the answers, but if you do know, can you tell us:

- What row the data starts on

- Is there an empty row in between recordsets, or 2 empty rows, or no empty rows and all the data is contiguous, defined (maybe) by instances of the value "Field1".

- Always the same count of rows per recordset (10 in each case in your last post but 3 in the example given in your first post).

And based on your last post, a new question if you know, do you only want the first 4 fields to be populated in the transposition even though each recordset has more field info than that, which by your inference should be ignored. Odd if so but that's the kind of info people would need to know in order to offer some kind of useful code suggestion.

 

[anna2005]

Sorry about not giving you enough info.

The data starts on row1.
I would want it to pull all the fields across.
There are the dashed lines between each record.
There are no blank rows.

 

[anna2005]

I forgot to say....the code that I tried was on page 316

 

[Tom Urtis]

You did say that about page 316 in your second post.

I have three last questions for you, regarding this first part of your data example:

COLUMN A
Field1=ABC123
Field2=BLUE
Field3=Soccer
Field4=999999
LocationID=426
Street1= Main Street
Street2=Downstream
City=Houston
Country=USA
Application="DNST"
====================================================
====================================================
Field1=ABC124


(1)
Do the dashes occupy one row or two rows? In other words, if the data starts in A1 as you said, then what cell is this...
Field1=ABC124
...in; A11, A12, or A13?

(2)
What are those dashed really? They look like strings of equal signs. In some out of the way cell on that sheet, enter this formula:
=CODE(LEFT(A11))
What does that formula return? 61 by any wild chance?

(3)
Is the syntax of each value really [HeaderName EqualsSign FieldValue], such as
"Country=USA"
Is it really like that with the "=" in between header and value.

I'm not trying to bedevil you with details but the code needs to address certain characters and intervals of recordsets so I'm trying to figure out:

(1)
If the dashes were not there how many empty rows would exist in between recordsets.

(2)
What are those row-separating characters that look like =======
Are they ascii code 61 or something else based on the formula I gave you.

(3)
What is the precise delimiter (that is, what separates) header value from record value, maybe it really is an equal sign "=" but without a screen shot I'm suspicious until you clarify.

Those pieces of info should do it, then the code can be done once we know what we really have.

 

[anna2005]

Again, my apologies. The data is given to me a Columnar report type fashion .

1) The dashes occupy two rows
That record begins at A13.
2) Yes, they return 61

3) Yes---it is an equal sign that would separate the header value from the record value.


how can I send you the screen shot? (I am new with the message board)[/img]

 

[Tom Urtis]

This macro does what you say you want based on the kind of data you say you have. It tested fine for me, no problems.

It leaves your original data intact. It uses the Areas property which has a maximum workable count of 8192 noncontiguous ranges, but with 12 rows (10 data + 2 intervening) per recordset it would be impossible to exceed that limitation on a spreadsheet having only 65,536 maximum rows, so we're safe by a long shot.

I assumed you have already created a worksheet whose tab name is Sheet2. If that is not correct, then create a Sheet2, or post back if you want the macro to do it for you.

You did not say what the sheet name is that holds the original data, so activate that sheet first, and run this macro from there:



Sub Test1()
With Application
.ScreenUpdating = False
.DisplayAlerts = False
.EnableEvents = False

Dim asn$, i%, Rowz&, NR&, cell As Range, Area As Range, dSht As Worksheet
asn = ActiveSheet.Name: Rowz = 0
Set dSht = Worksheets("Sheet2")

Sheets.Add After:=Worksheets(Sheets.Count)
Sheets(asn).Cells.Copy Cells
Columns(1).Replace What:="==*", Replacement:="", LookAt:=xlPart

With dSht
.Cells.Clear
.Range("A1:J1").Value = Array( _
"Field1", "Field2", "Field3", "Field4", "LocationID", _
"Street1", "Street2", "City", "Country", "Application")
NR = .Cells.Find(What:="*", After:=[A1], SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row + 1

For Each Area In Columns(1).SpecialCells(2).areas
i = 1
For Each cell In Range(Area.Address)
.Cells(NR, i).Value = Trim(Right(cell.Value, Len(cell.Value) - Application.Search("=", cell.Value)))
i = i + 1
Next cell
Rowz = Rowz + Area.Rows.Count + 1
NR = NR + 1
Next Area

.Columns.AutoFit
End With

ActiveSheet.Delete
.Goto Sheets(asn).Range("a1"), True
Set dSht = Nothing

.EnableEvents = True
.DisplayAlerts = True
.ScreenUpdating = True
End With

MsgBox "Macro is complete !!", 64, "Time for a beer."
End Sub

 

[anna2005]

wow!!! i finally got to try it out!!!\
it works perfectly.....i don't know what else to say thanks for your help and patience.