Solved

Macro to sort columns into designated column sequence

Posted on 2015-02-04
24
127 Views
Last Modified: 2015-02-14
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
Comment
Question by:ceneiqe
  • 10
  • 7
  • 7
24 Comments
 
LVL 29

Expert Comment

by:gowflow
ID: 40589120
Do you have any problem if in Rule I remove Col and keep it to a 1, 2, 3 etc... ??
gowflow
0
 
LVL 29

Expert Comment

by:gowflow
ID: 40589132
Also here it is not clear what you want !!!!
Col 5 --> Col35

what does it mean ?
gowflow
0
 
LVL 45

Expert Comment

by:aikimark
ID: 40589331
Your description does not match the sample workbook.  There are columns that appear to have been dropped by your mapping example.
0
 
LVL 45

Expert Comment

by:aikimark
ID: 40589393
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
 

Author Comment

by:ceneiqe
ID: 40594178
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
 
LVL 29

Expert Comment

by:gowflow
ID: 40594329
do what you want as long as you clarify what you want !!! as also here it is not clear.
gowflow
0
 

Author Comment

by:ceneiqe
ID: 40597761
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
 
LVL 45

Expert Comment

by:aikimark
ID: 40598487
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
 
LVL 29

Expert Comment

by:gowflow
ID: 40598732
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
 

Author Comment

by:ceneiqe
ID: 40602387
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
 
LVL 45

Expert Comment

by:aikimark
ID: 40602418
the data in worksheet Rule1 got all jumbled up
Do you mean beyond the sorting?
0
 
LVL 29

Expert Comment

by:gowflow
ID: 40602502
Did you try my solution ? is it ok ?
gowflow
0
6 Surprising Benefits of Threat Intelligence

All sorts of threat intelligence is available on the web. Intelligence you can learn from, and use to anticipate and prepare for future attacks.

 
LVL 29

Expert Comment

by:gowflow
ID: 40602519
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
 
LVL 45

Expert Comment

by:aikimark
ID: 40603800
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
 

Author Comment

by:ceneiqe
ID: 40607235
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
 

Author Comment

by:ceneiqe
ID: 40607262
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
 
LVL 29

Accepted Solution

by:
gowflow earned 500 total points
ID: 40607700
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
 
LVL 45

Expert Comment

by:aikimark
ID: 40607724
@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
 
LVL 29

Expert Comment

by:gowflow
ID: 40607786
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
 
LVL 45

Expert Comment

by:aikimark
ID: 40607873
That certainly was a big misunderstanding.  Thanks for clarifying, gowflow.
0
 
LVL 29

Expert Comment

by:gowflow
ID: 40607961
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
 

Author Comment

by:ceneiqe
ID: 40609938
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
 

Author Comment

by:ceneiqe
ID: 40609941
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
 
LVL 29

Expert Comment

by:gowflow
ID: 40609948
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

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

Drop Down List with Unique/Distinct Values (Part II - ComboBox or ListBox and Data Validation List Bonus!) David Miller (dlmille) Intro This article focuses on delivering unique, sorted lists to list objects (e.g., ComboBox, ListBox) and Dat…
Workbook link problems after copying tabs to a new workbook? David Miller (dlmille) Intro Have you either copied sheets to a new workbook, and after having saved and opened that workbook, you find that there are links back to the original sou…
This Micro Tutorial will demonstrate the scrolling table in Microsoft Excel using the INDEX function.
This Micro Tutorial will demonstrate how to create pivot charts out of a data set. I also added a drop-down menu which allows to choose from different categories in the data set and the chart will automatically update.

707 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

15 Experts available now in Live!

Get 1:1 Help Now