Sort multiple columns based on column header

Hi Langer,

I am not able to understand you code or the purpose of the find instruction

the following code will find the Charge Date column and perform a sort. Hope this helps

Public Sub pub_sub_SortChargeDate()

'#
'# declare private variables
'#
     Dim pvt_lng_SortColumnn As Long
     
'#
'# get the column number holding the charge dates, by searching for the
'# column header text
'#
     pvt_lng_SortColumn = Application.Match("Charge Date", Rows(1), 0)
     If pvt_lng_Column = 0 Then
          MsgBox "Unable to find the Charge Date column"
          Exit Sub
     End If
     
'#
'# sort the entire worksheet
'#
     With ThisWorkbook.Worksheets("Sheet1").Sort
               .SortFields.Clear
               .SortFields.Add Key:=Columns(pvt_lng_SortColumn), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
               .Header = xlYes
               .MatchCase = False
               .SetRange Range("A1").CurrentRegion
               .Orientation = xlTopToBottom
               .SortMethod = xlPinYin
               .Apply
     End With
     
End Sub

Langer,

Previous post contained a few typing errors

Please use code shown below

Public Sub pub_sub_SortChargeDate()

'#
'# declare private variables
'#
     Dim pvt_lng_SortColumn As Long
     
'#
'# get the column number holding the charge dates, by searching for the
'# column header text
'#
     pvt_lng_SortColumn = Application.Match("ChargeDate", Rows(1), 0)
     If pvt_lng_SortColumn = 0 Then
          MsgBox "Unable to find the Charge Date column"
          Exit Sub
     End If
     
'#
'# sort the entire worksheet
'#
     With ThisWorkbook.Worksheets("Sheet1").Sort
               .SortFields.Clear
               .SortFields.Add Key:=Columns(pvt_lng_SortColumn), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
               .Header = xlYes
               .MatchCase = False
               .SetRange Range("A1").CurrentRegion
               .Orientation = xlTopToBottom
               .SortMethod = xlPinYin
               .Apply
     End With
     
End Sub

Originally Posted by Langer101

Hi OllieB

Code runs fine but it doesnt actually sort though (I changed "ChargeDate" to "Charge date")

Can you post a copy of your workbook (easier to test)?

@Langer101,

I took a copy of the workbook from your first post, inserted the code from post #4 into a module, changed the "ChargeDate" literal into "Charge Date", ran the macro, and noticed that the data had been sorted perfectly. Have copied the updated workbook below

Langer101,

You are welcome to post a copy of your workbook, however the sort code is fairly straightforward and should have no interference from other code. Obvious question: you did check the name of the worksheet to be sorted, right? If needed I will gladly have a look at your workbook

PS - did you try the workbook I posted in #7? Did that one work?

I started re-writing the VBA code for the third button, but that is quite an undertaking without the specifications/requirements. That is unfortunately going to cost me too much time. If you take the code I posted at #4 and add it to the CommandButton3 routine just before the ActiveWorkbook.Save instruction it should just work fine.

Great. I am still itching to re-write your solution. When I have time I will give it a go. If only for the fun of it.

Question:

Before the find & replace action on the Publisher column it for example holds a value

' Moore, Reavis $ Santa Fe, N.M., 1993 - [1995] $ '

if I interpret the code correctly you are only interested in the second part and the result should become

'Santa Fe, N.M., 1993 - [1995]'

correct?

Question:

in your code you duplicate column Title to Author, and at some point you compare the values in the two columns and clear the author cell if it is the same as the title cell. As the author column is a copy of the title column, would this not always be true, and would the author column thus not always end up being blank? In other words: am I seeing this correctly?

Ok. Not really but I am making some good progress in writing a generic solution. Basically the code will be completely generic and will act upon instructions in several worksheets. For example

Worksheet 1_IMPORT_ACTIONS
on this worksheet you define which columns of the source data you want to keep or delete, and what the title name and cell format should be if you want to keep the column

Worksheet 2_DUPLICATE_COLUMNS
On this worksheet you specify which columns you want to copy &paste (i.e. duplicate) and what name should be assigned to them

Worksheet 3_FIND_REPLACE
On this worksheet you specify per column per action, what to search and what the replacement value should be. Obviously you can specify multiple find & replace actions for the same column

Worksheet 4_SPLIT_ELEMENTS
Here you can specify which subset you want for a cell value. For example the Charge Date column contains 'DD/MM/YYYY HH:MM:SS' and you are only interested in the date part. On this worksheet you then specify Charge Date as the column, a " " as a separator and 1 as the element to use.


