lookup multiple values

[andyjsmith]

Hi

I'm trying to find a way so that I can combine information fro
multiple sorted cells. The information is access dates to onlin
courses. These dates are in 2 columns but show many sessions o
different dates. So for example a person with username joebloggs and o
course code 100123 may have log on and log off times on 10 differen
days eg. joebloggs#100123 is shown in 10 rows next to 10 log on and 1
log off times.

I need to be able to lookup all log on and log off times fo
joebloggs#100123 and combine this in one cell separated by commas.

So I guess I need a way of running a VLOOKUP that doesn't just giv
highest, lowest or first value, but all values?

Please help this is driving me insane!!

 

[Frank Kabel]

Hi
one way (this requires VBA):
- download Alan Beban's array functions:
http://home.pacbell.net/beban
- download the free add-in Morefunc.xll
(http://longre.free.fr/english/)

Now use the following type of formula:
=MCONCAT(VLOOKUPS(lookup_value,lookup_range,2),", ")

 

[andyjsmith]

cheers i'll give it a tr

 

[andyjsmith]

hi again

doesn't work! MCONCAT works but not with the vlookup and the majo
problem is that it converts the values back to time codes separated b
commas and not date and times (even if I try to make a custom cel
format

 

[Frank Kabel]

Hi
some remarks
- first enter this formula as array formula
(CTRL+SHIFT+ENTER)
- use VLOOKUPS (from Alan's site)
- try the following addition to deal with date values

=MCONCAT(TEXT(VLOOKUPS
(lookup_value,lookup_range,2),"MM/DD/YYYY hh:mm"),", ")

 

[andyjsmith]

i see the problem

how do i install the arrayfunctions.xls to use the functions

 

[Frank Kabel]

Hi
I think Alan has provided some instructions. In general
copy the code from this workbook to a new module of your
workbook

 

[andyjsmith]

thanks for the help

what i dont know tho is how to use alan's vlookups. Do I have t
somehow install alans functions into excel so it recognises them?

sorry i'm not too hot on this as i never use excel for anything mor
complicated than IF and VLOOKUP function