Aggregating columns of dynamic 2D Array

T

tschatz

New Member
Joined
Jan 28, 2022
Messages
8
Office Version
  1. 365
Platform
  1. Windows
Hello,

Given the table (Table3) representing the 1:1 slots between 2 participants with the following data:

TIMESLOTGroup 1Group 2Group 3Group 4Group 5Group 6
09h00-09h15X8_1A - X8_BX8_1E - X8_2BX8_1B - X8_5X8_1C - X8_3X8_1D - X8_AX8_2A - X8_6
09h15-09h30X8_1A - X8_4X8_1C - X8_2AX8_6 - X8_3X8_2C - X8_AX8_2B - X8_BX8_1D - X8_5
09h30-09h45X8_2B - X8_6X8_1D - X8_3X8_2A - X8_5X8_1F - X8_2CX8_A - X8_BX8_1E - X8_4
09h45-10h00X8_1D - X8_6X8_A - X8_4X8_1C - X8_BX8_2A - X8_3X8_1F - X8_5X8_1B - X8_2B
10h00-10h15X8_1C - X8_6X8_1D - X8_2BX8_1B - X8_AX8_2C - X8_4X8_1F - X8_3X8_5 - X8_B
10h15-10h30X8_6 - X8_4X8_2B - X8_AX8_2C - X8_BX8_1B - X8_3X8_1D - X8_2AX8_1C - X8_5
10h30-10h45X8_1A - X8_6X8_2B - X8_4X8_2A - X8_AX8_1F - X8_BX8_1E - X8_3X8_1C - X8_2C
10h45-11h00X8_1F - X8_6X8_1D - X8_4X8_1C - X8_2BX8_1E - X8_AX8_1A - X8_3X8_2C - X8_5
11h00-11h15X8_6 - X8_BX8_5 - X8_4X8_1A - X8_AX8_2B - X8_3X8_1B - X8_2CX8_1E - X8_2A
11h15-11h30X8_6 - X8_AX8_3 - X8_BX8_1C - X8_4X8_1E - X8_5X8_1F - X8_2BX8_1A - X8_2A
11h30-11h45X8_1E - X8_6X8_5 - X8_3X8_1F - X8_AX8_1B - X8_BX8_2A - X8_4X8_1A - X8_2C
11h45-12h00X8_4 - X8_3X8_1E - X8_BX8_2B - X8_5X8_2C - X8_6X8_1C - X8_AX8_1B - X8_2A
12h00-12h15X8_2C - X8_3X8_1B - X8_4X8_1D - X8_BX8_6 - X8_5X8_1A - X8_2BX8_1F - X8_2A
12h15-12h30X8_1F - X8_4X8_1D - X8_2CX8_A - X8_3X8_1A - X8_5X8_2A - X8_BX8_1B - X8_6
12h30-12h45X8_1E - X8_2CX8_4 - X8_BX8_5 - X8_A


I would like to make a dropdown where the participants can select themselves and directly see during which timeslots and in which group they are allocated.

Book1
STUVWXYZAAAB
2ParticipantSlots to participateGroupARRAYROWWISE EVALUATION
3X8_1A09h00-09h15TRUEFALSEFALSEFALSEFALSEFALSETRUE
409h15-09h30TRUEFALSEFALSEFALSEFALSEFALSETRUE
510h30-10h45FALSEFALSEFALSEFALSEFALSEFALSEFALSE
610h45-11h00FALSEFALSEFALSEFALSEFALSEFALSEFALSE
711h00-11h15FALSEFALSEFALSEFALSEFALSEFALSEFALSE
811h15-11h30FALSEFALSEFALSEFALSEFALSEFALSEFALSE
911h30-11h45TRUEFALSEFALSEFALSEFALSEFALSETRUE
1012h00-12h15FALSEFALSEFALSEFALSETRUEFALSETRUE
1112h15-12h30FALSEFALSETRUEFALSEFALSEFALSETRUE
12FALSEFALSEFALSEFALSEFALSETRUETRUE
13FALSEFALSEFALSEFALSEFALSETRUETRUE
14FALSEFALSEFALSEFALSEFALSEFALSEFALSE
15FALSEFALSEFALSEFALSETRUEFALSETRUE
16FALSEFALSEFALSETRUEFALSEFALSETRUE
17FALSEFALSEFALSEFALSEFALSEFALSEFALSE
INPUT Overall
Cell Formulas
RangeFormula
T3:T11T3=FILTER(Table3[TIMESLOT],AB3:AB17)
V3:AA17V3=ISNUMBER(SEARCH($S$3,Table3[[Group 1]:[Group 6]]))
AB3:AB17AB3=OR(V3:AA3)
Dynamic array formulas.
Cells with Conditional Formatting
CellConditionCell FormatStop If True
AB3:AB17Cell Value=TRUEtextNO



Two questions:
  1. Is it possible to combine the current multistep calculation of the Slots (Column T) with a single formula?
  2. Any suggestions on how to get the respective Group for the timeslot?

Thanks a lot in advance!
 

Excel Facts

