Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Positioning Numeric Values Resulting from 6 Column Array Formula
Hi All,
Can anyone assist with a VBA solution to place multiple numeric values in one cell based on my scenario below. Domenic has provided me with a Worksheet Formula that places the values in individual cells. However, if a VBA solution can be provided to put multiple values in the same cell, I would appreciate your assistance. Below is Domenic's Worksheet Formula solution: 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. My Scenario: 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 http://www.officekb.com |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Summing all numeric values only in cells in a column | Excel Worksheet Functions | |||
entering formula in a numeric array | Excel Discussion (Misc queries) | |||
Positioning Numeric Values Resulting from 6 Column Array Formula | Excel Programming | |||
Positioning Numeric Values Resulting from 6 Column Array Formula | Excel Worksheet Functions | |||
How to use an array or matrix to return text vs. numeric values | Excel Worksheet Functions |