Macro to Reorganize data into format for Access Import

A

adibakale

Board Regular
Joined
Apr 10, 2015
Messages
52
[TABLE="width: 500"]
<tbody>[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]MERCHANT NAME[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]ACCOUNT NUMBER[/TD]
[TD]CARDHOLDER NAME[/TD]
[TD]CARDHOLDER ADDRESS[/TD]
[TD]DATE[/TD]
[TD]AMOUNT[/TD]
[TD]----CITY----COUNTRY----[/TD]
[TD]MCC[/TD]
[TD]DECISION[/TD]
[TD]NOTES[/TD]
[/TR]
[TR]
[TD][TABLE="width: 147"]
<tbody>[TR]
[TD]983764398764327 [/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 130"]
<tbody>[TR]
[TD]Doe, John L [/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 258"]
<tbody>[TR]
[TD] 123 Street Name [/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 69"]
<tbody>[TR]
[TD] 05/03/05[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 64"]
<tbody>[TR]
[TD] 4,000.00 [/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]GRUPO MAYAN
[/TD]
[TD][TABLE="width: 64"]
<tbody>[TR]
[TD] 01010[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]OK
[/TD]
[TD]TRAVEL CODE
[/TD]
[/TR]
[TR]
[TD]
[/TD]
[TD][TABLE="width: 130"]
<tbody>[TR]
[TD]Doe, Jane H[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 258"]
<tbody>[TR]
[TD] FOREST HILL CA [/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]MEXICO MEXICO[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[/TR]
[TR]
[TD]
[/TD]
[TD]Doe, John C
[/TD]
[TD][TABLE="width: 258"]
<tbody>[TR]
[TD] 11111-2222[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[/TR]
[TR]
[TD]
[/TD]
[TD]Doe, Jane P[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/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]
[/TR]
[TR]
[TD][TABLE="width: 147"]
<tbody>[TR]
[TD]3298472399393939 [/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 130"]
<tbody>[TR]
[TD] Doe,John [/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 258"]
<tbody>[TR]
[TD] 123 Street Name [/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 69"]
<tbody>[TR]
[TD] 04/27/05[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 64"]
<tbody>[TR]
[TD] 5,190.00 [/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 212"]
<tbody>[TR]
[TD] DIAMOND INTERNAL XIV [/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 64"]
<tbody>[TR]
[TD] 10101[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 64"]
<tbody>[TR]
[TD="width: 64"]OK[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]TRAVEL CODE[/TD]
[/TR]
[TR]
[TD]
[/TD]
[TD]Doe, Jane H[/TD]
[TD][TABLE="width: 258"]
<tbody>[TR]
[TD] NEEDVILLE TX [/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD][TABLE="width: 212"]
<tbody>[TR]
[TD] COZUMEL QROO Mexico[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[/TR]
[TR]
[TD]
[/TD]
[TD]
[/TD]
[TD][TABLE="width: 258"]
<tbody>[TR]
[TD] 11111-2222[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]
[/TD]
[TD]
[/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]
[/TR]
[TR]
[TD][TABLE="width: 147"]
<tbody>[TR]
[TD] 8768576857685768[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 130"]
<tbody>[TR]
[TD] Doe, John C[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 258"]
<tbody>[TR]
[TD] 123 Street Name APT 1
[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 69"]
<tbody>[TR]
[TD] 05/03/05[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 64"]
<tbody>[TR]
[TD] 1,526.25 [/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 212"]
<tbody>[TR]
[TD] HOTEL LACENDA DEL MAR [/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 64"]
<tbody>[TR]
[TD] 11010[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 64"]
<tbody>[TR]
[TD="width: 64"]OK[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]TRAVEL CODE[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][TABLE="width: 258"]
<tbody>[TR]
[TD] HARTLAND WI [/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][/TD]
[TD][/TD]
[TD][TABLE="width: 212"]
<tbody>[TR]
[TD] LOS CABOS BCS Mexico[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][TABLE="width: 258"]
<tbody>[TR]
[TD] 11111-2222[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][/TD]
[TD][/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]
[/TR]
</tbody>[/TABLE]
Table 1
[TABLE="width: 500"]
<tbody>[TR]
[TD][TABLE="width: 128"]
<tbody>[TR]
[TD="class: xl67, width: 128"]ACCOUNT NUMBER[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 145"]
<tbody>[TR]
[TD="class: xl67, width: 145"]CARDHOLDER NAME[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 145"]
<tbody>[TR]
[TD="class: xl67, width: 145"]CARDHOLDER NAME[/TD]
[/TR]
</tbody>[/TABLE]
1[/TD]
[TD][TABLE="width: 145"]
<tbody>[TR]
[TD="class: xl67, width: 145"]CARDHOLDER NAME[/TD]
[/TR]
</tbody>[/TABLE]
2[/TD]
[TD][TABLE="width: 145"]
<tbody>[TR]
[TD="class: xl67, width: 145"]CARDHOLDER NAME[/TD]
[/TR]
</tbody>[/TABLE]
3[/TD]
[TD][TABLE="width: 160"]
<tbody>[TR]
[TD="class: xl67, width: 160"]STREET[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 112"]
<tbody>[TR]
[TD="class: xl67, width: 112"]CITY[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 45"]
<tbody>[TR]
[TD="class: xl67, width: 45"]STATE[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 85"]
<tbody>[TR]
[TD="class: xl67, width: 85"]ZIP[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]DATE
[TABLE="width: 85"]
<tbody>[TR]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]AMOUNT[/TD]
[TD]MERCHANT NAME[/TD]
[TD]CITY[/TD]
[TD]COUNTRY[/TD]
[TD]MCC[/TD]
[TD]DECISION[/TD]
[TD]NOTES[/TD]
[/TR]
[TR]
[TD]983764398764327 [/TD]
[TD]Doe, John L
[/TD]
[TD]Doe, Jane H[/TD]
[TD]Doe, John C[/TD]
[TD]Doe, Jane P[/TD]
[TD]123 Street Name [/TD]
[TD]FORESTHILL[/TD]
[TD]CA[/TD]
[TD]11111-2222[/TD]
[TD][TABLE="width: 85"]
<tbody>[TR]
[TD] 05/03/05[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD] 4,000.00 [/TD]
[TD]GRUPO MAYAN[/TD]
[TD]MEXICO[/TD]
[TD]MEXICO[/TD]
[TD]01010[/TD]
[TD]OK[/TD]
[TD]TRAVEL CODE[/TD]
[/TR]
[TR]
[TD]329847239939393[/TD]
[TD][TABLE="width: 130"]
<tbody>[TR]
[TD] Doe,John [/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]Doe, Jane H[/TD]
[TD][/TD]
[TD][/TD]
[TD] 123 Street Name [/TD]
[TD]NEEDVILLE[/TD]
[TD]TX[/TD]
[TD]11111-2222[/TD]
[TD]04/27/05[/TD]
[TD]5,190.00[/TD]
[TD]DIAMOND INTERNAL XIV [/TD]
[TD]MEXICO[/TD]
[TD]MEXICO[/TD]
[TD]10101[/TD]
[TD]OK[/TD]
[TD]TRAVEL CODE[/TD]
[/TR]
[TR]
[TD]8768576857685768[/TD]
[TD] Doe, John C[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]123 Street Name APT 1[/TD]
[TD]HARTLAND[/TD]
[TD]WI[/TD]
[TD]11111-2222[/TD]
[TD]05/03/05[/TD]
[TD]1,526.25[/TD]
[TD]HOTEL LACENDA DEL MAR [/TD]
[TD]LOS CABOS BCS[/TD]
[TD]Mexico[/TD]
[TD]11010[/TD]
[TD]OK[/TD]
[TD]TRAVEL CODE[/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]
[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]
[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]
[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]
[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]
[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]
[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]
[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]
[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]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
Table 2
I am working on a business process where data is exported into Excel and I am trying to set up the process in Microsoft Access for tracking purposes. The data that is exported is done on a weekly basis and is normally thousands of records.

The data that is exported to excel is in the Format of Table 1 provided above. I am trying to create a Macro to reorganize the data as shown in Table 2.

The examples I provided show 3 records; the data I am working with has greater than 1,000 records, or rows of data.

This seems pretty complex to do, and it may be easier to accomplish this directly in Access - if so, please let me know.

I would like to reorganize the data so that the data for each record is all on 1 row and organized accordingly. Also, each record in the exported excel file is seperated by 1 blank row.

This is what I need to do:

1. Create additional columns to add (if any) additional CARDHOLDER NAMES. The Cardholder names in the original export file are in Column B.

2. Create additional colums to seperate the CardHolders City, State and Zip.

3. Create additional columns to seperate the Merchant Name, Merchant City, and Merchant Country.

Table 1 is the exported file, Table 2 is what I would like it to look like. Some issues I have noted are that some of the cities cities are 2 or more words, this makes using something like text-to-columns difficult because of the spaces between each word.

Any help with this would be greatly appreciated. Also, if it makes more sense to accomplish this task directly in Access, please let me know.

I appreciate any help/advice provided.

Thank you
 

Excel Facts

Can a formula spear through sheets?
Use =SUM(January:December!E7) to sum E7 on all of the sheets from January through December
You must log in or register to reply here.

Forum statistics

Threads
1,223,214
Messages
6,170,772
Members
452,353
Latest member
strainu

Share this page

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