Merge Excel columns from different worksheets to new sheet

InfoMan206

New Member
Joined
Jul 17, 2012
Messages
2
Using Excel 2003, I have a workbook with 7 worksheets each containing 160 rows plus a header, each worksheet has a key field (employee number) and several unique columns. Looking for an easy to understand solution to merge the columns from all the worksheets into a new sheet - once data is merged, the key field is only required in column A.


When the data is first exported from our in-house custom app it has an initial 13 rows of information which is normally deleted manually (along with a number of columns which do not contain useful data) - if there is an easy way to add part of the script to remove the first 13 lines of each worksheet prior to the merge it would be a bonus (the header line is initially on row 14 prior to me deleting the other info).


I've found scripts which will merge rows but not columns - any assistance would be great.


Thank you.


Jason
 

Some videos you may like

Excel Facts

Format cells as date
Select range and press Ctrl+Shift+3 to format cells as date. (Shift 3 is the # sign which sort of looks like a small calendar).

Mindpsyche

Well-known Member
Joined
Mar 19, 2012
Messages
760
Still not clear enough for me. So:

1. Your workbook has 7 sheets, each sheet with 160 rows of data.
2. The first 13 rows of this data needs to be deleted in all of the 7 sheets.
3. You need a new sheet inserted which contains only the unique columns from each of the other 7 sheets?

How can these columns be identified? Do they have unique headers or are they always fixed on the same column:

Eg: In sheet 1: You always keep Columns A, B and C.
In sheet 2: You always keep Columns D,E and F

Or is it random: The columns to keep on each sheet change. In this case how do you identify which column to keep for each sheet?

Anymore clarity from your side will help.
 

InfoMan206

New Member
Joined
Jul 17, 2012
Messages
2
Sorry for the delay Mindpsyche, the 14th row contains all the unique headers - and I always keep specific columns. Not sure if there is a way to post a sample excel file here - if needed I can host it on another server to provide it to you if needed.

The following is the manual script that I have people use to format the existing file to get to the point I need:

Insert new sheet at start of workbook

Medical Staff Tab:
· Delete Rows 1-13
· Delete Columns D,E,G,I,J,K
· Then move Columns A-Q to new sheet

Dental Staff Tab:
· Delete Rows 1-13
· Delete Columns B-K
· Then move Columns A-E to new sheet

Orderly Room Tab:
· Delete Rows 1-13
· Delete Columns B-K
· Then delete columns (confirm letters) C (Security Effective),
o K (TOS - Valid Terms Of Servi-Effective Date), L (TOS - Valid Terms Of Servi-Expiry Date),
o AI (PASS - Passport-Effective Date), AJ (PASS - Passport-Expiry Date)
· Then move Columns A-AJ to new sheet

Transport Tab:
· Delete Rows 1-13
· Delete Columns B-K
· Then move Columns A-I to new sheet

PSP Staff Tab:
· Delete Rows 1-13
· Delete Columns B-K
· Then delete column C (FIT - Fitness Test-Effective Date)
· Then move Columns A-D to new sheet

Security Section Tab:
· Delete Rows 1-13
· Delete Columns B-K
· Then move Columns A-I to new sheet

Post Deployment Period Tab:
· Delete Rows 1-13
· Delete Columns B-K
· Then move Columns A-E to new sheet

Training Officer Tab:
· Delete Rows 1-13
· Delete Columns B-K
· Then move Columns A-Q to new sheet

Once merged to main remove duplicate SN fields – after ensuring everything is lined up.
Select entire table, then Ctrl-H, then enter “-“ in the Find what entry, then enter “/” in the Replace with entry, then select Replace All (don’t worry if you see entries which look like ####### as this is just a width issue)

Select all columns containing dates, right click and select format, then date, then OK to select default date type

Ensure fields are in the correct order for the merge by confirming in the back-end database file

Ensure all users are out of the database

Backup the Master-be.mdb database file

Clear the contents of the DAG table

Import contents from Excel & verify results
 

Watch MrExcel Video

Forum statistics

Threads
1,127,387
Messages
5,624,402
Members
416,026
Latest member
melvic69

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