Combin copy total 78 sets

Kishan · May 9, 2023

Using Excel 2000

Hi,

I got my data in the range A6:AX18 there are 13 lines “row6 to row18” 24 columns “A to AX”

I want to make copies of 13 rows of 2 lines sets using =COMBIN(13,2) = means total 78 combinations like copy line1&2, 1&3, 1&4, 1&5, 1&6 so on up to 78 combinations.

Copy start from row 25 and after each set need one blank space in-between.

Here is the example of copy starting 3 sets. line1&2, 1&3, 1&4,

NX.xls

A

B

C

D

E

F

G

H

I

J

K

L

M

N

O

P

Q

R

S

T

U

V

W

X

Y

Z

AA

AB

AC

AD

AE

AF

AG

AH

AI

AJ

AK

AL

AM

AN

AO

AP

AQ

AR

AS

AT

AU

AV

AW

AX

1

2

3

4

Serial Numbers

EM

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

17

18

19

20

21

22

23

24

25

26

27

28

29

30

31

32

33

34

35

36

37

38

39

40

41

42

43

44

45

46

47

48

5

Serial Numbers

EM

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

17

18

19

20

21

22

23

24

25

26

27

28

29

30

31

32

33

34

35

36

37

38

39

40

41

42

43

44

45

46

47

48

6

1

N.X

1

N.X

1

N.X

1

N.X

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

17

18

19

20

21

22

23

24

25

26

27

28

29

7

2

N.X

1

2

N.X

1

N.X

1

N.X

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

17

18

19

20

21

22

23

24

25

26

27

28

29

8

3

N.X

1

2

3

N.X

1

N.X

1

2

3

N.X

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

17

18

19

20

21

22

23

24

25

26

27

28

29

30

9

4

N.X

1

2

N.X

1

N.X

1

2

3

4

5

6

N.X

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

17

18

19

20

21

22

23

24

25

26

27

28

29

30

10

5

N.X

1

N.X

1

N.X

1

N.X

1

2

3

4

N.X

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

17

18

19

20

21

22

23

24

25

26

27

28

29

11

6

1

N.X

1

2

N.X

1

N.X

1

2

3

N.X

1

N.X

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

17

18

19

20

21

22

23

24

25

26

27

28

29

12

7

1

N.X

1

N.X

1

N.X

1

2

N.X

1

N.X

1

N.X

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

17

18

19

20

21

22

23

24

25

26

27

28

29

13

8

1

N.X

1

N.X

1

N.X

1

N.X

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

17

18

19

20

21

22

23

24

25

26

27

28

29

30

31

14

9

N.X

1

N.X

1

N.X

1

2

3

N.X

1

N.X

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

17

18

19

20

21

22

23

24

25

26

27

28

29

30

31

15

10

1

2

N.X

1

2

N.X

1

2

N.X

1

2

3

N.X

1

N.X

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

17

18

19

20

21

22

23

24

25

26

27

28

29

16

11

1

2

N.X

1

2

3

N.X

1

N.X

1

2

3

N.X

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

17

18

19

20

21

22

23

24

25

26

27

28

29

17

12

1

2

N.X

1

2

N.X

1

N.X

1

2

3

N.X

1

2

3

N.X

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

17

18

19

20

21

22

23

24

25

26

27

28

29

30

18

13

1

2

N.X

1

2

N.X

1

2

3

4

5

6

7

8

9

10

N.X

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

17

18

19

20

21

22

23

24

25

26

27

28

29

30

19

20

21

22

23

24

Serial Numbers

EM

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

17

18

19

20

21

22

23

24

25

26

27

28

29

30

31

32

33

34

35

36

37

38

39

40

41

42

43

44

45

46

47

48

25

1

N.X

1

N.X

1

N.X

1

N.X

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

17

18

19

20

21

22

23

24

25

26

27

28

29

26

2

N.X

1

2

N.X

1

N.X

1

N.X

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

17

18

19

20

21

22

23

24

25

26

27

28

29

27

28

1

N.X

1

N.X

1

N.X

1

N.X

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

17

18

19

20

21

22

23

24

25

26

27

28

29

3

N.X

1

2

3

N.X

1

N.X

1

2

3

N.X

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

17

18

19

20

21

22

23

24

25

26

27

28

29

30

31

1

N.X

1

N.X

1

N.X

1

N.X

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