The idea/concept is that you would not have to write/change any code but can customise everything via the worksheets. Like I said work in progress, but getting there.

Ok. Not really but I am making some good progress in writing a generic solution. Basically the code will be completely generic and will act upon instructions in several worksheets. For example

Worksheet 1_IMPORT_ACTIONS
on this worksheet you define which columns of the source data you want to keep or delete, and what the title name and cell format should be if you want to keep the column

Worksheet 2_DUPLICATE_COLUMNS
On this worksheet you specify which columns you want to copy &paste (i.e. duplicate) and what name should be assigned to them

Worksheet 3_FIND_REPLACE
On this worksheet you specify per column per action, what to search and what the replacement value should be. Obviously you can specify multiple find & replace actions for the same column

Worksheet 4_SPLIT_ELEMENTS
Here you can specify which subset you want for a cell value. For example the Charge Date column contains 'DD/MM/YYYY HH:MM:SS' and you are only interested in the date part. On this worksheet you then specify Charge Date as the column, a " " as a separator and 1 as the element to use.


The idea/concept is that you would not have to write/change any code but can customise everything via the worksheets. Like I said work in progress, but getting there.

@Langer101,

Don't worry about the fact that the input is not always consistent or the same. I am trying to make it as flexible and robust as possible. Really making a lot of progress

@Langer101,

Don't worry about the fact that the input is not always consistent or the same. I am trying to make it as flexible and robust as possible. Really making a lot of progress. Almost done

@Langer,
I have built a generic solution. Please check the attached workbook, play with it and give me your feedback.

If you have specific questions on how to solve a requirement using the generic solution, let me know!

Originally Posted by Langer101

Removed:
• pvt_wbk_Target.Worksheets("Sheet2").Delete
• pvt_wbk_Target.Worksheets("Sheet3").Delete
The target worksheet will always only have 1 sheet

Ok

Originally Posted by Langer101

Added to Find Replace
• Publisher CTL*

In my tests I did not need this action as the column split only returned the publisher information

Originally Posted by Langer101

Added to Import Actions
• Column names that needed to be removed to
o As there may be additional columns that I’m not aware of is it possible to write it the other way around i.e. instead of saying delete the following, say instead delete ALL except for…..
• YYYY/MM/DD as output format for Charge date
Charge Date
I’m not sure why this happens but some dates are in DD/MM/YYYY format it possible to standardise on YYYY/MM/DD instead or is it my settings?

Any new columns would remain in the output and thus would serve as a trigger, because the header name would be "strange". If we reverse the logic you would never know that the column was missed. However this would be your choice, I would be happy to change the code.

Date formatting is a "pain in the butt" as Excel determines AUTOMATICALLY during the import based on the first couple of rows read what the format of the date should be (and yes the regional settings of the workstation do play a role). We can force the format of the column to TEXT but because your input file changes we do not know which column we should take. If the sample output you provided is what has been produced the the only solution I can see is to write a specific routine for transforming dates. Can be done!

Originally Posted by Langer101

Added to Column Order
• Annotation and Volume
• Added to Column order

No problem

Originally Posted by Langer101

I’ve tried the macro with some of the other lists available. I’ve attached a sample with the following results:
Barcode1 and 2
The idea is not to the same barcode twice.
Ideally Barcode 1 should contain any barcode other than those that start with ‘36180’. These should appear in the Barcode2 column
If the column only contains 36180 barcodes then we simply call the column Barcode1
• We have different barcode standards as legacy from two different library management systems hence some barcode start with “OCT*”, C*, 36*etc.

The best solution would be to send me a copy of the "generator" workbook with the most recent changes made by you, and a copy of the input and output. If you can describe your requirements I can build something

Originally Posted by Langer101

Price
Not all the Prices converted into currency

Needs a custom solution. I did not face that problem in my testing, but I guess we need to build something for this

Originally Posted by Langer101

Finally
Just thinking about the users that will be using this would it not be best to maybe add this to the ribbon or something? The less they see the better for everyone

[/QUOTE]

Manipulating the ribbon is not something I am very good at. Can we not alternatively create a console worksheet and hide the parameter worksheets (invisible) with VBA project protection so only you can change them?

I have just run a test on the version I have and all cells in the Charge Date column have a "general format" as opposed to the date format in your example. have you by any change tried to change the cell format and did that action result in some of the dates being misinterpreted?

Langer101, I have read up on the TextToColumn function and see a usage for date and price conversions. Can you please post the most recent version of the workbook, or shall I use the version that I already have?