Back into an answer in Excel
Use Data, What-If Analysis, Goal Seek to find the correct input cell value to reach a desired result
Sort by date Sort by votes
How about
+Fluff 1.xlsm
ABCDEFGHIJK
1TIMESLOTGroup 1Group 2Group 3Group 4Group 5Group 6
209h00-09h15X8_1A - X8_BX8_1E - X8_2BX8_1B - X8_5X8_1C - X8_3X8_1D - X8_AX8_2A - X8_6X8_1A09h00-09h15
309h15-09h30X8_1A - X8_4X8_1C - X8_2AX8_6 - X8_3X8_2C - X8_AX8_2B - X8_BX8_1D - X8_509h15-09h30
409h30-09h45X8_2B - X8_6X8_1D - X8_3X8_2A - X8_5X8_1F - X8_2CX8_A - X8_BX8_1E - X8_410h30-10h45
509h45-10h00X8_1D - X8_6X8_A - X8_4X8_1C - X8_BX8_2A - X8_3X8_1F - X8_5X8_1B - X8_2B10h45-11h00
610h00-10h15X8_1C - X8_6X8_1D - X8_2BX8_1B - X8_AX8_2C - X8_4X8_1F - X8_3X8_5 - X8_B11h00-11h15
710h15-10h30X8_6 - X8_4X8_2B - X8_AX8_2C - X8_BX8_1B - X8_3X8_1D - X8_2AX8_1C - X8_511h15-11h30
810h30-10h45X8_1A - X8_6X8_2B - X8_4X8_2A - X8_AX8_1F - X8_BX8_1E - X8_3X8_1C - X8_2C11h30-11h45
910h45-11h00X8_1F - X8_6X8_1D - X8_4X8_1C - X8_2BX8_1E - X8_AX8_1A - X8_3X8_2C - X8_512h00-12h15
1011h00-11h15X8_6 - X8_BX8_5 - X8_4X8_1A - X8_AX8_2B - X8_3X8_1B - X8_2CX8_1E - X8_2A12h15-12h30
1111h15-11h30X8_6 - X8_AX8_3 - X8_BX8_1C - X8_4X8_1E - X8_5X8_1F - X8_2BX8_1A - X8_2A
1211h30-11h45X8_1E - X8_6X8_5 - X8_3X8_1F - X8_AX8_1B - X8_BX8_2A - X8_4X8_1A - X8_2C
1311h45-12h00X8_4 - X8_3X8_1E - X8_BX8_2B - X8_5X8_2C - X8_6X8_1C - X8_AX8_1B - X8_2A
1412h00-12h15X8_2C - X8_3X8_1B - X8_4X8_1D - X8_BX8_6 - X8_5X8_1A - X8_2BX8_1F - X8_2A
1512h15-12h30X8_1F - X8_4X8_1D - X8_2CX8_A - X8_3X8_1A - X8_5X8_2A - X8_BX8_1B - X8_6
1612h30-12h45X8_1E - X8_2CX8_4 - X8_BX8_5 - X8_A
Master
Cell Formulas
RangeFormula
K2:K10K2=FILTER(Table3[TIMESLOT],MMULT(--(ISNUMBER(SEARCH(J2,Table3[[Group 1]:[Group 6]]))),SEQUENCE(COLUMNS(Table3[@[Group 1]:[Group 6]]),,,0))>0)
Dynamic array formulas.
 
Upvote 0
Solution
Hi Fluff,

that works like a charm! Thanks a lot.
You don't happen to know a handy formula for the extraction of the corresponding group?

E.g.
ParticipantSlots to participateGroup
X8_1A09h00-09h15Group 1
09h15-09h30Group 1
10h30-10h45Group 1
10h45-11h00Group 5
11h00-11h15Group 3
11h15-11h30Group 6
11h30-11h45Group 6
12h00-12h15Group 5
12h15-12h30Group 4


Thanks!
 
Upvote 0
As that's a totally different question, it needs a new thread. Thanks
 
Upvote 0
Thread for second question: "Any suggestions on how to get the respective Group for the timeslot?"
www.mrexcel.com

Filter table rows for partial string and return column headers as dynamic array

Hello, Given the table (Table3) representing the 1:1 slots between 2 participants with the following data: JKLMNOPQRSTU2TIMESLOTGroup 1Group 2Group 3Group 4Group 5Group 6ParticipantSlots to participateGroup309h00-09h15X8_1A - X8_BX8_1E - X8_2BX8_1B - X8_5X8_1C - X8_3X8_1D - X8_AX8_2A -...
www.mrexcel.com www.mrexcel.com
 
Upvote 0
You must log in or register to reply here.

Similar threads

RICH937
  • Solved
Using Dropdowns to filter Dynamic Array
Replies
3
Views
391
Georgiboy
Georgiboy
N
  • Solved
Filter Function returns #N/A from Dynamic Array but works with cell reference
Replies
6
Views
591
Peter_SSs
Peter_SSs
V
  • Solved
sum table based on rows and multiple years columns
Replies
2
Views
373
vampsthevampyre
V
T
  • Solved
Filter table rows for partial string and return column headers as dynamic array
Replies
3
Views
579
Fluff
Fluff
R
  • Solved
SUMIF VBA ARRAY & DICTIONARY
2 3
Replies
23
Views
3K
roykana
R

Forum statistics

Threads
1,214,920
Messages
6,122,264
Members
449,075
Latest member
staticfluids

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top