Duplicate in different cells

[JAWS1]

I have a large spreadsheet with duplicate entries adjacent on 2 rows is there a formula for this they are marriages and need pairing up as you can see from the reference numbers on right side are the same that I have edited. They are in pairs with the same reference
[TABLE="width: 769"]
<colgroup><col><col><col><col><col span="3"><col><col><col></colgroup><tbody>[TR]
[TD="align: right"]14[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]1858[/TD]
[TD] GREEN[/TD]
[TD]William[/TD]
[TD]SHERRNGTON[/TD]
[TD]Jane[/TD]
[TD]C29[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]121[/TD]
[/TR]
[TR]
[TD="align: right"]14[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]1858 [/TD]
[TD]SHERRNGTON[/TD]
[TD]Jane[/TD]
[TD]GREEN[/TD]
[TD]William[/TD]
[TD]C29[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]121[/TD]
[/TR]
[TR]
[TD="align: right"]28[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]1858[/TD]
[TD] DARBYSHIRE[/TD]
[TD]Leigh[/TD]
[TD]ARMSTEAD[/TD]
[TD]Elizabeth[/TD]
[TD]C29[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]122[/TD]
[/TR]
[TR]
[TD="align: right"]28[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]1858[/TD]
[TD] ARMSTEAD[/TD]
[TD]Elizabeth[/TD]
[TD]DARBYSHIRE[/TD]
[TD]Leigh[/TD]
[TD]C29[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]122[/TD]
[/TR]
[TR]
[TD="align: right"]8[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]1858[/TD]
[TD]GREGORY[/TD]
[TD]William[/TD]
[TD][/TD]
[TD][/TD]
[TD]C29[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]123[/TD]
[/TR]
[TR]
[TD="align: right"]8[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]1858[/TD]
[TD]DEAN[/TD]
[TD]Jane[/TD]
[TD][/TD]
[TD][/TD]
[TD]C29[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]123[/TD]
[/TR]
[TR]
[TD="align: right"]15[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]1858[/TD]
[TD]SMITH[/TD]
[TD]Peter[/TD]
[TD][/TD]
[TD][/TD]
[TD]C29[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]125[/TD]
[/TR]
[TR]
[TD="align: right"]15[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]1858[/TD]
[TD]PARKINSON[/TD]
[TD]Elizabeth[/TD]
[TD][/TD]
[TD][/TD]
[TD]C29[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]125[/TD]
[/TR]
[TR]
[TD="align: right"]20[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]1858[/TD]
[TD]BENNISON[/TD]
[TD]William[/TD]
[TD][/TD]
[TD][/TD]
[TD]C29[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]126[/TD]
[/TR]
[TR]
[TD="align: right"]20[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]1858[/TD]
[TD]THOMPSON[/TD]
[TD]Ann[/TD]
[TD][/TD]
[TD][/TD]
[TD]C29[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]126[/TD]
[/TR]
[TR]
[TD="align: right"]29[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]1858[/TD]
[TD]WOOD[/TD]
[TD]James[/TD]
[TD][/TD]
[TD][/TD]
[TD]C29[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]127[/TD]
[/TR]
[TR]
[TD="align: right"]29[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]1858[/TD]
[TD]LEIGH[/TD]
[TD]Margaret[/TD]
[TD][/TD]
[TD][/TD]
[TD]C29[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]127[/TD]
[/TR]
[TR]
[TD="align: right"]3[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]1858[/TD]
[TD]KENYON[/TD]
[TD]Ralph[/TD]
[TD][/TD]
[TD][/TD]
[TD]C29[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]128[/TD]
[/TR]
[TR]
[TD="align: right"]3[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]1858[/TD]
[TD]HUNTER[/TD]
[TD]Ellen[/TD]
[TD][/TD]
[TD][/TD]
[TD]C29[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]128[/TD]
[/TR]
[TR]
[TD="align: right"]4[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]1858[/TD]
[TD]ASHTON[/TD]
[TD]Joseph[/TD]
[TD][/TD]
[TD][/TD]
[TD]C29[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]129[/TD]
[/TR]
[TR]
[TD="align: right"]4[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]1858[/TD]
[TD]GREGORY[/TD]
[TD]Jane[/TD]
[TD][/TD]
[TD][/TD]
[TD]C29[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]129[/TD]
[/TR]
</tbody>[/TABLE]

 

