PDA

View Full Version : Need to standardize 100s of excel files that have columns in the wrong order



business1000
01-11-2015, 10:54 PM
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
01-12-2015, 09:00 AM
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
01-12-2015, 10:51 AM
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
01-12-2015, 11:40 AM
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 :sleuth: Are those the same files as these?)

business1000
01-12-2015, 01:39 PM
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
01-12-2015, 01:49 PM
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
01-12-2015, 04:25 PM
@ All
Timeout.

The OP has another issue at Need help removing unwanted commas that sometimes appear in rows in csv files (http://www.vbaexpress.com/forum/showthread.php?51512-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
01-12-2015, 04:31 PM
Business, clik my name above to PM me.

SamT
01-13-2015, 08:49 AM
@ 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.