UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
 
   Reply to this topicStart new topic
> Adapting An Index Array, Office 2013    
 
   
Zaddicus
post Jun 27 2019, 03:29 AM
Post#1



Posts: 139
Joined: 3-April 19
From: Cardiff


Hello UtterAccess,

I was wondering if anyone here would be able to help adapt one of my Indexing Array's to meet new requirements.

Background - An undefined number of companies would attend our event, during said event they could have 1 - 5 table meetings within a certain time-slot and maybe some stand meetings also. This company could then have multiple time-slots across the day. During the event it is nice for these companies to know what meetings they have and on what table etc so I created an array to identify who had a meeting within a certain slot and then listed it.

CODE
{=IFERROR(IF($D$2="","",IF(INDEX(Slots!$A:$A,SMALL(IF(ISNUMBER(SEARCH($D$2, Slots!$H:$H)*SEARCH("- Table",Slots!$H:$H)),ROW(Slots!$A:$A)),ROW(M1)))=0,"",INDEX(Slots!$A:$A,SMALL(IF(ISNUMBER(SEARCH($D$2, Slots!$H:$H)*SEARCH("- Table",Slots!$H:$H)),ROW(Slots!$A:$A)),ROW(M1))))),"")}


so basically this looks down the column and finds anyone with table meeting and lists them.

Column H Example:
CODE
+---------+----------------------+
| Person  |       Column H       |
+---------+----------------------+
| Jake    | Company 1 - At Stand |
| Amy     | Company 2 - At Stand |
| Gina    | Company 1 - Table 1  |
| Terry   | Company 1 - Table 2  |
| Raymond | Company 2 - Table 3  |
+---------+----------------------+


So if the above array was used to search for 'Company 1' the results would list like this:
CODE
+-----+--------+
| Row | Return |
+-----+--------+
|   1 | Gina   |
|   2 | Terry  |
+-----+--------+


Now this works perfectly for when I want to hand out the meetings for different companies because I just change the drop-down where the company name is listed ($D$2).

My Challenge:

I also need a print-out of all the meetings taking place during each meeting slot regardless of the company but I don't want to include tables where no meeting is taking place and to completely ignore all stand meetings.

I attempted to adapt my array to the below:
CODE
{=IFERROR(IF($D$2="","",IF(INDEX(Slots!$A:$A,SMALL(IF(ISNUMBER(SEARCH($D$2, INDIRECT(Admin!$J$5))*SEARCH("- Table 1",INDIRECT(Admin!$J$5))),ROW(Slots!$A:$A)),ROW(M1)))=0,"",INDEX(Slots!$A:$A,SMALL(IF(ISNUMBER(SEARCH($D$2, INDIRECT(Admin!$J$5))*SEARCH("- Table 1",INDIRECT(Admin!$J$5))),ROW(Slots!$A:$A)),ROW(M1))))),"")}


However this is not working: Alterations in this scenario
$D$2 = Cell with a drop-down with the meeting number (e.g. Meeting 1)
INDIRECT(Admin!$J$5) = Admin!$J$5, Basically looks up $D$2 and returns the sheet name and column (In the case of meeting 1 it returns "Slots!H;H")

Does anyone here have any ideas on what I'm over looking?

Other important information - all arrays return column A:A as this is the 'helper' column in every worksheet and is used for other look-up functions to return the data required in the meeting overview printouts.

thanks in advance

--------------------
~ Castiel


Skype: live:castielswift
Go to the top of the page
 
ADezii
post Jun 27 2019, 07:20 AM
Post#2



Posts: 2,710
Joined: 4-February 07
From: USA, Florida, Delray Beach


How about posting some Data showing what the 'Slots' look like and an explanation as to how an individual/company are assigned to a Slot?
Go to the top of the page
 
Zaddicus
post Jun 27 2019, 07:30 AM
Post#3



Posts: 139
Joined: 3-April 19
From: Cardiff


Sure,

Here is a very restricted example of the slots worksheet:

