Partial array slice

JackDanIce

Well-known Member
Joined
Feb 3, 2010
Messages
9,671
Office Version
365
Platform
Windows
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:
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
 

Some videos you may like

Excel Facts

Which Excel functions can ignore hidden rows?
The SUBTOTAL and AGGREGATE functions ignore hidden rows. AGGREGATE can also exclude error cells and more.

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
40,973
Office Version
365
Platform
Windows
Something like
Code:
   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
 

JackDanIce

Well-known Member
Joined
Feb 3, 2010
Messages
9,671
Office Version
365
Platform
Windows
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)
 
Last edited:

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
40,973
Office Version
365
Platform
Windows
You could use another array for the columns you want like
Code:
   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

Well-known Member
Joined
Feb 3, 2010
Messages
9,671
Office Version
365
Platform
Windows
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

MrExcel MVP
Joined
Aug 23, 2010
Messages
16,371
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

A
B
C
D
E
F
G
H
I
J
1
2
DataB1​
DataC1​
DataD1​
DataE1​
DataF1​
DataG1​
DataH1​
DataI1​
DataJ1​
3
DataB2​
DataC2​
DataD2​
DataE2​
DataF2​
DataG2​
DataH2​
DataI2​
DataJ2​
4
DataB3​
DataC3​
DataD3​
DataE3​
DataF3​
DataG3​
DataH3​
DataI3​
DataJ3​
5
DataB4​
DataC4​
DataD4​
DataE4​
DataF4​
DataG4​
DataH4​
DataI4​
DataJ4​
6
DataB5​
DataC5​
DataD5​
DataE5​
DataF5​
DataG5​
DataH5​
DataI5​
DataJ5​
7
DataB6​
DataC6​
DataD6​
DataE6​
DataF6​
DataG6​
DataH6​
DataI6​
DataJ6​
8

Code:
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

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
40,973
Office Version
365
Platform
Windows
As long as the columns are consecutive, you could use
Code:
      x = Join(Application.Index(ary, i, Evaluate("column(A:I)")), ",")
 

JackDanIce

Well-known Member
Joined
Feb 3, 2010
Messages
9,671
Office Version
365
Platform
Windows
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

Well-known Member
Joined
Feb 3, 2010
Messages
9,671
Office Version
365
Platform
Windows
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 :)
 
Last edited:

Watch MrExcel Video

Forum statistics

Threads
1,099,607
Messages
5,469,654
Members
406,665
Latest member
priyankape2005

This Week's Hot Topics

Top