Transposing data from antiquated format to new format.

[j239848]

I have an export from an old system that I need a macro/vba set up to transpose to an importable format.

Importable format would be to have a line for each record with headers correctly named per import format requirements.

Exported format includes all the values, but they are listed in a table like the one below. There are thousands of records, so it needs to be repetitive.

What I have set up so far works for most fields. I am stuck at Item Text.

The current code I am running searches for the old tag (ie: "Item Identifier:") and returns the value to the right ("L154"):

=INDEX(Raw!B:B, SMALL(IF($A$1=Raw!A:A, ROW(Raw!A1:A20000)-MIN(ROW(Raw!A1:A20000))+1, ""), ROW(Raw!A1)))


B:B because the return value is in column B, $A$1 is a cell that contains the search value, and the matches are in column A.


I need help editing this so that it will return the text that is in the cell three below the matched cell.
I will also need to update the code so that it will concatenate the text in the cells below the matched cell (until an empty cell is found).

Of course, then repeat the process for each instance of "Item Text:" found in the document.

Another issue I have come across is that there will be many files, each with a different number of records. Currently I am using the Autofill functionality to repeat the process for all other instances, but it requires me to know how many records I am looking for. Note the value A255.

Selection.AutoFill Destination:=ActiveCell.Range("A1:A255"), Type:= _
xlFillDefault

Last, I was able to write this to get the answer options reformatted correctly:

Sheets("Clean").Select
Range("P1").Select
Sheets("Raw").Select
Range("A1").Select
For loop_ctr = 1 To 10

Cells.Find(What:="(a)", After:=ActiveCell, LookIn:=xlFormulas, LookAt:= _
xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False _
, SearchFormat:=False).Activate

ActiveCell.Offset(0, 5).Range("A1").Select
ActiveCell.FormulaR1C1 = "=RIGHT(RC[-5],LEN(RC[-5])-4)"
ActiveCell.Copy
Sheets("Clean").Select
ActiveCell.Offset(1, 0).Range("A1").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Sheets("Raw").Select
Next loop_ctr


But again, it uses a loop counter, which means I would need to know the number of records.

I am in way over my head! Any help would be greatly appreciated.

--

[TABLE="width: 265"]
<colgroup><col width="201" style="width:151pt"> <col width="64" style="width:48pt"> </colgroup><tbody>[TR]
[TD="width: 201"]Unique ID:[/TD]
[TD="width: 64, align: right"]122[/TD]
[/TR]
[TR]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]Item Identifier:[/TD]
[TD]L154[/TD]
[/TR]
[TR]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]Item Path:[/TD]
[TD]TEST\01\[/TD]
[/TR]
[TR]
[TD] [/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Source:[/TD]
[TD]BOARD, p. 246[/TD]
[/TR]
[TR]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]Date Created:[/TD]
[TD="align: right"]9/20/2004[/TD]
[/TR]
[TR]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]Item Response:[/TD]
[TD]Multiple Choice[/TD]
[/TR]
[TR]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD][/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD]Correct Answer:[/TD]
[TD]B[/TD]
[/TR]
[TR]
[TD] [/TD]
[TD][/TD]
[/TR]
[TR]
[TD]P-Value:[/TD]
[TD="align: right"]0.7[/TD]
[/TR]
[TR]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]Item Total Correlation:[/TD]
[TD="align: right"]0.5[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Item Text:[/TD]
[TD][/TD]
[/TR]
[TR]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD="width: 265, colspan: 2"]As a general rule, when a parcel of land bounded by a street or[/TD]
[/TR]
[TR]
[TD="width: 265, colspan: 2"]road is conveyed, title to the parcel is presumed to extend[/TD]
[/TR]
[TR]
[TD]to_________________:[/TD]
[TD][/TD]
[/TR]
[TR]
[TD] [/TD]
[TD][/TD]
[/TR]
[TR]
[TD](a) the near right-way-line.[/TD]
[TD][/TD]
[/TR]
[TR]
[TD] [/TD]
[TD][/TD]
[/TR]
[TR]
[TD](b) the centerline of the road.[/TD]
[TD][/TD]
[/TR]
[TR]
[TD] [/TD]
[TD][/TD]
[/TR]
[TR]
[TD](c) the far right-of-way line[/TD]
[TD][/TD]
[/TR]
[TR]
[TD] [/TD]
[TD][/TD]
[/TR]
[TR]
[TD](d) the center of the pavement.[/TD]
[TD][/TD]
[/TR]
[TR]
[TD] [/TD]
[TD][/TD]
[/TR]
[TR]
[TD="width: 265, colspan: 2"](e) the mid-point between the curbs.[/TD]
[/TR]
</tbody>[/TABLE]


Here is the reformatted file as I have it. The top row has the old tag (which the look up is referencing), second row has the new tag (so that I can just delete the top row and be ready for importing), third row is the first return value.

[TABLE="width: 500"]
<tbody>[TR]
[TD]Unique ID:[/TD]
[TD]Item Identifier: [/TD]
[TD]Source:[/TD]
[TD]etc.[/TD]
[TD]Item Text:[/TD]
[TD](a)[/TD]
[TD](b)[/TD]
[TD](c)[/TD]
[TD](d)[/TD]
[TD](e)[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Unique_ID[/TD]
[TD]Item Name[/TD]
[TD]Source[/TD]
[TD][/TD]
[TD]Item Text[/TD]
[TD]Answer 1[/TD]
[TD]Answer 2[/TD]
[TD]Answer 3[/TD]
[TD]Answer 4[/TD]
[TD]Answer 5[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]122[/TD]
[TD]L154[/TD]
[TD]BOARD, p. 246[/TD]
[TD][/TD]
[TD]As a general rule, when a parcel of land bounded by a street or
road is conveyed, title to the parcel is presumed to extend
to_________________:
[/TD]
[TD]the near right-way-line.[/TD]
[TD]the centerline of the road.[/TD]
[TD]the far right-of-way line[/TD]
[TD]the center of the pavement.[/TD]
[TD]the mid-point between the curbs.[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]