Re-arrangement

[Guest]

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]

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/fillhand.htm#cursor

--

 

[Guest]

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

 

[Guest]

sorry wrong example given from my last message.

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/fillhand.htm#cursor

 

[Guest]

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