VLOOKUP help returning multiple results

[Guest]

I am working with 2 sheets and need to be able find a data match on one sheet
and display multiple results on another sheet - here's a simplified version.

sheet 1 named Activities, sheet 2 named Calendar
Activities sheet will list 100+ activities which will all have a start date.
Some activities will have the same start date. And any of the dates may
change.
Example of Activities sheet:
Col 1 Col 2
Start 01-Jan-07
Identify site 05-Feb-07
Enter in database 05-Jan-07
Contact project ldr 01-Jan-07
Sign Lease 15-Mar-07

Calendar sheet:
The calendar sheet will look like a calendar. 01-Jan-07 = A1:A8, 02-Jan-07 =
B1:B8 and so on. I want to display all activities (from Activities sheet) to
display on the corresponding date in the calendar. I am not an Excel
poweruser by any means - hopefully there's a more simple answer than what
I've found so far.

Many thanks,
El

 

[JP]

Hello El,

On the Calendar tab you want to insert columns between A and B to
insert the results of your lookup.

For example, insert a column between A and B, then in B1 enter the
formula:

=INDEX(Activities!A1:B100,SMALL(IF(Activities!
A1:B100="Start",ROW(Activities!A1:B100)-ROW(Activities!
A1)+1,ROW(B100)+1),1),2)

Assuming your data is in Activities tab, A1 through B100. This formula
will return the first occurrence of "Start" on the Activities sheet.

Check out this site for more info: http://www.bettersolutions.com/excel/EUV214/LN031821611.htm


HTH,
JP

 

[Guest]

JP, thanks for responding so quickly. However, I need this formula to return
all occurences of a match - not just the first. The date is my search
criteria. Example, I want to search the Activities sheet for all activities
with a date of "01-Jan-07". On the Calendar sheet I have 6 lines underneath
a heading of 01-Jan-07 - so what I want to see listed under that heading is
"Start" and "Contact project ldr"...and whatever else that may have a date of
01-Jan-07 from the Activities sheet. Maybe I should be using another function
besides VLOOKUP?

Thanks,
El

 

[Pete_UK]

Can you confirm that you want your dates going across in your Calendar
sheet, and that you want 6 (or is it 8?) cells below to contain the
activities?

I think it would be better to have the dates going down in column A,
with activities listed across in columns B to H (or however many you
think you need).

You can do this by having a COUNTIF formula in an extra column on your
Activities sheet, and then use MATCH with INDEX (rather than VLOOKUP)
on your Calendar sheet. I can give you further details of the actual
formula if you can confirm your preferred layout on the Calendar
sheet.

Pete

 

[Guest]

Hi Pete,
Yes, that's about it. It looks like a calendar, A1=Sunday, B1=Monday,
C1=Tuesday and on to G1=Saturday. A2=31-Dec-06, A3,A4,A5,A6,A7,A8 are
blank... B2= 01-Jan-07, B3,B4...B8 are blank..and so on. All the days of the
year are like that and on this calendar. What I need is to have all
activities from the Activities sheet to be listed under the appropriate date
on the calendar. Because I couldn't figure this out, what I did originally
was to sort all the activities by date then copy/pasted the activities to the
appropriate date on the calendar. Problem is, it becomes obsolete once the
dates change. Sorry if it's confusing - I'm confusing me.

Thanks,
El

 

[Guest]

Hi Pete,
Yes, that's about it. It looks like a calendar, A1=Sunday, B1=Monday,
C1=Tuesday and on to G1=Saturday. A2=31-Dec-06, A3,A4,A5,A6,A7,A8 are
blank... B2= 01-Jan-07, B3,B4...B8 are blank..and so on. All the days of the
year are like that and on this calendar. What I need is to have all
activities from the Activities sheet to be listed under the appropriate date
on the calendar. Because I couldn't figure this out, what I did originally
was to sort all the activities by date then copy/pasted the activities to the
appropriate date on the calendar. Problem is, it becomes obsolete once the
dates change.

Thanks,
El

 

[Pete_UK]

I thought you said you could have several activities on one day, so
you would need to display them going across (not days of the week).
Anyway, this is what I have done:

In the Activities sheet, with Activity in column A and date in Column
B and with a header row, I put this formula in C2:

=B2&"_"&COUNTIF(B$2:B2,B2)

and copied it down. It gives these results for your sample data:

01-Jan-07 39083_1
05-Feb-07 39118_1
05-Jan-07 39087_1
01-Jan-07 39083_2
15-Mar-07 39156_1

i.e. it takes each serial number for the date and increments a counter
to keep track of duplicates (you can see this for the second event on
1st Jan).

