• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Reorder headers and store into 1d array

YasserKhalil

Well-Known Member
Hello everyone

I have the first row with some headers in about 12 columns and I need to search for specific headers from 1d array.

Code:
    a = Array("Header3", "Header5", "Header10")
    For Each c In Range("A1").CurrentRegion.Rows(1).Cells
        x = Application.Match(c.Value, a, 0)
        If Not IsNumeric(x) Then
            ReDim Preserve s(n)
            s(n) = c.Column
            n = n + 1
        Else
            ReDim Preserve t(k)
            t(k) = c.Column
            k = k + 1
        End If
    Next c
The code works and reorders the headers into two new 1d arrays the array s() and the array t() then I am using this line to get a final 1d array with the new order I desire

Code:
ar = Split(Join(s, ",") & "," & Join(t, ","), ",")
Is there a smarter approach to get the same result of the final 1d array??

The question posted here too
 

YasserKhalil

Well-Known Member
No need for a workbook. Only the headers of column A "Header1" .. column B "Header2" and so on
The target is to get the columns numbers in an array but in specific order. The non-matching first then the matching ones

I already done this by two variables s() and t() and then concatenate both of them to get the final 1d array
The expected output for the twelve columns in that case:
76467
 

p45cal

Well-Known Member
There's very little wrong with what you've done. The only thing is, because you've used string functions, you end up with string type variables, which won't matter here because whatever you do with them they'll be implicitly coerced to longs/integers.
[As an aside, in general, if you're going to use this technique with string variables, it would be a bit more robust to use another delimiter apart from a comma, this is because a comma is commonly used within, for example, a cell, which would split a single string value into two or more when maybe that's not wanted. So instead use another character (or combination of characters) as a delimiter. For example a ¬]
To preserve the data types, even if they're different within an array, you can loop through them:
Code:
…
    k = k + 1
  End If
Next c
'sort the arrays as you wish to here then:
ReDim result(1 To UBound(s) - LBound(s) + UBound(t) - LBound(t) + 2)
For Each itm In s
  i = i + 1
  result(i) = itm
Next itm
For Each itm In t
  i = i + 1
  result(i) = itm
Next itm
It won't be slow.
 
Last edited:

Marc L

Excel Ninja
to get the final 1d array
Different ways, one of them :​
Code:
Sub Demo1()
    Dim V, W, C%, H$(1 To 12)
        V = Application.Index([A1:L1].Value2, 1, 0)
        W = Filter(Application.IfError(Application.Match(["Header"&{3,5,10}], V, 0), False), False, False)
        For C = 0 To UBound(W):  H(12 - UBound(W) + C) = V(W(C)):  V(W(C)) = False:  Next
        V = Filter(V, False, False)
        For C = 0 To UBound(V):  H(C + 1) = V(C):  Next
        Debug.Print Join(H, ", ")
End Sub
 

Marc L

Excel Ninja
Another one :​
Code:
Sub Demo2()
    Dim V, W, R%, C%, L%, H$(1 To 12)
        V = Application.Index([A1:L1].Value2, 1, 0)
        W = Application.Match(V, ["Header"&{3,5,10}], 0)
        R = 12 - Application.Count(W)
    For C = 1 To 12
        If IsError(W(C)) Then L = L + 1: H(L) = V(C) Else R = R + 1: H(R) = V(C)
    Next
        Debug.Print Join(H, ", ")
End Sub
 

Marc L

Excel Ninja
Last but not least :​
Code:
Sub Demo3()
  Const D = ","
    Dim V, C$
        V = "{""Header3"",""Header5"",""Header10""}"
    With [A1].CurrentRegion.Rows(1)
        C = Join$(Filter(Evaluate("IFERROR(MATCH(" & V & D & .Address & ",0),FALSE)"), False, False), D)
        C = Join$(Filter(Evaluate("IF(ISNA(MATCH(" & .Address & D & V & ",0)),COLUMN(" & .Address & "))"), False, False), D) _
            & IIf(C > "", D & C, "")
        V = Application.Index(.Value2, 1, Evaluate("{" & C & "}"))
    End With
        Debug.Print Join(V, ", ")
End Sub
 

YasserKhalil

Well-Known Member
Amazing my tutor. Thank you very much
As for Demo3, I tried to get the output to be the number of columns (not the headers text) but I couldn't
In fact, the target is to get 1d array of the columns numbers (to use later in reordering the columns)
To get what I mean I will use the 1d array like that
Code:
    a = Range("A1").CurrentRegion.Value
    a = Application.Index(a, Evaluate("ROW(1:" & UBound(a, 1) & ")"), h)
    Range("A10").Resize(UBound(a, 1), UBound(a, 2)).Value = a
h is the desired output (1d array with the columns numbers in the new order).
Instead of using `V = "{""Header3"",""Header5"",""Header10""}"`, I would like to use `V=Array("Header3", "Header5", "Header10")` and note that these texts of headers are just for illustration so using such these parts `["Header"&{3,5,10}]` not working on real dataset
 

Marc L

Excel Ninja
According to Demo3 :​
  • The V variable at the beginning for headers to match must be in this text format rather than an array 'cause of the formula used …

  • The C variable contains the columns new order as text and Evaluate("{" & C & "}") just converts it to an array …
Let's see how could be its revamped version with V & C as arrays instead of texts :​
Code:
Sub Demo3r()
  Const D = ","
    Dim V, C
        V = [{"Header3","Header5","Header10"}]
    With [A1].CurrentRegion.Rows(1)
        C = Join(Filter(Application.IfError(Application.Match(V, .Value2, 0), False), False, False), D)
        C = Split(Join(Filter(Evaluate("IF(ISNA(MATCH(" & .Address & ",{""" & Join(V, """,""") & """},0))," _
                            & "COLUMN(" & .Address & "))"), False, False), D) & IIf(C > "", D & C, ""), D)
        Debug.Print Join(Application.Index(.Value2, 1, C), ", ")
    End With
End Sub
 
Top