Re-arrangement

William · 10-16-2005, 11:05 PM

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

David McRitchie · 10-17-2005, 12:05 PM

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" <[email protected]> wrote in message news:[email protected]...
> 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
>
>

William · 10-26-2005, 04:05 AM

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" <[email protected]> wrote in message news:[email protected]...
> > 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
> >
> >
>
>
>

William · 10-26-2005, 04:05 AM

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" <[email protected]> wrote in message news:[email protected]...
> > 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
> >
> >
>
>
>

William · 10-26-2005, 05:05 AM

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" <[email protected]> wrote in message news:[email protected]...
> > 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
> >
> >
>
>
>