asked on

Macro to sort columns into designated column sequence

Macro should read in Worksheet 'Rule' and then map the columns sequence accordingly.
Col1 = ColA
Col2 = ColB
Col3 = ColC
and so on.

For example, Col5 = ColE
Original Data, ColE heading is "Number". See Worksheet 'Original Data'
Macro should place "inside?" in ColE. See Worksheet 'Desired Result'

Column-Sequence.xlsx

NOTE: Do not replace data, but to swop columns and the corresponding data below according to the sequence.

I have a total of 146 columns but I just place some columns examples in here and then later I can add on the change into the macro. I am not sure if this method is workable. If not, i will type in all the correct sequence for mapping. Please advise. Thanks!

Jacques Geday

Do you have any problem if in Rule I remove Col and keep it to a 1, 2, 3 etc... ??
gowflow

Jacques Geday

Also here it is not clear what you want !!!!
Col 5 --> Col35

what does it mean ?
gowflow

aikimark

Your description does not match the sample workbook. There are columns that appear to have been dropped by your mapping example.

aikimark

In the attached workbook, I alter a new worksheet that I've prepopulated with the data. Also, I've parsed and sorted the data in the Rules worksheet for easier consumption by the code.
Column-Sequence.xlsm

ceneiqe

ASKER

Thanks let me check and test with my data.

just to double confirm: does it mean i can continue to add in my column number accordingly in the 'Rule' worksheet ?

Jacques Geday

do what you want as long as you clarify what you want !!! as also here it is not clear.
gowflow

ceneiqe

ASKER

hi aikimark,

Thanks, you got what i meant.
please see my attached revised file.

Column-Sequence--revised-.xlsm

see worksheets tabs highlight in red.
Explanation for worksheet "Rule" can be found in "Rule1"

aikimark

I copy/pasted value column A and sorted descending by column C.
I tweaked the code to look at columns C and A instead of B and A, and to look at the Rules1 worksheet instead of the Rules worksheet.

After running the code I saw some blank columns. I think there might be some problem with your new rules or a problem with my understanding of your problem.

As before, I used the Q_28610086 worksheet for the results.
Column-Sequence--revised-.xlsm

Jacques Geday

In the last file you posted what I don't understand is that you have 133, 140, 141 Are these columns ? these are in B or desired but they are not in Original shall we assume that the number is a column number regardless if it is in the original or not ?

If yes then I got a macro that produces blank columns but it copy the columns from their position in Col A to the desired position or column number in B

Pls note if my explanation is correct then it is not corresponding to your desired worksheet at all.
pls check if this is what you want.
gowflow
Column-Sequence--revised-V01.xlsm

ceneiqe

ASKER

hi aikimark, there is an error in
"wksTgt.Columns(rng.Value).Value = wksTgt.Columns(rng.Offset(0, -2).Value).Value"

and the data in worksheet Rule1 got all jumbled up.

hi gowflow,
Yes, 133, 140, 141 = they are also column numbers.
I did not list down all the column numbers. There are a total of 146 headers with corresponding data.
I only listed 53 headers as they are the most important ones and I thought i could add them later once the macro is ready as i am still working on the remaining header sequence.

So Column A is the original sequence, and column B is the desired sequence. Objective is to map the original to the desired sequence.

aikimark

the data in worksheet Rule1 got all jumbled up

Do you mean beyond the sorting?

Jacques Geday

Did you try my solution ? is it ok ?
gowflow

Jacques Geday

Oooopss !!! just checking I just realized I posted the wrong workbook that have NO solution for you. I am terribly sorry I posted an earlier version.

Pls see this one I added a button in sheet rule that when activated will do the arrangement as per your request. PLs check and let me know.

gowflow
Column-Sequence--revised-V02.xlsm

aikimark

Rather than rearrange in place, this code copies the column data to the Q_28610086 worksheet. Be sure to empty the Q_28610086 worksheet (at least the first row) before you test.

Sub Q_28610086()
    Dim wksMapping As Worksheet
    Dim wksSrc As Worksheet
    Dim wksTgt As Worksheet
    Dim rng As Range
    Set wksMapping = Worksheets("Rule1")
    Set wksSrc = Worksheets("Original Data1")
    Set wksTgt = Worksheets("Q_28610086")
    Application.ScreenUpdating = False
    For Each rng In wksMapping.Range(wksMapping.Range("c1"), wksMapping.Range("c1").End(xlDown))
        'Debug.Print "moving: " & wksSrc.Cells(1, rng.Offset(0, -2).Value).Value, "to column: " & rng.Value
        wksTgt.Columns(rng.Value).Value = wksSrc.Columns(rng.Offset(0, -2).Value).Value
    Next
    
    'NOTE: if replacing the original data1 worksheet, delete it and rename
    'the Q_28610086 worksheet
    
    Application.ScreenUpdating = True
End Sub

Open in new window

ceneiqe

ASKER

hi aikimak and goflow the results i have got from the macro doesn't match with what i wanted.

