Searching in Excel for certain values, if found give text from cell to the left of where we found the value

0
votes

First let me explain what I want to achieve.

I currently have an Excel like this:

Names                               | Standards  
James                               | Standard 1  
James                               | Standard 2  
James                               | Standard 3  
Francis                             | Standard 1  
Francis                             | Standard 2  
Francis                             | Standard 3  
Leon                                | Standard 2  
Leon                                | Standard 3  
Peter                               | Standard 2  
Michael                             | Standard 3

And I want to create something like this:

Standard   | Name 1 | Name 2  | Name 3 | Name 4  
Standard 1 | James  | Francis |        |
Standard 2 | James  | Francis | Leon   | Peter  
Standard 3 | James  | Francis | Leon   | Michael

My real Excel has more than 300 standards, so I would like to automate this using Excel Formula. I know this is possible, but I haven't used Excel in a while, so I could use a push in the right direction.

Couple of things I need (I think):

  • Need to count how many times people in the names column mention a standard. So I want to know that I need 2 names for standard 1 and 4 for standard 3. I think I can do this by using the COUNTIF method.

  • We need to search for the location of the standards. I think I can do this by using the Match function. This gives us the location of the first match in my original Excel. By sorting my original Excel a-z and combining it with the countif result I know where all the matches are (first match + countif = location of the last match, and everything inbetween is also that standard).

  • For the first name that mentioned a standard, I will reference the cell left of the first match (because the names are in the cell to the left of the standard I found). For the second name I will reference the cell left of the cell below the first match. I keep doing this till I find as many names as Countif mentioned. So I need an IF statement that makes sure that if 2 people mention standard 1 only gets 2 names and 2 cells with a "".

  • How will I reference the cells? By another if statement that uses this: Excel Reference To Current Cell , Correct me if I am wrong, but can't I then just say THIS.CELL=cell location I found (probably should use INDIRECT here?).

This is just me brainstorming, but I would love to know if people have any other ideas for my problem or have some feedback for my current plan.

An important thing to mention is that I want to do this using Excel Formula. I do realise that this isn't always the best, but VBA is not an option atm. I am also not worried about performance issues, because I think i'll just copy all the values after I found all the names using formulas.

Thanks in advance!

1
excelexcel-formula
Can you not use a pivot table? Seems ideal for what you need - Jonathan
I am not really familiar with pivot tables, I'll look into it and try to see what I can do with it. - CvP
Sounds good, please just ask if you want more help. I think that if you drag the 'standards' field and the 'names' field to the row data box, that should do it. - Jonathan
I'll report back tomorrow! - CvP
@Jonathan I am playing around a bit with the pivot tables and I agree that this is way easier than what I originally proposed. The only problem now is that I don't know how to progress with my pivot table. I made a pivot table with both my standards and names both in the "Rowslabels" and that gives me exactly what I want to see. The only problem is the layout. I want the names to be to the right of the standards and not below them. So I thought logic dictates that I should use the "Columnlabels", but when I put the names in there I get a sum/numbers instead of names, Any ideas? - CvP

1 Answers

0
votes

Depending on how you want to have the layout, I think you should use a pivot table. Drag the 'Standards' and 'Names' fields to the 'rows' data box and then right-click on a standard, click 'Field Settings' - 'Layout and Print' - 'Show item labels in tabular form'. (See example below.)

example 1 1

If you definitely need the data in the format in your question, I would edit the pivot table by dragging the 'names' field to the 'columns' data box. Then drag the 'standards' field from the field list above a second time and duplicate it in the 'values' box (see example below).

In the space underneath the pivot table, use an IF formula to only copy the name if there is a 1. This kind of approach will obviously be quite fragile, so if you can make do with the first approach, I think you will run into fewer problems in the future.

example 2