Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.worksheet.functions
Sam via OfficeKB.com
 
Posts: n/a
Default Positioning Numeric Values Resulting from 6 Column Array Formula

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

--
Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.a...tions/200601/1
  #2   Report Post  
Posted to microsoft.public.excel.worksheet.functions
Domenic
 
Posts: n/a
Default Positioning Numeric Values Resulting from 6 Column Array Formula

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=COLUM NS($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!

In article <59d10266dd1b3@uwe, "Sam via OfficeKB.com" <u4102@uwe
wrote:

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

  #3   Report Post  
Posted to microsoft.public.excel.worksheet.functions
Sam via OfficeKB.com
 
Posts: n/a
Default Positioning Numeric Values Resulting from 6 Column Array Formula

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:


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


...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

Domenic wrote:
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=COLU MNS($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


--
Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.a...tions/200601/1
Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Match then lookup Tenacity Excel Worksheet Functions 9 December 3rd 05 06:30 AM
Formula Problem - interrupted by #VALUE! in other cells!? Ted Excel Worksheet Functions 17 November 25th 05 06:18 PM
Return Count for LAST NonBlank Cell in each Row Sam via OfficeKB.com Excel Worksheet Functions 12 April 17th 05 10:36 PM
Propagate Array Formula Down Column [email protected] Excel Discussion (Misc queries) 1 February 20th 05 08:42 AM
Match / Vlookup within an Array formula Hari Prasadh Excel Discussion (Misc queries) 3 February 3rd 05 05:37 PM


All times are GMT +1. The time now is 01:04 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"