Excel Array Formula Series #2: TRANSPOSE function

Hello all, hope you are well!

My problem:

I have a mastersheet which uses TRANSPOSE() as an array function to return values from another workbook. The transpose has an INDIRECT in it so it knows which file t look at and which sheet within that file it should look at.

The issue is that, unlike paste special > transpose, TRANSPOSE returns blank cells as 0. The data it is looking at contains both blank cells and 0s, so I want to be able to return both. I tried an IF(ISBLANK()) but couldn't get this to work in the array.

Two spreadsheets are attached. I am not tied to TRANSPOSE so anything you think would work would be great.

Thanks,

Tom

Is there a maximum size of the array when moving data as this:


ActiveSheet.Range(Astr).Value = Application.WorksheetFunction.Transpose(TemPvalues)


The array is declared like this


Dim TemPvalues() as string

ReDim TemPvalues(ColLast - ColFirst + 1, NumTot)

where

ColLast - ColFirst + 1= 700

NumTot=15000

Astr contains 700 columns and 15000 Rows


FYI - I am reading the data from other file types - so importing data into Excel

Hi,

I have a list of names in column B, separated by categories. Each category name is followed by list of names. Each category has different # of names under it.

For eg. a category name in cell B4 is followed by 6 names (B5:B10); then follows a blank cell (B11); then 2nd category name in a cell (B12) followed by 4 names (B13:B16); and so on and so forth.

I want to fill the column A with numbers against the names (in column B) only and not against the blank cells and category names.

I can fill the series using + sign or "=A5+1" which is not what I am looking for. When I add a name in any of the categories or delete a name from a category, the fill series has to be updated completly, from below the changed row, manually.

I tried the following array formula -

{=IF(AND(COUNTIF(B6,"*")>0,COUNTIF(B5,"*")>0),(IF(COUNTIF(B4,"*")>0,B5+1,B3+1)),"")}

This formula returns 1 in A5 but each subsequent cell in column A is giving #VALUE! except the cells in column A that have category name or blank cell in column B.

So it is doing one part i.e. not numbering cells against category names and blank cells but it is not adding the way I want them to.

Would greatly appreciate if someone can help me with this.

Thanks.

I have a formula for a spline function that I grabbed off the internets. It will only work if the input ranges and output ranges are columns.

I would really like to use this when inputs and outputs are in rows, but cant figure out how to make it work...Application.transpose() does not help either

any help would be greatly appreciated.

Code:

Function CHSplineA(Xa As Variant, Ya As Variant, Xint As Variant)

    Dim i As Long, n As Long, nInt As Long, Yint() As Double, j As Long, T As Double
    Dim L() As Double, S() As Double, M() As Double, H() As Double, Cubica() As Double
    Dim Alpha As Double, Beta As Double, Tau As Double

    If TypeName(Xa) = "Range" Then Xa = Xa.Value2
    If TypeName(Ya) = "Range" Then Ya = Ya.Value2
    If TypeName(Xint) = "Range" Then Xint = Xint.Value2

    n = UBound(Xa)
    nInt = UBound(Xint)

    ReDim L(1 To n - 1)
    ReDim S(1 To n)
    ReDim M(1 To n)
    ReDim H(1 To n)
    
    ReDim Yint(1 To nInt, 1 To 1)
    ReDim FinalVal(1 To nInt)
    ReDim Cubica(1 To n - 1, 1 To 4)

    i = 1
    L(i) = Xa(i + 1, 1) - Xa(i, 1)
    H(i) = Ya(i + 1, 1) - Ya(i, 1)
    S(i) = (H(i) / L(i))
    M(i) = S(i)
    For i = 2 To n - 1
        L(i) = Xa(i + 1, 1) - Xa(i, 1)
        H(i) = Ya(i + 1, 1) - Ya(i, 1)
        S(i) = (H(i) / L(i))
        M(i) = (S(i - 1) + S(i)) / 2
    Next i
    H(i) = H(i - 1)
    S(i) = S(i - 1)
    M(i) = S(i)

    For i = 1 To n - 1
        Cubica(i, 1) = 2 * (Ya(i, 1) - Ya(i + 1, 1)) + (M(i) + M(i + 1)) * L(i)
        Cubica(i, 2) = 3 * (Ya(i + 1, 1) - Ya(i, 1)) - (2 * M(i) + M(i + 1)) * L(i)
        Cubica(i, 3) = M(i) * L(i)
        Cubica(i, 4) = Ya(i, 1)
    Next i

    For i = 1 To nInt
        j = 0
        Do
            j = j + 1
        Loop While (Xint(i, 1) > Xa(j + 1, 1) And j < n - 1)
        T = (Xint(i, 1) - Xa(j, 1)) / L(j)
        
        FinalVal(i) = Cubica(j, 1) * T ^ 3 + Cubica(j, 2) * T ^ 2 + Cubica(j, 3) * T + Cubica(j, 4)
    Next i
    
    CHSplineA = FinalVal

End Function

Hello;

1) I need to calculate M43:: sum[m=1 to m=9] G(m)*sin(m*x) for each value of tabulated x in column L.
The 9 values of "m" are tabulated in $D$21:$D$29
The 9 values of G(m) are tabulated in $C$21:$C$29
The first x value is in cell L43

