[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 152
  • Last Modified:

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!
0
ceneiqe
Asked:
ceneiqe
  • 10
  • 7
  • 7
1 Solution
 
gowflowCommented:
Do you have any problem if in Rule I remove Col and keep it to a 1, 2, 3 etc... ??
gowflow
0
 
gowflowCommented:
Also here it is not clear what you want !!!!
Col 5 --> Col35

what does it mean ?
gowflow
0
 
aikimarkCommented:
Your description does not match the sample workbook.  There are columns that appear to have been dropped by your mapping example.
0
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
aikimarkCommented:
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
0
 
ceneiqeAuthor Commented:
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 ?
0
 
gowflowCommented:
do what you want as long as you clarify what you want !!! as also here it is not clear.
gowflow
0
 
ceneiqeAuthor Commented:
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"
0
 
aikimarkCommented:
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
0
 
gowflowCommented:
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
0
 
ceneiqeAuthor Commented:
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.
0
 
aikimarkCommented:
the data in worksheet Rule1 got all jumbled up
Do you mean beyond the sorting?
0
 
gowflowCommented:
Did you try my solution ? is it ok ?
gowflow
0
 
gowflowCommented:
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
0
 
aikimarkCommented:
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

0
 
ceneiqeAuthor Commented:
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
0
 
ceneiqeAuthor Commented:
attached is my comments in the file
see worksheet 'Rule' and my comments/workings in Column G, H and I.

Column-Sequence--revised-.xlsm
0
 
gowflowCommented:
I think I got it !!! was not an easy one I can tell you although it looked easy but not obvious.

As you only listed Col 1 to 53 and are pointing to other columns obviously the way we are copying will show a blank column, so for you to test that the routine is fine and doing it  wright when the column is blank I replaced the field or the heading by Col and its number so when you see Col and a number this means the column was not listed initially and indicate that it is pulling the correct info.

Please run the button Arrange Columns and let me know.
gowflow
Column-Sequence--revised-V03.xlsm
0
 
aikimarkCommented:
@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?
0
 
gowflowCommented:
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
0
 
aikimarkCommented:
That certainly was a big misunderstanding.  Thanks for clarifying, gowflow.
0
 
gowflowCommented:
Look, this is my assumption, will still need OP to confirm maybe I am also totally off track !! :)
Let's wait and see.

gowflow
0
 
ceneiqeAuthor Commented:
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 ----

0
 
ceneiqeAuthor Commented:
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.
0
 
gowflowCommented:
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
0

Featured Post

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

  • 10
  • 7
  • 7
Tackle projects and never again get stuck behind a technical roadblock.
Join Now