I wanted in this sequence for the fields (at least for 1-53):
1
2
3
4
35
6
8
7
68
37
12
31
140
141
63
133
9
22
23
14
17
18
27
25
26
11
5
46
10
52
19
29
32
34
15
16
62
61
59
60
21
41
43
39
40
42
44
13
24
51
33
53
20

but i got this :
Field1
Field2
Field3
Field4
Field27
Field6
Field8
Field7
Field17
Field29
Field26
Field11
Field48
Field20
Field35
Field36
Field21
Field22
Field31
Field53
Field41
Field18
Field19
Field49
Field24
Field25
Field23
(blank)
Field32
(blank)
Field12
Field33
Field51
Field34
Field5
(blank)
Field10
(blank)
Field44
Field45
Field42
Field46
Field43
Field47
(blank)
Field28
(blank)
(blank)
(blank)
(blank)
Field50
Field30
Field52 --- This should be Field20 but I got Field 52 (at row 53).
(blank)
(blank)
(blank)
(blank)
(blank)
Field39
Field40
Field38
Field37
Field15
(blank)
(blank)
(blank)
(blank)
Field9

ceneiqe

ASKER

attached is my comments in the file
see worksheet 'Rule' and my comments/workings in Column G, H and I.

Column-Sequence--revised-.xlsm

ASKER CERTIFIED SOLUTION

Jacques Geday

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

aikimark

@gowflow

So, the column mappings are intentionally incomplete?!?

If so, that requires a second pass of the source data to copy the unmapped columns.

Also, if this is true, why bother with some of the identity column mappings?

Jacques Geday

No you and I got trapped in a wrong understanding of the columns we both used copy/paste wrongly. We should Copy Col B and paste in Col A we were doing it reverse this is why we were getting this Field27 at position 5 !!!

now this being fixed if you notice in Col A he listed Col 1 to 53 but actually in Col B he is referring to Col 68,140 etc,, which in principle are not listed in A but would actually exist in the workbook. As we are using a fictive workbook these columns then are blank so to indicate we are picking the correct column I decided to simply change its label.

Hope I was able to clarify.
gowflow

aikimark

That certainly was a big misunderstanding. Thanks for clarifying, gowflow.

Jacques Geday

Look, this is my assumption, will still need OP to confirm maybe I am also totally off track !! :)
Let's wait and see.

gowflow

ceneiqe

ASKER

hi gowflow your macro work but it is having a conflict with another add-in in my excel program:

Merge Tables Wizard for Microsoft Excel' has fired an exception. Click the 'Details' button to see the detailed information about the error.

Detailed technical information follows:
---
(Inner Exception)
Machine Name: XXXXXXXXXXXXXX
Current User: XXXXXXX\YYYYYYY

Application Domain: C:\Users\XXXXXXX\AppData\Roaming\Add-in Express\Merge Tables Wizard for Microsoft Excel\
Assembly Codebase: file:///C:/Windows/Microsoft.NET/Framework/v2.0.50727/mscorlib.dll
Assembly Full Name: mscorlib, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089
Assembly Version: 2.0.0.0

Exception Source: mscorlib
Exception Type: System.Runtime.InteropServices.COMException
Exception Message: Exception from HRESULT: 0x800A8029
Exception Target Site: InvokeDispMethod

---- Stack Trace ----
System.RuntimeType.InvokeDispMethod(name As String, invokeAttr As BindingFlags, target As Object, args As Object[], byrefModifiers As Boolean[], culture As Int32, namedParameters As String[])
mscorlib.dll: N 00000 (0x0) JIT
System.RuntimeType.InvokeMember(name As String, bindingFlags As BindingFlags, binder As Binder, target As Object, providedArgs As Object[], modifiers As ParameterModifier[], culture As CultureInfo, namedParams As String[])
mscorlib.dll: N 0488 (0x1E8) IL
System.Type.InvokeMember(name As String, invokeAttr As BindingFlags, binder As Binder, target As Object, args As Object[], culture As CultureInfo)
mscorlib.dll: N 0000 (0x0) IL
AddinExpress.AddIns.Common.XL.adxExcelUtils.GetProperty(target As Object, propertyName As String, args As Object[])
mscorlib.dll: N 0000 (0x0) IL
AddinExpress.AddIns.Common.XL.adxWorksheetUtils.IsWorksheet(ASheet As Object)
mscorlib.dll: N 0003 (0x3) IL
AddinExpress.AddIns.Common.XL.ExcelButtonEnabledStateHandler.adxExcelEvents_SheetActivate(sender As Object, hostObj As Object)
mscorlib.dll: N 0000 (0x0) IL
AddinExpress.MSO.ADXExcelAppEvents.DoSheetActivate(sheet As Object)
mscorlib.dll: N 0017 (0x11) IL

Exception Source:
Exception Type: AddinExpress.MSO.ADXExternalException
Exception Message: An error has occurred in the code of the add-in.
Exception Target Site: Object reference not set to an instance of an object.

---- Stack Trace ----

ceneiqe

ASKER

i just need to click OK then i can continue with excel but it is just popping up randomly while i am in the workbook.

Jacques Geday

no clue what the hell is this !!!!????

Is this error popping up only when you run my macro ???
Can you post the whole workbook ?? cannot debug part of something I don't see.

gowlfow