Aug 03 2019 12:36 PM
I have two columns (A and B). Column A contains the dates and Column B contains the names of people who are required to attend the duty. Now based on this can i have names of people and just below their names, I want the all the dates that they are required to report to duty.
Aug 03 2019 03:12 PM - edited Aug 03 2019 03:15 PM
Hello @balaram51,
The format would be slightly different but this formula should get you close to what you are looking for:
In cell D2: =IF(ISNUMBER(SEARCH(D$1,$B2)),$A2,"")
and copy this over and down to K7.
**EDIT: Of course this is assuming that the data that you have given starts at cell A1.
Hope this helps!
PReagan
Aug 05 2019 04:42 PM
Hi
The problem with using the Search & Isnumber is, The extracted dates from the left column of the source data, will have gaps between the extracted values. So if there are three dates to extract for one onf the names, they won't be consecutive.
I think I can do it in Power Query but let's get a consultation on solving this situation with functions from @Sergei Baklan
Aug 06 2019 05:47 AM
If by Power Query some simple coding is required, not everything could be done from user interface. For such sample (I sorted names alphabetically)
the generated script is
let
Source = Excel.CurrentWorkbook()
{[Name="Table1"]}[Content],
NamesToLists = Table.TransformColumns(
Source,
{{"Persons required", Splitter.SplitTextByDelimiter(",")}}
),
ExpandNames = Table.ExpandListColumn(
NamesToLists,
"Persons required"
),
TrimmNames = Table.TransformColumns(
ExpandNames,
{{"Persons required", Text.Trim, type text}}
),
FormatAsDate = Table.TransformColumnTypes(
TrimmNames,
{{"Date", type date}}
),
GroupNames = Table.Group(
FormatAsDate,
{"Persons required"},
{{"Count", each _, type table [Date=date, Persons required=text]}}
),
AddCustom = Table.AddColumn(
GroupNames,
"Custom", each [Count][Date]
),
SortByNames = Table.Sort(
AddCustom,
{{"Persons required", Order.Ascending}}
),
CreateTable = Table.FromColumns(
SortByNames[Custom],
SortByNames[Persons required]
)
in
CreateTable
You may check step by step in the attached file.
Aug 06 2019 05:55 AM
Aug 06 2019 05:58 AM
Hi Nabil - didn't think in this direction, will check some later
Aug 06 2019 06:04 AM
Eagerly waiting for it
Aug 06 2019 08:00 AM
Okay, next iteration is with Dynamic Arrays and TEXTJOIN to select and sort names
In N3 for that
=SORT(
UNIQUE(
TRIM(
MID(
SUBSTITUTE(
TEXTJOIN(",",TRUE,Table1[[Persons required]:[Persons required]]),
",",
REPT(" ",LEN(
TEXTJOIN(",",TRUE,Table1[[Persons required]:[Persons required]])
))
),
(COLUMN(N1:Z1)-COLUMN($N$1))*
LEN(TEXTJOIN(",",TRUE,Table1[[Persons required]:[Persons required]]))+1,
LEN(TEXTJOIN(",",TRUE,Table1[[Persons required]:[Persons required]]))
)
),TRUE),,1,TRUE)
and pull dates in more traditional way
=IFERROR(
INDEX(Table1[Date],
AGGREGATE(15,6,
1/ISNUMBER(SEARCH(N$3,Table1[Persons required]))*(ROW(Table1[Persons required])-ROW(Table1[[#Headers], [Persons required]])),ROW()-ROW(N$3))
),
"")
Without dynamic arrays that's most probably with helper column/row to extract all names and after that select unique from them. Or VBA.
Aug 06 2019 08:11 AM
OMG
You are the Excel Super Star !!
I don't have Office 365 (with insider) at work so will have to wait until I go back home and check your beautiful formulas meticulously...
That's too much fun
Thank you Sergei
If you come up with a solution using regular functions let me know
meantime I will take care of the VBA option which will be (for the first time ever) easier than other options.
Have a great day
Nabil
Aug 06 2019 09:12 AM
Aug 06 2019 01:11 PM
Nabil, at work I'm on Monthly (Targeted) channel, it's with DA about a month or so. Hope will be soon on Monthly channel as well.
If use the same pattern from MrExcel to split separated text on columns/rows, first in mind is to generate row with all names from the table
by
=TRIM(
MID(
SUBSTITUTE(
TEXTJOIN(",",TRUE,Table1[[Persons required]:[Persons required]]),
",",
REPT(" ",LEN(
TEXTJOIN(",",TRUE,Table1[[Persons required]:[Persons required]])
))
),
(COLUMN()-COLUMN($W$1))*
LEN(TEXTJOIN(",",TRUE,Table1[[Persons required]:[Persons required]]))+1,
LEN(TEXTJOIN(",",TRUE,Table1[[Persons required]:[Persons required]]))
))
when extract from here unique names only
=IFERROR(INDEX($W$2:$AJ$2,AGGREGATE(15,6,1/(COUNTIF($V$3:V$3,$W$2:$AJ$2)=0)*(COLUMN($W$2:$AJ$2)-COLUMN($V$1)),1)),"")
and add dates as in previous sample
Aug 06 2019 10:14 PM
I copied your data to the attached file. The formula in D2, copied down rows and across columns, is:
=IF(ROW()-1>SUMPRODUCT(--ISNUMBER(FIND(D$1,$B$2:$B$7))),"",
LOOKUP(2,1/(FREQUENCY(0,1/(1+(
1/$A$2:$A$7*ISNUMBER(FIND(D$1,$B$2:$B$7))*(COUNTIF(D$1:D1,$A$2:$A$7)=0))))),
$A$2:$A$7))
Note that the results for Ronny and Max in your screenshot are incorrect.
Aug 07 2019 12:12 AM
I fathomed that the repetition of ISNUMBER-FIND in my previous formula makes it patently less elegant. Thus, I was compelled to upgrade the formula in D2 of the attached file to this:
=IFERROR(1/(1/(
LOOKUP(2,1/(FREQUENCY(0,1/(1+(
1/$A$2:$A$7*ISNUMBER(FIND(D$1,$B$2:$B$7))*(COUNTIF(D$1:D1,$A$2:$A$7)=0))))),
$A$2:$A$7)-$A$2*(ROW()>2)))+$A$2*(ROW()>2),"")
Aug 07 2019 03:01 AM
Hello Sergei @Sergei Baklan and Hi Twifoo @Twifoo
By all means this is not a regular project and I am really excited to see this "ALL You Can Eat Excel Buffet"... You guys went above and beyond the MVP award. So I'll be nominating you for an imaginary award as follows:
Excel Nobel Prize 2019 goes to Sergei Baklan
Excel Oscar 2019 goes to Twifoo
I also came out with a VBA solution to add to the series. But, anyway that was too much fun.
Nabil Mourad
Aug 07 2019 03:32 AM
Hello @nabilmourad,
@balaram51 sought, and you eagerly waited, for a formula solution so I gave one. My later formula is inevitably an upgrade of the previous one.
Cheers,
Twifoo
Aug 08 2019 02:36 PM
Most challenging part here is to extract list of names for the headers of the resulting table. If names are predefined any variant of regular formula is more preferable.
Aug 08 2019 03:38 PM
The approach is basically that outlined by @PReagan and @Twifoo but I have set out to determine whether dynamic arrays have something to offer here.
The condition that each name is present in the 'persons required' list is
= ISNUMBER( SEARCH( Names, PersonsRequired ) )
This spills to gives a 6x8 Boolean array.
To filter out the unwanted dates for each person, I need to change this formula to apply column by column by using the intersection operator on the names row, giving a final form
= FILTER( date, ISNUMBER( SEARCH( @Names, PersonsRequired ) ) )
Each instance of the formula gives a single dynamically-sized column of dates.
Aug 08 2019 04:34 PM
I think I have just about achieved the other part of deriving a list of unique names!
I started by defining a named formula 'string' that runs all of the individual lists of names together
= "{""" & SUBSTITUTE( TEXTJOIN( ", ", 1, PersonsRequired ), ",", """,""" ) & """}"
and then wraps them in double quotes and braces to create a string version of an array constant.
A further named formula 'split' uses the old Macro4 function
=EVALUATE(string)
to create an array of individual person names.
I am not quite home and dry because one of the 'Sam's has 4 characters whereas the other two have the correct value. This messes up the identification of unique values
= UNIQUE( split, TRUE )
Aug 09 2019 12:30 AM
Hi
I am missing the second step in the applied steps (NameToList) I see the M code but how did you do it from the interface of the Query Editor?
Aug 09 2019 02:06 AM
It was getting late! All that was needed was a TRIM so that the Names row is now given by
= UNIQUE( TRIM(split), 1 )
Please accept may apologies, I came across this discussion and started answering without reading the prior posts with sufficient care. I saw your first post used PQ and assumed the following posts were refinements of the original. I now see that you followed up by exploring formula solutions and had already adopted the use of TEXTJOIN; I will examine your subsequent steps later.