lookup multiple values

A

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!!
 
A

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
 
F

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"),", ")
 
F

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
 
A

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
 
Top