Another Excel macro question

[steveo275]

Hello,

I've asked about something similar before here:

Excel Macro question.

I have a similar request. The last one worked great. However, I tried to figure out the macro and how to expand it but it's a bit beyond me.

I need my "Header" sheet:

Header

*

A

B

C

D

E

F

G

H

I

J

K

L

M

N

O

P

Q

R

S

T

U

V

W

X

Y

Z

AA

AB

AC

AD

AE

AF

AG

AH

AI

AJ

AK

AL

AM

AN

AO

AP

AQ

AR

AS

1

Ref Key

Type

Customer

Ref Type

Number

PO

Total

Currency

Sales Type

Ref Date

Due Date

Terms

Ship To Name

Add1

Add2

Add3

City

State

Zip

Country

Phone

Fax

Billto

BTAdd1

BTAdd2

BTAdd3

BTCity

BTState

BTZip

BTCountry

BTPhone

BTFax

Discount

Tax

Sales Loc

Notes

Station

Related

Rep1

Rep2

Period

Year

Exempt

Exempt ID

Exchg Rate

2

1000

H

888888

IN

1000

*

100

*

Invoice

1/1/2012

2/1/2012

*

*

*

*

*

*

*

*

*

*

*

*

*

*

*

*

*

*

*

*

*

0

0

CAP

*

*

*

*

*

7

2012

Yes

0

0

3

2000

H

488080

IN

2000

*

100

*

Invoice

1/1/2012

2/1/2012

*

*

*

*

*

*

*

*

*

*

*

*

*

*

*

*

*

*

*

*

*

0

0

CAP

*

*

*

*

*

7

2012

Yes

0

0

<tbody>
</tbody>

Excel tables to the web >> Excel Jeanie HTML 4

To find and match each "ref key" on the "Trailer" sheet:

Trailer

*

A

B

C

D

E

F

G

H

I

J

K

L

M

N

O

P

Q

R

S

T

U

1

Ref Key

Type

Customer

Ref Type

Number

Seq

Qty

UM

Product

Price

Tax

Product Name

Product Description

Detail

Adjst

Ext Price

Cost

Return Code

Ship Loc

Sales Loc

Agent

2

1000

I

888888

IN

1000

1

1

Case

10001

100

No

*

*

*

0

100

50

*

CAP

CAP

88888

3

1000

I

888888

IN

1000

1

1

Case

10002

100

No

*

*

*

0

100

50

*

CAP

CAP

88888

4

2000

I

488080

IN

2000

2

1

Case

10001

100

No

*

*

*

0

100

50

*

CAP

CAP

88888

5

2000

I

488080

IN

2000

2

1

Case

10002

100

No

*

*

*

0

100

50

*

CAP

CAP

88888

<tbody>
</tbody>

Excel tables to the web >> Excel Jeanie HTML 4

And create a new sheet called "Results" that looks like this. So, its saying for each header record "ref key" (ex: 1000) find each trailer record "ref key" with the same number (1000) and then copy the header row exactly and put the each matching trailer row under it.

Results

*

A

B

C

D

E

F

G

H

I

J

K

L

M

N

O

P

Q

R

S

T

U

V

W

X

Y

Z

AA

AB

AC

AD

AE

AF

AG

AH

AI

AJ

AK

AL

AM

AN

AO

AP

AQ

AR

AS

1

1000

H

888888

IN

1000

*

100

*

Invoice

1/1/2012

2/1/2012

*

*

*

*

*

*

*

*

*

*

*

*

*

*

*

*

*

*

*

*

*

*

*

*

*

*

*

*

*

7

2012

Yes

0

0

2

1000

I

888888

IN

1000

1

1

Case

10001

100

No

*

*

*

0

100

50

*

CAP

CAP

88888

*

*

*

*

*

*

*

*

*

*

*

*

*

*

*

*

*

*

*

*

*

*

*

*

3

1000

I

888888

IN

1000

1

1

Case

10002

100

No

*

*

*

0

100

50

*

CAP

CAP

88888

*

*

*

*

*

*

*

*

*

*

*

*

*

*

*

*

*

*

*

*

*

*

*

*

4

2000

H

488080

IN

2000

*

100

*

Invoice

1/1/2012

2/1/2012

*

*

*

*

*

*

*

*

*

*

*

*

*

*

*

*

*

*

*

*

*

*

*

*

*

*

*

*

*

7

2012

Yes

0

0

5

2000

I

488080

IN

2000

2

1

Case

10001

100

No

*

*

*

0

100

50

*

CAP

CAP

88888

*

*

*

*

*

*

*

*

*

*

*

*

*

*

*

*

*

*

*

*

*

*

*

*

6

2000

I

488080

IN

2000

2

1

Case

10002

100

No

*

*

*

0

100

50

*

CAP

CAP

88888

*

*

*

*

*

*

*

*

*

*

*

*

*

*

*

*

*

*

*

*

*

*

*

*

<tbody>
</tbody>

Excel tables to the web >> Excel Jeanie HTML 4

The columns have to remain as is. The program I'm importing this data with is looking for a certain amount of columns. Thanks again.

 

[patel45]

from FAQ

• Make sure you are in the correct forum for the question you are asking.
• Word your questions clearly.
• ALWAYS state what version of Excel and Windows you are using (i.e., Excel 97, Windows XP, etc.)
• Don't use a subject line like HELP ME! or EXCEL question. Instead, say something like Trouble With Pivot Tables In XL97. And remember, saying URGENT doesn't make it so to anyone but you.
• Provide background information for your question when needed. Include example data and formulas in your post. The more you tell us up front, the easier it is for us to answer your questions. If your formulas aren't giving the results you want, include the results you want to get, as well as the results you're getting. Your post should include at least:

 

[steveo275]

My sincerest apologies to the board for not reading the FAQ. I'm sure this has devastated the MrExcel community. I'm not sure how I'm going to sleep at night knowing the pain my subject has caused all of you. We must put this travesty behind us and look forward. It's the only way the healing process can begin.