need a macro for arranging the data
 

Hi All,

I have the data that needs to be arrange in a particular order. for eg.)

Name Marks Comment
Alpha 70 Good
Alpha 80 Very Good
Beta 85 very good
Beta 75 good
beta 60 Average

from the given data I need to pick up the Name with Max mark & its comments.

This is a sample data But I have 5000 lineitems that need to sorted out.

Can any macro be done for this routine job.

thanks,

need a macro for arranging the data
 

Doesn't Excel's sort do that for you?

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

"yagna" wrote in message
...
Hi All,

I have the data that needs to be arrange in a particular order. for eg.)

Name Marks Comment
Alpha 70 Good
Alpha 80 Very Good
Beta 85 very good
Beta 75 good
beta 60 Average

from the given data I need to pick up the Name with Max mark & its
comments.

This is a sample data But I have 5000 lineitems that need to sorted out.

Can any macro be done for this routine job.

thanks,

need a macro for arranging the data
 

This is to be done in the next sheet & only the Line item need to be displayed.

If I sort, there will be repetition of the name & If i do pivot of Max of
marks & then do vlookup is also time consuming.
Hope you understand.
thanks
yagna

"Bob Phillips" wrote:

Doesn't Excel's sort do that for you?

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

"yagna" wrote in message
...
Hi All,

I have the data that needs to be arrange in a particular order. for eg.)

Name Marks Comment
Alpha 70 Good
Alpha 80 Very Good
Beta 85 very good
Beta 75 good
beta 60 Average

from the given data I need to pick up the Name with Max mark & its
comments.

This is a sample data But I have 5000 lineitems that need to sorted out.

Can any macro be done for this routine job.

thanks,

need a macro for arranging the data
 

Lets say the data looked like:

TYPE SCORE COMMENT
plum 23 comment1
apple 1 comment2
orange 2 comment3
apple 50 comment4
pear 12 comment5
apple 10 comment6
pear 87 comment7
plum 100 comment8
pear 54 comment9
orange 4 comment10
orange 78 comment11

The first step is to sort by name and score (descending) to get:

TYPE SCORE COMMENT
apple 50 comment4
apple 10 comment6
apple 1 comment2
orange 78 comment11
orange 4 comment10
orange 2 comment3
pear 87 comment7
pear 54 comment9
pear 12 comment5
plum 100 comment8
plum 23 comment1

Notice that the first occurance of a type is the maximum score! The next
step is to do an advanced filter from column A onto column D unique records
only. Data now looks like:

TYPE SCORE COMMENT TYPE
apple 50 comment4 apple
apple 10 comment6 orange
apple 1 comment2 pear
orange 78 comment11 plum
orange 4 comment10
orange 2 comment3
pear 87 comment7
pear 54 comment9
pear 12 comment5
plum 100 comment8
plum 23 comment1

In E2 enter:
=VLOOKUP(D2,$A$2:$C$12,2,FALSE)

In F2 enter:
=VLOOKUP(D2,$A$2:$C$12,3,FALSE)

and copy down. In the end cols D thru E will show:

TYPE
apple 50 comment4
orange 78 comment11
pear 87 comment7
plum 100 comment8

which is what you want. To automate this use the Macro Recorder
--
Gary''s Student


"yagna" wrote:

This is to be done in the next sheet & only the Line item need to be displayed.

If I sort, there will be repetition of the name & If i do pivot of Max of
marks & then do vlookup is also time consuming.
Hope you understand.
thanks
yagna

"Bob Phillips" wrote:

Doesn't Excel's sort do that for you?

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

"yagna" wrote in message
...
Hi All,

I have the data that needs to be arrange in a particular order. for eg.)

Name Marks Comment
Alpha 70 Good
Alpha 80 Very Good
Beta 85 very good
Beta 75 good
beta 60 Average

from the given data I need to pick up the Name with Max mark & its
comments.

This is a sample data But I have 5000 lineitems that need to sorted out.

Can any macro be done for this routine job.

thanks,