Import, Filter and Sort Multiple Columns

[tonyk1051]

Hi,

So every 2 weeks i get 2 workbooks, the first workbook has only one worksheet where i have to reorganzie a few columns of data (lines go up to 20000) the 2nd workbook has 3 worksheets, same headers but all in a different order, i have pull all the data from those 3 worksheets into a new worksheet and do that for several columns. So for example header name "item ID" is present in all 3 worksheets but in workheet one its in column W, worksheet 2 has it in column L, etc. each worksheet has usually about 10000 lines. Is there a setting or macro that can do all this in seconds? I ve attached 2 workbooks so you get what i mean.

[SamT]

I would start by opening a new Workbook named: Scratch Book; Sketch Book; or Project Map.

Then, on each sheet in to other books, copy Row 1, The headers, to an empty row and Series Fill the row under it with column numbers or Column Letters (I prefer Letters 'cuz I hate counting on my fingers.)

Everything hereafter refers to the new Map sheet in Project Map Workbook,

Copy these sets of two rows to a sheet in Project Map starting in Column C, Row 4. Enter the Originating sheets names in Column A, the same row as the headers.

Jump your mind ahead to the final stage of all this work and list the Final working set of Headers in Row 1 (starting in Column C.) I will henceforth refer to headers a Fields, since this project is best visualized as Database work.

Finally, reorganize the Field/Column-ID Cell Pairs for each sheet to fit the Field Names in Row 1. If a field is not used in the Final stages, just move it outside the working area. Note: Don't move the pairs up or down, just side to side in the same Row(s)


When done, you will have a 'ragged' map of where each column of each sheet belongs in the final result. If done right, we won't even need to see the actual data sheets, except for testing, which the samples you have provided will suffice for.

This prep work is important to insure that the programming/coding is fast, accurate, and testable.

I doubt it can be done in seconds, but certainly faster than a cup of coffee.

[snb]

You can sort columns to a custom list.