Displaying data from multiple fields based on certain criteria.

[Graham C1600]

Hi,
I've been asked to try and source a solution to a problem we are facing. It's a bit complicated so hopefully this will explain my requirements.

I have an excel document with numerous tabs and it's to do with exam passes or fails. The first sheet (let's call it dashboard) looks a little like this :-

Teacher	Name	Geography	History	English	Science	Maths
Mrs H	Stephen	Fail	Fail	Pass	Pass	Fail
Mr B	Richard	Pass	Pass	Pass	Pass	Fail
Mrs C	John	Fail	Fail	Fail	Fail	Fail
Mr W	Martin	Pass	Pass	Fail	Pass	Pass

So basically this tells me what they have passed and what they have failed.

I then have a further 5 sheets names Geography, History etc... The Geography one look a little like this :-

Name	Geography	Pass or Fail	Remediation Actions
		Fail	Revise more

My ask is a follows, I want to be able to click on a specific name (say Stephen) and it then looks across to see what he has failed. Then open a new sheet and populate this new sheet with the tables from Geography, History and Maths sheets as these were the subjects he failed but populate the name with the same name clicked i.e Stephen. So hopefully if I clicked Stephen I would see a new sheet with 3 smaller tables on it showing which subjects he failed and remediation actions. The reason for this is so I can see all the info in one place to pass back to students. Hopefully looking like this.

Name	Geography	Pass or Fail	Remediation Actions
Stephen	Geography	Fail	Revise more

Name	History	Pass or Fail	Remediation Actions
Stephen	History	Fail	Study Harder

Name	Maths	Pass or Fail	Remediation Actions
Stephen	Maths	Fail	Read more Books

Hope this makes sense.

Thanks

 

[Graham C1600]

Ok here goes Dante,
Dashboard Tab:-

																						Failure 1	Failure 2	Failure 3	Failure 4	Failure 5			Failure 6
Heading 1	Heading 2	Heading 3	Heading 4	Heading 5	Heading 6	Heading 7	Heading 8	Heading 9	Heading 10	Heading 11	Heading 12	Heading 13	Heading 14	Heading 15	Heading 16	Heading 17	Heading 18	Heading 19	Heading 20	Heading 21	Heading 22	Heading 23	Heading 24	Heading 25	Heading 26	Heading 27	Heading 28	Heading 29	Heading 30
1212343	C-1500	Taylores	Simon T	Batch 186	12/09/2020	02/07/2020	72	Pass	3	1212343	C-1500	Taylores	Generic 1	Generic 2	Generic 3	Generic 4	Generic 5	Generic 6	Generic 7	Y	Active	Y	Y	Y	Y	Y	Generic 8	Generic 8	Y
344422	C-1501	Musics	Brian W	Batch 187	13/09/2020	02/07/2020	73	Pass	4	344422	C-1501	Musics	Generic 1	Generic 2	Generic 3	Generic 4	Generic 5	Generic 6	Generic 7	Y	Active	Y	Y	Y	Y	Y	Generic 9	Generic 9	Y
3636	C-1502	Brewery	Suzanne R	Batch 188	14/09/2020	02/07/2020	74	FAIL	2	3636	C-1502	Brewery	Generic 1	Generic 2	Generic 3	Generic 4	Generic 5	Generic 6	Generic 7	N	Active	N	N	N	Y	Y	Generic 10	Generic 10	Y
3463425253	C-1503	Sample	Clare C	Batch 189	15/09/2020	02/07/2020	75	FAIL	3	3463425253	C-1503	Sample	Generic 1	Generic 2	Generic 3	Generic 4	Generic 5	Generic 6	Generic 7	N	Active	N	Y	N	N	N	Generic 11	Generic 11	N
232534636	C-1504	Cloggies	Graham C	Batch 190	16/09/2020	02/07/2020	76	FAIL	2	232534636	C-1504	Cloggies	Generic 1	Generic 2	Generic 3	Generic 4	Generic 5	Generic 6	Generic 7	N	Active	N	N	Y	Y	Y	Generic 12	Generic 12	Y
345345625	C-1505	Bakers	Mark D	Batch 191	17/09/2020	02/07/2020	77	FAIL	1	345345625	C-1505	Bakers	Generic 1	Generic 2	Generic 3	Generic 4	Generic 5	Generic 6	Generic 7	N	Active	Y	N	N	N	N	Generic 13	Generic 13	N
123432	C-1506	Tom's	Simon T	Batch 192	18/09/2020	02/07/2020	78	FAIL	32	123432	C-1506	Tom's	Generic 1	Generic 2	Generic 3	Generic 4	Generic 5	Generic 6	Generic 7	N	Active	N	N	N	N	N	Generic 14	Generic 14	N

