Rearrange filtering data by "same number" in all the 5 positions

[motilulla]

Hello,

Sheet1... has data sample as data received in original format.

I want to have data filtered by the "same number" in all the 5 positions as shown in the Sheet2 leaving 1 space after each of the next number (minimum from 1 to maximum 29 numbers)

For a more detail 1 image is attached showing "sheet1 original sample data". And 2nd image is showing filtering data by "same number" in all the 5 positions.

Pease suggest VBA solution

Sheet1... data sample as data received in original format.

*	A	B	C	D	E	F	G	H	I	J
1
2
3
4
5				n1	n2	n3	n4	n5
6				1	2	3	4	48
7				1	3	4	5	34
8				1	4	5	6	28
9				1	4	7	10	46
10				2	7	8	10	47
11				2	7	8	10	47
12				1	2	4	11	44
13				5	8	10	13	31
14				1	3	6	18	24
15				3	5	13	18	39
16				1	6	7	18	28
17				1	4	10	19	23
18				4	8	11	19	46
19				2	8	10	19	46
20				1	8	18	19	39
21				1	2	6	20	36
22				5	7	18	20	30
23				1	2	5	21	32
24				1	2	5	21	32
25				5	6	16	23	27
26				9	16	18	23	32
27				13	18	20	23	30
28				1	4	21	24	46
29				17	18	19	24	40
30				5	7	21	25	37
31				6	9	10	26	49
32				2	11	13	26	50
33				1	4	18	27	42
34				2	5	21	27	42
35				6	9	10	27	35
36				3	18	22	27	32
37				1	11	22	28	44
38				1	5	23	29	32
39				6	16	18	29	38
40				1	2	15	30	35
41				5	14	28	30	47
42				1	3	21	32	39
43				1	10	12	32	33
44				2	20	24	32	46
45				6	7	12	33	39
46				2	3	33	34	42
47				2	10	18	34	35
48				3	10	26	35	44
49				7	18	30	37	39
50				2	3	13	38	42
51				1	3	4	38	40
52				3	16	34	38	48
53				12	18	35	38	45
54				18	20	35	38	48
55				18	21	29	38	41
56				18	31	32	38	48
57				1	9	18	39	48
58				15	18	29	39	41
59				9	18	30	39	45
60				2	11	18	40	46
61				4	20	36	40	41
62				2	23	28	40	43
63				6	11	35	41	44
64				8	18	22	41	42
65				18	23	37	41	42
66				1	2	3	42	48
67				1	10	23	42	46
68				9	10	19	42	49
69				16	18	28	42	49
70				2	19	28	42	49
71				1	20	41	42	50
72				18	23	27	42	44
73				2	3	25	43	48
74				1	2	26	44	50
75				3	4	8	44	50
76				3	7	25	45	50
77				6	10	21	45	49
78				6	10	21	45	49
79				6	13	22	45	49
80				10	29	30	45	49
81				4	12	25	46	48
82				10	28	40	47	48
83				2	24	31	48	50
84
85
86
87

Sheet2...Result Required data filtered by the "same number" in all the 5 positions

*	A	B	C	D	E	F	G	H	I	J
1
2
3
4
5				n1	n2	n3	n4	n5
6				1	4	10	19	23
7				1	3	21	32	39
8				1	11	22	28	44
9				1	10	23	42	46
10				1	20	41	42	50
11				1	2	15	30	35
12
13				2	7	8	10	47
14				2	23	28	40	43
15				2	24	31	48	50
16				2	19	28	42	49
17				1	2	26	44	50
18				2	20	24	32	46
19				1	2	6	20	36
20				2	11	13	26	50
21				1	2	5	21	32
22
23				3	7	25	45	50
24				1	3	6	18	24
25				3	4	8	44	50
26				3	16	34	38	48
27				2	3	25	43	48
28				2	3	13	38	42
29				1	2	3	42	48
30				2	3	33	34	42
31				3	10	26	35	44
32
33				4	8	11	19	46
34				1	4	21	24	46
35				1	3	4	38	40
36				4	12	25	46	48
37				1	2	3	4	48
38				1	2	4	11	44
39				4	20	36	40	41
40
41				1	5	23	29	32
42				5	8	10	13	31
43				5	14	28	30	47
44				5	7	21	25	37
45				1	2	5	21	32
46				2	5	21	27	42
47				1	3	4	5	34
48
49				6	10	21	45	49
50				5	6	16	23	27
51				6	11	35	41	44
52				6	9	10	27	35
53				1	4	5	6	28
54				6	7	12	33	39
55				6	13	22	45	49
56
57				6	10	21	45	49
58				2	7	8	10	47
59				10	28	40	47	48
60				1	10	12	32	33
61				6	9	10	26	49
62				10	29	30	45	49
63				2	8	10	19	46
64				9	10	19	42	49
65				1	4	7	10	46
66
67				15	18	29	39	41
68				1	9	18	39	48
69				1	8	18	19	39
70				17	18	19	24	40
71				18	21	29	38	41
72				18	23	27	42	44
73				3	5	13	18	39
74				2	11	18	40	46
75				8	18	22	41	42
76				16	18	28	42	49
77				18	31	32	38	48
78				9	16	18	23	32
79				13	18	20	23	30
80				1	4	18	27	42
81				3	18	22	27	32
82				7	18	30	37	39
83				5	7	18	20	30
84				18	23	37	41	42
85				2	10	18	34	35
86				18	20	35	38	48
87				9	18	30	39	45
88				6	16	18	29	38
89				1	6	7	18	28
90				12	18	35	38	45
91
92
93

