Partial array slice

[JackDanIce]

Hi,

Have a 2D array R x C in size, C max (column count) = 14.

Is there syntax (without explicit ref e.g. arr(x,1) & arr(x,2) & ... & arr(x,8) ) to join first 8 columns per row?

Psuedo code:

myKey = LEFT(join(arr), 8)

where 8 represents 8th column (not 8th char!)

Ideally, without creating a function to mimic this either.

TIA,
Jack

 

[Fluff]

Something like

   ary = Range("A1:O20").Value2
   For i = 1 To UBound(ary)
      x = Join(Application.Index(ary, i, Array(1, 2, 3, 4, 5, 6, 7, 8)), ",")
   Next i

 

[Marcelo Branco]

Maybe...

myKey = Join(Application.Transpose(Application.Index(arr, 0, 8)), ",")
MsgBox myKey

M.

 

[JackDanIce]

Thank you for the replies.

Marcelo, unfortunately your suggestion returns a msgbox with lots of FALSEs!

Is it because you're slicing an entire column before transposing it? If so, I apologise for any confusion in explanation. I'd like to join the first 8 elements in a row of an array.


Fluff, yours is what I was after, though without writing out 1, 2, 3.. 8!
However, it works and seems like I can't avoid explicitly using 1, 2, 3... 8 (or via loop x = 1 to 8) & your suggestion slots into my existing code easy.

Thank you both for replying!



(Background, I have a horizontal calendar, 18 columns per month, which I convert into a flat-table (i.e. no gaps). Each value per calendar row turns into a "next" row on the flat table, hence needing a key when I turn flat-table back to the calendar view to detect when a "new" row on the calendar view is found)

 

[Fluff]

You could use another array for the columns you want like

   ary = Range("A1:O20").Value2
   Cols = Array(1, 2, 3, 4, 5, 6, 7, 8)
   For i = 1 To UBound(ary)
      x = Join(Application.Index(ary, i, Cols), ",")
   Next i

 

[JackDanIce]

Cool, thanks, understood your suggestion Fluff, I was just curious to see if there was syntax equivalence to "LEFT" or anyway to avoid explicitly listing all the column numbers out; after no joy searching online and seems not!

 

[Marcelo Branco]

Thank you for the replies.

Marcelo, unfortunately your suggestion returns a msgbox with lots of FALSEs!

Maybe I misunderstood what you want.
My test
Data

[Table="class: grid"][tr][td="bgcolor: #DCE6F1"][/td][td="bgcolor: #DCE6F1"]

A​

[/td][td="bgcolor: #DCE6F1"]

B​

[/td][td="bgcolor: #DCE6F1"]

C​

[/td][td="bgcolor: #DCE6F1"]

D​

[/td][td="bgcolor: #DCE6F1"]

E​

[/td][td="bgcolor: #DCE6F1"]

F​

[/td][td="bgcolor: #DCE6F1"]

G​

[/td][td="bgcolor: #DCE6F1"]

H​

[/td][td="bgcolor: #DCE6F1"]

I​

[/td][td="bgcolor: #DCE6F1"]

J​

[/td][/tr]
[tr][td="bgcolor: #DCE6F1"]

1​

[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

2​

[/td][td][/td][td]

DataB1​

[/td][td]

DataC1​

[/td][td]

DataD1​

[/td][td]

DataE1​

[/td][td]

DataF1​

[/td][td]

DataG1​

[/td][td]

DataH1​

[/td][td]

DataI1​

[/td][td]

DataJ1​

[/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

3​

[/td][td][/td][td]

DataB2​

[/td][td]

DataC2​

[/td][td]

DataD2​

[/td][td]

DataE2​

[/td][td]

DataF2​

[/td][td]

DataG2​

[/td][td]

DataH2​

[/td][td]

DataI2​

[/td][td]

DataJ2​

[/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

4​

[/td][td][/td][td]

DataB3​

[/td][td]

DataC3​

[/td][td]

DataD3​

[/td][td]

DataE3​

[/td][td]

DataF3​

[/td][td]

DataG3​

[/td][td]

DataH3​

[/td][td]

DataI3​

[/td][td]

DataJ3​

[/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

5​

[/td][td][/td][td]

DataB4​

[/td][td]

DataC4​

[/td][td]

DataD4​

[/td][td]

DataE4​

[/td][td]

DataF4​

[/td][td]

DataG4​

[/td][td]

DataH4​

[/td][td]

DataI4​

[/td][td]

DataJ4​

[/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

6​

[/td][td][/td][td]

DataB5​

[/td][td]

DataC5​

[/td][td]

DataD5​

[/td][td]

DataE5​

[/td][td]

DataF5​

[/td][td]

DataG5​

[/td][td]

DataH5​

[/td][td]

DataI5​

[/td][td]

DataJ5​

[/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

7​

[/td][td][/td][td]

DataB6​

[/td][td]

DataC6​

[/td][td]

DataD6​

[/td][td]

DataE6​

[/td][td]

DataF6​

[/td][td]

DataG6​

[/td][td]

DataH6​

[/td][td]

DataI6​

[/td][td]

DataJ6​

[/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

8​

[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]
[/table]

Sub aTest()
    Dim arr As Variant, myKey As Variant
    
    arr = Range("B2:J7")
    
    myKey = Join(Application.Transpose(Application.Index(arr, 0, 8)), ",")
    MsgBox myKey
End Sub

I've gotten this in Message Box
DataI1,DataI2,.....,DataI6

This is what you want, isn't?

M.

 

[Fluff]

As long as the columns are consecutive, you could use

      x = Join(Application.Index(ary, i, Evaluate("column(A:I)")), ",")

 

[JackDanIce]

Hi Marcelo, I think you did mis-understand, with Fluff's help I .. urgh did create a function to abstract it away from the main procedure!.

If my first row is: A, B, C, D, E, F, G, H, I J etc ,I wanted key to be "ABCDEFG"
Second row: 1, 2, 3, 4, 5, 6, 7, 8, 9, 10 => key "12345678"

The data is sorted, In my data, top 5 rows all have key: "ABCDEFG" but remaing columns have different date values (i.e. 5 different dates).

So for each data row, I need to map the value to a date but keep in the same row as the key as I turn the flat-table view, back to a calendar view - that's why I'm "splitting" my array by the 8th column. Hope this explains, it's just an annoying data translation I'm trying to figure out.

 

[JackDanIce]

Evaluate is word I seem to struggle with in VBA (not sure why!), though this I get - it's returning an array of (column) numbers that map to A:I.

And yes, the columns are consecutive