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