Using VBA to find duplicate or non-duplicate rows (common or non-common) in sheet 1 and move to sheet 2 in a row

[bahrami]

Hello, please check my file if possible. Thank you for your efforts.

Example file-01.xlsm
	A	B	C	D	E	F	G	H	I
1	Row	National Code	name	last name	Union name	Board code	Board code	Side	Election date
2	1	181-842934-9	Sasan	Bahrami	Tools Union	first round	1	First Vice President	1391/03/27
3	2	181-842934-9	Sasan	Bahrami	Tools Union	second period	2	Second Vice President	1395/02/26
4	3	181-842934-9	Sasan	Bahrami	Tools Union	second period	4	Secretary	1399/02/26
5	4	181-987654-3	Amir	Bahrami	Electric Trade Union	Fourth period	6	Inspector	1400/02/30
Sheet1

Example file-01.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L	M	N
1	Row	National Code	name	last name	Union name	Side of the first period	Date of the first period	Side of the second period	Date of the first period	Side of the Thirdperiod	Date of the Thirdperiod	Side of the Fourth period	Date of the Fourth period	Number of company courses in the board of directors
2	1	181-842934-9	Sasan	Bahrami	Tools Union	First Vice President	1391/03/27	Second Vice President	1395/02/26	Secretary	1399/02/26	0	0	3
3	2	181-987654-3	Amir	Bahrami	Electric Trade Union	0	0	0	0	0	0	Inspector	1400/02/30	1
4
Sheet2

 

[bebo021999]

Option Explicit
Sub test()
Dim Lr&, k&, i&, cell As Range, s As String, key
Dim dic As Object
Set dic = CreateObject("Scripting.dictionary")
With Worksheets("Sheet1")
    Lr = .Cells(Rows.Count, "B").End(xlUp).Row
    For Each cell In .Range("B2:B" & Lr)
        If Not dic.exists(cell.Value) Then
            s = cell.Offset(0, 1) & "|" & cell.Offset(0, 2) & "|" & cell.Offset(0, 3) & "|" & cell.Offset(0, 6) & "|" & cell.Offset(0, 7)
            dic.Add cell.Value, s
        Else
            s = s & "|" & cell.Offset(0, 6) & "|" & cell.Offset(0, 7)
            dic(cell.Value) = s
        End If
    Next
End With
With Worksheets("Sheet2")
    .Range("A1:G1").Value = Array("Row", "National Code", "Name", "Last name", "Union name", "Side Election Date", "Election Date")
    For Each key In dic.keys
        k = k + 1
        .Cells(k + 1, 1).Value = k
        .Cells(k + 1, 2).Value = key
        For i = 0 To UBound(Split(dic(key), "|"))
            If i >= 5 Then
                If i Mod 2 > 0 Then
                    .Cells(1, i + 3).Value = "Side Election Date"
                Else
                    .Cells(1, i + 3).Value = "Election Date"
                End If
            End If
            .Cells(k + 1, i + 3).Value = Split(dic(key), "|")(i)
        Next
    Next
End With
End Sub

Before code running:

Book1
	A	B	C	D	E	F	G	H	I
1	Row	National Code	name	last name	Union name	Board code	Board code	Side	Election date
2	1	1818429349	Sasan	Bahrami	Tools Union	first round	1	First Vice President	1391/03/27
3	2	1818429349	Sasan	Bahrami	Tools Union	second period	2	Second Vice President	1395/02/26
4	3	1818429349	Sasan	Bahrami	Tools Union	second period	4	Secretary	1399/02/26
5	4	1819876543	Amir	Bahrami	Electric Trade Union	Fourth period	6	Inspector	1400/02/30
Sheet1

After running code:

Book1
	A	B	C	D	E	F	G	H	I	J	K
1	Row	National Code	Name	Last name	Union name	Side Election Date	Election Date	Side Election Date	Election Date	Side Election Date	Election Date
2	1	1818429349	Sasan	Bahrami	Tools Union	First Vice President	1391/03/27	Second Vice President	1395/02/26	Secretary	1399/02/26
3	2	1819876543	Amir	Bahrami	Electric Trade Union	Inspector	1400/02/30
Sheet2

 

[bahrami]

Hello for your efforts, thank you for this

If the values of the election period are replaced in the specified columns and the number of participants in the elections is added in the last column. It will be great. Thank you again

 

[bahrami]

Hello, considering that the national code is transferred non-duplicate in Sheet 2, but the names (first and last name) are duplicated in Sheet 2.Thank you for your help