CODE
+-----------------+-----------------------------------------------+--------------------------------------------------+--------------------------------------------------+--------------------------------------------------+-------------------------------------------------+
|     Helper      |                   Meeting 1                   |                    Meeting 2                     |                    Meeting 3                     |                    Meeting 4                     |                    Meeting 5                    |
+-----------------+-----------------------------------------------+--------------------------------------------------+--------------------------------------------------+--------------------------------------------------+-------------------------------------------------+
| AdamCole        | 11:45 - 11:50 - Meeting Sponge - At Stand     | Networking Time                                  | Networking Time                                  | Networking Time                                  | Networking Time                                 |
| AdamRainsford   | Networking Time                               | 12:10 - 12:30 - Meeting SAPConcur - Table 8      | Networking Time                                  | Networking Time                                  | Networking Time                                 |
| AlanFlynn       | 11:45 - 12:05 - Meeting BPM-D - Table 16      | Networking Time                                  | Networking Time                                  | 14:40 - 15:00 - Meeting PwC  - Table 5           | Networking Time                                 |
| AmanuelYemane   | Networking Time                               | 12:10 - 12:30 - Meeting Sponge  - Table 12       | Networking Time                                  | Networking Time                                  | Networking Time                                 |
| AndreaPonti     | 11:50 - 12:10 - Meeting  Sage - Table 7 - OVF | Networking Time                                  | 12:35 - 12:55 - Meeting Accountagility - Table 1 | Networking Time                                  | 15:10 - 15:30 -Meeting  Prophix - Table 4 - OVF |
| AndrewCrean     | Networking Time                               | Networking Time                                  | Networking Time                                  | 14:40 - 14:45 - Meeting SAPConcur - At Stand     | Networking Time                                 |
| AndrewInsch     | Networking Time                               | Networking Time                                  | Networking Time                                  | 14:40 - 15:00 - Meeting Accountagility - Table 2 | Networking Time                                 |
| AndrewSimcock   | Networking Time                               | Networking Time                                  | Networking Time                                  | Networking Time                                  | Networking Time                                 |
| AngelitaPereira | Networking Time                               | Networking Time                                  | 12:35 - 12:55 - Meeting Prophix - Table 4        | Networking Time                                  | Networking Time                                 |
| AnnieWoodward   | 11:45 - 12:05 - Meeting Prophix - Table 4     | 12:10 - 12:30 - Meeting Accountagility - Table 1 | Networking Time                                  | Networking Time                                  | Networking Time                                 |
| AnthonyTasgal   | Networking Time                               | Networking Time                                  | Networking Time                                  | Networking Time                                  | Networking Time                                 |
| AntonyKing      | Networking Time                               | Networking Time                                  | Networking Time                                  | Networking Time                                  | Networking Time                                 |
+-----------------+-----------------------------------------------+--------------------------------------------------+--------------------------------------------------+--------------------------------------------------+-------------------------------------------------+


Helper is Column A and Meeting 1 is H (I've hidden the rest as it's just irrelevant to the question)

So ideally If Meeting 1 was selected in $d$2 the list would be:
AnnieWoodward
AndreaPonti
AlanFlynn


This data is slightly old as we no longer assign timeslots in the meeting columns so instead of say "11:45 - 12:05 - Meeting Prophix - Table 4" it now says "Prophix - Table 4"

Edit: Table unclear - example of document here on my public google drive
Edit edit: worth noting if you do look at this basic MI workbook the sheet I need the array to pull the list of all table meetings is 'AMO' (All Meeting Overview)
This post has been edited by Zaddicus: Jun 27 2019, 07:56 AM

--------------------
~ Castiel


Skype: live:castielswift
Go to the top of the page
 
ADezii
post Jun 27 2019, 09:02 AM
Post#4



Posts: 2,710
Joined: 4-February 07
From: USA, Florida, Delray Beach


I just have a couple of questions before I proceed any further.
  1. If Meeting 2 was selected, given your Sample Data, the Helpers would be? (please verify).
    CODE
    AdamRainsford
    AmanuelYemane
    AnnieWoodward
  2. If Meeting 3 was selected, given your Sample Data, the Helpers would be? (please verify).
    CODE
    AndreaPonti
    AngelitaPereira
  3. Can I assume the Meeting Slots are in consecutive Columns, namely: Meeting 1 [H], Meeting 2 [I], Meeting 1 [J], etc.

