Nov 30 2017
12:51 AM
- last edited on
Jul 25 2018
10:30 AM
by
TechCommunityAP
Nov 30 2017
12:51 AM
- last edited on
Jul 25 2018
10:30 AM
by
TechCommunityAP
Hi,
There is an Excel table that we use to monitor reading compliance - columns are people names, rows are document codes, and cells are the reading dates.
Please find below a simplified version:
John Doe | Jane Doe | John Smith | Gandalf | |
THIS-123-V10 | 02-11-17 | 05-10-14 | ||
IS-456-V10 | 01-09-17 | 01-09-17 | ||
A-789-V10 | 27-09-17 | |||
TEST-753-V10 | 02-11-17 | 05-10-14 |
I need a quick way to check who has read a specific code and, if possible, on which date. I want a formula where if I put a code, it will detect all cells with a value on the corresponding row and will pull the person's name from the top cell on the same column.
I already have a formula where if I put a person's name and a list of documents, if confirms if they have read it or not, but this is different. I need all results, not just from a limited list.
Thank you
Edit: Forgot to mention - this goes on a separate sheet
Edit 2: Office 2010 :)
Nov 30 2017 03:12 AM - edited Nov 30 2017 03:14 AM
Kaloyan,
is this what you had in mind?
John Doe | Jane Doe | John Smith | Gandalf | |||
THIS-123-V10 | 02.11.2017 | 05.10.2014 | John Doe, Gandalf | |||
IS-456-V10 | 01.09.2017 | 01.09.2017 | Jane Doe, John Smith | |||
A-789-V10 | 27.09.2017 | John Smith | ||||
TEST-753-V10 | 02.11.2017 | 05.10.2014 | John Doe, Gandalf |
{=TEXTJOIN(", ",TRUE,IF(($B$2:$E$5)*($A$2:$A$5=A2),$B$1:$E$1,""))}
Nov 30 2017 06:39 AM
Nov 30 2017 08:26 AM - edited Nov 30 2017 08:28 AM
Is It possible to show us the desired layout so that we can provide you some suggestions?
I can only think of array formula solution, but it will be very slow if your data is huge.
My steps:
=OFFSET(MainTable[#HEADERS],0,1,1,COLUMNS(MainTable[#HEADERS])-1)
=MainTable[Code]* Code is the column name in MainTable for document codes
=SubSheet!$A$1This is what I guess you have a cell to select a document code in anther sheet, "SubSheet".
=OFFSET(NameList,MATCH(SelectedCode,CodeList,0),0,1)
=(SelectedRow<>"")*(COLUMN(SelectedRow)-1)
=SMALL(TRANSPOSE(IF(HitColumnArray=0,"X",HitColumnArray)),ROW())
{=IFERROR(INDEX(NameList,1,IndexList) & " " & INDEX(SelectedRow,1,IndexList),"")}
I know that this is not a quick way. However, the defined names are just for you to follow the steps a bit easier. The defined names can be grouped into a single formula.
Again,
Nov 30 2017 09:14 AM
Kaloyan,
a formula solution would need as many helper columns as your original data. And it would be an array formula - something like Willy's solution.
Feb 05 2023 08:49 AM
@Detlef Lewin Hi, can i take this on my 2013 excel?i badly need it for my project.. if it cant make it to my excel then how this works in google sheet?i seldomly using that google sheet
Feb 05 2023 12:54 PM
Early on in 2019 I decided that my Office 2010 was unfit for any purpose I had, so spent some of my income on 365 insider beta. Your problem would result in
= LET(
SelectNamesλ, LAMBDA(d, TEXTJOIN(", ", ,FILTER(names, d, ""))),
selectedNames, BYROW(readingDates, SelectNamesλ),
HSTACK(documentCodes, selectedNames)
)
I have read that Google has implemented Lambda but I have never used sheets
Feb 05 2023 09:43 PM - edited Feb 05 2023 09:45 PM
like this?
google sheet online search?
Feb 07 2023 04:55 AM
@Detlef Lewin did not get this to work in my 365 version.