testing

[Jewano]

vArray = Application.Index(Range("A1:A10").Value, Evaluate("={1,2,3,4,5,6,7,8,9,10}"), Evaluate("={1,1,1,1,1,1,1,1,1,1}"))
vArray = Application.Index(Range("A1:A10").Value, Evaluate("={10,9,8,7,6,5,4,3,2,1}"), Evaluate("={1,1,1,1,1,1,1,1,1,1}")) ' Transpose it back to front
And its probably worth noting that when you do it that way , sometimes things seem to work better when you use just Range
vArray = Application.Index(Range("A1:A10"), Evaluate("={1,2,3,4,5,6,7,8,9,10}"), Evaluate("={1,1,1,1,1,1,1,1,1,1}"))
vArray = Application.Index(Range("A1: A10"), Evaluate("={10,9,8,7,6,5,4,3,2,1}"), Evaluate("={1,1,1,1,1,1,1,1,1,1}")) ' Transpose it back to front
Sometimes , better still, using Cells is another option
vArray = Application.Index(Cells, Evaluate("={1,2,3,4,5,6,7,8,9,10}"), Evaluate("={1,1,1,1,1,1,1,1,1,1}"))
vArray = Application.Index(Cells,("A1:A10"), Evaluate("={10,9,8,7,6,5,4,3,2,1}"), Evaluate("={1,1,1,1,1,1,1,1,1,1}")) ' Transpose it back to front


Using the Index( Arr() , Rws(), Clms() ) ) technique seems to be very versatile, and just one example would be to do that transpose, or variations of it.
[FONT=Arial]vArray = Application.Index(Range("A1:A10").Value, Evaluate("={1,2,3,4,5,6,7,8,9,10}"), Evaluate("={1,1,1,1,1,1,1,1,1,1}"))
vArray = Application.Index(Range("A1:A10").Value, Evaluate("={10,9,8,7,6,5,4,3,2,1}"), Evaluate("={1,1,1,1,1,1,1,1,1,1}")) ' Transpose it back to front
And its probably worth noting that when you do it that way , sometimes things seem to work better when you use just Range
vArray = Application.Index(Range("A1:A10"), Evaluate("={1,2,3,4,5,6,7,8,9,10}"), Evaluate("={1,1,1,1,1,1,1,1,1,1}"))
vArray = Application.Index(Range("A1: A10"), Evaluate("={10,9,8,7,6,5,4,3,2,1}"), Evaluate("={1,1,1,1,1,1,1,1,1,1}")) ' Transpose it back to front
Sometimes , better still, using Cells is another option
vArray = Application.Index(Cells, Evaluate("={1,2,3,4,5,6,7,8,9,10}"), Evaluate("={1,1,1,1,1,1,1,1,1,1}"))
vArray = Application.Index(Cells,("A1:A10"), Evaluate("={10,9,8,7,6,5,4,3,2,1}"), Evaluate("={1,1,1,1,1,1,1,1,1,1}")) ' Transpose it back to front



_____ Workbook: rejestr2.xls ( Using Excel 2007 32 bit )
Row\Col
A
B
C
D

1jedenTrzeci


2
Worksheet: Mójarkusz

[Jewano]

Test post

h t t p:/ /w w w.eileenslounge.com/viewtopic.php?p=271035#p271035
http://www.eileenslounge.com/viewtopic.php?p=271035#p271035

Arial

Courier New

__________________________________________________ __________________________________________________ __________________________________________________ _____________________



vArray


Index( Arr() , Rws(), Clms() ) )


Ref
https://www.excelforum.com/excel-new-users-basics/1099995-application-index-with-look-up-rows-and-columns-arguments-as-vba-arrays.html#post4571172
https://www.excelforum.com/tips-and-tutorials/758402-vba-working-with-areas-within-2d-arrays.html#post5408376
http://www.eileenslounge.com/viewtopic.php?p=271035#p271035
https://www.ozgrid.com/forum/index.php?thread/1227920-slicing-a-2d-array/&postID=1239241#post1239241
https://eileenslounge.com/viewtopic.php?p=274367&sid=6b84ff6917c71e849aaeaa281d06fc31#p27436
https://eileenslounge.com/viewtopic.php?f=30&t=34217&p=265384#p265384

Like saided already, if default Option Base 0 it is at then like
Dim Stats(20, 23) As Variant
is same is
Dim Stats(0 To 20, 0 To 23) As Variant

it do mean is a 2 Dimensional array that has first dimension size of 21 and second dimension size of 24
You can say like 21x24 sized 2 D array

It is not really a table or a range, but we can think of it like that just for our convenience to think like it looks like this:





















































