find values from column and place it in individual

  • Thread starter Thread starter crapit
  • Start date Start date
C

crapit

How to get all value for a particular cell value such
that it appear in individual columns (assuming that the max. points is 10)

code points1 points2 point3 point4 point5
yuui 10.00 60.00 85.00 90.00
mnhj 20.00 40.00 80.00
eeew 20.00 30.00 70.00


original

A B
1 CODE POINTS
2 abc 45.00
3 yuui 85.00
4 abcd 15.00
5 mnhj 40.00
6 abce 30.00
7 azxc 60.00
8 yuui 10.00
9 eeew 20.00
10 rtyu 2.00
11 mnhj 80.00
12 tyhj 25.00
13 ujyn 45.00
14 dfgr 30.00
15 ukjk 55.00
16 yuui 60.00
17 erwt 20.00
18 eeew 70.00
19 mnhj 20.00
20 nmbg 20.00
21 eeew 30.00
22 yuui 90.00
 
I'm not sure what max points is 10 means, but maybe you could use a macro:

Option Explicit
Sub testme()
Dim CurWks As Worksheet
Dim NewWks As Worksheet
Dim iRow As Long
Dim res As Variant

Set CurWks = Worksheets("sheet1")
Set NewWks = Worksheets.Add

With CurWks
.Range("A1", .Cells(.Rows.Count, "A").End(xlUp)).AdvancedFilter _
Action:=xlFilterCopy, _
copytorange:=NewWks.Range("a1"), _
unique:=True

For iRow = 2 To .Cells(.Rows.Count, "A").End(xlUp).Row
res = Application.Match(.Cells(iRow, "A").Value, _
NewWks.Range("a:a"), 0)
If IsError(res) Then
'this shouldn't happen!
MsgBox "Error with row #: " & iRow
Else
NewWks.Cells(res, NewWks.Columns.Count) _
.End(xlToLeft).Offset(0, 1).Value _
= .Cells(iRow, "B").Value
End If
Next iRow
End With
End Sub

If you're new to macros:

Debra Dalgleish has some notes how to implement macros here:
http://www.contextures.com/xlvba01.html

David McRitchie has an intro to macros:
http://www.mvps.org/dmcritchie/excel/getstarted.htm

Ron de Bruin's intro to macros:
http://www.rondebruin.nl/code.htm

(General, Regular and Standard modules all describe the same thing.)
 
Back
Top