Langer101, made the necessary changes to the workbook (master) copy that I have. Noticed the following situation for date conversion

input:
15/10/2012
12/25/2012

after TextToColumn specifying dd/mm/yyyy

15/10/2012
12/25/2012

notice that the 2nd date has not been changed. The funny thing is that the format of the first date has changed to date, whereas the format of the second date has remained of type general. Using this trick we would know which date was invalid, and can then force the date to dd/mm/yyyy format using code. Your thoughts?

Hi Langer101,

- processing the CSV file you have provided the date part was isolated fine using my solution, and it remains in the same format as delivered in the CSV. I am not sure what the TextToColumns adds in your solution. If we do not know the source format of the date, or if the format is not consistent in the CSV it would be almost impossible to re-format them into something consistent;

- replacing a "," with a ".", works in case of US/UK settings as the "." is the decimal notation. On a PC with for example a Dutch regional setting this is not going to work. What I can do is ensure the price is shown correctly for the PC the Excel file is opened on (so somebody with a UK setting would see a "." and somebody with an NL setting would see ","). This would require some additional coding;

- for barcode processing we could introduce a step that would clear a certain cell for a given condition. That would allow you to clear barcodes starting with CL in column 1 and retain them in column 2, while you could for example clear 381* in column2 while retaining them in column 1

The file you attached is the old version, so I am not sure why you included it. Do you want to keep using the old version and use the new version I provided to you purely as an example of some code, or do you want to enhance the new version to meet your needs?

If you want to enhance the new version, please post the last version (and also a CSV file that is causing problems) so no changes that you have made will be lost.

Hi Langer101,

Can you please answer the following questions:

a. what is the date format delivered in the CSV mm/dd/yyyy, dd/mm/yyyy or yyyy/mm/dd?
b. is this always the same or can it differ per CSV file (in other words dd/mm/yyyy for CSV1 and mm/dd/yyyy fo CSV2)?
c. if it can be different per CSV, will it at least be consistent within the CSV?
d. what is the format you would like to have in the output workbook?

Langer101,

Herewith a new version.

1. Forced date conversion has now been built in. The assumption is that the data format in the CSV file is day/month/year. The user can specify the desired output format on tab 5_FORCE_FORMAT

2. Forced numeric conversion has now been built in. See tab 5_FORCE_FORMAT - if NUMERIC is specified it uses TextToColumn (always) accepting the "."as a decimal separator which is forced by the find&replace ","by "."in the preceding step, so this should always work

3. For barcodes I only added for find & replace rules as the generic capabilities appeared sufficient. The only thing I was not able to solve is that if the barcode1 column is empty (and thus removed) that the caption of barcode2 changes back to barcode1. Sorry.

Let me know your thoughts. IF you encounter problems then please also submit the CSV file that is causing the problem, otherwise it is difficult to test for me.

Langer,

I opened the same version and have neither the 1004 error nor the type mismatch.
- What do you mean by 'enable the sheet'?
- If you get a type mismatch on that statement then the CSV file contains a date in a different format then specified by you.

Can you please post the CSV file

Hi Langer,

The strange thing is that processing the same CSV I do not receive any errors??

Dates & prices are all looking fine

So what I did is I added a check to the software to see if the resulting date is a valid date, if so it stores it else it will show you a message with eh value and the row number where it encountered the problem.

Revised version enclosed

Langer,

All dates are now converted into proper date serials using the following approach:

'#
'# by extracting the year, month and day and subsequently passing them to the DateSerial function, the variable pvt_date_Work will contain
'# a valid date serial value irrespective of the display format or regional settings (this is a internal format)
'#
    pvt_date_Work = DateSerial(Val(Mid$(pvt_xls_Cell.Value, 7, 4)), Val(Mid$(pvt_xls_Cell.Value, 4, 2)), Val(Left$(pvt_xls_Cell.Value, 2)))

'#
'# if the date serial is valid, the Format$ instruction is used to determine how the date should be presented to the user - the display format
'# is determined by the parameter on the FORCE_FORMAT worksheet, again independent from the regional setting
'#
    If IsDate(pvt_date_Work) Then
         pvt_xls_Cell.Value = Format$(pvt_date_Work, .Cells(pvt_lng_RowNumber, "C").Value)
    Else
         MsgBox "Unable to interprete the date " & pvt_xls_Cell.Value & " on row " & pvt_lng_RowNumber
    End If

Because the date (internally) is a proper date serial value, sorting is not a problem, regardless of the displayed format.