Then in the Calendar sheet I put the numbers 1 to 7 in cells B1:H1,
and filled consecutive dates down from A2, starting with 1st Jan 2007.
Then I put this formula in B2 of this sheet:

=IF(ISNA(MATCH($A2&"_"&B$1,Activities!$C$2:$C
$1000,0)),"",INDEX(Activities!$A$2:$A$1000,MATCH($A2&"_"&B
$1,Activities!$C$2:$C$1000,0)))

The formula can be copied into C2:H2, and then the formulae in B2:H2
can be copied down for as many dates as you have. This will pick up
the activies and put them in appropriate cells on the row that the
date occurs. If you have more than one activity on a particular date,
these will be shown across the row in columns B to H, and if you are
likely to have more than 7 activities on any particular date, you can
just extend the numbering across the top and copy the formula into
more columns. You will have blanks where there are no activities.

I have assumed that you might have up to 1000 entries on the
activities sheet - adjust this as necessary three times in the
formula.

Hope this helps.

Pete

 

[Guest]

Yes, there could be several activities for one day...listed on one of the six
lines under the day/date. In my example 2 activities have a date of
01-Jan-07..."Start" and "Contact project ldr". On the calendar, I want it to
look like this:

01-Jan-07 <--row 2
Start <--row3
Contact project ldr <--row 4
rows 5 through 8 are blank because there are no other activities that have a
date of 01-Jan-07.

I'm sure I'm not explaining this well but it sounds like it can't be done if
I want the results/activities to show up on lines under the date instead of
across. Bottom line is it has to look like a calendar.

But thanks for trying. El.

 

[Pete_UK]

There are many styles of calendar, so there is not much point in
saying you want it to "look" like a calendar.

Do you want seven columns, one for each day of the week, and blocks of
7 rows representing each week? So, if the first column represents
Sunday then the first date will be 31st Dec 2006 in B2, then in B9 the
date would be 7th Jan 2007, then in B16 the date would be 14th Jan
2007, and so on?

Pete

By the way, did you try the solution I offered before?

 

[Pete_UK]

I've put a sample workbook together based on a calendar format. If you
would like to see it then send me an email to:

pashurst <at> auditel.net

(change the obvious), and I can post it to you.

Pete

 

[Guest]

One alternative formulas play which might deliver it here ...

Illustrated in this sample at:
http://www.flypicture.com/download/NTEwNTE=
Listing multiple activities under date cols.xls

(If reading this in MS webpage, do a copy n paste of the entire link above
into your browser, inclusive of the "=" at the end. Do not click direct on
the link)

Source data is assumed in a sheet: A,
within cols A and B,
col A = activities descriptions, col B = dates,
data from row1 down

In a helper sheet: x,

List the calendar dates in A1 down, eg:
01-Jan-07
02-Jan-07
03-Jan-07
etc

Put in B1:
=IF(A!$B1="","",IF(A!$B1=INDEX($A:$A,COLUMNS($A:A)),ROW(),""))
Copy B1 across by as many cols as there are calendar dates listed in col A,
then fill down to cover the max expected extent of source data in A.

Then in your results sheet: R
(this is your "calendar" sheet)

In A1:
=IF(INDEX(x!$A:$A,COLUMNS($A:A))=0,"",INDEX(x!$A:$A,COLUMNS($A:A)))
Copy A1 across as far as required to return the calendar dates from col A in
sheet: x.

In A2:
=IF(ROWS($1:1)>COUNT(x!B:B),"",INDEX(A!$A:$A,SMALL(x!B:B,ROWS($1:1))))
Copy A2 across/fill down as far as required to return the activities listed
in col A in sheet: A under the appropriate calendar date

 

[Guest]

Max,
This is very very close! But on the calendar sheet (your sheet R) I want to
see "seven columns, one for each day of the week, and blocks of 7 rows
representing each week? So, if the first column represents Sunday then the
first date will be 31st Dec 2006 in B2, then in B9 the date would be 7th Jan
2007, then in B16 the date would be 14th Jan 2007, and so on"...like Pete_UK
said - except the first col representing Sunday would be A. Whereas you have
all the dates going across.

Is it possible to do what I want? If yes, I will work with what you have
already done and figure it out.

Thanks so much for your time and help!
El

 

[Pete_UK]

Awaiting your email if you want a copy of my calendar version - it is
laid out how you asked for it.

Pete

 

[Guest]

Pete,
Your calendar version is exactly what I am looking for.

Thanks again for your help,
El

 

[Pete_UK]

Glad to hear it - thanks for feeding back.

Pete

Pete,
Your calendar version is exactly what I am looking for.

Thanks again for your help,
El





- Show quoted text -

 

[Max]

Looks like you've got exactly what you wanted from Pete.
Reserve the earlier offering for use in another scenario which suits.