Excel VBA will also be thinking Like that : For 21 rows and 24 columns, you can put in array and it will have similar dimensions
Example is like: If I did do
Dim MyArr1() As Variant
Let MyArr1()=Range("A1:X21").Value

or
Dim MyArr2() As Variant
Let MyArr2()=Range("B2:Y22").Value

then all arrays are like 21x24, and Element type it is Variant,
but only small differences:,
Stats() it is fixed size, but like MyArr2() and MyArr1() they is dynamic, not a fixed size).
and also
MyArr2() and MyArr1() will be like (1 To 21, 1 To 24) always: For array from a spreadsheet range, always dimension indicia start at 1
So this is why we can think, for convenience that
Arr() is like similar to Arr(row , column)
But it is a bit more complicates, so I did try to explain that for you

Jewano

[Jewano]

testing for
https://www.excelforum.com/excel-programming-vba-macros/1334235-automate-status.html#post5433028


'
Sub Clms()
Dim Ws As Worksheet: Set Ws = ThisWorkbook.Worksheets("IndexWithArraysWTF") '--Change to Suit Your Sht Preferrence

Dim Clms() As Variant: Let Clms() = Evaluate("=column(A:H)") ' Returns 1, 2, 3, 4, 5, 6, 7, 8
Dim vTemp As Variant: Let vTemp = Evaluate("=mod(column(A:H),4)") 'Retuns Long Number 1
Let Clms() = Evaluate("=If(column(A:H),mod(column(A:H),4))") 'Returns 1, 2, 3, 0, 1, 2, 3, 0
Let Clms() = Evaluate("=If(column(A:G),mod(column(A:H),4))") 'Returns 1, 2, 3, 0, 1, 2, 3, error
Let Clms() = Evaluate("=If(column(),mod(column(A:H),4))") 'Returns 1, 2, 3, 0, 1, 2, 3, 0
Let Clms() = Evaluate("=If(row(),mod(column(A:H)-1,4))") 'Returns 0, 1, 2, 3, 0, 1, 2, 3
Let Clms() = Evaluate("=Index((mod(column(A:H)-1,4)+1),)") 'Returns 1, 2, 3, 4, 1, 2, 3, 4
End Sub

Sub Rws()
Dim Ws As Worksheet: Set Ws = ThisWorkbook.Worksheets("IndexWithArraysWTF") '--Change to Suit Your Sht Preferrence
Dim Rws() As Variant: Let Rws() = Evaluate("=column(E:L)") ' Returns 5, 6, 7, 8, 9, 10, 11, 12
Let Rws() = Evaluate("=Index((int(column(E:L)/4)),)") 'Returns 1, 1, 1, 2, 2 ,2, 2, 3
Let Rws() = Evaluate("=Index((int((column(E:L)-1)/4)),)") 'returns 1, 1, 1, 1, 2, 2, 2, 2
Let Rws() = Evaluate("=Index(((int((column(E:L)-1)/4))+1),)") 'returns 2, 2, 2, 2, 3, 3, 3, 3
End Sub
'
Sub AppIndexRT23C1234()
Dim Ws As Worksheet: Set Ws = ThisWorkbook.Worksheets("IndexWithArraysWTF")
Dim Rws() As Variant: Let Rws() = Evaluate("=row(2:3)") 'Returns 2 D 1 "column" Array , values 2 / 3
Dim Clms() As Variant: Let Clms() = Evaluate("=column(A:D)") ' Returns 1, 2, 3, 4

Dim arrOut() As Variant
Let arrOut() = Application.Index(Ws.Cells, Rws(), Clms())
End Sub
'
Sub AppIndexRT23CT1234()
Dim Ws As Worksheet: Set Ws = ThisWorkbook.Worksheets("IndexWithArraysWTF")
Dim Rws() As Variant: Let Rws() = Evaluate("=row(2:3)") 'Returns 2 D 1 "column" Array , values 2 / 3
Dim Clms() As Variant: Let Clms() = Evaluate("=row(1:4)") 'Returns 2 D 1 "column" Array , values 1 / 2 / 3 / 4

Dim arrOut() As Variant
Let arrOut() = Application.Index(Ws.Cells, Rws(), Clms())
End Sub
'
Sub AppIndexR23CT1234()
Dim Ws As Worksheet: Set Ws = ThisWorkbook.Worksheets("IndexWithArraysWTF")
Dim Rws() As Variant: Let Rws() = Evaluate("column(B:C)") 'Returns 1 D Array 2, 3
Dim Clms() As Variant: Let Clms() = Evaluate("=row(1:4)") 'Returns 2 D 1 "column" Array , values 1 / 2 / 3 / 4

