sort problem

Mike · 11-28-2005, 02:55 PM

I have added a tab delimited table to the bottom of this post to help explain
what I am trying to do.

I need to sort my data by Sample Type with Standard data first, then an
empty row then Qaulity Control data; that then needs to be ordered by Sample
Name with an empty row between the different QC levels. Anything starting
with RB or PD can have it's row deleted.

I have no idea where to start ANY help is greatly appreciated.

Thanks
Mike

data table before sort:
Sample Name Dil. Factor Accuracy (%) Calc. Conc. Calc.
Conc. Comments Sample Type
Equil-1 1 N/A 111.0000 Unknown
RB-1b 1 N/A 0.0000 Unknown
PB-1 1 N/A 825.0000 Unknown
PB/IS-1 1 N/A < 0 Unknown
QC 100ng/mL-1 1 170 170.0000 Quality Control
QC 50.0ng/mL-1 1 110 54.9000 Quality Control
QC 100ng/mL dil10-1 10 117 117.0000 Quality Control
QC 5.00ng/mL-1 1 111 5.5600 Quality Control
QC 0.500ng/mL-1 1 91.2 0.4560 Quality Control
Cal 200ng/mL-1 1 101 201.0000 Standard
Cal 100ng/mL-1 1 99.1 99.1000 Standard
Cal 50.0ng/mL-1 1 96.7 48.4000 Standard
Cal 25.0ng/mL-1 1 93.9 23.5000 Standard
Cal 10.0.ng/mL-1 1 114 11.4000 Standard
Cal 5.00ng/mL-1 1 112 5.5800 Standard
Cal 2.50ng/mL-1 1 107 2.6800 Standard
Cal 1.00ng/mL-1 1 108 1.0800 Standard
Cal 0.500ng/mL-1 1 91.6 0.4580 Standard
Cal 0.100ng/mL-1 1 4.5 0.0845 Standard
488/Day1/Gr6/0hr 1 N/A < 0 Unknown
489/Day1/Gr6/0hr 1 N/A 0.0162 Unknown
490/Day1/Gr6/0hr 1 N/A 0.2830 Unknown
491/Day1/Gr6/0.03hr 1 N/A 0.4420 Unknown
492/Day1/Gr6/0.03hr 1 N/A 0.7010 Unknown
493/Day1/Gr6/0.03hr 1 N/A 0.3250 Unknown
494/Day1/Gr6/0.08hr 1 N/A 20.1000 Unknown
495/Day1/Gr6/0.08hr 1 N/A 10.6000 Unknown
QC 100ng/mL-1 1 190 190.0000 Quality Control
QC 50.0ng/mL-1 1 200 44.9000 Quality Control
QC 100ng/mL dil10-1 10 125 101.0000 Quality Control
QC 5.00ng/mL-1 1 166 5.77 Quality Control
QC 0.500ng/mL-1 1 95.0 0.6560 Quality Control

data table after sort:
Sample Name Dil. Factor Accuracy (%) Calc. Conc. Calc.
Conc. Comments Sample Type
Cal 200ng/mL-1 1 101 201.0000 Standard
Cal 100ng/mL-1 1 99.1 99.1000 Standard
Cal 50.0ng/mL-1 1 96.7 48.4000 Standard
Cal 25.0ng/mL-1 1 93.9 23.5000 Standard
Cal 10.0.ng/mL-1 1 114 11.4000 Standard
Cal 5.00ng/mL-1 1 112 5.5800 Standard
Cal 2.50ng/mL-1 1 107 2.6800 Standard
Cal 1.00ng/mL-1 1 108 1.0800 Standard
Cal 0.500ng/mL-1 1 91.6 0.4580 Standard
Cal 0.100ng/mL-1 1 4.5 0.0845 Standard

QC 100ng/mL-1 1 170 170.0000 Quality Control
QC 100ng/mL-1 1 190 190.0000 Quality Control

QC 50.0ng/mL-1 1 110 54.9000 Quality Control
QC 50.0ng/mL-1 1 200 44.9000 Quality Control

QC 100ng/mL dil10-1 10 117 117.0000 Quality Control
QC 100ng/mL dil10-1 10 125 101.0000 Quality Control

QC 5.00ng/mL-1 1 111 5.5600 Quality Control
QC 5.00ng/mL-1 1 166 5.77 Quality Control

QC 0.500ng/mL-1 1 91.2 0.4560 Quality Control
QC 0.500ng/mL-1 1 95.0 0.6560 Quality Control

