Best approach

[srrlex]

I have the following columns of data and would like to have a formula that returns the name whenever one of the corresponding fields matches a certain value.

1/7/2018 1/8/2018 1/9/2018 1/10/2018 1/11/2018 1/12/2018 1/13/2018
Person Sunday Monday Tuesday Wednesday Thursday Friday Saturday
Karen W C1 C1 C1 C1 C1 W
Bobbie W C1 C1 C1 C1 C1 W
Jessica W C1 C1 C1 C1 C1 W
Kristen W N12 N12 W W WN1 WN1
Samantha WN1 W W N12 N12 W W
Wanda W T2 T2 T2 W T2 W
Mysti W T1 T1 T1 T2 T1 W
Debbie WM1 W T1 T1 T1 T1 W
Lynne W M1 M1 M1 M1 M1 W
Kim W M1 M1 M1 M1 M1 W
Teresia W W NM1 NM1 NM1 W WM1
Dot WM2 NM1 W W W NM1 WM2
Missy W FNW1 FNW1 FNW1 FNW1 FNW1 W
Nicki FNW2 FNW2 FNW2 FNW2 FNW2 W W
Lisa W W W W W FNW2 FNW2
Payton W W W W MNW1 MNW1 W
BritB MNW1 MNW1 MNW1 MNW1 W W MNW1
Larry 0 0 0 0 0 0 0
Chanisty 0 0 0 0 0 0 0
Brianna 0 0 0 0 0 0 0

Sorry the spacing did not work out very well in the copied text above, however, there are 8 columns (name and then the 7 days of the week). I would like to have a formula that produces the associated name (just once) anytime there is a field in the array that is equal to C1. So formula result would produce a list that reads Karen, Bobbie and Jessica. I would prefer not to have a lookup statement if possible.

 

[Rick Rothstein]

I am sure one of our "formula people" will eventually come along and give you one, but I could not think of a formula to do what you wanted in the short time I had before I had to leave, so I wrote you a macro just in case you wanted to try it. The macro assume all of your values in Columns B2:H2 on down are constants (that is, they are not the result of formulas in those cells). If that is the case, then this macro should do what you want...

[table="width: 500"]
[tr]
	[td]Sub GetNameWithC1()
  Dim SearchText As String, Persons() As Variant
  SearchText = "C1"
  Columns("B:H").Replace SearchText, "#N/A", xlWhole, , False, , False, False
  Intersect(Columns("B:H").SpecialCells(xlConstants, xlErrors).EntireRow, Columns("A")).Copy Range("K1")
  Columns("B:H").Replace "#N/A", "C1"
End Sub[/td]
[/tr]
[/table]

HOW TO INSTALL MACROs
------------------------------------
If you are new to macros, they are easy to install and use. To install it, simply press ALT+F11 to go into the VB editor and, once there, click Insert/Module on its menu bar, then copy/paste the above code into the code window that just opened up. That's it.... you are done. To use the macro, go back to the worksheet with your data on it and press ALT+F8, select the macro name (GetNameWithC1) from the list that appears and click the Run button. The macro will execute and perform the action(s) you asked for. If you will need to do this again in this same workbook, and if you are using XL2007 or above, make sure you save your file as an "Excel Macro-Enabled Workbook (*.xlsm) and answer the "do you want to enable macros" question as "Yes" or "OK" (depending on the button label for your version of Excel) the next time you open your workbook.

 

[srrlex]

Rick,
I am sorry that it has taken me so long to thank you for your response, but I really appreciate you taking the time to write a macro in response to my question. Unfortunately, due to my unfamiliarity with macros, I could not take your input, expand it and make it work for what I am trying to accomplish. I realized that in my haste to get something posted, I did not describe my request very well, so I will attempt to do so more accurately with this post.
Originally, I indicated that I was trying to list the associated name whenever a “C1” was in a field. This is only partially correct. What I needed to say is that I desire to have multiple categories that can show an individual’s name whenever one of the criteria is in a field of the data. Certain categories can have multiple criteria that could be applicable, Perhaps the following grid will explain my desired results based upon the data that I originally submitted.


Desired Category Header  Clinic Transport Medicine NightMed Triage
Criteria from data set === C1 T1, T2 or T3 M1 NM1, WM1, WM2 N12
Results to list ======== Karen Wanda Lynne Teresia Kristen
Bobbie Mysti Kim Dot Samantha
Jessica Debbie Debbie


I hope this makes more sense and again I really appreciate your response. Originally, I thought that a pivot table was the best approach to get this information, but I could not figure out how to make that work either. If you have any other suggestions, I would be appreciative if you would share them with me.


Thanks!

 

[StephenCrump]

Desired Category Header  Clinic Transport Medicine NightMed Triage
Criteria from data set === C1 T1, T2 or T3 M1 NM1, WM1, WM2 N12
Results to list ======== Karen Wanda Lynne Teresia Kristen
Bobbie Mysti Kim Dot Samantha
Jessica Debbie Debbie

I am guessing that this is one set of criteria, with one set of results, in which case a possible formula approach:

J12: =IFERROR(INDEX($A$3:$A$22,SMALL(IF(MMULT(--ISNUMBER(MATCH($B$3:$H$22,YourList,)),{1;1;1;1;1;1;1}),ROW(A$3:A$22)-ROW(A$3)+1),ROWS(J$12:J12))),"")

K12: =IFERROR(INDEX($A$3:$A$22,SMALL(IF(MMULT(--ISNUMBER(MATCH($B$3:$H$22,MyList,)),{1;1;1;1;1;1;1}),ROW(B$3:B$22)-ROW(B$3)+1),ROWS(K$12:K12))),"")
Both array-entered, and copied down.

Excel 2010
	A	B	C	D	E	F	G	H	I	J	K
1		7 Jan 2018	8 Jan 2018	9 Jan 2018	10 Jan 2018	11 Jan 2018	12 Jan 2018	13 Jan 2018		YourList	MyList
2	Person	Sunday	Monday	Tuesday	Wednesday	Thursday	Friday	Saturday		C1	T2
3	Karen	W	C1	C1	C1	C1	C1	W		T1	xxx
4	Bobbie	W	C1	C1	C1	C1	C1	W		T2	yyy
5	Jessica	W	C1	C1	C1	C1	C1	W		T3
6	Kristen	W	N12	N12	W	W	WN1	WN1		M1
7	Samantha	WN1	W	W	N12	N12	W	W		NM1
8	Wanda	W	T2	T2	T2	W	T2	W		WM1
9	Mysti	W	T1	T1	T1	T2	T1	W		WM2
10	Debbie	WM1	W	T1	T1	T1	T1	W		N12
11	Lynne	W	M1	M1	M1	M1	M1	W
12	Kim	W	M1	M1	M1	M1	M1	W		Karen	Wanda
13	Teresia	W	W	NM1	NM1	NM1	W	WM1		Bobbie	Mysti
14	Dot	WM2	NM1	W	W	W	NM1	WM2		Jessica	Larry
15	Missy	W	FNW1	FNW1	FNW1	FNW1	FNW1	W		Kristen	Brianna
16	Nicki	FNW2	FNW2	FNW2	FNW2	FNW2	W	W		Samantha
17	Lisa	W	W	W	W	W	FNW2	FNW2		Wanda
18	Payton	W	W	W	W	MNW1	MNW1	W		Mysti
19	BritB	MNW1	MNW1	MNW1	MNW1	W	W	MNW1		Debbie
20	Larry			yyy						Lynne
21	Chanisty									Kim
22	Brianna						xxx			Teresia
23										Dot
Sheet1