QUOTE
I don't want to include tables where no meeting is taking place and to completely ignore all stand meetings.

How would you determine this, I see nothing in the Sample Data that would indicate either condition.
This post has been edited by ADezii: Jun 27 2019, 09:04 AM
Go to the top of the page
 
Zaddicus
post Jun 27 2019, 09:16 AM
Post#5



Posts: 139
Joined: 3-April 19
From: Cardiff


SO to answer your questions:

QUOTE
If Meeting 2 was selected, given your Sample Data, the Helpers would be? (please verify).
AdamRainsford
AmanuelYemane
AnnieWoodward

Correct

QUOTE
If Meeting 3 was selected, given your Sample Data, the Helpers would be? (please verify).
AndreaPonti
AngelitaPereira

Correct

QUOTE
Can I assume the Meeting Slots are in consecutive Columns, namely: Meeting 1 [H], Meeting 2 [I], Meeting 1 [J], etc.

Yes, Meeting 1 is H, Meeting 2 is I... all the way up to Meeting 15 (V)

QUOTE
I don't want to include tables where no meeting is taking place and to completely ignore all stand meetings.
How would you determine this, I see nothing in the Sample Data that would indicate either condition.

Poor articulation on my part, basically anything in the meeting column that has either "Networking time" or "xxxxx - At Stand" should not be listed as they are not table meetings

I've been doing a little playing around and ended up with the below - however the returning data isn't matching
CODE
{=IFERROR(IF($D$2="","",IF(INDEX(Slots!$A:$A,SMALL(IF(ISNUMBER(SEARCH("Table 1", INDIRECT(Admin!$H$7))),ROW(Slots!$A:$A)),ROW(M1)))=0,"",INDEX(Slots!$A:$A,SMALL(IF(ISNUMBER(SEARCH("Table 1", INDIRECT(Admin!$H$7))),ROW(Slots!$A:$A)),ROW(M1))))),"")}

So for example that listed a name under table 1 but the person was actually at table 16 and when i changed "table 1" to "table 2" no results po[pulated even though there is a table 2 in meeting slot 1 (Column H)
This post has been edited by Zaddicus: Jun 27 2019, 09:17 AM

--------------------
~ Castiel


Skype: live:castielswift
Go to the top of the page
 
ADezii
post Jun 27 2019, 12:00 PM
Post#6



Posts: 2,710
Joined: 4-February 07
From: USA, Florida, Delray Beach


Thanks for the clarification. I find this a little difficult, at least for me, to accomplish this using strictly Excel Functions. This should, however, be a relatively simple task using VBA Code within an Excel Macro. If this acceptable, I can give it a shot - a Sample Spreadsheet, void of any sensitive Data would be a hugh help also. Just let me know.
Go to the top of the page
 
ADezii
post Jun 27 2019, 01:47 PM
Post#7



Posts: 2,710
Joined: 4-February 07
From: USA, Florida, Delray Beach


I had some spare time so I whipped up a Macro for you. Click on the only Command Button on Sheet1 to generate what hopefully are the results that you are looking for. I am much better with Code than I am with Excel Functions which is why I took this approach. Good Luck with your Project.
Attached File(s)
Attached File  Meetings2.zip ( 31.66K )Number of downloads: 2
 
Go to the top of the page
 
Zaddicus
post Jul 1 2019, 02:31 AM
Post#8



Posts: 139
Joined: 3-April 19
From: Cardiff


Hi, sorry for the late reply. treated myself to a long weekend laugh.gif

That macro works great - I can use the results of it and some formula's to pull some additional data smile.gif thank you very much for the help

--------------------
~ Castiel


Skype: live:castielswift
Go to the top of the page
 
ADezii
post Jul 1 2019, 07:04 AM
Post#9



Posts: 2,710
Joined: 4-February 07
From: USA, Florida, Delray Beach


yw.gif , Good Luck with your Project.
Go to the top of the page
 


Custom Search


RSSSearch   Top   Lo-Fi    12th December 2019 - 09:55 PM