Need help sorting data
 

I have a list of 16,0000 recipients each of them has products from 1-41
unfortunately the data provided doesnt put the products in each column
properly. Although I have columns 1 to 41, recipients who dont have certain
products screw it up, one may have products 1,3,7,9,16,22,23,24,41, another
may have all of them or just one or two, I need to find a way to get the data
to sort under the proper column, so that product 3 is under column 3 and so
on. right now the products have no names just 1 to 41, is this possible?

Need help sorting data
 

You'll need to provide more information. What, exactly, does the data look
like, and how, exactly, do you want it stored?

Regards,
Fred.

"Yookaroo" wrote in message
...
I have a list of 16,0000 recipients each of them has products from 1-41
unfortunately the data provided doesnt put the products in each column
properly. Although I have columns 1 to 41, recipients who dont have
certain
products screw it up, one may have products 1,3,7,9,16,22,23,24,41,
another
may have all of them or just one or two, I need to find a way to get the
data
to sort under the proper column, so that product 3 is under column 3 and
so
on. right now the products have no names just 1 to 41, is this possible?

Need help sorting data
 

here is a snippet from my csv file

Sort_order,Bag
Bun,DMC,findernumber,salutation,firstname,lastname ,address1,address2,city,province,postcode,New
Postal
Code,language,specialty,product_01,product_02,prod uct_03,product_04,product_05,product_06,product_07 ,product_08,product_09,product_10,product_11,produ ct_12,product_13,product_14,product_15,product_16, product_17,product_18,product_19,product_20,produc t_21,product_22,product_23,product_24,product_25,p roduct_26,product_27,product_28,product_29,product _30,product_31,product_32,product_33,product_34,pr oduct_35,product_36,product_37,product_38,product_ 39,product_40,product_41
E-000197,1/1,1(V),346036,DR.,WALTER,KUTCHER,BARRIE GI ASSOCIATES,301-5
QUARRY RIDGE RD,BARRIE,ON,L4M7G1,L4M
7G1,E,60,1,2,4,5,6,7,8,9,14,19,22,23,24,25,26,28,2 9,30,31,32,41,,,,,,,,,,,,,,,,,,,,
E-000200,1/1,1(V),508094,DR.,MURALI,KRISHNAN,ROYAL COURT MED CENTRE,203-1
QUARRY RIDGE RD,BARRIE,ON,L4M7G1,L4M
7G1,E,60,1,2,4,5,6,7,8,9,14,19,22,23,24,25,26,28,2 9,30,31,32,41,,,,,,,,,,,,,,,,,,,,
E-000207,1/1,1(V),543230,DR.,RIMA,PETRONIENE,BARRIE GI ASSOCIATES,301-5
QUARRY RIDGE RD,BARRIE,ON,L4M7G1,L4M
7G1,E,60,1,2,4,5,6,7,8,9,14,19,22,23,24,25,26,28,2 9,30,31,32,41,,,,,,,,,,,,,,,,,,,,
E-000242,1/1,1(V),645584,DR.,USHA,KRISHNAN,308-5 QUARRY RIDGE
RD,,BARRIE,ON,L4M7G1,L4M
7G1,E,60,1,2,4,5,6,7,8,9,14,19,22,23,24,25,26,28,2 9,30,31,32,41,,,,,,,,,,,,,,,,,,,,
E-001159,1/1,1(V),321031,DR.,MARIANNE,BELAU,ROYAL COURT MED CENTRE,204-5
QUARRY RIDGE RD,BARRIE,ON,L4M7G1,L4M
7G1,E,18,1,2,3,4,5,6,7,8,9,13,14,15,16,19,20,21,22 ,23,24,25,26,27,28,29,30,31,32,33,34,35,36,37,38,4 1,,,,,,,
E-001850,1/1,1(V),261838,DR.,GEOFFREY,BOND,5 QUARRY RIDGE ROAD,SUITE
305,BARRIE,ON,L4M7G1,L4M
7G1,E,1,1,2,3,4,5,6,7,8,9,13,14,19,21,27,28,29,30, 31,32,33,34,35,36,37,38,41,,,,,,,,,,,,,,,
E-002737,1/2,1(V),261260,DR.,PIROSKA,FEJES,ROYAL COURT MED CENTRE,204-5
QUARRY RIDGE RD,BARRIE,ON,L4M7G1,L4M 7G1,E,

I need to find a way to make all products match up to a column so that
column 3 only has product 3 in it, column 4 only has product 4 in it etc...
I need to do this without losing any numbers so if product 5 is in column 3 I
need it shifted over to column 5?

Any help is most appreciated.



"Fred Smith" wrote:

You'll need to provide more information. What, exactly, does the data look
like, and how, exactly, do you want it stored?

Regards,
Fred.

"Yookaroo" wrote in message
...
I have a list of 16,0000 recipients each of them has products from 1-41
unfortunately the data provided doesnt put the products in each column
properly. Although I have columns 1 to 41, recipients who dont have
certain
products screw it up, one may have products 1,3,7,9,16,22,23,24,41,
another
may have all of them or just one or two, I need to find a way to get the
data
to sort under the proper column, so that product 3 is under column 3 and
so
on. right now the products have no names just 1 to 41, is this possible?