Failure 1 tab:-

Check/Column Name	Precondition	Priority	Action required	Issue	Action
Heading 23	Has it	Yes	Try again	Not known	Plan again
Heading 23	Had it	No	Leave it	Simple	Leave

Failure 2 tab:-

Check/Column Name	Precondition	Priority	Action required	Issue	Action
Heading 24	Hard	Yes	Merge	Migrate	re-code

Output Tab:-

Heading 1	Heading 2	Heading 3	Heading 4	Heading 5	Heading 6	Heading 7	Heading 8	Heading 9	Heading 10	Heading 11
3636	C-1502	Brewery	Suzanne R	Batch 188	14/09/2020	02/07/2020	74	Pass	2	3636
Check/Column Name	Precondition	Priority	Action required	Issue	Action
Heading 23	Has it	Yes	Try again	Not known	Plan again
Heading 23	Had it	No	Leave it	Simple	Leave
Check/Column Name	Precondition	Priority	Action required	Issue	Action
Heading 24	Hard	Yes	Merge	Migrate	re-code

So let me explain,
Looking down Column I to find a fail.
All the fails are listed in Columns W,X,Y,Z,AA & AD. If it shows an "N" it has failed.
Double click the name in Column C8 (this is the first that has failed). We can see across to column AD it failed on 3 counts (W8,X8 & Y8). So the ask here is to look across the row for the N's in columns W,X,Y,Z,AA & AD.
Now on the output tab I want to display like I have in the sample. Note that I have only used 2 x failures but realistically there could be 6. There will be a further 4 tabs created to show failures 3,4,5 & 6.

Hope this makes sense. It worked really well on the small dummy one you did so hopefully you can replicate onto this spreadsheet.

I also have another spreadsheet with pretty much the same layout but a few more failures. Are you able to explain how I can adapt your code to include some more looking for the N's and displaying more failures ?

Thanks for your time Dante.

Graham

 

[DanteAmor]

Right now I have more questions than solutions.
1. The macro should create the sheets: Failure 1 tab and Failure 2 tab?
2. Why did you put Heading 23 twice in the output tab?
3. In the Ouput tab you put these data: "Precondition, Priority, Action required, Issue, Action". Where do I get this data from?
4. In your example for "Brewery" did you omit "Heading 25"?

 

[Graham C1600]

Dante,
Answers below:-
1. The macro should create the sheets: Failure 1 tab and Failure 2 tab?
The macros needs to look across the worksheet and where there is a "N" in columns W,X,Y,Z,AA & AD and then pull data from the relevant other tabs i.e Failure 1 etc.. I only added 2 x Failure tabs but there will be 6 on the finished spreadsheet.
2. Why did you put Heading 23 twice in the output tab?
This is because for this failure there are 2 x possible solutions. So on the Dashboard tab is shows as a failure "Heading 23" but there are a couple of solutions to fix it. It's a bit of duplication but i'd rather have it if i could.
3. In the Ouput tab you put these data: "Precondition, Priority, Action required, Issue, Action". Where do I get this data from?
The output tab is what i want the actual output to look like when you double click the name in Column C. So this wouldn't be there in the start of the spreadsheet. The output has taken data from the dashboard tab and also the Failure Tabs (depending if they failed of not.
4. In your example for "Brewery" did you omit "Heading 25"?
Yes i did, there would be another 4 tabs in the finished spreadsheet showing failures 3,4,5,& 6. Do you want me to add the other tabs and upload them ? they will be pretty much the same as failures 1 & 2 in structure but the text will be slightly different.

Thanks

 

[DanteAmor]

I'm sorry but I don't understand your answers.

I will try again.

1. The macro should create the sheets: Failure 1 tab and Failure 2 tab? Yes or No.

2. Why did you put Heading 23 twice in the output tab? How to know if it is one or two?

3. In the Ouput tab you put these data: "Precondition, Priority, Action required, Issue, Action". Where do I get this data from?

How to put those data? you can explain in detail, at least explain 2 of those data.

4. In your example for "Brewery" did you omit "Heading 25"? Yes or No.

 

[Graham C1600]

I'm sorry but I don't understand your answers.

I will try again.

1. The macro should create the sheets: Failure 1 tab and Failure 2 tab? Yes or No.
In the example i sent it is yes. However on my actual spreadsheet there are 6 failure tabs. I will load a new form with all 6 failures on.

