Positioning Numeric Values Resulting from 6 Column Array Formula

[Sam via OfficeKB.com]

Hi All,

Is it possible to position/ place numeric values resulting from a six column
array worksheet Formula?

Sample Data - Row of results from Array Formula:
-----Col"N"----Col"O"----Col"P"----Col"Q"----Col"R"----Col"S"
Row4-35--------40--------62--------63----------70---------74

The numeric values above are individually housed in Column "A" to be used as
Numeric Labels.
Column "D" houses the the Column Positions where the numeric values should be
located.

My 1st (first) blank Column after the six column array entered Formula is
Column "T", this will always be the first column - Column Number 1.

Sample Data - Numeric Labels and Column Numbers:
Col"A"------------------Col"D"
30----------------------8
35----------------------1
37----------------------3
40----------------------3
45----------------------10
50----------------------2
53----------------------4
57----------------------11
60----------------------5
62----------------------1
63----------------------6
70----------------------9
72----------------------7
74----------------------9
75----------------------12

So based on the above Array Formula (Row4, Columns "N" to "S"), the 1st
numeric value to be placed in its relevant Column is value 35. Numeric Label
35 is located in Column "A" and Column "D" houses the Column Numbers where
the Numeric Values should be placed. For Numeric Label 35, the Column Number
is 1. Column "T" is the 1st column - value 35 should be placed in Column "T".
Numeric Label 40 should be placed in the 3rd Column (counting Column "T" as 1
- one), that is Column "V".

Is it possible for the Formula to check the numeric values in the Array
Formula Result (Row4, Column "N" to Column "S" 35,40,62,63,70,74) against
their Numeric Labels in Column "A" and their Column Number in Column "D" and
concatenate values that have the same Column Number. For example; Numeric
Labels 35 and 62 both have 1 (one) as their Column Number and should be in
the same Column - Column "T".


Thanks
Sam

 

[Domenic]

Try the following, which will place values in individual cells...

T4, copied across and down:

=SMALL(IF(($A$1:$A$15=$N$4:$S$4)*($B$1:$B$15=COLUMNS($T$4:T4)),$A$1:$A$15
),ROWS(T$4:T4))

....confirmed with CONTROL+SHIFT+ENTER.

Note that the formula will return #NUM! where the cell's column number
doesn't have a matching value. You can use conditional formatting to
hide them. It's the simplest and, I think, most efficient way.

Also, I've assumed that the values in Column A are listed in ascending
order. If this is not the case, and you want your values listed on a
first come, first serve basis, post back. Otherwise, your values will
be listed in ascending order, regardless of actual order.

Hope this helps!

 

[Sam via OfficeKB.com]

Hi Domenic,

Thank you very much for your assistance. The Formula as explained works Great!

Try the following, which will place values in individual cells...

T4, copied across and down:

...confirmed with CONTROL+SHIFT+ENTER.

Based on my original scenario: If someone could work around your Formula
using VBA so that multiple values could be placed in one cell that would also
be useful. I may try a Post in the Programming Forum.

Once again, Thank you .

Cheers
Sam

Try the following, which will place values in individual cells...

T4, copied across and down:

=SMALL(IF(($A$1:$A$15=$N$4:$S$4)*($B$1:$B$15=COLUMNS($T$4:T4)),$A$1:$A$15
),ROWS(T$4:T4))

...confirmed with CONTROL+SHIFT+ENTER.

Note that the formula will return #NUM! where the cell's column number
doesn't have a matching value. You can use conditional formatting to
hide them. It's the simplest and, I think, most efficient way.

Also, I've assumed that the values in Column A are listed in ascending
order. If this is not the case, and you want your values listed on a
first come, first serve basis, post back. Otherwise, your values will
be listed in ascending order, regardless of actual order.

Hope this helps!

Hi All,

[quoted text clipped - 48 lines]

Thanks
Sam