Transposing data from antiquated format to new format.

j239848

New Member
Joined
Jun 6, 2017
Messages
1
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.

--

Unique ID:122
Item Identifier:L154
Item Path:TEST\01\
Source:BOARD, p. 246
Date Created:9/20/2004
Item Response:Multiple Choice
Correct Answer:B
P-Value:0.7
Item Total Correlation:0.5
Item Text:
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_________________:
(a) the near right-way-line.
(b) the centerline of the road.
(c) the far right-of-way line
(d) the center of the pavement.
(e) the mid-point between the curbs.

<colgroup><col width="201" style="width:151pt"> <col width="64" style="width:48pt"> </colgroup><tbody>
</tbody>


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.

Unique ID:Item Identifier: Source:etc.Item Text:(a)(b)(c)(d)(e)
Unique_IDItem NameSourceItem TextAnswer 1Answer 2Answer 3Answer 4Answer 5
122L154BOARD, p. 246As 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_________________:
the near right-way-line.the centerline of the road.the far right-of-way linethe center of the pavement.the mid-point between the curbs.

<tbody>
</tbody>


 

Excel Facts

Easy bullets in Excel
If you have a numeric keypad, press Alt+7 on numeric keypad to type a bullet in Excel.

Forum statistics

Threads
1,213,510
Messages
6,114,048
Members
448,543
Latest member
MartinLarkin

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top