Can someone please help in deriving the array formula to be entered in M43 ??
For other values of x in L44, L45, ... one would simply copy M43 and paste in M44, M45, ...

2) If it's not too much trouble, how about:
N43:: -sum[m=1 to m=9] G(m)*cos(m*x)/m for each value of tabulated x

Your help would be greatly appreciated.

Regards.

Hi All,

For filtering out unique values from a column and listing them all together in an adjacent column, this formula I found on another forum: {=OFFSET($A$1,MATCH(0,MMULT(--TRANSPOSE(TRANSPOSE($A$1:$A$15)=B$1:B1),ROW(INDIRECT("1:"&ROW()-1))/ROW(INDIRECT("1:"&ROW()-1))),0)-1,0,1,1)}

works just great, yet I am trying to understand exactly how its middle part works, but haven't been able to so far!
Maybe I am a bit behind on the math involved here, but could somebody please explain how the matrix transposition and multiplication works in this case, in particular - how can he compare that transposed original list is equal to part of new filtered list? and what is the second array in the MMULT(array1,array2) in this case, it seems it would be all 1/1's, or is it getting into matrix division here somehow?

I tried taking the formula apart into pieces, but after several hours still am stuck with the middle part...

I take the liberty to quote part of this 2005 post http://www.excelforum.com/tips-and-t...ue-values.html by Alan, describing the formula's use:
"... If you have a list in A1:A13 as
follows:

Alan
Bob
Charlie
Alan
Alan
Alan
Charlie
Dave
Charlie
Bob
Edgar
Bob
Bob

In
B1 enter either "Alan" or link to A1

In B2 enter this as an array
formula:

{=OFFSET($A$1,MATCH(0,MMULT(--TRANSPOSE(TRANSPOSE($A$1:$A$13)=B$1:B1),ROW(INDIRECT("1:"&ROW()-1))/ROW(INDIRECT("1:"&ROW()-1))),0)-1,0,1,1)}

To
enter the array formula, type it in without the curly brackets, and then enter
using Shift-Ctrl-Enter and excel will put the braces on the formula.

Copy
that formula down to B2:B13.

It returns 'Not Applicable' in B6:B13 since there are no more unique entries. ..."



It seems that Alan has not posted there for a long time, so I am asking in this forum, which appears quite active and hoping with gratitude to be enlightened.

Roman

How to create a number series within an array formula, I mean, the series starts with 1 and will finish with 8 for example;
{1;2;3;4;5;6;7;8} - It will be the result within my array formula.
In Aij matrix means i=1 to 8 {i+1;i+1;i+1;i+1;i+1;i+1;i+1;i+1}.
I need to retrieve it within an array formula.

How can I do that just using an array formula.



Luthius

I have a list like this:

Sheet1

  A B C 1 Alfa Status D 75 2 Bravo Status A   3 Charlie Status B 483 4 Delta Status B 78 5 Echo Status C   6 Foxtrot Status A   7 Golf Status B   8 Hotel Status B 12 9 India Status A   10 Juliett Status A 405 11 Kilo Status C   12 Lima Status B   13 Mike Status A   14 November Status B 27 15 Oscar Status B 13 16 Papa Status B   17 Quebec Status C 140 18 Romeo Status B 426 19 Sierra Status C 484 20 Tango Status D 197

Excel tables to the web >> Excel Jeanie HTML 4

I want to single out all Status B values, and put them in a new table in descending order.

In this case, it would look like this:

Sheet1

  A B C 10 Charlie Status B 483 11 Romeo Status B 426 12 Delta Status B 78 13 November Status B 27 14 Oscar Status B 13 15 Hotel Status B 12

Excel tables to the web >> Excel Jeanie HTML 4

So I need to ignore blank values, determine which row has the highest quantity, and then return the value in column A and column C.

I know I can use array formulas to a certain extent, but I'm stumped as to how to store the row of the highest values in the array while ignoring blanks. Any ideas?

Hi All,

I have the following data which is just a small slice of the larger dataset. As you can see from the next table I would like to transpose the table and at the same time sum up each column. I have one of the MrExcel PodCasts about an offset function and I thougt this might work but to no avail. Any thoughts? Results should be the third table.

Sheet2

* A B C D E F 1 * 1 2 3 4 5 2 1.1 36 77 75 77 96 3 1.2 96 86 15 28 96 4 1.3 84 29 17 97 50

Excel tables to the web >> Excel Jeanie HTML 4

Sheet2

* I J 1 * Summary 2 1 #REF! 3 2 * 4 3 * 5 4 * 6 5 *
Spreadsheet Formulas Cell Formula J2 {=SUM(IF($B$1:$F$1=I2,SUM(OFFSET($B$1:$F$1,1,0,3,0)),0))} Formula Array:
Produce enclosing { } by entering
formula with CTRL+SHIFT+ENTER!

Excel tables to the web >> Excel Jeanie HTML 4

Sheet2

* I J 1 * Summary 2 1 216 3 2 192 4 3 107 5 4 202 6 5 242
Spreadsheet Formulas Cell Formula J2 =SUM(B2:B4) J3 =SUM(C2:C4) J4 =SUM(D2:D4) J5 =SUM(E2:E4) J6 =SUM(F2:F4)

Excel tables to the web >> Excel Jeanie HTML 4