How to Search Through Columns and Display Multiple Instances of Specific Value in a Row

[gregwadz]

So I have Sheet 1 which contains Client Name (Column A), Date 1 (Column B), Date 2 (Column C).

I then have Sheet 2 which contains individual dates in each cell under row A (A1 - 1/1/2017, A2 - 1/2/2017, A3 - 1/3/2017, etc).

I need a formula that searches through columns B and C on Sheet 1 and displays all Client Names that meet the specific date in the specified row. So if the client has the date 1/1/17, I want their name to display under row 1. I want ALL the clients that meet that specific date to display in the row.

I have included some sample tables to help explain what I'm trying to accomplish.

Sheet 1

Client	Date 1	Date 2
Bob Smith	1/1/2017	1/3/2017
Bill Jones	1/2/2017	1/3/2017
Jane Johnson	1/1/2017	1/4/2017
Jack Daniels	1/1/2017	1/2/2017

<tbody>
</tbody>

Sheet 2

Date	Client 1	Client 2	Client 3	Client 4
1/1/2017	Bob Smith	Jane Johnson	Jack Daniels
1/2/2017	Bill Jones	Jack Daniels
1/3/2017	Bob Smith	Bill Jones
1/4/2017	Jane Johnson

<tbody>
</tbody>

The order of the clients does not matter, but I would like columns B-K available for up to 10 clients to be inserted under one date. I tried to use a LOOKUP function but I would not be able to display multiple clients. I have a somewhat limited knowledge of Excel. Any help is appreciated. Thank you!

 

[gregwadz]

Getting an error on Line 9:

For Each Dn In Rng2: Dic.Add (Dn.Value), Array(Dn, 0): Next

Error reads:

Run-time error '457': The key is already associated with an element of this collection.

 

[MickG]

I think this is because you have duplicate dates in sheet2 col "A".
I'm not sure if you intended to have duplicate dates , it can be overcome ,but you will then have either duplicate return values or one of the duplicates will not have any values.
I'm also not sure why your data and results do not match date/person wise????

 

[gregwadz]

Hi Mick, the reason the results were not matching is because I forgot to include the header in the sample spreadsheet. I also had to modify the original code a little bit. Instead of:

For Each Dn In Rng
If Not Dic.exists(Dn.Value) Then
Dic.Add Dn.Value, .Cells(Dn.Row - 1, 1)
Else
Dic(Dn.Value) = Dic(Dn.Value) & "," & .Cells(Dn.Row - 1, 1)

I just simply removed the '- 1' and the data outputs the correct names and dates.

As for the second the macro, you were correct that the error was coming from having duplicate dates in "Sheet2" column A. I removed the extra dates and the macro runs without an error, but the macro doesn't do anything. Nothing changes on sheet 1 or 2 after running the second macro.

Cheers

 

[MickG]

Have a look at this example:-
When you see its running Ok swap the sheet2 Date list for your Date list.
https://app.box.com/s/wi2hmczw59b7qbdfo5ss8lv8wqxx2ga1

 

[gregwadz]

Thanks for the reply, Mick. I see that your example includes all of the dates in calendar format, but how do I get that to my spreadsheet? Do I transfer all of my data from my spreadsheet to the 'Greg' file? My working spreadsheet is a bit more complicated then I have shown you and I think transferring my data to that file will mess up the macros you're using.

If there's no better way around getting the calendar view, I may just manually sort the dates in sheet 2. It won't be exactly a calendar, but it will show which appointments are upcoming in chronological order.

Really appreciate the help you've given.

 

[MickG]

It depend how and where you want to run the code:-
You can run it from a button or from when you change a value or select a sheet , if you let me know I'll give you details of how to achieve it.

 

[gregwadz]

A button as you have on the example file would be great.

So just so I am clear... I would input all of the dates in Sheet1, then run the original macro which will output all the dates/names onto Sheet2, and then I would press the button and it would format it onto the calendar?

Also would this calendar be able to change over time? Say after a year, will the calendar continue onto the next year and so on?

 

[MickG]

You're lost me a bit now!!!
My assumption (based on your latest requirement) was that your sheet 1 was as per My sheet1 in file "Greg", the code as per file "Greg" was run and the dates in sheet2 where updated with sheet1 names.

NB:- The original macro is not needed because it gave a list of names with dates rather that updating a list of dates with related names.