Dim arrOut() As Variant
Let arrOut() = Application.Index(Ws.Cells, Rws(), Clms())
End Sub


Sub burakGenerateSequentialColumnIndiciesFromLetters() 'Dec 9 usefulgyaan.wordpress.com/2013/06/12/vba-trick-of-the-week-slicing-an-array-without-loop-application-index/
'Variables for...
Dim LB As Long, UB As Long '...User Given start and Stop Column as a Number
Let LB = 2: Let UB = 25
Dim strLtrLB As String, strLtrUB As String '...Column Letter corresponding to Column Number
'There are many ways to get a Column Letter from a Column Number - excelforum.com/tips-and-tutorials/1108643-vba-column-letter-from-column-number-explained.html
Let strLtrLB = Split(Cells(1, LB).Address, "$")(1) 'An Address Method
Let strLtrUB = Replace(Replace(Cells(1, UB).Address, "1", ""), "$", "") 'A Replace Method
'Obtain Column Indicies using Spreadsheet Function Column via VBA Evaluate Method
Dim Clms() As Variant
Let Clms() = Evaluate("column(" & strLtrLB & ":" & strLtrUB & ")") 'Returns 1 D "pseudo" Horizontal Array of sequential numbers from column number of LB to UB
'Or
Clms() = Evaluate("column(" & Split(Cells(1, LB).Address, "$")(1) & ":" & Replace(Replace(Cells(1, UB).Address, "1", ""), "$", "") & ")")
End Sub


https://www.excelforum.com/excel-programming-vba-macros/1138627-dividing-the-items-of-an-array-over-multiple-columns.html
https://www.excelforum.com/excel-programming-vba-macros/1138428-multidimensional-array-to-single-column-range.html#post4381996
https://www.excelforum.com/tips-and-tutorials/758402-vba-working-with-areas-within-2d-arrays.html#post5408376
https://www.excelforum.com/excel-programming-vba-macros/1328703-copy-1-dim-array-to-and-2-dim-array.html#post5410028


I am looking for excel which can automate to display this:

Backlog In Progress Done
US-101 US-202 US-304
US-205 US-305 US-201

These data will be coming from the datasheet:

ID Status
US-101 Backlog
US-202 In Progress
US-304 Done
US-205 Backlog
US-305 In Progress
US-201 Done

Is it transpose you want…

display this:
_
Backlog__In Progress__Done
US-101__US-202____US-304
US-205__US-305____US-201

from These data
_
_ID_____Status
US-101__Backlog
US-202__In Progress
US-304__Done
US-205__Backlog
US-305__In Progress
US-201__Done
Is it 2 columns to transpose to the 3 columns?


Hello nigelog
Just some info – you may know it already so can ignore this then ….

When OP is new he knows nothing about forum software sometimes he does not know things like… code tags; tables format; forum editor it does “eat” extra spaces etc. etc.

OP, it will often try to show table like maybe

Header1___Header2
_ a ________ B
_c_________d

But we will all then see after posting in final post is like:

Header1 Header2
a Reply With Quote

Example, look here, https:/2/www.excelforum.com/excel-programming-vba-macros/1334235-automate-status.html
You see difference between post and what is in quote after you hit Reply With Quote
http://i.imgur.com/TIDS37S.jpg https://i.imgur.com/TIDS37S.jpg
http://i.imgur.com/VE8aYty.jpg https://i.imgur.com/VE8aYty.jpg

Look here:
https://www.excelforum.com/excel-programming-vba-macros/1334235-automate-status.html#post5432969

( Is difficult for me to post link until I have at least 10 post rule is )

[DocAElstein]

Hello Jewano,
( a belated , Welcome to ExcelFox )

Why have you created a new profile to do this testing? :confused:

Alan

[Jewano]

