Finding all Text in a column that matches a numeric value

[joechamp]

Hi,

Could someone please help!

I've created a brainstorming activity in which we create a list of text answers in column 1, then in column 2 we go through and assign each text a specific numeric value 1-5. Each numeric value is actually assigned to a category name.

Here is where I need help with a formula. On a seperate tab, I'm trying to group ALL text into the 5 categories based on the assigned numeric value given in column 2 in the original sheet. Is there a formula I can use to look and list all text assigned a specific numeric values in column 2?

Thanks,

Joechamp

 

[prabby25101981]

A macro should do this easily. Here's the code (I am assuming that Sheet1 has headers in row 1 and then values, in Sheet2, there are values from 1 to 5 in A1 to A5 and then you want the data):

Sub PickText()
Dim LRow As Long
LRow = Sheets("Sheet1").Range("A65536").End(xlUp).Row
For i = 2 To LRow
    Select Case Sheets("Sheet1").Range("B" & i).Value
        Case 1
            Sheets("Sheet2").Range("A" & Sheets("Sheet2").Range("A65536").End(xlUp).Row + 1).Value = _
                Sheets("Sheet1").Range("A" & i).Value
        Case 2
            Sheets("Sheet2").Range("B" & Sheets("Sheet2").Range("B65536").End(xlUp).Row + 1).Value = _
                Sheets("Sheet1").Range("A" & i).Value
        Case 3
            Sheets("Sheet2").Range("C" & Sheets("Sheet2").Range("C65536").End(xlUp).Row + 1).Value = _
                Sheets("Sheet1").Range("A" & i).Value
        Case 4
            Sheets("Sheet2").Range("D" & Sheets("Sheet2").Range("D65536").End(xlUp).Row + 1).Value = _
                Sheets("Sheet1").Range("A" & i).Value
        Case 5
            Sheets("Sheet2").Range("E" & Sheets("Sheet2").Range("E65536").End(xlUp).Row + 1).Value = _
                Sheets("Sheet1").Range("A" & i).Value
    End Select
Next
End Sub

 

[joechamp]

Hi Prabby,

Thanks for your quick response. I'm not sure how to edit the VB to make it match up to the specif cells in my workbook. Could I send you the file so you can look at it...I could really use your help in writing a quick VB script for the document that I'm working in.

Thanks,

JoeChamp

 

[prabby25101981]

You can gimme the example of where the data is featuring in your workbook and I can change it. Otherwise, you can IM me and I can provide you details of my email...

 

[Peter_SSs]

Could I send you the file so you can look at it...

Welcome to the MrExcel board!

That's generally not a good idea in that once you do that other users tend not to get involved because they don't know what developments have taken place in private correspondence. That is, you tend to reduce your pool of possible helpers from hundreds, to one. If that user turns out to not be able to solve your problem or loses interest, you are back to square one. It also defeats the basic purpose of this being a public forum.

I'm not sure exactly what you are after, but for a formula approach, you might be able to adapt my suggestion in post #6 here