Need help sorting data
 

Hi,

Are you really telling us that the data is entered in on cell as
7G1,E,60,1,2,4,5,6,7,8,9,14,19,22,23,24,25,
or are you saying that each of these is in a separate column? If you
already have the data in different columns with just numbers to indicate the
products there is no sort command to automatically insert blanks cells
between missing item.
Instead I would set up a 41 column area to the right of your data with the
numbers 1 to 41 on row 1. Lets say this is starts in BB1.
Enter the following formula in BB2:

=IF(OR($G1:$AU1=BB$1),BB$1,"")

This assumes that the product numbers appear in G1:AU1 if all 41 are there.
Copy this formula down and to the right as needed.

--
If this helps, please click the Yes button.

Cheers,
Shane Devenshire


"Yookaroo" wrote:

here is a snippet from my csv file

Sort_order,Bag
Bun,DMC,findernumber,salutation,firstname,lastname ,address1,address2,city,province,postcode,New
Postal
Code,language,specialty,product_01,product_02,prod uct_03,product_04,product_05,product_06,product_07 ,product_08,product_09,product_10,product_11,produ ct_12,product_13,product_14,product_15,product_16, product_17,product_18,product_19,product_20,produc t_21,product_22,product_23,product_24,product_25,p roduct_26,product_27,product_28,product_29,product _30,product_31,product_32,product_33,product_34,pr oduct_35,product_36,product_37,product_38,product_ 39,product_40,product_41
E-000197,1/1,1(V),346036,DR.,WALTER,KUTCHER,BARRIE GI ASSOCIATES,301-5
QUARRY RIDGE RD,BARRIE,ON,L4M7G1,L4M
7G1,E,60,1,2,4,5,6,7,8,9,14,19,22,23,24,25,26,28,2 9,30,31,32,41,,,,,,,,,,,,,,,,,,,,
E-000200,1/1,1(V),508094,DR.,MURALI,KRISHNAN,ROYAL COURT MED CENTRE,203-1
QUARRY RIDGE RD,BARRIE,ON,L4M7G1,L4M
7G1,E,60,1,2,4,5,6,7,8,9,14,19,22,23,24,25,26,28,2 9,30,31,32,41,,,,,,,,,,,,,,,,,,,,
E-000207,1/1,1(V),543230,DR.,RIMA,PETRONIENE,BARRIE GI ASSOCIATES,301-5
QUARRY RIDGE RD,BARRIE,ON,L4M7G1,L4M
7G1,E,60,1,2,4,5,6,7,8,9,14,19,22,23,24,25,26,28,2 9,30,31,32,41,,,,,,,,,,,,,,,,,,,,
E-000242,1/1,1(V),645584,DR.,USHA,KRISHNAN,308-5 QUARRY RIDGE
RD,,BARRIE,ON,L4M7G1,L4M
7G1,E,60,1,2,4,5,6,7,8,9,14,19,22,23,24,25,26,28,2 9,30,31,32,41,,,,,,,,,,,,,,,,,,,,
E-001159,1/1,1(V),321031,DR.,MARIANNE,BELAU,ROYAL COURT MED CENTRE,204-5
QUARRY RIDGE RD,BARRIE,ON,L4M7G1,L4M
7G1,E,18,1,2,3,4,5,6,7,8,9,13,14,15,16,19,20,21,22 ,23,24,25,26,27,28,29,30,31,32,33,34,35,36,37,38,4 1,,,,,,,
E-001850,1/1,1(V),261838,DR.,GEOFFREY,BOND,5 QUARRY RIDGE ROAD,SUITE
305,BARRIE,ON,L4M7G1,L4M
7G1,E,1,1,2,3,4,5,6,7,8,9,13,14,19,21,27,28,29,30, 31,32,33,34,35,36,37,38,41,,,,,,,,,,,,,,,
E-002737,1/2,1(V),261260,DR.,PIROSKA,FEJES,ROYAL COURT MED CENTRE,204-5
QUARRY RIDGE RD,BARRIE,ON,L4M7G1,L4M 7G1,E,

I need to find a way to make all products match up to a column so that
column 3 only has product 3 in it, column 4 only has product 4 in it etc...
I need to do this without losing any numbers so if product 5 is in column 3 I
need it shifted over to column 5?

Any help is most appreciated.



"Fred Smith" wrote:

You'll need to provide more information. What, exactly, does the data look
like, and how, exactly, do you want it stored?

Regards,
Fred.

"Yookaroo" wrote in message
...
I have a list of 16,0000 recipients each of them has products from 1-41
unfortunately the data provided doesnt put the products in each column
properly. Although I have columns 1 to 41, recipients who dont have
certain
products screw it up, one may have products 1,3,7,9,16,22,23,24,41,
another
may have all of them or just one or two, I need to find a way to get the
data
to sort under the proper column, so that product 3 is under column 3 and
so
on. right now the products have no names just 1 to 41, is this possible?

Need help sorting data
 

