earthworm
Well-known Member
- Joined
- May 19, 2009
- Messages
- 773
- Office Version
- 2019
- 2016
- Platform
- Windows
I have a below sample data set
I need to split data based on below table in seperate worksheet and save on PC Desktop
Example Apply Filter on Column A1 and select value based on Range G1 and then select the first 5 rows of the filtered data , copy and paste the data in new worksheet along with header and save it on desktop. then again select data of filtered data in Column A1 and select the from 6th rosw of remaining data till 5 data and save second sheet and lastly select last value of A1 and save it in seperate worksheet . There will be 3 files containing all A1 data. and move to next value I.E A2 and so on.
Testing.xlsb | |||||||
---|---|---|---|---|---|---|---|
A | B | C | D | E | |||
1 | S.no | A | B | C | D | ||
2 | A1 | 783 | 873 | 724 | 745 | ||
3 | A1 | 867 | 796 | 903 | 796 | ||
4 | A1 | 993 | 780 | 713 | 737 | ||
5 | A1 | 701 | 859 | 909 | 850 | ||
6 | A1 | 829 | 918 | 745 | 750 | ||
7 | A1 | 812 | 850 | 874 | 906 | ||
8 | A1 | 880 | 783 | 769 | 919 | ||
9 | A1 | 734 | 837 | 876 | 944 | ||
10 | A1 | 888 | 732 | 864 | 749 | ||
11 | A1 | 942 | 773 | 711 | 864 | ||
12 | A1 | 920 | 758 | 771 | 825 | ||
13 | A2 | 964 | 754 | 912 | 964 | ||
14 | A2 | 839 | 727 | 872 | 895 | ||
15 | A2 | 926 | 834 | 730 | 811 | ||
16 | A2 | 792 | 793 | 767 | 938 | ||
17 | A2 | 892 | 857 | 950 | 902 | ||
18 | A2 | 982 | 979 | 866 | 973 | ||
19 | A3 | 765 | 880 | 789 | 777 | ||
20 | A3 | 918 | 883 | 789 | 956 | ||
21 | A3 | 795 | 739 | 735 | 747 | ||
22 | A3 | 724 | 807 | 747 | 718 | ||
23 | A3 | 976 | 943 | 864 | 752 | ||
24 | A3 | 983 | 769 | 848 | 992 | ||
25 | A3 | 855 | 717 | 954 | 890 | ||
26 | A3 | 989 | 917 | 873 | 849 | ||
27 | A3 | 862 | 792 | 956 | 954 | ||
28 | A4 | 908 | 865 | 848 | 780 | ||
29 | A4 | 911 | 740 | 952 | 701 | ||
30 | A4 | 851 | 999 | 786 | 736 | ||
31 | A4 | 868 | 987 | 794 | 857 | ||
32 | A4 | 805 | 888 | 725 | 835 | ||
33 | A4 | 788 | 763 | 951 | 798 | ||
34 | A4 | 901 | 912 | 855 | 932 | ||
35 | A4 | 757 | 736 | 761 | 937 | ||
36 | A4 | 708 | 989 | 965 | 903 | ||
37 | A4 | 782 | 769 | 802 | 877 | ||
38 | A4 | 864 | 990 | 829 | 721 | ||
39 | A4 | 790 | 918 | 971 | 843 | ||
40 | A4 | 866 | 892 | 817 | 711 | ||
41 | A4 | 790 | 786 | 718 | 818 | ||
42 | A4 | 928 | 978 | 798 | 786 | ||
43 | A5 | 980 | 746 | 751 | 832 | ||
44 | A5 | 962 | 928 | 847 | 982 | ||
45 | A5 | 890 | 838 | 704 | 825 | ||
46 | A5 | 821 | 924 | 792 | 877 | ||
47 | A5 | 989 | 730 | 798 | 956 | ||
48 | A5 | 913 | 957 | 991 | 777 | ||
49 | A5 | 899 | 880 | 812 | 901 | ||
50 | A5 | 988 | 999 | 863 | 881 | ||
51 | A5 | 842 | 765 | 767 | 799 | ||
52 | A5 | 908 | 747 | 999 | 933 | ||
53 | A5 | 914 | 967 | 897 | 781 | ||
54 | A5 | 733 | 726 | 918 | 771 | ||
55 | A5 | 969 | 870 | 929 | 943 | ||
56 | A5 | 853 | 831 | 905 | 987 | ||
57 | A5 | 895 | 794 | 707 | 715 | ||
58 | A5 | 975 | 803 | 935 | 790 | ||
59 | A5 | 813 | 836 | 764 | 708 | ||
60 | A5 | 847 | 946 | 788 | 990 | ||
61 | A5 | 990 | 823 | 723 | 753 | ||
62 | A5 | 711 | 874 | 708 | 960 | ||
63 | A5 | 755 | 747 | 905 | 830 | ||
64 | A5 | 878 | 831 | 849 | 832 | ||
65 | A5 | 733 | 957 | 994 | 704 | ||
66 | A5 | 869 | 894 | 966 | 804 | ||
67 | A5 | 989 | 853 | 951 | 848 | ||
68 | A5 | 982 | 839 | 973 | 750 | ||
69 | A5 | 714 | 763 | 968 | 868 | ||
70 | A5 | 967 | 823 | 834 | 860 | ||
Sheet1 |
I need to split data based on below table in seperate worksheet and save on PC Desktop
Testing.xlsb | ||||||
---|---|---|---|---|---|---|
G | H | I | J | |||
1 | Total Count | Data Count | SplitSize | Number of Files | ||
2 | A1 | 11 | 5 | 3 | ||
3 | A2 | 6 | 5 | 2 | ||
4 | A3 | 9 | 5 | 2 | ||
5 | A4 | 15 | 5 | 3 | ||
6 | A5 | 28 | 5 | 6 | ||
Sheet1 |
Cell Formulas | ||
---|---|---|
Range | Formula | |
H2:H6 | H2 | =COUNTIF(A:A,G2) |
J2:J6 | J2 | =CEILING(H2/I2,1) |
Named Ranges | ||
---|---|---|
Name | Refers To | Cells |
_FilterDatabase | =Sheet1!$A$1:$E$70 | H2:H6 |
Example Apply Filter on Column A1 and select value based on Range G1 and then select the first 5 rows of the filtered data , copy and paste the data in new worksheet along with header and save it on desktop. then again select data of filtered data in Column A1 and select the from 6th rosw of remaining data till 5 data and save second sheet and lastly select last value of A1 and save it in seperate worksheet . There will be 3 files containing all A1 data. and move to next value I.E A2 and so on.