Need to standardize 100s of excel files that have columns in the wrong order

[business1000]

I have hundreds of files every week that need their columns in the right order (attachment provided).
I need help developing a macro that will look at each file, pull the right data, and save it to a new file in destination folder with just the columns I need - in the column order I need them in.
I can convert the files to csv files if that would help come up with a solution.

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

17

18

19

20

Common/Destination File Column Names ==>>

Machine ID

Issue ID

Issue Cat

Operator Issue Start Date

Operator Issue End Date

No. Issue Points

No. Issue Points Addressed

No. Issue Points Resolved

Resolution 1st Level Review Status

Resolution 1st Level Review Date

Resolution 2nd Level Review Status

Resolution 2nd Level Review Date

Case Status

Case Status Date

Column Naming Variation

1

Machine ID

Issue ID

Issue Cat

Operator Issue Start Date

Operator Issue End Date

No. Issue Points

No. Issue Points Addressed

No. Issue Points Resolved

Resolution 1st Level Review Status

Resolution 1st Level Review Date

Resolution 2nd Level Review Status

Resolution 2nd Level Review Date

Case Status

Case Status Date

Column Naming Variation

2

MachineID

IssueID

IssueCat

OperatorIssueStartDate

OperatorIssueEndDate

No.IssuePoints

No.IssuePointsAddressed

No.IssuePointsResolved

Resolution1stLeve ReviewStatus

Resolution1stLevelReviewDate

Resolution2ndLevelReview Status

Resolution2ndLevelReviewDate

CaseStatus

CaseStatusDate

Column Naming Variation

3

MachineID

IssueID

IssueCat

OperatorIssueStartDate

OperatorIssueEndDate

No.IssuePoints

No.IssuePointsAddressed

No.IssuePointsResolved

Resolution1stLeve ReviewStatus

Resolution1stLevelReviewDate

CaseStatus

CaseStatusDate

Column Naming Variation

4

MachineID

IssueID

OperatorIssueStartDate

OperatorIssueEndDate

No.IssuePoints

No.IssuePointsAddressed

No.IssuePointsResolved

ResolutionReviewStatus

ResolutionReviewDate

CaseStatus

CaseStatusDate

Column Naming Variation

5

IssueID

OperatorIssueStartDate

OperatorIssueEndDate

No.IssuePoints

No.IssuePointsAddressed

No.IssuePointsResolved

ResolutionReviewStatus

ResolutionReviewDate

Column Naming Variation

6

Machine Code

Issue No.

CAT

Issue Start Date

Issue End Date

No. Issue Points

No. Issue Points Addressed

No. Issue Points Resolved

Review Status

Review Date

[SamT]

Just to clarify the situation, although you are "saying" that the column Headers (Names) are out of order, the problem is that the Headers are not consistent in spelling or format. And it could be both.

Example of inconsistent Column Headers

MachineID	Issue ID		OperatorIssueStartDate	OperatorIssueEndDate	No.IssuePoints
	IssueID		OperatorIssueStartDate	OperatorIssueEndDate	No.IssuePoints
Machine Code	Issue No.	CAT	Issue Start Date	Issue End Date	No. Issue Points

All columns in all files in same order from left to right





Example of Out of Order Headers

1	2	3	4	5	6
Machine ID	Issue ID	Issue Cat	Operator Issue Start Date	Operator Issue End Date	No. Issue Points
Machine ID	Operator Issue End Date	Issue ID	Issue Cat	Operator Issue Start Date	No. Issue Points
OperatorIssueStartDate	OperatorIssueEndDate	MachineID	IssueID	IssueCat	No.IssuePoints

Columns in random order from left to right in different files

[business1000]

Yes, the problems are:
The column headers are not called the same thing.
The column headers are out of order.
There are extra columns of other data which is not needed in the final product - therefore we do not want to copy them to the final file product.
Not all data fields are populated for every row.

Thanks for prompt interest in helping.

[SamT]

Are there missing column Headers?

Not all data fields are populated for every row. Why is that a problem in the final product? What should the final product show in place of empty fields?

If there are NO missing Headers, organizing the columns is a simple matter ofgoing down a list and

Find >>Cut (or Copy) >> Insert

Standardizing the Headers is easy once the column order is fixed.

in Range("A1") Paste an array of standard headers

Getting rid of extra columns is just as easy.


BTW, The columns must be standardized before we can deal with the Last Name comma First Name problem.( Assumption check Are those the same files as these?)

[business1000]

It is not a problem in the final product that some columns has missing data - just because there was no data to populate there in the source files.

I like the approach: as long as the code knows the output ideal column header names and output ideal column order, the code will match the possible source columns names to ideal column names and wil ltake the data in the columns with it as it matches the column headers and properly orders them.
I agree that it is key to set up your template with the possible column header matches for each output header like you did.
Yes, there are going to be other columns that are in the source file that are not in the equation, and the code has to ignore them.
Thanks again for any help
I would think I need to fix the comma issue and get the data in the correct columns before I run code for this part.

[Kenneth Hobs]

It would be best to post an example csv file or two. It is likley that merging or joining files must have the same number of columns.

If you were to strip our some ",," out, I fear that you would wind up with more fields in one record than another.

[SamT]

@ All
Timeout.

The OP has another issue at Need help removing unwanted commas that sometimes appear in rows in csv files
He has strongly hinted that these are the same files

I would think I need to fix the comma issue and get the data in the correct columns before I run code for this part.

I have PM'ed him about the question.

In that thread, the issue is the value in the Logon ID column may contain its own comma, which will add an additional field to that record when the file is saved as a csv.

In that thread, there are 19 fields, where in this thread the final result will have 14 fields.

IMO, the OP has not yet provided enough clear information for VBAX to continue with either thread. I will work with Business1K and will add a thread Icon when all necessary information is available.

Thank you for your patience.
SamT

[SamT]

Business, clik my name above to PM me.

[SamT]

@ All,

Business1000 and I have been communicating quite a bit and he would like to wait a while before continuing work on this particular issue.