0

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!

Community
  • 1
  • 1
CvP
  • 324
  • 1
  • 4
  • 17
  • Can you not use a pivot table? Seems ideal for what you need – Jonathan Feb 24 '17 at 22:20
  • I am not really familiar with pivot tables, I'll look into it and try to see what I can do with it. – CvP Feb 24 '17 at 22:28
  • 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 Feb 24 '17 at 22:30
  • 1
    I'll report back tomorrow! – CvP Feb 24 '17 at 22:44
  • @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 Feb 25 '17 at 10:14

1 Answers1

0

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

Jonathan
  • 1,015
  • 1
  • 9
  • 25
  • Thanks a lot for all the help. I think I now know how to make something nice from my Excel. I do got another question though, is there a simple way to make the empty cells go away? Or another way of saying it, is there a way to make a cell move to the left if that cell to the left is empty? – CvP Feb 25 '17 at 17:34
  • I'm not sure if this is exactly what you mean, but within the pivot table you can rearrange the order of each cell by clicking and dragging. So, for example, if you click and drag the name 'Peter' to the left of 'Michael', it will move Peter before Michael – Jonathan Feb 25 '17 at 19:18
  • What I meant was: when you use the formula to make the 1's turn into a name or a "", it sometimes gives "" like it is supposed to. After this step I want to make it look a bit better by deleting all the cells that answer "". So instead of James Leon "" Peter I will get James Leon Peter. The problem is, I can't use F5-> options -> blanks to find all the blanks, because it isn't really a blank cell (it's a cell with a formula that returns ""). So the question is, how do I select only the cells with "" so that I can delete them? – CvP Feb 26 '17 at 14:05