testing

**Jewano** · 10-11-2020, 05:51 PM

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

[FONT=Arial]
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.
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
1	jeden		Trzeci
2

Worksheet: Mójarkusz

**Jewano** · 11-21-2020, 01:09 PM

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

__________________________________________________ __________________________________________________ __________________________________________________ _____________________

[FORMULA]vArray[/FORMULA]

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** · 12-04-2020, 01:33 PM

testing for
https://www.excelforum.com/excel-pro...ml#post5433028

Code:

'
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

Code:

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-pro...e-columns.html
https://www.excelforum.com/excel-pro...ml#post4381996
https://www.excelforum.com/tips-and-...ml#post5408376
https://www.excelforum.com/excel-pro...ml#post5410028

Originally Posted by Debanjan84

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…

Originally Posted by Debanjan84

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 [B
c d

The problem is that for normal text the forum editors they all usually take out all but one single space in between words.

But you can see what the OP posted if you first look what is in quote after you hit 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

http://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** · 12-04-2020, 02:31 PM

Hello Jewano,
( a belated , Welcome to ExcelFox )

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

Alan

**Jewano** · 12-04-2020, 02:41 PM

I can testit there, but cannot edit for longer than 20 days is possible.
( I did see this first , then it is to this 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** · 12-04-2020, 02:57 PM

Originally Posted by Jewano

...Is To do it OK is?

No issues

( Yes, (is to do it OK is ) )

**DocAElstein** · 12-04-2020, 03:12 PM

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

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

**Jewano** · 12-05-2020, 03:31 AM

https://www.mrexcel.com/board/thread.../#post-4370502
https://www.mrexcel.com/board/thread...2#post-4370985
https://www.excelforum.com/excel-pro...ml#post4382887
https://www.mrexcel.com/board/thread...2#post-4375560

test in supportjzke of this post: https://www.excelforum.com/excel-pro...ml#post5434477

Header1___Header2
_ a ________ B
_c_________d

Originally Posted by Debanjan84

can automate to display ...... data will be coming from the datasheet:...

What do it mean?? How do it come??

Is it transpose you want…

Originally Posted by Debanjan84

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:
[FORMULA]=INDEX(LEFT(A2:A7,6),{1,2,3;4,5,6},{1,1,1;1,1,1})[/FORMULA]
or
[FORMULA]=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)))[/FORMULA]

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

Code:

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