Equil-1 1 N/A 111.0000 Unknown
RB-1b 1 N/A 0.0000 Unknown
PB-1 1 N/A 825.0000 Unknown
PB/IS-1 1 N/A < 0 Unknown
488/Day1/Gr6/0hr 1 N/A < 0 Unknown
489/Day1/Gr6/0hr 1 N/A 0.0162 Unknown
490/Day1/Gr6/0hr 1 N/A 0.2830 Unknown
491/Day1/Gr6/0.03hr 1 N/A 0.4420 Unknown
492/Day1/Gr6/0.03hr 1 N/A 0.7010 Unknown
493/Day1/Gr6/0.03hr 1 N/A 0.3250 Unknown
494/Day1/Gr6/0.08hr 1 N/A 20.1000 Unknown
495/Day1/Gr6/0.08hr 1 N/A 10.6000 Unknown

Pete · 11-28-2005, 08:10 PM

Do you want a macro to do this (as you have posted in the programming
forum), or do you want to do it manually ?

Pete

Mike · 11-29-2005, 09:25 AM

Pete Thanks for the reply.

I am doing this in a macro. This is just representative data. The first
part of the macro imports data, this part is to format the report.

The way I see it I have a couple of choices.

1.) Go through the data and search for "Standard" data and move it to it's
desired location, then get the "Qaulity Control" data and move it to it's
desired location, then get all other data.

2.) This would be to sort based on a custom list. Have a cutom list like
an array and loop through somehow??

I am not trying to get anyone to do this for me but I don't know what
approach to take...

Thanks for any input,
Mike

"Pete" wrote:

> Do you want a macro to do this (as you have posted in the programming
> forum), or do you want to do it manually ?
>
> Pete
>
>

Pete · 11-29-2005, 12:15 PM

You seem to have Calc. Conc twice in the headings - if this is correct
then you have 7 columns of data, which I assume will occupy columns A
to G, with the headings in row 1. In your example you have 3 values for
Sample Type, i.e. "Standard", "Quality Control" and "Unknown", and you
want to get rid of any rows which have RB or PD in column A.

If I was doing this manually I would enter the heading "Sort_Order" in
cell H1 and in H2 the formula:

= IF ( OR(LEFT(A2,2) = "RB", LEFT(A2,2) = "PD"), "Z", IF (G2 =
"Standard", "A", IF(G2 = "Quality Control", "B", "C")))

Copy this formula down and fix the values using Copy | Edit | Paste
Special etc. If you have more than 3 Sample Types, you might think
about a lookup table to give you the sort orders.

Then select the block of data including the headings and sort on
Sort_Order and Sample Name. Any rows with RB or PD in column A will
appear at the bottom of the data (sorted as "Z") and these can be
deleted - set up a filter on column H for "Z" and Edit | Delete Row,
then remove the filter. You can then delete column H.

Most of this can be done by recording a macro from the keyboard as you
do it, then tweaking it in the VBA editor.

You could then have a little routine in a loop which compares A2 with
A3, A3 with A4 etc and inserts a blank row where these are not the
same, continuing until you reach the bottom of the data.

Hope this helps.

Pete

Mike · 11-29-2005, 01:45 PM

Pete adding the sort order column was a great idea. I wish I had thought of
it! I was trying to do it based on a custom list. I would add the list but
my only reference to the list was by number and I couldn't count on everyone
using the macro having the same number of lists.

Thanks for you idea.
Mike

"Pete" wrote:

> You seem to have Calc. Conc twice in the headings - if this is correct
> then you have 7 columns of data, which I assume will occupy columns A
> to G, with the headings in row 1. In your example you have 3 values for
> Sample Type, i.e. "Standard", "Quality Control" and "Unknown", and you
> want to get rid of any rows which have RB or PD in column A.
>
> If I was doing this manually I would enter the heading "Sort_Order" in
> cell H1 and in H2 the formula:
>
> = IF ( OR(LEFT(A2,2) = "RB", LEFT(A2,2) = "PD"), "Z", IF (G2 =
> "Standard", "A", IF(G2 = "Quality Control", "B", "C")))
>
> Copy this formula down and fix the values using Copy | Edit | Paste
> Special etc. If you have more than 3 Sample Types, you might think
> about a lookup table to give you the sort orders.
>
> Then select the block of data including the headings and sort on
> Sort_Order and Sample Name. Any rows with RB or PD in column A will
> appear at the bottom of the data (sorted as "Z") and these can be
> deleted - set up a filter on column H for "Z" and Edit | Delete Row,
> then remove the filter. You can then delete column H.
>
> Most of this can be done by recording a macro from the keyboard as you
> do it, then tweaking it in the VBA editor.
>
> You could then have a little routine in a loop which compares A2 with
> A3, A3 with A4 etc and inserts a blank row where these are not the
> same, continuing until you reach the bottom of the data.
>
> Hope this helps.
>
> Pete
>
>