2. Why did you put Heading 23 twice in the output tab? How to know if it is one or two?
This can be seen from looking at the failure tabs. Sometimes there might be 2 issues to report on and sometimes only one.


3. In the Ouput tab you put these data: "Precondition, Priority, Action required, Issue, Action". Where do I get this data from?
View attachment 17562
How to put those data? you can explain in detail, at least explain 2 of those data.

This data is found on the failure tabs.

4. In your example for "Brewery" did you omit "Heading 25"? Yes or No.
I will upload the full forms so you can see everything.

So the output tab is taking details from the dashboard tab (Headings 1 to 11)
Then takes details from the relevant failure tabs (W,X,Y,Z,AA & AD)
This is all based on double clicking the name in Column C.

Below are updated forms.

																						Failure 1	Failure 2	Failure 3	Failure 4	Failure 5			Failure 6
Heading 1	Heading 2	Heading 3	Heading 4	Heading 5	Heading 6	Heading 7	Heading 8	Heading 9	Heading 10	Heading 11	Heading 12	Heading 13	Heading 14	Heading 15	Heading 16	Heading 17	Heading 18	Heading 19	Heading 20	Heading 21	Heading 22	Heading 23	Heading 24	Heading 25	Heading 26	Heading 27	Heading 28	Heading 29	Heading 30
1212343	C-1500	Taylores	Simon T	Batch 186	12/09/2020	06/07/2020	68	Pass	3	1212343	C-1500	Taylores	Generic 1	Generic 2	Generic 3	Generic 4	Generic 5	Generic 6	Generic 7	Y	Active	Y	Y	Y	Y	Y	Generic 8	Generic 8	Y
344422	C-1501	Musics	Brian W	Batch 187	13/09/2020	06/07/2020	69	Pass	4	344422	C-1501	Musics	Generic 1	Generic 2	Generic 3	Generic 4	Generic 5	Generic 6	Generic 7	Y	Active	Y	Y	Y	Y	Y	Generic 9	Generic 9	Y
3636	C-1502	Brewery	Suzanne R	Batch 188	14/09/2020	06/07/2020	70	FAIL	2	3636	C-1502	Brewery	Generic 1	Generic 2	Generic 3	Generic 4	Generic 5	Generic 6	Generic 7	N	Active	N	N	N	Y	Y	Generic 10	Generic 10	Y
3463425253	C-1503	Sample	Clare C	Batch 189	15/09/2020	06/07/2020	71	FAIL	3	3463425253	C-1503	Sample	Generic 1	Generic 2	Generic 3	Generic 4	Generic 5	Generic 6	Generic 7	N	Active	N	Y	N	N	N	Generic 11	Generic 11	N
232534636	C-1504	Cloggies	Graham C	Batch 190	16/09/2020	06/07/2020	72	FAIL	2	232534636	C-1504	Cloggies	Generic 1	Generic 2	Generic 3	Generic 4	Generic 5	Generic 6	Generic 7	N	Active	N	N	Y	Y	Y	Generic 12	Generic 12	Y
345345625	C-1505	Bakers	Mark D	Batch 191	17/09/2020	06/07/2020	73	FAIL	1	345345625	C-1505	Bakers	Generic 1	Generic 2	Generic 3	Generic 4	Generic 5	Generic 6	Generic 7	N	Active	Y	N	N	N	N	Generic 13	Generic 13	N
123432	C-1506	Tom's	Simon T	Batch 192	18/09/2020	06/07/2020	74	FAIL	32	123432	C-1506	Tom's	Generic 1	Generic 2	Generic 3	Generic 4	Generic 5	Generic 6	Generic 7	N	Active	N	N	N	N	N	Generic 14	Generic 14	N

Failure 1

Check/Column Name	Precondition	Priority	Action required	Issue	Action
Heading 23	Has it	Yes	Try again	Not known	Plan again
Heading 23	Had it	No	Leave it	Simple	Leave

Failure 2

Check/Column Name	Precondition	Priority	Action required	Issue	Action
Heading 24	Hard	Yes	Merge	Migrate	re-code

Failure 3

Check/Column Name	Precondition	Priority	Action required	Issue	Action
Heading 25	Has it	Yes	No Action	Known	Repeat

Failure 4

Check/Column Name	Precondition	Priority	Action required	Issue	Action
Heading 26	Do it	Yes	Leave again	Not known	Plan again
Heading 26	Do it	No	Repeat now	Simple	Leave

Failure 5

Check/Column Name	Precondition	Priority	Action required	Issue	Action
Heading 27	Known	Not	Return	Blank	Plan again

Failure 6