The Sinppet I pasted in my second post is a csv snippet. All the numbers
appear one each in the 41 columns, they are in order across them however they
don't line up to the proper column, just paste the piece my sample and you
will see what I mean as I have included the header as well.



"Shane Devenshire" wrote:

Hi,

Are you really telling us that the data is entered in on cell as
7G1,E,60,1,2,4,5,6,7,8,9,14,19,22,23,24,25,
or are you saying that each of these is in a separate column? If you
already have the data in different columns with just numbers to indicate the
products there is no sort command to automatically insert blanks cells
between missing item.
Instead I would set up a 41 column area to the right of your data with the
numbers 1 to 41 on row 1. Lets say this is starts in BB1.
Enter the following formula in BB2:

=IF(OR($G1:$AU1=BB$1),BB$1,"")

This assumes that the product numbers appear in G1:AU1 if all 41 are there.
Copy this formula down and to the right as needed.

--
If this helps, please click the Yes button.

Cheers,
Shane Devenshire


"Yookaroo" wrote:

here is a snippet from my csv file

Sort_order,Bag
Bun,DMC,findernumber,salutation,firstname,lastname ,address1,address2,city,province,postcode,New
Postal
Code,language,specialty,product_01,product_02,prod uct_03,product_04,product_05,product_06,product_07 ,product_08,product_09,product_10,product_11,produ ct_12,product_13,product_14,product_15,product_16, product_17,product_18,product_19,product_20,produc t_21,product_22,product_23,product_24,product_25,p roduct_26,product_27,product_28,product_29,product _30,product_31,product_32,product_33,product_34,pr oduct_35,product_36,product_37,product_38,product_ 39,product_40,product_41
E-000197,1/1,1(V),346036,DR.,WALTER,KUTCHER,BARRIE GI ASSOCIATES,301-5
QUARRY RIDGE RD,BARRIE,ON,L4M7G1,L4M
7G1,E,60,1,2,4,5,6,7,8,9,14,19,22,23,24,25,26,28,2 9,30,31,32,41,,,,,,,,,,,,,,,,,,,,
E-000200,1/1,1(V),508094,DR.,MURALI,KRISHNAN,ROYAL COURT MED CENTRE,203-1
QUARRY RIDGE RD,BARRIE,ON,L4M7G1,L4M
7G1,E,60,1,2,4,5,6,7,8,9,14,19,22,23,24,25,26,28,2 9,30,31,32,41,,,,,,,,,,,,,,,,,,,,
E-000207,1/1,1(V),543230,DR.,RIMA,PETRONIENE,BARRIE GI ASSOCIATES,301-5
QUARRY RIDGE RD,BARRIE,ON,L4M7G1,L4M
7G1,E,60,1,2,4,5,6,7,8,9,14,19,22,23,24,25,26,28,2 9,30,31,32,41,,,,,,,,,,,,,,,,,,,,
E-000242,1/1,1(V),645584,DR.,USHA,KRISHNAN,308-5 QUARRY RIDGE
RD,,BARRIE,ON,L4M7G1,L4M
7G1,E,60,1,2,4,5,6,7,8,9,14,19,22,23,24,25,26,28,2 9,30,31,32,41,,,,,,,,,,,,,,,,,,,,
E-001159,1/1,1(V),321031,DR.,MARIANNE,BELAU,ROYAL COURT MED CENTRE,204-5
QUARRY RIDGE RD,BARRIE,ON,L4M7G1,L4M
7G1,E,18,1,2,3,4,5,6,7,8,9,13,14,15,16,19,20,21,22 ,23,24,25,26,27,28,29,30,31,32,33,34,35,36,37,38,4 1,,,,,,,
E-001850,1/1,1(V),261838,DR.,GEOFFREY,BOND,5 QUARRY RIDGE ROAD,SUITE
305,BARRIE,ON,L4M7G1,L4M
7G1,E,1,1,2,3,4,5,6,7,8,9,13,14,19,21,27,28,29,30, 31,32,33,34,35,36,37,38,41,,,,,,,,,,,,,,,
E-002737,1/2,1(V),261260,DR.,PIROSKA,FEJES,ROYAL COURT MED CENTRE,204-5
QUARRY RIDGE RD,BARRIE,ON,L4M7G1,L4M 7G1,E,

I need to find a way to make all products match up to a column so that
column 3 only has product 3 in it, column 4 only has product 4 in it etc...
I need to do this without losing any numbers so if product 5 is in column 3 I
need it shifted over to column 5?

Any help is most appreciated.



"Fred Smith" wrote:

You'll need to provide more information. What, exactly, does the data look
like, and how, exactly, do you want it stored?

Regards,
Fred.

"Yookaroo" wrote in message
...
I have a list of 16,0000 recipients each of them has products from 1-41
unfortunately the data provided doesnt put the products in each column
properly. Although I have columns 1 to 41, recipients who dont have
certain
products screw it up, one may have products 1,3,7,9,16,22,23,24,41,
another
may have all of them or just one or two, I need to find a way to get the
data
to sort under the proper column, so that product 3 is under column 3 and
so
on. right now the products have no names just 1 to 41, is this possible?