Macro to Reorganize data into format for Access Import

adibakale

Board Regular
Joined
Apr 10, 2015
Messages
52
MERCHANT NAME
ACCOUNT NUMBERCARDHOLDER NAMECARDHOLDER ADDRESSDATEAMOUNT----CITY----COUNTRY----MCCDECISIONNOTES
983764398764327

<tbody>
</tbody>
Doe, John L

<tbody>
</tbody>
123 Street Name

<tbody>
</tbody>
05/03/05

<tbody>
</tbody>
4,000.00

<tbody>
</tbody>
GRUPO MAYAN
01010

<tbody>
</tbody>
OK
TRAVEL CODE

Doe, Jane H

<tbody>
</tbody>
FOREST HILL CA

<tbody>
</tbody>


MEXICO MEXICO



Doe, John C
11111-2222

<tbody>
</tbody>







Doe, Jane P






3298472399393939

<tbody>
</tbody>
Doe,John

<tbody>
</tbody>
123 Street Name

<tbody>
</tbody>
04/27/05

<tbody>
</tbody>
5,190.00

<tbody>
</tbody>
DIAMOND INTERNAL XIV

<tbody>
</tbody>
10101

<tbody>
</tbody>
OK

<tbody>
</tbody>
TRAVEL CODE

Doe, Jane H
NEEDVILLE TX

<tbody>
</tbody>


COZUMEL QROO Mexico

<tbody>
</tbody>





11111-2222

<tbody>
</tbody>















8768576857685768

<tbody>
</tbody>
Doe, John C

<tbody>
</tbody>
123 Street Name APT 1

<tbody>
</tbody>
05/03/05

<tbody>
</tbody>
1,526.25

<tbody>
</tbody>
HOTEL LACENDA DEL MAR

<tbody>
</tbody>
11010

<tbody>
</tbody>
OK

<tbody>
</tbody>
TRAVEL CODE
HARTLAND WI

<tbody>
</tbody>
LOS CABOS BCS Mexico

<tbody>
</tbody>
11111-2222

<tbody>
</tbody>

<tbody>
</tbody>
Table 1

ACCOUNT NUMBER

<tbody>
</tbody>
CARDHOLDER NAME

<tbody>
</tbody>
CARDHOLDER NAME

<tbody>
</tbody>
1
CARDHOLDER NAME

<tbody>
</tbody>
2
CARDHOLDER NAME

<tbody>
</tbody>
3
STREET

<tbody>
</tbody>
CITY

<tbody>
</tbody>
STATE

<tbody>
</tbody>
ZIP

<tbody>
</tbody>
DATE

<tbody>
</tbody>
AMOUNTMERCHANT NAMECITYCOUNTRYMCCDECISIONNOTES
983764398764327 Doe, John L
Doe, Jane HDoe, John CDoe, Jane P123 Street Name FORESTHILLCA11111-2222
05/03/05

<tbody>
</tbody>
4,000.00 GRUPO MAYANMEXICOMEXICO01010OKTRAVEL CODE
329847239939393
Doe,John

<tbody>
</tbody>
Doe, Jane H 123 Street Name NEEDVILLETX11111-222204/27/055,190.00DIAMOND INTERNAL XIV MEXICOMEXICO10101OKTRAVEL CODE
8768576857685768 Doe, John C123 Street Name APT 1HARTLANDWI11111-222205/03/051,526.25HOTEL LACENDA DEL MAR LOS CABOS BCSMexico11010OKTRAVEL CODE

<tbody>
</tbody>
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
 

Some videos you may like

Excel Facts

Lock one reference in a formula
Need 1 part of a formula to always point to the same range? use $ signs: $V$2:$Z$99 will always point to V2:Z99, even after copying

Watch MrExcel Video

Forum statistics

Threads
1,127,332
Messages
5,624,053
Members
416,007
Latest member
csf

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
Top