Lookup multiple cell values and place contents in a single cell

[New Hope UMC]

I received help on this earlier and the solution did not work, 'cause
I did not explain it well enough. So here we go...
Sheet "Team Breakout"
Cell D:27 is the company name
Column C Column D
Cell C:35 "A" All Teams achieving an "A" score
Cell C:36 "B" All Teams achieving an "B" score
Cell C:37 "C" All Teams achieving an "C" score
Cell C:38 "D" All Teams achieving an "D" score
Cell C:39 "F" All Teams achieving an "D" score
The above represents the layout I need.
The actual data this comes from is as follows:
Sheet "CrewData"
Column A Column B Column C
Company Name "Team" The score
What I need is this:
Cell D35; give me all of the teams achieving an "A" score (which is
represented ">=90%, <=100%") by looking in the at column A (matching
the Company Name in Cell D:27) and column C matching the ">=90%,
<=100%" Score. Cell D35 could then contain up to 85 different numbers
Then repeat the process for B,C,D and F scores appropriately.
Any clues?

 

[Don Guillett Excel MVP]

I received help on this earlier and the solution did not work, 'cause
I did not explain it well enough. So here we go...
Sheet "Team Breakout"
Cell D:27 is the company name
Column C                       Column D
Cell C:35 "A"               All Teams achieving an "A" score
Cell C:36 "B"               All Teams achieving an "B" score
Cell C:37 "C"               All Teams achieving an "C" score
Cell C:38 "D"               All Teams achieving an "D" score
Cell C:39 "F"               All Teams achieving an "D" score
The above represents the layout I need.
The actual data this comes from is as follows:
Sheet "CrewData"
Column A             Column B   Column C
Company Name     "Team"      The score
What I need is this:
Cell D35; give me all of the teams achieving an "A" score (which is
represented ">=90%, <=100%") by looking in the at column A (matching
the Company Name in Cell D:27) and column C matching the ">=90%,
<=100%" Score. Cell D35 could then contain up to 85 different numbers
Then repeat the process for B,C,D and F scores appropriately.
Any clues?

"If desired, send your file to dguillett @gmail.com I will only look
if:
1. You send a copy of this message on an inserted sheet
2. You give me the newsgroup and the subject line
3. You send a clear explanation of what you want
4. You send before/after examples and expected results."

 

[Don Guillett Excel MVP]

What would be the purpose of placing all those "up to 85 numbers" in one cell?

An Excel worksheet has quite enough cells to keep you happy without pounding
multiple data into one cell in order to save space.

What will you do with the 85 numbers after you get them into D35?

Post the solution that did not work or a message ID of that posting.

Better yet..............post your workbook on line for download.

http://www.savefile.com/

http://freefilehosting.net/

Add a description to an empty sheet.

Gord Dibben     MS Excel MVP



- Show quoted text -

Gord, I got the workbook and sent this. I agree with your assessment

Option Explicit
Option Private Module

Sub GetCompanyScoreSAS()
Dim mc As String
Dim mg As Variant
Dim lr As Long
Dim dlr As Integer
Dim ms As String
Dim cc As Long
Dim i As Long
mc = Range("C1") '"Quest"

With Sheets("DATA")
lr = .Cells(Rows.Count, 1).End(xlUp).Row
dlr = 2
For Each mg In Array("A", "B", "C", "D", "F")
ms = ""
cc = 0
For i = 1 To lr
If .Cells(i, "a") = mc And .Cells(i, "c") = mg Then
ms = ms & "," & .Cells(i, "b")
cc = cc + 1
End If
Next i
Cells(dlr, "C") = cc
Cells(dlr, "D") = ms
dlr = dlr + 1
Next mg
End With
End Sub

 

[Gord Dibben]

Thanks Don.


Gord