Macro to Reorganize data into format for Access Import

[adibakale]

					MERCHANT NAME
ACCOUNT NUMBER	CARDHOLDER NAME	CARDHOLDER ADDRESS	DATE	AMOUNT	----CITY----COUNTRY----	MCC	DECISION	NOTES
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>

AMOUNT

MERCHANT NAME

CITY

COUNTRY

MCC

DECISION

NOTES

983764398764327

Doe, John L

Doe, Jane H

Doe, John C

Doe, Jane P

123 Street Name

FORESTHILL

CA

11111-2222

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

4,000.00

GRUPO MAYAN

MEXICO

MEXICO

01010

OK

TRAVEL CODE

329847239939393

Doe,John <tbody> </tbody>

Doe, Jane H

123 Street Name

NEEDVILLE

TX

11111-2222

04/27/05

5,190.00

DIAMOND INTERNAL XIV

MEXICO

MEXICO

10101

OK

TRAVEL CODE

8768576857685768

Doe, John C

123 Street Name APT 1

HARTLAND

WI

11111-2222

05/03/05

1,526.25

HOTEL LACENDA DEL MAR

LOS CABOS BCS

Mexico

11010

OK

TRAVEL 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