[estevaoba]

Considering your list starts from an even row, try this in the first cell next to William GREEN.
Copy across and down.

[TABLE="width: 64"]
<tbody>[TR]
  [TD="width: 64"]=IF(ISEVEN(ROW()),OFFSET(E2,1,0),OFFSET(E2,-1,0))[/TD]
[/TR]
</tbody>[/TABLE]

 

[BrianJN1]

I can offer you either a set of formulae which need to be selected as a block and then dragged down the two columns, or you could run a macro which I've supplied below.

Excel 2013/2016
	A	B	C	D	E	F	G	H	I	J
1	14	2	1858	GREEN	William	SHERRNGTON	Jane	C29	1	121
2	14	2	1858	SHERRNGTON	Jane	GREEN	William	C29	1	121
3	28	2	1858	DARBYSHIRE	Leigh	ARMSTEAD	Elizabeth	C29	1	122
4	28	2	1858	ARMSTEAD	Elizabeth	DARBYSHIRE	Leigh	C29	1	122
5	8	3	1858	GREGORY	William	DEAN	Jane	C29	1	123
6	8	3	1858	DEAN	Jane	GREGORY	William	C29	1	123
7	15	3	1858	SMITH	Peter	PARKINSON	Elizabeth	C29	1	125
8	15	3	1858	PARKINSON	Elizabeth	SMITH	Peter	C29	1	125
9	20	3	1858	BENNISON	William	THOMPSON	Ann	C29	1	126
10	20	3	1858	THOMPSON	Ann	BENNISON	William	C29	1	126
11	29	3	1858	WOOD	James	LEIGH	Margaret	C29	1	127
12	29	3	1858	LEIGH	Margaret	WOOD	James	C29	1	127
13	3	4	1858	KENYON	Ralph	HUNTER	Ellen	C29	1	128
14	3	4	1858	HUNTER	Ellen	KENYON	Ralph	C29	1	128
15	4	4	1858	ASHTON	Joseph	GREGORY	Jane	C29	1	129
16	4	4	1858	GREGORY	Jane	ASHTON	Joseph	C29	1	129
Sheet1

Cell Formulas
Range	Formula
F1	=D2
F2	=D1
G1	=E2
G2	=E1

Sub MarriagePairs()
Dim lastrow As Long
lastrow = Cells(Rows.Count, "A").End(xlUp).Row
Dim a As Long
For a = 1 To lastrow Step 2
Range("D" & a + 1 & ":E" & a + 1).Copy
Range("F" & a).PasteSpecial
Range("D" & a & ":E" & a).Copy
Range("F" & a + 1).PasteSpecial
Next
End Sub

 

[JAWS1]

thank you all for suggestions

 

[JAWS1]

Thanks the macro worked perfectly it did 13,000 entries in less than 3 minutes thank you .you are a star.

 

[BrianJN1]

Thank you, you're welcome.

Three minutes? I had no idea of timing.

Upon reflection I should have included :
Application.Screenupdating=false
Application.Calculation= xlCalculationManual
as the first two lines
and
Application.Screenupdating=True
Application.Calculation= xlCalculationAutomatic
as the last two.

That would have made the run somewhat faster.

 

[JAWS1]

Not bothered about the timing only too glad that I have a macro to sort the indexes out have done another 100,000 since thank you very much made an old man happy.

 

[BrianJN1]

Great. If it so reusable I should apply for a patent before the corporations get it!!