17

18

19

20

21

22

23

24

25

26

27

28

29

32

4

N.X

1

2

N.X

1

N.X

1

2

3

4

5

6

N.X

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

17

18

19

20

21

22

23

24

25

26

27

28

29

30

Hoja1

Here is a total 78 COMBIN

NX.xls

A

B

C

D

1

2

3

4

Numbers

Pick

5

13

2

6

1

2

7

1

3

8

1

4

9

1

5

10

1

6

11

1

7

12

1

8

13

1

9

14

1

10

15

1

11

16

1

12

17

1

13

18

2

3

19

2

4

20

2

5

21

2

6

22

2

7

23

2

8

24

2

9

25

2

10

26

2

11

27

2

12

28

2

13

29

3

4

30

3

5

31

3

6

32

3

7

33

3

8

34

3

9

35

3

10

36

3

11

37

3

12

38

3

13

39

4

5

40

4

6

41

4

7

42

4

8

43

4

9

44

4

10

45

4

11

46

4

12

47

4

13

48

5

6

49

5

7

50

5

8

51

5

9

52

5

10

53

5

11

54

5

12

55

5

13

56

6

7

57

6

8

58

6

9

59

6

10

60

6

11

61

6

12

62

6

13

63

7

8

64

7

9

65

7

10

66

7

11

67

7

12

68

7

13

69

8

9

70

8

10

71

8

11

72

8

12

73

8

13

74

9

10

75

9

11

76

9

12

77

9

13

78

10

11

79

10

12

80

10

13

81

11

12

82

11

13

83

12

13

COMBIN

Thank you in advance

Regards,
Kishan

DanteAmor · May 9, 2023

Hello @Kishan, thanks for posting on MrExcel.

Try this macro:

VBA Code:

Sub combin_13_2()
  Dim a As Variant, b As Variant
  Dim i As Long, j As Long, k As Long, m As Long, n As Long
  
  a = Range("A6:AX18").Value
  n = WorksheetFunction.Combin(13, 2) * 3
  ReDim b(1 To n, 1 To UBound(a, 2))
  
  For i = 1 To UBound(a, 1)
    For j = i + 1 To UBound(a, 1)
      m = m + 1
      For k = 1 To UBound(a, 2)
        b(m, k) = a(i, k)
        b(m + 1, k) = a(j, k)
      Next
      m = m + 2
    Next
  Next
  
  Range("A25").Resize(UBound(b, 1), UBound(b, 2)).Value = b
End Sub

--------------
Let me know the result and I'll get back to you as soon as I can.
Sincerely
Dante Amor
--------------

Kishan · May 10, 2023

DanteAmor said:

Hello @Kishan, thanks for posting on MrExcel.

Try this macro:

VBA Code:

Sub combin_13_2()
  Dim a As Variant, b As Variant
  Dim i As Long, j As Long, k As Long, m As Long, n As Long
 
  a = Range("A6:AX18").Value
  n = WorksheetFunction.Combin(13, 2) * 3
  ReDim b(1 To n, 1 To UBound(a, 2))
 
  For i = 1 To UBound(a, 1)
    For j = i + 1 To UBound(a, 1)
      m = m + 1
      For k = 1 To UBound(a, 2)
        b(m, k) = a(i, k)
        b(m + 1, k) = a(j, k)
      Next
      m = m + 2
    Next
  Next
 
  Range("A25").Resize(UBound(b, 1), UBound(b, 2)).Value = b
End Sub

--------------
Let me know the result and I'll get back to you as soon as I can.
Sincerely
Dante Amor
--------------

Hello Dante Amor,

I am very glad to see your post and this make me so happy after trying it. Incredible it copies combination of 78 rows in milliseconds what a nice solution. I am glad and value your help.

I wish you a good luck.

Kind Regards,
Kishan

Combin copy total 78 sets

Kishan

Well-known Member

Excel Facts

DanteAmor

Well-known Member

Kishan

Well-known Member

Similar threads

Forum statistics

Share this page

Combin copy total 78 sets

Kishan

Well-known Member

Excel Facts

DanteAmor

Well-known Member

Kishan

Well-known Member

Similar threads

Forum statistics

Share this page

We've detected that you are using an adblocker.

Which adblocker are you using?

Disable AdBlock

Disable AdBlock Plus

Disable uBlock Origin

Disable uBlock