Thank you all.

I am using Excel 2000

Regards,
Moti

 

[motilulla]

Questions:
What do you mean by "filter"? I don't see any rows is being filtered.

@Akuini, True there is non-filter I don't know how to apply what I want in the post#1, sheet1 example there are 5 numbers in each row n1, n2, n3, n4 and n5

First step filter those rows have that has "number 1" in any of 5 position and copy them in sheet2 as shown in the sheet2 result (off course as all the numbers are in ascending order "number 1" will be find in only 1st position....

Now take example of "number 10" which can find in the any of 5 position from n1 to n5 filter all rows that has number 10 in any position and copy them in sheet2 under the previous numbers..... in this way I want to be filtered and copied all rows separately from number 1 to 29... hope this makes it more cleared.

Questions:
What do you mean by "same number" & "5 position"? How do you determine which one is the "same number"?

I mean it any number that fined in any row within n1 to n5... like 10 in n1, 10 in n2, 10 in n3, 10 in n4 or 10 in n5 is a same number but in different position among n1 to n5.

Hope this helps

Kind Regards,
Moti

 

[Akuini]

Still not clear to me.
In sheet2 (the result), it looks like you arrange data by groups (and separate them by a blank row), for example the first group, you highlight number "1" with red. Why the other rows that has 1 in it doesn't belong to the first group? for example row 17, 19, etc.

 

[motilulla]

Still not clear to me.
In sheet2 (the result), it looks like you arrange data by groups (and separate them by a blank row), for example the first group, you highlight number "1" with red. Why the other rows that has 1 in it doesn't belong to the first group? for example row 17, 19, etc.

@Akuini, that is exactly I want to arrange data by groups... your observation is spot on it is my big mistake copying and pasting rows manually creating an example sheet2 which is not correct I am sorry for the inconvenience I am sure you got my point view perfectly ok please help with macro to accomplish this task automatically by grouping them 1 to 29 separately giving one line space between each of the group.

Thank you in advance

Kind Regards,
Moti

 

[Akuini]

it is my big mistake copying and pasting rows manually creating an example sheet2 which is not correct

So what the correct result should look like?

 

[motilulla]

So what the correct result should look like?

@Akuini, ok here is a sheet3 with original data and sheet4 expected result filtered by group in this example there is shown only with grouped by 1, 2 and 10 but I need from 1 to 29 to be sorted.

Sheet3 Orignal Data

*	A	B	C	D	E	F	G	H	I	J
1
2
3
4
5				n1	n2	n3	n4	n5
6				1	2	3	4	48
7				1	3	4	5	34
8				1	4	5	6	28
9				1	4	7	10	46
10				2	7	8	10	47
11				2	7	8	10	47
12				1	2	4	11	44
13				5	8	10	13	31
14				1	3	6	18	24
15				3	5	13	18	39
16				1	6	7	18	28
17				1	4	10	19	23
18				4	8	11	19	46
19				2	8	10	19	46
20				1	8	18	19	39
21				1	2	6	20	36
22				5	7	18	20	30
23				1	2	5	21	32
24				1	2	5	21	32
25				5	6	16	23	27
26				9	16	18	23	32
27				13	18	20	23	30
28				1	4	21	24	46
29				17	18	19	24	40
30				5	7	21	25	37
31				6	9	10	26	49
32				2	11	13	26	50
33				1	4	18	27	42
34				2	5	21	27	42
35				6	9	10	27	35
36				3	18	22	27	32
37				1	11	22	28	44
38				1	5	23	29	32
39				6	16	18	29	38
40				1	2	15	30	35
41				5	14	28	30	47
42				1	3	21	32	39
43				1	10	12	32	33
44				2	20	24	32	46
45				6	7	12	33	39
46				2	3	33	34	42
47				2	10	18	34	35
48				3	10	26	35	44
49				7	18	30	37	39
50				2	3	13	38	42
51				1	3	4	38	40
52				3	16	34	38	48
53				12	18	35	38	45
54				18	20	35	38	48
55				18	21	29	38	41
56				18	31	32	38	48
57				1	9	18	39	48
58				15	18	29	39	41
59				9	18	30	39	45
60				2	11	18	40	46
61				4	20	36	40	41
62				2	23	28	40	43
63				6	11	35	41	44
64				8	18	22	41	42
65				18	23	37	41	42
66				1	2	3	42	48
67				1	10	23	42	46
68				9	10	19	42	49
69				16	18	28	42	49
70				2	19	28	42	49
71				1	20	41	42	50
72				18	23	27	42	44
73				2	3	25	43	48
74				1	2	26	44	50
75				3	4	8	44	50
76				3	7	25	45	50
77				6	10	21	45	49
78				6	10	21	45	49
79				6	13	22	45	49
80				10	29	30	45	49
81				4	12	25	46	48
82				10	28	40	47	48
83				2	24	31	48	50
84
85
86
87

Sheet4 Filter Data By Groups

*	A	B	C	D	E	F	G	H	I	J
1
2
3
4
5				n1	n2	n3	n4	n5
6				1	2	3	4	48
7				1	3	4	5	34
8				1	4	5	6	28
9				1	4	7	10	46
10				1	2	4	11	44
11				1	3	6	18	24
12				1	6	7	18	28
13				1	4	10	19	23
14				1	8	18	19	39
15				1	2	6	20	36
16				1	2	5	21	32
17				1	2	5	21	32
18				1	4	21	24	46
19				1	4	18	27	42
20				1	11	22	28	44
21				1	5	23	29	32
22				1	2	15	30	35
23				1	3	21	32	39
24				1	10	12	32	33
25				1	3	4	38	40
26				1	9	18	39	48
27				1	2	3	42	48
28				1	10	23	42	46
29				1	20	41	42	50
30				1	2	26	44	50
31
32				1	2	3	4	48
33				2	7	8	10	47
34				2	7	8	10	47
35				1	2	4	11	44
36				2	8	10	19	46
37				1	2	6	20	36
38				1	2	5	21	32
39				1	2	5	21	32
40				2	11	13	26	50
41				2	5	21	27	42
42				1	2	15	30	35
43				2	20	24	32	46
44				2	3	33	34	42
45				2	10	18	34	35
46				2	3	13	38	42
47				2	11	18	40	46
48				2	23	28	40	43
49				1	2	3	42	48
50				2	19	28	42	49
51				2	3	25	43	48
52				1	2	26	44	50
53				2	24	31	48	50
54
55				1	4	7	10	46
56				2	7	8	10	47
57				2	7	8	10	47
58				5	8	10	13	31
59				1	4	10	19	23
60				2	8	10	19	46
61				6	9	10	26	49
62				6	9	10	27	35
63				1	10	12	32	33
64				2	10	18	34	35
65				3	10	26	35	44
66				1	10	23	42	46
67				9	10	19	42	49
68				6	10	21	45	49
69				6	10	21	45	49
70				10	29	30	45	49
71				10	28	40	47	48
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87

Thank you in advance

Kind Regards,
Moti

 

[Akuini]

Try this:
I put the result in col L.

Sub motilulla()
Dim i As Long, j As Long, k As Long, n As Long, g As Long
Dim va, vb
Dim d As Object

n = Range("E" & Rows.Count).End(xlUp).Row
va = Range("E6:I" & n)
ReDim vb(1 To UBound(va, 1) * 10, 1 To 6)


For k = 1 To 29
    
    For i = 1 To UBound(va, 1)
            For j = 1 To 5
                If va(i, j) = k Then
                    g = g + 1
                    For w = 1 To 5
                        vb(g, w) = va(i, w)
                    Next
                    vb(g, 6) = k
                    
                    Exit For
                End If
            Next
        
    Next
    g = g + 1
Next

Range("L6").Resize(g, 6) = vb

End Sub

 

[motilulla]

Try this:
I put the result in col L.

Sub motilulla()
Dim i As Long, j As Long, k As Long, n As Long, g As Long
Dim va, vb
Dim d As Object

n = Range("E" & Rows.Count).End(xlUp).Row
va = Range("E6:I" & n)
ReDim vb(1 To UBound(va, 1) * 10, 1 To 6)


For k = 1 To 29
   
    For i = 1 To UBound(va, 1)
            For j = 1 To 5
                If va(i, j) = k Then
                    g = g + 1
                    For w = 1 To 5
                        vb(g, w) = va(i, w)
                    Next
                    vb(g, 6) = k
                   
                    Exit For
                End If
            Next
       
    Next
    g = g + 1
Next

Range("L6").Resize(g, 6) = vb

End Sub

Akuini, this is it absolutely amazing! You nail my viewpoint also I like the end column you added who is showing which number of group is sorted out.

I am very happy to have a solved my query more best then as I required.

Thank you so much for your interest and time you spent to solve it.

Good Luck and happy New Year 2022

Kind Regards,
Moti

 

[Akuini]

You're welcome, glad to help & thanks for the feedback.