| Home |
| Search |
| Today's Posts |
|
#1
|
|||
|
|||
Re-arrangement
Hi,
I got an excel file from my client, but the problem is I need to re-arrange it in order to have a clearer view on the data receive. It consist of Hundred thousand of entries and it is every month the data will come in so that is why I need a permanent solution to this, pls help. The data come in as sort by brand but I need it to be sort by outlet so that I can better view on the individual outlet progress. It goes like this (pls select on the below and cut & paste it on a excel file to have a better view):- Period ending: Jan-05 Feb-05 Mar-05 Apr-05 May-05 Jun-05 Jul-05 --------------------------------------------------------------------------------------------------------------------------------------------------- Stock Item : xxxxxxxxxxxxxx Sales $ 0 0 0 0 0 0 0 Qnty 0 0 0 0 0 0 0 Stock Item : xxxxxxxxxxxxxx Barcode 50219124 Sales $ 0 0 0 0 0 0 0 Brands Namexxxxxxxx Qnty 0 0 0 0 0 0 0 Outletxxxx Sales $ 0 0 0 0 0 0 0 Qnty 0 0 0 0 0 0 0 Outletxxx Sales $ 0 0 0 0 0 0 0 Qnty 0 0 0 0 0 0 0 outletxxxx Sales $ 0 0 0 0 0 0 0 Stock Item : xxxxx xxxxxxxx Sales $ 601.83 710.74 1229.26 131.04 114.66 286.43 134.25 Qnty 81 93 150 16 14 32 15 Stock Item : xxxxxxxxxxxxxx Barcode 12300308793 Sales $ 601.83 710.74 1229.26 131.04 114.66 286.43 134.25 xxxxx sssssssssss 20 Qnty 81 93 150 16 14 32 15 Outletxxx Sales $ 0 0 0 0 0 0 0 Qnty 0 0 0 0 0 0 0 Outletxxx Sales $ 0 31.24 16.38 0 0 0 8.95 Qnty 0 4 2 0 0 0 1 328PANJANG Sales $ 0 0 8.19 0 0 0 0 Qnty 0 0 1 0 0 0 0 509SERANG Sales $ 7.43 0 0 0 0 0 0 Qnty 1 0 0 0 0 0 0 ALEXANDRA Sales $ 0 7.43 24.57 0 0 0 0 Qnty 0 1 3 0 0 0 0 ALJUNIED Sales $ 0 25.33 8.19 0 0 0 0 Qnty 0 3 1 0 0 0 0 ANCHORAGE Sales $ 0 7.43 8.19 0 0 0 0 Qnty 0 1 1 0 0 0 0 ANGMOKIO Sales $ 0 44.58 8.19 0 0 0 0 |
|
#2
|
|||
|
|||
|
Re-arrangement
Hi William,
Can't say that your example gave me a clear view of your problem. Perhaps less data carefully chosen would have worked better. I will assume that each item appears on it's own row. You can sort your columns in any order you want up to three columns in a sort (Data, Sort). To sort on more than 3 you would sort the groups of minors before groups of major sort items. http://www.mvps.org/dmcritchie/excel/sorting.htm You mention re-arrangement, if that means rearranging the order of columns you can grab a column demarcation below the column letters holding the shift key move it where you want. Look for the cursor to change to a pointer when it touches the line then you can move the column... http://www.mvps.org/dmcritchie/excel...and.htm#cursor -- --- HTH, David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001] My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm Search Page: http://www.mvps.org/dmcritchie/excel/search.htm "William" wrote in message ... Hi, I got an excel file from my client, but the problem is I need to re-arrange it in order to have a clearer view on the data receive. It consist of Hundred thousand of entries and it is every month the data will come in so that is why I need a permanent solution to this, pls help. The data come in as sort by brand but I need it to be sort by outlet so that I can better view on the individual outlet progress. It goes like this (pls select on the below and cut & paste it on a excel file to have a better view):- Period ending: Jan-05 Feb-05 Mar-05 Apr-05 May-05 Jun-05 Jul-05 ---------------------------------------------------------------------------------------------------------------------------------- ----------------- Stock Item : xxxxxxxxxxxxxx Sales $ 0 0 0 0 0 0 0 Qnty 0 0 0 0 0 0 0 Stock Item : xxxxxxxxxxxxxx Barcode 50219124 Sales $ 0 0 0 0 0 0 0 Brands Namexxxxxxxx Qnty 0 0 0 0 0 0 0 Outletxxxx Sales $ 0 0 0 0 0 0 0 Qnty 0 0 0 0 0 0 0 Outletxxx Sales $ 0 0 0 0 0 0 0 Qnty 0 0 0 0 0 0 0 outletxxxx Sales $ 0 0 0 0 0 0 0 Stock Item : xxxxx xxxxxxxx Sales $ 601.83 710.74 1229.26 131.04 114.66 286.43 134.25 Qnty 81 93 150 16 14 32 15 Stock Item : xxxxxxxxxxxxxx Barcode 12300308793 Sales $ 601.83 710.74 1229.26 131.04 114.66 286.43 134.25 xxxxx sssssssssss 20 Qnty 81 93 150 16 14 32 15 Outletxxx Sales $ 0 0 0 0 0 0 0 Qnty 0 0 0 0 0 0 0 Outletxxx Sales $ 0 31.24 16.38 0 0 0 8.95 Qnty 0 4 2 0 0 0 1 328PANJANG Sales $ 0 0 8.19 0 0 0 0 Qnty 0 0 1 0 0 0 0 509SERANG Sales $ 7.43 0 0 0 0 0 0 Qnty 1 0 0 0 0 0 0 ALEXANDRA Sales $ 0 7.43 24.57 0 0 0 0 Qnty 0 1 3 0 0 0 0 ALJUNIED Sales $ 0 25.33 8.19 0 0 0 0 Qnty 0 3 1 0 0 0 0 ANCHORAGE Sales $ 0 7.43 8.19 0 0 0 0 Qnty 0 1 1 0 0 0 0 ANGMOKIO Sales $ 0 44.58 8.19 0 0 0 0 |
|
#3
|
|||
|
|||
|
Re-arrangement
Hi David,
I have many brands in a single outlet, at the end of the month they will compile the whole month sales report in their HQ, as from what you had seen the cut & paste example. They had more than 100 outlets and I have about 15 brands selling with them. So I need to see the progress of the individual outlet, something thing like this:- Xxx outlet Stock Item: xxx FILTER Sales $ 0 0 0 0 0 0 0 Qnty 0 0 0 0 0 0 0 Stock Item : xxx FILTER Barcode 50219124 Sales $ 0 0 0 0 0 0 0 xxx FILTER 20' Qnty 0 0 0 0 0 0 0 Stock Item : BEN & HED Sales $ 12660.41 12922.31 12354.31 12731.57 12700.07 11987.36 12322.72 Qnty 1399 1373 1179 1215 1212 1144 1176 Stock Item : BEN & HED Barcode 50219209 Sales $ 12660.41 12922.31 12354.31 12731.57 12700.07 11987.36 12322.72 BEN & HED SP/F20 Qnty 1399 1373 1179 1215 1212 1144 1176 follow by another outlet... Rather than:- Stock Item : xxx FILTER Sales $ 0 0 0 0 0 0 0 Qnty 0 0 0 0 0 0 0 Stock Item : xxx FILTER Barcode 50219124 Sales $ 0 0 0 0 0 0 0 xxx FILTER Qnty 0 0 0 0 0 0 0 236WHITLEY Sales $ 0 0 0 0 0 0 0 Qnty 0 0 0 0 0 0 0 305OUTRAM Sales $ 0 0 0 0 0 0 0 Qnty 0 0 0 0 0 0 0 328PANJANG Sales $ 0 0 0 0 0 0 0 Qnty 0 0 0 0 0 0 0 509SERANG Sales $ 0 0 0 0 0 0 0 Stock Item : BEN & HED Sales $ 12660.41 12922.31 12354.31 12731.57 12700.07 11987.36 12322.72 Qnty 1399 1373 1179 1215 1212 1144 1176 Stock Item : BEN & HED Barcode 50219209 Sales $ 12660.41 12922.31 12354.31 12731.57 12700.07 11987.36 12322.72 BEN & HED Qnty 1399 1373 1179 1215 1212 1144 1176 236WHITLEY Sales $ 244.35 237.69 293.41 251.52 220.08 0 0 Qnty 27 25 28 24 21 0 0 305OUTRAM Sales $ 171.95 218.14 241 220.05 220.03 209.57 230.51 Qnty 19 23 23 21 21 20 22 328PANJANG Sales $ 144.8 82.88 146.7 157.19 199.08 293.39 282.89 Qnty 16 9 14 15 19 28 27 509SERANG Sales $ 271.5 167.19 178.14 272.46 209.6 125.76 125.76 Is it possible to write a macro to solve this problem? Cause the report will come in monthly. Sorry for the trouble and thank you very much for your time. Regards, William "David McRitchie" wrote: Hi William, Can't say that your example gave me a clear view of your problem. Perhaps less data carefully chosen would have worked better. I will assume that each item appears on it's own row. You can sort your columns in any order you want up to three columns in a sort (Data, Sort). To sort on more than 3 you would sort the groups of minors before groups of major sort items. http://www.mvps.org/dmcritchie/excel/sorting.htm You mention re-arrangement, if that means rearranging the order of columns you can grab a column demarcation below the column letters holding the shift key move it where you want. Look for the cursor to change to a pointer when it touches the line then you can move the column... http://www.mvps.org/dmcritchie/excel...and.htm#cursor -- --- HTH, David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001] My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm Search Page: http://www.mvps.org/dmcritchie/excel/search.htm "William" wrote in message ... Hi, I got an excel file from my client, but the problem is I need to re-arrange it in order to have a clearer view on the data receive. It consist of Hundred thousand of entries and it is every month the data will come in so that is why I need a permanent solution to this, pls help. The data come in as sort by brand but I need it to be sort by outlet so that I can better view on the individual outlet progress. It goes like this (pls select on the below and cut & paste it on a excel file to have a better view):- Period ending: Jan-05 Feb-05 Mar-05 Apr-05 May-05 Jun-05 Jul-05 ---------------------------------------------------------------------------------------------------------------------------------- ----------------- Stock Item : xxxxxxxxxxxxxx Sales $ 0 0 0 0 0 0 0 Qnty 0 0 0 0 0 0 0 Stock Item : xxxxxxxxxxxxxx Barcode 50219124 Sales $ 0 0 0 0 0 0 0 Brands Namexxxxxxxx Qnty 0 0 0 0 0 0 0 Outletxxxx Sales $ 0 0 0 0 0 0 0 Qnty 0 0 0 0 0 0 0 Outletxxx Sales $ 0 0 0 0 0 0 0 Qnty 0 0 0 0 0 0 0 outletxxxx Sales $ 0 0 0 0 0 0 0 Stock Item : xxxxx xxxxxxxx Sales $ 601.83 710.74 1229.26 131.04 114.66 286.43 134.25 Qnty 81 93 150 16 14 32 15 Stock Item : xxxxxxxxxxxxxx Barcode 12300308793 Sales $ 601.83 710.74 1229.26 131.04 114.66 286.43 134.25 xxxxx sssssssssss 20 Qnty 81 93 150 16 14 32 15 Outletxxx Sales $ 0 0 0 0 0 0 0 Qnty 0 0 0 0 0 0 0 Outletxxx Sales $ 0 31.24 16.38 0 0 0 8.95 Qnty 0 4 2 0 0 0 1 328PANJANG Sales $ 0 0 8.19 0 0 0 0 Qnty 0 0 1 0 0 0 0 509SERANG Sales $ 7.43 0 0 0 0 0 0 Qnty 1 0 0 0 0 0 0 ALEXANDRA Sales $ 0 7.43 24.57 0 0 0 0 Qnty 0 1 3 0 0 0 0 ALJUNIED Sales $ 0 25.33 8.19 0 0 0 0 Qnty 0 3 1 0 0 0 0 ANCHORAGE Sales $ 0 7.43 8.19 0 0 0 0 Qnty 0 1 1 0 0 0 0 ANGMOKIO Sales $ 0 44.58 8.19 0 0 0 0 |
|
#4
|
|||
|
|||
|
Re-arrangement
sorry wrong example given from my last message.
"David McRitchie" wrote: Hi William, Can't say that your example gave me a clear view of your problem. Perhaps less data carefully chosen would have worked better. I will assume that each item appears on it's own row. You can sort your columns in any order you want up to three columns in a sort (Data, Sort). To sort on more than 3 you would sort the groups of minors before groups of major sort items. http://www.mvps.org/dmcritchie/excel/sorting.htm You mention re-arrangement, if that means rearranging the order of columns you can grab a column demarcation below the column letters holding the shift key move it where you want. Look for the cursor to change to a pointer when it touches the line then you can move the column... http://www.mvps.org/dmcritchie/excel...and.htm#cursor -- --- HTH, David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001] My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm Search Page: http://www.mvps.org/dmcritchie/excel/search.htm "William" wrote in message ... Hi, I got an excel file from my client, but the problem is I need to re-arrange it in order to have a clearer view on the data receive. It consist of Hundred thousand of entries and it is every month the data will come in so that is why I need a permanent solution to this, pls help. The data come in as sort by brand but I need it to be sort by outlet so that I can better view on the individual outlet progress. It goes like this (pls select on the below and cut & paste it on a excel file to have a better view):- Period ending: Jan-05 Feb-05 Mar-05 Apr-05 May-05 Jun-05 Jul-05 ---------------------------------------------------------------------------------------------------------------------------------- ----------------- Stock Item : xxxxxxxxxxxxxx Sales $ 0 0 0 0 0 0 0 Qnty 0 0 0 0 0 0 0 Stock Item : xxxxxxxxxxxxxx Barcode 50219124 Sales $ 0 0 0 0 0 0 0 Brands Namexxxxxxxx Qnty 0 0 0 0 0 0 0 Outletxxxx Sales $ 0 0 0 0 0 0 0 Qnty 0 0 0 0 0 0 0 Outletxxx Sales $ 0 0 0 0 0 0 0 Qnty 0 0 0 0 0 0 0 outletxxxx Sales $ 0 0 0 0 0 0 0 Stock Item : xxxxx xxxxxxxx Sales $ 601.83 710.74 1229.26 131.04 114.66 286.43 134.25 Qnty 81 93 150 16 14 32 15 Stock Item : xxxxxxxxxxxxxx Barcode 12300308793 Sales $ 601.83 710.74 1229.26 131.04 114.66 286.43 134.25 xxxxx sssssssssss 20 Qnty 81 93 150 16 14 32 15 Outletxxx Sales $ 0 0 0 0 0 0 0 Qnty 0 0 0 0 0 0 0 Outletxxx Sales $ 0 31.24 16.38 0 0 0 8.95 Qnty 0 4 2 0 0 0 1 328PANJANG Sales $ 0 0 8.19 0 0 0 0 Qnty 0 0 1 0 0 0 0 509SERANG Sales $ 7.43 0 0 0 0 0 0 Qnty 1 0 0 0 0 0 0 ALEXANDRA Sales $ 0 7.43 24.57 0 0 0 0 Qnty 0 1 3 0 0 0 0 ALJUNIED Sales $ 0 25.33 8.19 0 0 0 0 Qnty 0 3 1 0 0 0 0 ANCHORAGE Sales $ 0 7.43 8.19 0 0 0 0 Qnty 0 1 1 0 0 0 0 ANGMOKIO Sales $ 0 44.58 8.19 0 0 0 0 |
|
#5
|
|||
|
|||
|
Re-arrangement
The data now is sort by total brand sales then follow by the break up from
individual outlet sales of that brand. Need it to be re-arrange it to be outlets sales of that brand, Cause there is many outlet selling that the same brand too. Example:- Stock Item : FILTER Sales $ 0 0 0 0 0 0 0 Qnty 0 0 0 0 0 0 0 Stock Item : FILTER Barcode 50219124 Sales $ 0 0 0 0 0 0 0 FILTER Qnty 0 0 0 0 0 0 0 xxxOutlet 1 Sales $ 0 0 0 0 0 0 0 Stock Item : BEN HED Sales $ 12660.41 12922.31 12354.31 12731.57 12700.07 11987.36 12322.72 Qnty 1399 1373 1179 1215 1212 1144 1176 Stock Item : BEN HED Barcode 50219209 Sales $ 12660.41 12922.31 12354.31 12731.57 12700.07 11987.36 12322.72 BEN HED Qnty 1399 1373 1179 1215 1212 1144 1176 xxxOutlet 1 Sales $ 244.35 237.69 293.41 251.52 220.08 0 0 "David McRitchie" wrote: Hi William, Can't say that your example gave me a clear view of your problem. Perhaps less data carefully chosen would have worked better. I will assume that each item appears on it's own row. You can sort your columns in any order you want up to three columns in a sort (Data, Sort). To sort on more than 3 you would sort the groups of minors before groups of major sort items. http://www.mvps.org/dmcritchie/excel/sorting.htm You mention re-arrangement, if that means rearranging the order of columns you can grab a column demarcation below the column letters holding the shift key move it where you want. Look for the cursor to change to a pointer when it touches the line then you can move the column... http://www.mvps.org/dmcritchie/excel...and.htm#cursor -- --- HTH, David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001] My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm Search Page: http://www.mvps.org/dmcritchie/excel/search.htm "William" wrote in message ... Hi, I got an excel file from my client, but the problem is I need to re-arrange it in order to have a clearer view on the data receive. It consist of Hundred thousand of entries and it is every month the data will come in so that is why I need a permanent solution to this, pls help. The data come in as sort by brand but I need it to be sort by outlet so that I can better view on the individual outlet progress. It goes like this (pls select on the below and cut & paste it on a excel file to have a better view):- Period ending: Jan-05 Feb-05 Mar-05 Apr-05 May-05 Jun-05 Jul-05 ---------------------------------------------------------------------------------------------------------------------------------- ----------------- Stock Item : xxxxxxxxxxxxxx Sales $ 0 0 0 0 0 0 0 Qnty 0 0 0 0 0 0 0 Stock Item : xxxxxxxxxxxxxx Barcode 50219124 Sales $ 0 0 0 0 0 0 0 Brands Namexxxxxxxx Qnty 0 0 0 0 0 0 0 Outletxxxx Sales $ 0 0 0 0 0 0 0 Qnty 0 0 0 0 0 0 0 Outletxxx Sales $ 0 0 0 0 0 0 0 Qnty 0 0 0 0 0 0 0 outletxxxx Sales $ 0 0 0 0 0 0 0 Stock Item : xxxxx xxxxxxxx Sales $ 601.83 710.74 1229.26 131.04 114.66 286.43 134.25 Qnty 81 93 150 16 14 32 15 Stock Item : xxxxxxxxxxxxxx Barcode 12300308793 Sales $ 601.83 710.74 1229.26 131.04 114.66 286.43 134.25 xxxxx sssssssssss 20 Qnty 81 93 150 16 14 32 15 Outletxxx Sales $ 0 0 0 0 0 0 0 Qnty 0 0 0 0 0 0 0 Outletxxx Sales $ 0 31.24 16.38 0 0 0 8.95 Qnty 0 4 2 0 0 0 1 328PANJANG Sales $ 0 0 8.19 0 0 0 0 Qnty 0 0 1 0 0 0 0 509SERANG Sales $ 7.43 0 0 0 0 0 0 Qnty 1 0 0 0 0 0 0 ALEXANDRA Sales $ 0 7.43 24.57 0 0 0 0 Qnty 0 1 3 0 0 0 0 ALJUNIED Sales $ 0 25.33 8.19 0 0 0 0 Qnty 0 3 1 0 0 0 0 ANCHORAGE Sales $ 0 7.43 8.19 0 0 0 0 Qnty 0 1 1 0 0 0 0 ANGMOKIO Sales $ 0 44.58 8.19 0 0 0 0 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Automatic label arrangement in a pie chart | Excel Discussion (Misc queries) | |||
| Tab Arrangement wizard | Excel Worksheet Functions | |||
Linear Mode
