Checking content of cells to the left and right of a cell that contains a TRUE string for a search

Zogmaster

New Member
Joined
Mar 25, 2021
Messages
9
Office Version
  1. 365
Platform
  1. Windows
Hi,

I have a list of 10 names in column D1:D10.

I have three columns of data that I need to sort to get what I need from.

A1:A130 contains the number of tries between 1 and 6 that a student has attempted
B1:B130 contains the list of students names in attempt order. The names will be one of the listed names in D1:D10 but in attempt order
C1:C130 contains the results which are all between 0 - 1000

In columns E1 (student name) I want the name of the student starting with D1.
In column F1 (1st attempt) I want to search column B1:B130 for the student I have placed into E1 and if I get a match, I need to then to check the cell immediately to the left of the matched cell (this is the bit I am having difficulty with) and if that is set to "1", then I want to place the results from the cell immediately to the right of the name matched cell into F1 as the result.

Columns G1 - K1 will be the same but checking for a "2", "3", "4", "5", and finally "6" which correspond to the 2nd through 6th attempt. Some may only have 3 attempts.

I am struggling to find the right formula that will work.

My end result I want to look like this

E1 (student name), F1 (1st attempt score), G1 (2nd attempt score), H1 (3rd attempt score), I1 (4th attempt score), J1 (5th attempt score), K1 (6th attempt score)

then continue down until I have all the student information

E2 (student name), F2 (1st attempt score), G2 (2nd attempt score), H2 (3rd attempt score), I2 (4th attempt score), J2 (5th attempt score), K2 (6th attempt score)
E3 (student name), F3 (1st attempt score), G3 (2nd attempt score), H3 (3rd attempt score), I3 (4th attempt score), J3 (5th attempt score), K3 (6th attempt score)

.... etc until all 10 students and their corresponding results from each attempt has been recorded.

I hope this makes sense, but if not please let me know what else would be useful.
Paul
 

Excel Facts

Highlight Duplicates
Home, Conditional Formatting, Highlight Cells, Duplicate records, OK to add pink formatting to any duplicates in selected range.

Toadstool

Well-known Member
Joined
Mar 5, 2018
Messages
1,448
Office Version
  1. 2016
Platform
  1. Windows
Hi Zogmaster,

I'm just using 4 names for brevity but does this do what you ask?

Zogmaster.xlsx
ABCDEFGHIJK
11Charles22BertBert335566   
21Bert33CharlesCharles22447788222333
32Charles44KimKim99111444   
42Bert55AlfAlf111111555   
53Bert66      
63Charles77      
74Charles88      
81Kim99      
91Alf11      
102Kim111      
112Alf1111      
125Charles222      
136Charles333      
143Kim444      
153Alf555      
Sheet1
Cell Formulas
RangeFormula
F1:K15F1=IFERROR(INDEX($C$1:$C$130, MATCH(1, INDEX(($B$1:$B$130=$E1) * ($A$1:$A$130=COLUMN()-COLUMN($E1)), 0, 1), 0)),"")
E1:E4E1=D1
 

Zogmaster

New Member
Joined
Mar 25, 2021
Messages
9
Office Version
  1. 365
Platform
  1. Windows
OMG that is genius!! thank you so much for your help Toadstool
 

Toadstool

Well-known Member
Joined
Mar 5, 2018
Messages
1,448
Office Version
  1. 2016
Platform
  1. Windows
You're welcome!
Thanks for the feedback.
 
Solution

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
48,504
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

Welcome to the MrExcel board!

I am not suggesting anything wrong with the proposed solution but since you have Microsoft 365 you can do the same job with much fewer formula cells - in this case just 5 (the green ones below).
The other results automatically spill down column E and across columns F, G, H, ... individually by row so you don't have to decide how far to copy anything across.

I have assumed that the attempts for each person progress down the column as in Toadstool's example. If not a modification is required but would still only require the five formula cells..

21 03 26.xlsm
ABCDEFGHIJK
11Charles22BertBert335566
21Bert33CharlesCharles22447788222333
32Charles44KimKim99111444
42Bert55AlfAlf111111555
53Bert66
63Charles77
74Charles88
81Kim99
91Alf11
102Kim111
112Alf1111
125Charles222
136Charles333
143Kim444
153Alf555
Attempts
Cell Formulas
RangeFormula
E1:E4E1=D1:D4
F1:H1,F3:H4,F2:K2F1=TRANSPOSE(FILTER(C$1:C$15,B$1:B$15=E1,""))
Dynamic array formulas.
 

Zogmaster

New Member
Joined
Mar 25, 2021
Messages
9
Office Version
  1. 365
Platform
  1. Windows
I haven't used TRANSPOSE before so will certainly give it a go thank you so much Peter_SS's
 

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
48,504
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

You're welcome. :)
 

Zogmaster

New Member
Joined
Mar 25, 2021
Messages
9
Office Version
  1. 365
Platform
  1. Windows
I have a very similar challenge, but this time it is a two team battle statistical playbook. My spreadsheet is here on my cloud storage: Warriors Quest - 24-25 March 2021 - War Statistics - Blow by Blow.xlsx

The data between rows A - L is all hard coded. This is the source data to populate all the chart fields or used to calculate totals from.

It is the cells in rows P - AL (The attacking sequence) and cells AR - CD (the defending sequence) that I would really love to automate.

There are two teams. Warriors Quest (my own team) and the chart information is at the top, and the Opposition Team who's data us underneath as you will see in the spreadsheet.

Is there a way to auto populate each cell with the relevant data from that entered between rows A - L?

Each team member can have up to a maximum of 6 attacks in any battle, but there are n number of possible attacks as there could be multiple opposition players attacking. In the current game there is a max of 10 attacks made as you will see.

Can you help?
Paul
 

Zogmaster

New Member
Joined
Mar 25, 2021
Messages
9
Office Version
  1. 365
Platform
  1. Windows
The above is only used by myself and another friend and I know it is a bit of a brain tester.

Any guidance will be really welcomed.

Paul
 
Last edited by a moderator:

Zogmaster

New Member
Joined
Mar 25, 2021
Messages
9
Office Version
  1. 365
Platform
  1. Windows
I altered the headers for the charts as I got muddled up with which was which.

They are now much clearer and I have used Attacks Made and Defenses Made, which is much better.

Paul
 

Watch MrExcel Video

Forum statistics

Threads
1,129,590
Messages
5,637,279
Members
416,963
Latest member
samfuge

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
Top