OK....
Assume your data is in the range A2:B12.
Create these named ranges:
InsertNameDefine
Name: Acct
Refers to: =$A$2:$A$12
Name: Line
Refers to: =$B$2:$B$12
List the unique Acct numbers in a range of cells. Assume they're in the
range D2:D4. You can do this several ways: type them in manually, use a
formula, or use the advanced filter to extract the uniques and copy to a new
location.
Enter this array formula** in E2:
=MIN(IF(Acct=D2,Line))
Copy down to D4
Enter this array formula** in F2:
=IF(COLUMNS($E2:F2)<=COUNT(1/FREQUENCY(IF(Acct=$D2,Line),Line)),MIN(IF((Acct=$D 2)*(LineE2),Line)),"")
Copy down to F4.
Select the range F2:F4 and copy across until you get a solid column full of
blanks.
** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER). Hold down both the CTRL key and the SHIFT
key then hit ENTER.
This portion of the formula can be calculation intensive if you have a large
amount of data:
COUNT(1/FREQUENCY(IF(Acct=$D2,Line),Line))
You can place this as a separate formula in a helper cell and then refer to
that cell instead of using it in every formula. That formula returns the
count of unique line numbers per acct. This is used as an error trap so that
when the formula is copied across and as it "runs out of data" it will
return a blank rather than an error.
--
Biff
Microsoft Excel MVP
"jbf" wrote in message
...
Yes...that would be great.
"T. Valko" wrote:
This is what I want the result to look like.
1010 12
5
7
1020 22
13
1030 10
I can get the result to look like this:
1010...5...7...12
1020...13...22
1030...10
Do you want to go with that?
--
Biff
Microsoft Excel MVP
"jbf" wrote in message
...
Hi Biff,
I've read quite a few of the posts on returning multiple corresponding
values but everything is basically over my head. I don't have any
training
or
exp in this stuff. I've tried to replicate the example at:
http://office.microsoft.com/en-us/ex...0551033#Remove
I was able to return only one value for ashish 234. I couldn't get the
formula to return 534 or 834. Here's the formula I have.
=INDEX($A$1:$B$7,SMALL(IF($A$1:$A$7=A10,ROW($A$1:$ A$7)),ROW(1:1)),2)
Even if I get this right, I'm not sure if it is what I need. I have a
data
set where one value can have multiple corresponding values. It's not a
1:1
relationship. I want all values returned but only the distinct values.
Here's
an example of the data.
Acct Line
1010 12
1010 12
1010 12
1010 12
1010 12
1010 5
1010 7
1010 5
1020 22
1020 13
1030 10
This is what I want the result to look like.
1010 12
5
7
1020 22
13
1030 10
Can you help, please?
"Biff" wrote:
Hi!
this formula works if the sheet is sorted by the value i'm looking
up
and
if
there are no duplicates in the field I want returned.
The sheet does not need to be sorted and it doesn't matter if there
are
dupe
return values.
Post the *EXACT* formula that you tried.
I then want to include this
in a weekly dashboard for the 50+ managers i'm monitoring.
have you got anything else?
Pivot table or filter
Biff
"MetricsShiva" wrote in
message
...
this formula works if the sheet is sorted by the value i'm looking
up
and
if
there are no duplicates in the field I want returned. Otherwise i
get
either
incorrect values returned or errors..
basically, i have a sheet listing jobs scheduled by managers. I
want
to
be
able to look up the manager's name and return a list of all the
job's
scheduled and the dates they were scheduled on. I then want to
include
this
in a weekly dashboard for the 50+ managers i'm monitoring.
Thanks for the response, but have you got anything else?
"Biff" wrote:
Hi!
The basic formula is something like this:
Entered as an array using the key combo of CTRL,SHIFT,ENTER:
=INDEX(B$1:B$10,SMALL(IF(A$1:A$10=lookup_value,ROW ($1:$10)),ROW(1:1)))
Then copy down.
Where column A contains the lookup_value and column B contains the
values
to
be returned.
Need more specific details to offer a more robust suggestion.
Biff
"MetricsShiva" wrote in
message
...
i want to look up a name that occurs several times in one column
of a
spreadsheet and return corresponding values from each row the
name
occurs
on.
Vlookup returns only one value. How can I get multiple values?