Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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? |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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? |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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? |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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? |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Sorting Data | Excel Discussion (Misc queries) | |||
Sorting of data | Excel Worksheet Functions | |||
Sorting data to match existing data | Excel Discussion (Misc queries) | |||
colors of bar charted data don't follow data after sorting | Charts and Charting in Excel | |||
SORTING DATA | Excel Worksheet Functions |