How to automate the generation of a MASTER worksheet from two worksheets with a different layout?

[Nite0wls]

Dear all,

I'm looking into how I can create a MASTER worksheet from two different worksheets. The two sheets are not equal in layout and the least complex one need to be transformed during the import to match with the other worksheet.

A simplified version of each worksheet is below as well the wanted result.


I would like to automate the building of the MASTER worksheet to prevent mistakes during manual copy and pasting the information. On import of the information an additional field is added to determine the origin of the line and to colour code the lines based on their original worksheet.


Thanks for any suggestions on how to approach this challenge.


First WorkSheet - DNIS:

Tab Pos DNIS

DNIS

Purpose

Brand

Department

Region

Country

Product

DDIUser

Languages

AltWorkgroup

AltDisplay

AltGreeting

AltWhisper

AltSkills

ProductMenu

InQueueAudio

RecordCalls

ScreenPop

Disposition

Holiday

Emergency

BatPhone

SourceDNIS

Carrirer

AdminContact

Notes

1

441173048804

Sales

Regus

SSC

UK

UK

en-GB

IST

IST

TRUE

TRUE

TRUE

BT

Spare Nr

2

441173048805

Sales

Regus

SSC

UK

UK

en-GB

IST

IST

TRUE

TRUE

TRUE

BT

Spare Nr

3

441173048806

DDI

Regus

SSC

UK

UK

Tom.Jones

en-GB

IST

IST

TRUE

TRUE

TRUE

BT

Spare Nr

<tbody>
</tbody>

Second Worksheet - REGUS:

Tab Pos Regus	DNIS	Product	RetailGroup	Alternate Display	Alternate Greeting	Alternate Whisper	Country	Languages	Site
1	81357641200 >		SPARE Nr				Australia	en-AU	APAC
1	81357641201 >		AU Hotline				Australia	en-AU	APAC
1	81357641202 >	MR	AU BMP Line				Australia	en-AU	APAC

<tbody>
</tbody>

Combined Worksheet - MASTER:

DNIS Table Row

DNIS

Purpose

Brand

Department

Region

Country

Product

DDIUser

Languages

AltWorkgroup

AltDisplay

AltGreeting

AltWhisper

AltSkills

ProductMenu

InQueueAudio

RecordCalls

ScreenPop

Disposition

Holiday

Emergency

BatPhone

SourceDNIS

Carrirer

AdminContact

Notes

Table

REGUS Table Row

DNIS

n/a

n/a

RetailGroup

Site

Country

Product

n/a

Languages

n/a

AltDisplay

AltGreeting

AltWhisper

n/a

n/a

n/a

n/a

n/a

n/a

n/a

n/a

n/a

n/a

n/a

n/a

n/a

Table

1

441173048804

Sales

Regus

SSC

UK

UK

en-GB

IST

IST

TRUE

TRUE

TRUE

BT

Spare Nr

DNIS

2

441173048805

Sales

Regus

SSC

UK

UK

en-GB

IST

IST

TRUE

TRUE

TRUE

BT

Spare Nr

DNIS

3

441173048806

DDI

Regus

SSC

UK

UK

Tom.Jones

en-GB

IST

IST

TRUE

TRUE

TRUE

BT

Spare Nr

DNIS

1

81357641200

SPARE Nr

APAC

Australia

en-AU

REGUS

2

81357641201

AU Hotline

APAC

Australia

en-AU

REGUS

3

81357641202

AU BMP Line

APAC

Australia

MR

en-AU

REGUS

<tbody>
</tbody>

 

[oldbrewer]

so the first table is copied in as is and the second table is copied (table 2 col to master col) as below?

1-1......2-2.....4-5....10-6.....8-7.......9-10......5-12......6-13....7-14.....and column with table heading to col 28 ?

 

[Nite0wls]

so the first table is copied in as is and the second table is copied (table 2 col to master col) as below?

1-1......2-2.....4-5....10-6.....8-7.......9-10......5-12......6-13....7-14.....and column with table heading to col 28 ?

Your almost correct, the exact match is:
1 - 1
2 - 2
4 - 5
10 - 6
8 - 7
3 - 8
9 - 10
5 - 12
6 -13
7 - 14

and the added TABLE description in position 28

Thanks!

 

[oldbrewer]

make a copy of second table, switch on record macro, and do a series of control x (cut) and paste on whole columns to get them into the correct locations for pasting, stop macro then highlight all data in table 2, go to first empty cell in col A and paste

the macro can be used each day like a robot..........