one criteria/mult. values

[Guest]

I have two columns of importance in the same sheet one is acct numbers, the
other is a date. I want to produce a sheet that shows the acct number once
and then displays which date coincide with it. for instance:

acct # | date | date | date |
5 | 1/15|2/2 | 3/9 |
2 |4/11 |
3 |3/7 |3/5 |

I only want to show acct once, but show every date the acct showed up on
next to it, how many dates it occurs on differs as I tried to show in the
example. Please help!

 

[L. Howard Kittle]

One way.

With your acct#'s in A39:A43 and the dates in B, C, D columns.

Select four cells in a row and while selected type in this formula. While
still selected hit Ctrl -Shift - Enter. (array entered)

=VLOOKUP(F39,A39:E43,{2,3,4,5},0)

Enter you lookup acct# in F39

This will return up to four dates for any given acct#. If an acct# has only
two dates, you will get two dates and two 0's.

Any time you want to change the formula, you will have to select all four
cells and make the change to the one shown in the formula bar and re-enter
using Ctrl-Shift-Enter.

This formula would return 9 values. You would have to select 9 cells in a
row to enter as above.

=VLOOKUP(F39,A39:J43,{2,3,4,5,6,7,8,9,10},0)

HTH
Regards,
Howard