Check/Column Name	Precondition	Priority	Action required	Issue	Action
Heading 30	Mighty	Yes	Abandon	Not known	Plan again
Heading 30	Small	No	Submit	Simple	Leave

Output Tab

Heading 1	Heading 2	Heading 3	Heading 4	Heading 5	Heading 6	Heading 7	Heading 8	Heading 9	Heading 10	Heading 11
3636	C-1502	Brewery	Suzanne R	Batch 188	14/09/2020	06/07/2020	70	Pass	2	3636
Check/Column Name	Precondition	Priority	Action required	Issue	Action
Heading 23	Has it	Yes	Try again	Not known	Plan again
Heading 23	Had it	No	Leave it	Simple	Leave
Check/Column Name	Precondition	Priority	Action required	Issue	Action
Heading 24	Hard	Yes	Merge	Migrate	re-code
Check/Column Name	Precondition	Priority	Action required	Issue	Action
Heading 25	Has it	Yes	No Action	Known	Repeat

Thanks

 

[DanteAmor]

Perhaps there are many doubts.
We are going to solve only the first one and we are moving forward.

1. The macro should create the sheets: Failure 1 tab and Failure 2 tab? Yes or No.
In the example i sent it is yes. However on my actual spreadsheet there are 6 failure tabs. I will load a new form with all 6 failures on.

So every time you run the macro, the macro should create 6 sheets?

 

[Graham C1600]

Perhaps there are many doubts.
We are going to solve only the first one and we are moving forward.

1. The macro should create the sheets: Failure 1 tab and Failure 2 tab? Yes or No.
In the example i sent it is yes. However on my actual spreadsheet there are 6 failure tabs. I will load a new form with all 6 failures on.

So every time you run the macro, the macro should create 6 sheets?

Dante,

So the standard form will have the dashboard populated with data. The tabs Failure 1-6 will be there with standard data on them so they do not need creating.
So when you double click a name down column C, I want a look across the row to columns W,X,Y,Z,AA & AD to see if there is an N there. The failure tabs reflect these columns so if there is an N in column W (this is failure 1).

The ask here on double click is as follows :-

1. Create a new sheet
2. Firstly on the new sheet, show headings 1 - 11 as per the name clicked in column C.
3. Then depending if there are N's in W,X,Y,Z,AA & AD, also add to the new sheet the small tables from the related failures.

Thanks and apologies for any confusion.

 

[DanteAmor]

Sorry, but it's still very confusing to me.
I just want you to answer a simple question:

1. The macro should create the sheets: Failure 1 tab and Failure 2 tab? Yes or No.

"In the example i sent it is yes. "
" The tabs Failure 1-6 will be there with standard data on them so they do not need creating. "

Please answer Yes or No.
Your answers are ambiguous, sometimes yes, sometimes no.

After I understand the first of my doubts, we move on to the next question. While I can't move forward.

 

[Graham C1600]

Sorry, but it's still very confusing to me.
I just want you to answer a simple question:

1. The macro should create the sheets: Failure 1 tab and Failure 2 tab? Yes or No.

"In the example i sent it is yes. "
" The tabs Failure 1-6 will be there with standard data on them so they do not need creating. "

Please answer Yes or No.
Your answers are ambiguous, sometimes yes, sometimes no.

After I understand the first of my doubts, we move on to the next question. While I can't move forward.

The macro will not create failure sheets, only the output sheet. The failure sheets are already there in the spreadsheet.

 

[DanteAmor]

Try this

Considerations:
1. You manually create the "Output" sheet. The macro will clean the sheet and put the new information each time you double click.
2. Put the code on the sheet where you want the event.
3. You must have created the sheets "Failure 1" to "Failure 6"
4. The columns to validate are: "W", "X", "Y", "Z", "AA", "AD"
5. You must double click on any cell in column "B".

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
  If Target.Column <> 2 Then Exit Sub
  If Target.Value = "" Or Target.Row < 3 Then Exit Sub

  Dim sh2 As Worksheet, i As Long, arr As Variant
  Set sh2 = Sheets("Output")
  sh2.Cells.Clear
  arr = Array("W", "X", "Y", "Z", "AA", "AD")
  Range("A2:K2, A" & Target.Row & ":K" & Target.Row).Copy sh2.Range("A2")
  
  For i = 0 To UBound(arr)
    If Range(arr(i) & Target.Row) = "Y" Then
      With Sheets("Failure " & i + 1)
        .Range("A1", .Range("F" & Rows.Count).End(3)).Copy sh2.Range("A" & Rows.Count).End(3)(3)
      End With
    End If
  Next
  
  Cancel = True
  sh2.Select
End Sub