Sorting Data based on Colour and Placing them in Range

[haxmania1]

Hello Folks,

I wish to sort the Data and place them in range as mentioned below. Suppose for MRP ranging between 200 to 400 the Qty should be totalled and should be placed in new tab

Eg:

M.R.P. Qty
231.00 1.35
231.00 1.35
750.00 0.55
750.00 0.55
950.00 9.40
1024.00 3.60
2561.00 16.50
6000.00 26.95

Now i wish to club the Qty as per the below format
MRP QTY
200 - 400 2.70
400 - 600 0
600 - 800 .95
800 - 1000 9.40
1000 - 1500 3.60
1500 - 2500 0
2500 - 3500 16.50
3500 + 26.95

attached sheet for your reference.

[MarvinP]

Hi,

You can create a pivot table and group the rows by 200. See the attached. I hope you are using a newer version of Excel as the 2003 version may not do this correctly.

[haxmania1]

Its Perfect. thank you for your help. Can you tell me how do I change the 200 Range in Pivot Table? I tried with Pivot Table, but cannot change the range. Would be glad if you could help me out.

Regards
haxmania1

[MarvinP]

Hi,

Look under "Grouping Numbers" on this site:
http://www.contextures.com/xlPivot07.html#numbers

[haxmania1]

Hi,

Look under "Grouping Numbers" on this site:
http://www.contextures.com/xlPivot07.html#numbers

Range worked. But when I am trying to try with my Actual Data. I am not able to group the same. Getting the data like this when inserted pivot table. Please ponder some light on to it.

Sum of Qty
M.R.P. Total
231 1.350000024
247 61.15000039
303 8.050000001
348 1.450000048
374 0.150000006
388 1.550000049
395 122.9499987
409 1.850000024
413 0.400000006
418 1.949999988
421 9.000000007
424 1.350000024
426 6.75
431 75.89999996
435 1.150000025
443 4.400000095
450 6.750000209
453 5.749999929
459 3.250000089
462 1.649999976
465 6.949999809
467 1.299999952
473 12.39999962
493 0.100000001
505 0.300000012
509 30.69999981
510 3.700000048
517 2.400000006
534 0.050000001
535 5.699999809
552 5.599999905
562 42.69999981
563 8.900000192
585 22.4000001
589 2.849999905
594 21.44999993
595 7.699999809
596 1.799999952
617 11.5
623 13.75
629 0.050000001
638 47.70000076
639 32.00000024
651 0.550000012
652 2.349999905
667 4.300000191
683 1.950000025
690 2.650000095
697 82.89999962
708 22.85000038
712 56.5
719 31.5
726 26.35000038
734 128.500001
735 4.199999809
752 56.30000019
761 27.19999981
770 6.300000001
771 208.9499993
777 60.20000069
779 75.00000017
786 73.05000037
799 34.44999951
808 15
816 24
825 0.200000003
830 36.35000115
840 15
849 33.55000005
859 6.050000001
863 10.34999981
878 11.89999962
883 55.40000067
897 465.8500014
905 0.100000001
912 66.14999977
915 17.50000048
919 11.79999995
922 11.49999981
927 92.44999998
944 52.1500001
963 11.49999981
966 0.050000001
971 0.050000001
972 37.74999991
975 15.09999982
979 9.250000048
980 4.099999905
984 29.35000038
992 3
994 326.0000002
997 12.60000038
1011 12
1013 20.79999948
1016 38.40000019
1022 10.75
1023 24.55000019
1048 12.14999962
1058 24.19999981
1095 5.050000191
1100 2.849999953
1101 7.400000095
1117 22.59999991
1131 10.89999986
1146 13.69999981
1147 13.80000019
1160 16.94999981
1199 23.70000044
1204 0.200000003
1222 30
1249 12.00000024
1258 1.399999976
1260 2.700000048
1263 11.35000038
1266 12.69999981
1267 20.25000048
1275 5.5
1276 9.350000381
1283 30
1288 0.050000001
1308 6.099999905
1320 23.10000026
1321 15
1332 70.2000001
1335 20.10000019
1352 8.850000191
1358 9.649999619
1366 3.25
1380 0.300000012
1403 18.44999981
1418 12
1419 13.80000019
1435 13.10000014
1438 1.799999952
1443 5.400000095
1454 1.800000012
1473 20.79999924
1486 6.650000095
1496 6.199999809
1499 16.10000002
1514 12.5
1518 34.69999981
1533 57.29999924
1543 165.1999998
1552 0.200000007
1573 9.900000215
1600 102.8500004
1627 11.49999962
1643 21.30000019
1646 26.39999962
1686 7.949999809
1694 8.649999619
1699 23.10000038
1704 15
1735 1.450000048
1773 5.599999905
1774 10.19999981
1782 14.95000005
1791 17.1500001
1806 25.19999981
1813 187.6499995
1834 13.54999952
1907 11.80000019
1946 15
1965 10.35000014
2017 12.5
2021 13
2024 11.79999981
2025 0.949999988
2096 11.19999981
2098 24.89999962
2100 42.50000095
2137 10.65000038
2175 10
2184 29.94999981
2206 51.09999998
2236 10.64999962
2308 0.100000001
2309 17.89999981
2317 27
2333 10.60000038
2443 4
2450 14.14999986
2481 16.95000029
2490 51.3499999
2658 3.549999952
2721 21.64999962
2729 25.10000038
2935 17.99999981
2944 21.60000019
2946 6.950000286
3001 7.799999908
3074 5.800000191
3255 23.69999974
3313 16.25
3342 48.59999943
3417 48.4999994
3429 3.5
3464 26.14999981
3468 18.0999999
3512 13.94999976
3558 27.39999962
3660 21.15000019
3667 17.50000038
3776 11.75
3800 0.100000001
3808 30.80000067
3882 5.050000191
3991 0.100000001
3996 99.64999991
4018 20.75
4031 62.80000076
4038 13
4380 5.599999905
4542 19.5
4877 13
4917 9.300000191
5013 54.34999943
5320 9.300000191
5438 65.20000005
5521 15.85000038
5668 21.55000019
6145 9
8805 11.80000019
9155 26
10212 13
10430 22.89999962
11643 11.80000019
11805 12.64999962
(blank) 5667.600003
Grand Total 11335.20001

[MarvinP]

You need to remove the blank in Col A and the word Grand Total for this pivot to work correctly.

If you have a mix of numbers and text then the pivot table won't allow you to do a group of numbers as some of the data is text. I hope that makes sense. Simply delete the rows with a blank in it and the one that has the grand total in it and create a new pivot table.....