I can testit there (https://www.excelforum.com/development-testing-forum/1329800-test-solved.html), but cannot edit for longer than 20 days is possible.
( I did see this (https://excelfox.com/forum/showthread.php/2345-Appendix-Thread-(-Codes-for-other-Threads-HTML-Tables-etc-)/page2) first , then it is to this (https://excelfox.com/forum/showthread.php/2346-Testing-Posts-Internet-Forum-Software?p=11376&viewfull=1#post11376) gonded.

( Is all here referenced many your postings ( Doc.AElstein ) is also , is why I came here is )

Is To do it OK is?

Jewano

[DocAElstein]

...Is To do it OK is?

No issues :)
( Yes, (is to do it OK is ) )

[DocAElstein]

Jewano,
Please if you get a PM, then after read it delete it:
Because is...
If your Post box is full, then you can not get any more PMs.
( Is only small limit numbert of PMs can have at excelfox )

Alan

http://i.imgur.com/xpFYD81.jpg https://i.imgur.com/xpFYD81.jpg
http://i.imgur.com/2IJzxg4.jpg https://i.imgur.com/2IJzxg4.jpg

[Jewano]

https://www.mrexcel.com/board/threads/vba-copy-2-dimensional-array-into-1-dimensional-single-column.908760/#post-4370502
https://www.mrexcel.com/board/threads/vba-copy-2-dimensional-array-into-1-dimensional-single-column.908760/page-2#post-4370985
https://www.excelforum.com/excel-programming-vba-macros/1138627-dividing-the-items-of-an-array-over-multiple-columns.html#post4382887
https://www.mrexcel.com/board/threads/vba-copy-2-dimensional-array-into-1-dimensional-single-column.908760/page-2#post-4375560


test in supportjzke of this post: https://www.excelforum.com/excel-programming-vba-macros/1334235-automate-status.html#post5434477


Header1___Header2
_ a ________ B
_c_________d



can automate to display ...... data will be coming from the datasheet:... :confused: What do it mean?? How do it come??

Is it transpose you want…

display this:
_
Backlog__In Progress__Done
US-101__US-202____US-304
US-205__US-305____US-201

from These data
_
_ID_____Status
US-101__Backlog
US-202__In Progress
US-304__Done
US-205__Backlog
US-305__In Progress
US-201__Done
Is it 2 columns to transpose to the 3 columns?
Or
Is it 1 columns to transpose to the 3 columns
Data is it in a worksheet? …… “coming from the datasheet…”..

It can be formula , ( is CSE ( type 2 ) enter (might be not in 365 Office ) . It can be from 1 column or 2 columns, - I do show it is in the attached workbook, (Transpozycja.xls ) like that is:
=INDEX(LEFT(A2:A7,6),{1,2,3;4,5,6},{1,1,1;1,1,1})
or
=INDEX(LEFT(A2:A7,6),(COLUMN(A:C)+((ROW(1:2)-1)*3)),(ROW(1:2)/ROW(1:2))*(COLUMN(A:C)/COLUMN(A:C)))


We can do it same as in VBA, I did also do it in attached workbook, (Transpozycja.xls )
Data can be in worksheet DataWKSheet – see it is in attached workbook is

Option Explicit
Sub TransSpozgy() ' https://www.excelforum.com/excel-programming-vba-macros/1334235-automate-status.html
Let ThisWorkbook.Worksheets("VBA Solution").Range("A1:C1").Value = Array("Backlog", "In Progress", "Done")
Let ThisWorkbook.Worksheets("VBA Solution").Range("A2:C3").Value = Application.Index(ThisWorkbook.Worksheets("DataWKSheet").Evaluate("=if({1},LEFT(A2:A7,6))"), Evaluate("=column(A:C)+((row(1:2)-1)*3)"), Evaluate("=(ROW(1:2)/ROW(1:2))*(COLUMN(A:C)/COLUMN(A:C))"))
End Sub
' Or
Sub TransSprogy() ' https://www.excelforum.com/excel-programming-vba-macros/1334235-automate-status.html
Let ThisWorkbook.Worksheets("VBA Solution").Range("A1:C1").Value = Array("Backlog", "In Progress", "Done")
Dim Lr As Long
Let Lr = ThisWorkbook.Worksheets("DataWKSheet").Range("A" & ThisWorkbook.Worksheets("DataWKSheet").Rows.Count & "").End(xlUp).Row
Let ThisWorkbook.Worksheets("VBA Solution").Range("A2:C" & ((Lr - 1) / 3) + 1 & "").Value = Application.Index(ThisWorkbook.Worksheets("DataWKSheet").Evaluate("=if({1},LEFT(A2:A" & Lr & ",6))"), Evaluate("=column(A:C)+((row(1:" & (Lr - 1) / 3 & ")-1)*3)"), Evaluate("=(ROW(1:" & (Lr - 1) / 3 & ")/ROW(1:" & (Lr - 1) / 3 & "))*(COLUMN(A:C)/COLUMN(A:C))"))
End Sub



Ref:
https://www.mrexcel.com/board/threads/vba-copy-2-dimensional-array-into-1-dimensional-single-column.908760/#post-4370502
https://www mrexcel.com/board/threads/vba-copy-2-dimensional-array-into-1-dimensional-single-column.908760/page-2#post-4370985
https://www.excelforum.com/excel-programming-vba-macros/1138627-dividing-the-items-of-an-array-over-multiple-columns.html#post4382887
https://www.mrexcel.com/board/threads/vba-copy-2-dimensional-array-into-1-dimensional-single-column.908760/page-2#post-4375560